Recently on holiday on the NSW Central Coast I needed a few bits and pieces so I ventured in to the local department store. 

I don’t have the shopping gene so I seldom shop and then only when I really need something. So OK, that means I haven’t been to my share of shops but the place I went into seemed pretty amazing! 

It was absolutely full of stuff! You could barely get down the aisles but, along with the curios, there were some really handy things. Finding them was hit and miss and it certainly was for me a case of “better to arrive than journey hopefully”.

Recent styles were “cheek by jowl” with Dickensian artefacts… clothes, crockery, string, stationary most anything you might need. A bit of a “one stop shop” and the price was right! It reminded me a bit of when I used to go shopping with my Mum 35 years ago at the discount food store with everything everywhere. Talk about retail aversion therapy!

Being an engineer by background and more analytical than is good for me, I left the shop wondering if the store could survive as the overriding impression was that it had far too much stock. Simply and nostalgia aside, there seemed to be far too much money locked up in “them there” shelves. Surely there was a better way!

Retailers are faced with the difficult problem of trying to match the stock they hold against customer demand in an environment of continual change, often driven by seasonal demand and fashion. The trick is to carry just enough stock so that each customer can find what they want when they want it so you don’t lose a selling opportunity but not so much stock that it sits on the shelf until it is disposed of in next years sale.

One of the big issues is the “forest for the trees” problem. The buying habits of customers are diverse as they come in all shapes and sizes with different style and colour preferences. This means that there are an enormous number of combinations all being continually influenced by season and fashion. Retailers servicing multiple stores have this problem only magnified. With this amount of data it is easy to see how inventory managers have great difficulty seeing the “forest for the trees”.

Faced with an economic and competitive landscape demanding tighter margins for survival it is imperative that only sufficient stock is held to satisfy customer demand. Retailers can no longer afford to make their decisions at the class or category level. Just because there is a run on jeans in one locale it doesn’t mean that the size 12, female stonewashed is moving in all stores or at all.

In the world of retail, of stores and SKUs, there is a well known maxim “Retail is Detail” and we all know that the “devil is in the detail”.

Size 12, female stonewashed jeans may not have been sold last week but if buyers are planning at the total jeans level this will not be visible leading to wrong buying decisions and sub optimal stocking levels.

Thankfully the ever increasing power of computers and the application of NASA inspired techniques with fast and easy methods for seeing the data are making the buyer’s job easier and more accurate.

The power of the latest computers is making it possible to plan at the SKU store intersection and spot trends at the lowest level but this is still overwhelming from a inventory buying perspective.

To overcome this automated buying techniques are deployed based on optimal stock models. Algorithms that review the sales trends determine what the optimal stocking levels are for each SKU in each store and an inventory order is raised on this basis. 

All automation algorithms are not equal and some are clearly better than others in predicting future sales. At its simplest level the buying decision may be to replace the inventory from sales from the previous week. The difficulty with this approach is that it does not take into account the amount of stock on the shelf in the store, the changes in seasons, population demographics and fashions or unusual purchases. 

An unusual purchase may be a local mum buying a dozen pink shorts for her daughter’s Netball team when only 2 usually sell each week. Ideally the store would only hold 2 or 3 items (enough to allow for restocking lead times).

But how is it possible to forecast the correct stock levels when seasonal, fashion and unusual events are occurring? 

Over the years different techniques have been used such as Moving Average and Replacement but none of these do a good job in sorting out the “noise” from the “one off” exceptional sales like the pink shorts for the team or the cyclic run on pencils and pads prior to the return to school.

This is where a NASA mathematician comes to an unexpected rescue. Rudolf Kalman observed that he could apply his linear filtering technique that strips unwanted noise out of streams of data to the problem of trajectory estimation leading to its incorporation in the Apollo navigation computer. 

The Kalman filter as it is now known is widely used in navigational and guidance systems, radar tracking and satellite orbit determination as well as in econometrics. It has now been shown to be effective at eliminating retail “noise” even a run on pink shorts or stonewashed jeans enabling retailers to create better forecasts, hone their stock models and radically drive down inventory levels.

Through the correct application of these filters reductions in inventory of between 10% & 20% and improvements in stock turns of 10% are not uncommon. Shelf space and dollars freed from over stocking can be refocussed towards more profitable and faster moving items. 

This leads to potential savings from stock reductions and increases in revenue from related improvements in stock turn amounting to millions of dollars in even medium sized retailers.

So if next time you visit a store and everything is “just so” and you begin to yearn for the old cramped, nostalgic quaint experience that has “Gone with Gowings” remember NASA chose the stonewashed.

Business Intelligence has gathered increasing profile in recent years as both the IT and Finance functions have become aware of its potential to improve business.  

In this paper I profile typical BI scenarios focusing on the deployment challenges and outcomes and hopefully provoke providers to deliver BI solutions in under 45 days .

Introduction
 
The ongoing crisis in the financial world as we lurch from one bankruptcy to another leaves us with little room for joy and there seems, not surprisingly, like that old Chinese saying suggests, that we are living in “interesting” if not fraught times. 

Certainly the events of the last few years affect us as Finance people. Quite apart from the financial challenges we know from Enron that the Government response to AIG, Freddie and Fanny Mae and other banking failures was to legislate even more disclosure.

For any of us who have worked in the US or for a US subsidiary we know the impact of Sarbanes Oxley. It took the US business community some time to comply and my guess is our next “must do” BI project will be disclosure driven.

Agile and now Lean are the words for our times as we need to be responsive more than ever. We need a BI environment that allows us to adapt to the whirlwinds of change or risk failure.

It was Brutus in Shakespeare’s Julius Caesar, who observed,

There is a tide in the affairs of men. Which, taken at the flood, leads on to fortune;
Omitted, all the voyage of their life is bound in shallows and in miseries.
On such a full sea are we now afloat, and we must take the current when it serves,
Or lose our ventures.

With the financial news we receive every day you have got to think that we are on such a sea? The tide is going out and we had better get on board or risk getting stranded. 

I understand that it was George Bernard Shaw who observed that “We learn from history that we learn nothing from history”? 

Is that like saying “History repeats itself because no one was listening the first time”? This reasonably leads to the corollary “if we do not learn from the mistakes of history we are doomed to repeat them” which provides a useful segue to the topic of this white paper.

To us at Procuity, Business Intelligence gives visibility to our corporate history and we think that its effective use provides some of the tools required to avoid repeating past mistakes.

In this paper we provide you with an opportunity to leverage, hopefully to your advantage, the experience of some industry exemplars to grasp the “Art of the Possible”.

To this end we are going to reflect on some recent corporate BI scenarios which we hope are instructive.

Our goal is to persuade you that BI is essential although it did occur to us that in today’s climate the term Business Intelligence could perhaps be seen by some of the general public as an oxymoron. 

Our hope is that the Business Intelligence scenarios we review in this white paper might enhance your own opportunities for success by suggesting another set of possibilities. We are mindful that the best indicator of success is success itself, but failures can also be instructive so, first to some failures. 

International Foods Supplier

A few years ago I met with a large food manufacturer at the behest of the finance folk as they wanted to automate their monthly Sales and Operating Plan (SOP) process. The spreadsheet they were using at the time was over 7.5 MB as they had used every column and were challenging the 65k Excel 2003 row limitations. 

Conservatively based on the low end of commonly accepted per cell error rates of 1 in 10,000 we could be talking over 400 errors (on the high end 4,000). Obviously someone in planning had done their sums and hence the call.

While preparing to demonstration to them we spoke with one of the Management Accountants and asked curiously how responsive IT was to the Finance reporting needs. He said that he had had to wait up to 2 years to get reports that they wanted. This was clearly not “business intelligent”.

They were using a well-known ERP for inventory management and data wasn’t a problem so we were surprised he accepted those sorts of time horizons to get visibility into the business. 

He was a slave to another department’s priorities which were clearly out of sync with Finance priorities. 

Sadly the project was shelved as the IT Project Manager simply could not believe that it was possible to deliver the desired outcomes in the time frames that were our everyday experience – under 6 weeks. His experience of “the possible”, having worked for some large IT vendor was measured in months, not weeks.

To pick up on that it was recently stated that the CIO at Defence announced that he was establishing a new operating regime to deal with their vendors. 

He was frustrated by long lead times and failure to deliver and would now only give repeat business to vendors that could deliver outcomes in less than 90 days. He called it the “90 day drop”.

While not entirely clear what qualifies as a deliverable in this context but the scenarios speak of BI outcomes most of which were delivered in under half that time. We now have access to DIY BI technologies like OLAP that allow us to partner with IT for the infrastructure support but then push ahead and deliver BI outcomes ourselves in timeframes of less than half the 90 days. 

The challenge to us in the finance community is to specify what can be delivered in less than 90 days but typically in half that time. Let’s call it the “45 day drop”. Go for the quicker lower risk wins. We all know from experience the longer the project the higher the risk and much is achievable in 45 days with today’s DIY technologies. 

The purpose of our scenarios is to give you some sense of what is achievable in the 45 days. 

Firstly let us look at that fairly traditional use for BI in the monthly management accounts within a highly fractured geographically dispersed, poorly connected environment.

APAC Medical Supplier – Better Meds 

The finance function at a supplier of medical equipment and pharmaceuticals to the Asia Pacific region had the monthly challenge of producing a number of management report decks with over 100 pages of tables, commentary and charts with often multiple charts per page. 

The effort was hugely “handraulic”, retyping ledger reports, emails and performance estimates into spreadsheets and took over 3 weeks of effort resulting in many late nights for finance and unacceptable error rates. The senior executives were unaware of the staff effort required, nor perhaps grasped the potential business risk. 

Starting with the existing deck a data mart and related OLAP cubes were created that acted as a repository for all the reporting data. Some data was drawn from the key Ledgers and ERPs or from reports produced by these systems where we were unable or IT were unwilling for us to connect directly. 

The cubes were enabled for “writeback” so performance commentary and data currently entered into the old Excel spreadsheets, now connected to the Cubes could be written back into a robust data store.

With Excel attached to the underlying data it safely became the report design tool leveraging existing competencies. A consistent “look and feel” was adopted across the deck rather than the lot looking like a ransom note. Critical data relationships such as Margins and Margin Percents, Actual to Budget and Actual to Forecast were codified into auditable rules and applied consistently across the data making reports consistent. Everything tied up and footed.

The team took every success they could get, a bit like chipping away at a prison wall. As early successes were achieved, more integration followed and finally the entire wall came down and the deck was automated. 

The total project took nearly a year to finally tie up all the loose ends, with one success at a time, but the actual effort required was still less than 90 days. One of the larger single projects.

FGA – 360 Degree Performance & Industry Reporting

A few years ago I met with a divisional Director in Canberra of a Federal Government Agency and we got talking about Business Intelligence. He went silent for a moment as if he was having some sort of epiphany.

He then went on to describe a Business Intelligence project he was dreaming about but was sure wasn’t possible. No one to date had even offered a solution. 

He was charged with collecting and disseminating industry intelligence around panel suppliers to the organisation. This involved among other things, reporting to the executive industry and a senate committee, regular 360o performance reviews with vendor actual and comparative performance period-on-period.

The post-review data was resident on an Access data base and with the application of over 70 different queries which applied complex weightings and CPI movements, manipulated data could be drawn into Excel so that multiple charts could be created ready for pasting into a Word document that would be PDF’ed and sent to the appropriate recipients.

This was a manual process with reports to go to over 100 industry recipients as well as 50 or so internal users of the consolidated and individual reports. It produced a print run of greater than 2,500 pages. Although having a similar look and feel each report was bespoke as every report had a different length as every supplier did different projects and it required page numbering and indexing. 

The estimated time required to complete the task manually exceeded the 6 months sampling window. The agency had a difficult problem as they had published their reporting intention to the industry community and their executive.

Fortunately we did not know the problem was perceived to be impossible to solve and so the consultant’s BI solution was to design OLAP cubes with the appropriate dimensions and apply the required statistical and weighting rules to the data. 

Mind you, we did a Proof of Concept to demonstrate to our client it was eminently doable which leads to another takeaway. If in doubt do a small Proof of Concept to prove the potential. Even $10k (unbelievable to those in the larger end of consulting town) can get you somewhere down the track to a production system and can greatly reduce the acquisition risk. Excel, connected to the underlying data using  was again used for report creation because of its enormous flexibility and charting capabilities. The effort for the job was again under 45 days and a previously impossible process can now be completed in hours from data acquisition to report creation. 

Technical and Further Education Provider - Student Reporting 

We had an opportunity to become involved with a TAFE that was keen to get real business insight into its students, the courses they took and the employers that sponsored apprentices into these courses.

“Money is too tight to mention” in the education sector as we know and success is predicated on how well you can match courses and students. This requires an understanding of student and employer demographics and to make it all work, automate the compliance reporting to ensure funding is secured.

Again an OLAP ETL was used to dynamically feed the key data from the Student Management System and Ledger to the cubes from which Excel again was used to create the required reporting and the dashboard that you see. 

Excel as we know is a great slave but a poor master but used as a window on a robust multi-dimensional data store its capacity as a report design tool, accessible by end users, is without peer.

Weighted contact hours and targets for government reporting, highly granular student demographics analysis, historical snapshots, and employer reporting are now easily available. 

The project was owned by the user, the Student Administrator took 30 days and replaced stand alone Excel and Crystal Student Reporting. We think that this is a great story and feel privileged to be part of it.

A National Hardware Supplier 

A large national hardware distributor was wrestling with doing the budgets for its stores. The challenge was how do you present the requirements of the business to people working on the shop floor simply and yet have the sort of controls you need to wrap around the process.

The solution again lay with cube technology because they not only wanted to capture the data but they wished to analyse the line and consolidated data “on the fly”. 

This requirement introduces the notion of real-time consolidation. Ensure when you deploy multi-dimensional technologies they cannot only “write-back” which is essential for budgeting and forecasting but they aggregate the data “on the fly”. 

Having to run a process that takes time to report the results is a total waste of finance time and sub-optimal. 

Avoid these technologies like the plague.

The input forms were initially designed in Excel by the finance folk and then using the CALUMO web publish functionality were published to the web where one of our consultants added some additional herbs and spices, the “Do not try this at home” sort of stuff. 

Of course you would not deliver Excel-based input templates to the stores as it is not the right tool.

The store guys are expert with questions about compost, paint and a kilogram of 25mm galvanised flathead nails but Excel is well out of their comfort zone and besides if they are a bit determined they can break it. 

Another take-away, Web is definitely the delivery mechanism to the non-finance user.  

Regional managers were now able to set budgets for each of their stores using the web and the cube write-back functionality which became immediately available to store managers. These are the guys that half an hour before were probably giving you advice on the latest cordless ½” hammer drill.

The managers could either agree the target or modify it to their expectation by major stock group. Once completed the Regional managers could approve or return it and the users were alerted by the system for the next step.

This project, which has been an enormous success, was delivered again in under 90 days but also included over 36 bespoke web-based reports and a bunch of other stuff supporting the process.

A State Rail Operator – Divisional and Corporate Reporting, Budgeting

This our penultimate story delivers a lot of hope for us finance people.

We responded to an RFT from a large rail operator and despite the resolute efforts of the IT Department to address their own needs we were selected by finance. This was one of those gratifying cases where Finance stayed resolute about addressing their problems and sense prevailed.

The initial project was to deliver detailed cost centre and consolidated reporting across the organisation, a process that hitherto was largely manual using a combination of ledger reports, Excel and Word. This organisation, mind you, was not trivial having over 5,000 staff and 5 operating divisions. 

Because of the contractual delay we were left with 3 weeks to deliver the project. 1.5 consultants were deployed. They connected to SAP BW and delivered the reports on time and budget. 4.5 weeks of effort. 

Once IT had provided finance with the OLAP foundation which allowed them to produce the monthly reports deck within days of month end instead of weeks, they found that the business divisions weren’t using these reports but rather using a combination of Excel and Word to produce Business Line based reports.

It is a truism that access to BI increases the demand for BI and so with a “good job lads” we now set about turning these reports on their side. This resulted in another 3 week engagement which saw the consultants map thousands of line items to an alternate hierarchy to produce Business Line reports saving man weeks of effort every month and importantly that balanced to the centre P&L’s. Same numbers, two views.

With this under the belt the next pain point was the time based Cross Charge mechanism. Within a further 3 weeks the existing process which took six days each month was automated to 1 hour and balanced “to the cent”.

Then came a request for multi-currency treasury reporting, of which the dashboard was an important element for the Treasurer and following that automating the annual budgets. 

With the budgets a further requirement was for users to be able to plan at the node or consolidated level but then prorate the budgeted number down to the centre account month detail based on prior year actuals as a basis for the monthly management reporting comparatives.

This was 5 large projects in less than 5 months confirmed the art of the possible with today’s OLAP technologies.

Sustainability Reporting 

Our last BI scenario is very topical. 

We have all noticed what seems like unusual changes to our weather patterns and have no doubt read or heard about this INCONVENIENT TRUTH called Climate Change.

It is in this context that we have been following what we see is the next big BI need, Sustainability Reporting. According to the Emissions Green Paper  our clients began collecting data for comparative sustainability reporting by July 2009. 

This is a compelling event as there are financial consequences for failing to comply. To that end we are working with a number of our clients developing a sustainability reporting platform, using OLAP Cubes, Excel and the Web.

We have produced necessarily an application that is very nimble allowing us to rapidly respond to the changes that will inevitably emerge. This solution was delivered in well under 6 weeks.

A number of our clients see this as providing them a prototype environment in the event they are able one day to capture this data in their ERP.

At the moment it is too costly to create additional fields in the ERP while the standards are still in a state of flux.

Conclusion 

To finish up, a few reflections on what things you should be looking for and what perhaps you should avoid.

Firstly 
Importantly, look for Quick Wins ie; around 45 days but not longer than 90 days ie; our scenarios demonstrate positive business outcomes from typically between $60k and $150k in services. Remember the longer the project the greater the risk of failure or budget blow out. If in doubt try a “Proof of Concept”.

Secondly 
The size of your business is a variable but not as significant as you may think. Computers can handle sales in billions as easily as millions particularly if you only pull monthly or weekly balance data for your first foray in to BI, ie; do not be persuaded that the time and cost should be significantly different as it will probably come down to the preparedness of IT and/or the systems vendor to let you connect to your ERP.

Thirdly
Do not get seduced into the thought that BI equals data warehouse. We hear it over and over again. “No we aren’t ready for BI as IT is building the data warehouse”, but you cannot just mothball the business till the IT guys are done, ie; this is not always a show stopper.

You do not have to have a big data warehouse to get into BI as in our experience data warehouses are usually created by IT folk who with the best intention in the world create a big store full of all sorts of stuff that you may never need or are not mature enough as an organisation to use. This leads to high cost, long time frames and high risk with a corresponding failure to deliver.

A better approach and in line with a lower risk, fast delivery, low cost strategy is to use information specific data marts ie; do a GL cube and get that BI sorted then move on to an HR cube or a Sales cube. Get your wins along the way. Gather in the benefits of the increased business visibility that BI gives early.

I think of it as being BI Agile. Recognise that you may need to iterate and this approach supports you changing your mind or the business responding to the market and you rapidly incrementing till you get a successful outcome.

Fourthly
Ensure the technology you adopt for BI has a multi-dimensional/OLAP capability, supports write-back, calculates on the fly, is fast, does immediate consolidations, integrates with Excel as an end user design tool and provides web publish functionality and seamlessly management of tablets and smart phones.

Go for as much DIY as you can as no one is as interested in your priorities as you.

So no excuses for failure, STORM the GATES – because in this financial climate your job may be on the line.

"Without Knowledge the People Fail"

Over three thousand years ago an ancient writer penned the proverb "Without a vision or knowledge the people fail" and so it is today that knowledge of a company’s profitability is the ultimate determinant of its success or failure. 

As crucial as this measure is, however, it remains a mystery to many management teams.

While most have a view of their profits at the corporate level and then aggregated by product group, line of business, geographic location, or customer segment, they still lack visibility to individual customer profitability. 

Customers are at the centre of every business and ensuring that each customer is as profitable as possible should be a priority.

Without this view and a useful knowledge of the drivers most affecting profitability, business executives must make crucial business decisions with a limited understanding of their expected financial impact.

Chief executives making strategic decisions about which products, sales channels, or existing customers to grow or divest need to know in advance how their choices will affect the bottom line.

This requires a detailed understanding of both current as well as future profitability. Unfortunately, senior management often receives conflicting customer value or profitability metrics from its marketing and finance teams. The disconnects in the marketing and finance data spring from shared challenges:

• no centralized framework to define and model customer profitability,
  
• limited detail behind the customer value metric, and 
  
• lack of a unified view of profitability tying together all elements such as product, customer, service, channel, and pricing data. 
  

These challenges obstruct the CFO organization’s ability to provide detailed, useful financial analysis of customer profitability that ties to the financial statements and can be integrated into daily business operations. Isolated efforts to allocate detail costs back to specific customers are so time-consuming, ad-hoc, and error-prone that their results aren’t suitable for presentation to general management, let alone appropriate for use when making major business decisions.

Attempts to partner with the IT organization and build customized profitability models often produce inflexible programs that require frequent, costly updates. While more accurate than spreadsheets, they are not at the level needed to yield meaningful analysis for customer focused decisions on segmentation, pricing, channel optimization, product bundles, etc.

In today’s dynamic, challenging business environment, these labour-intensive efforts are often abandoned in favour of more pressing matters. The result is a continued status quo in which management makes educated guesses about which customers are profitable. 

Considering that at least 80% of most companies’ profits come from just 20% of their customers, and that studies indicate that a mere 1% price increase improves an account’s profitability by 8%, uncertainty about customer profitability places management at a significant strategic disadvantage.

Essentials of Customer Profitability Management

Based on their prior experiences helping customers address these challenges, we have identified four critical components of customer profitability solutions:

• A centralized, interactive repository which delivers a consistent view of profitability across the enterprise.
  
• A framework for the business users to build and maintain the profitability model. This component performs the allocations and calculations consistently across the enterprise but is able to provide multi-dimensional views including customer, product, service, and line of business.
  
• A robust OLAP business intelligence platform that helps the business analyze and report on the profitability metrics and the supporting detail. The platform leverages the data from the centralized repository to provide dashboards, reporting, and analytics to users across the enterprise.
  
• Activity-based cost capabilities that can be added as the customer profitability methodology evolves

A Unified, Multiple-Dimension View of Profitability

OLAP offers a solution to manage a centralized and consistent, 360-degree view of customer profitability and its many dimensions.

These powerful, scalable and easy to use tools provide a way of defining, calculating, and managing customer profitability through dashboards, reporting, and analytics – from the summary level down into the detail profitability data itself. 

The granular detail also allows you to analyze the drivers, yielding insights for improvements to daily processes, practices, and strategic actions.

Customer Profitability Management in Action

Encourage Profitable Customer Behaviours

By exploiting the full range of profitability drivers captured in the data store OLAP solutions provide you with sophisticated tools for analysing metrics and supporting details. 

Reporting and dashboards can rank customers based on profit contribution and reveal the underlying behaviours affecting profitability.

This insight allows management to focus on profitable activities, discourage costly ones, and predict future customer actions and preferences. Actionable intelligence is delivered to all relevant users in measures that can be used across the enterprise.

Such centralized metrics allow marketing, finance and other general management to coordinate strategic customer retention, maintenance, and acquisition decisions.

Making Informed Customer Management Decisions

By arming marketing management with a detailed view of the costs of customer behaviours, interactions and profitability, Jedox enables them to make better decisions based on accurate, detailed information. For example, customer campaigns can be prepared based on historical results versus revenue drivers which are not as accurate.

This will enable the business to offer the appropriate service or product at the right price, at the right time, to the right customer. In addition, the detailed cost information helps management streamline and focus the customer service and relationship management organizations as well as their actions.

Developing Objective, Centralized Profitability Measures

While the solutions’ profitability analytics provide information that can better inform your business decisions, you can be confident in the accuracy of the information as it can be reconciled back to company results posted to the general ledger. Centrally controlled, consistent, and detailed, they are easily managed and can evolve with changes in the customer and company environment.

Improve Your Profitability One Customer at a Time

With experience in providing actionable insights into customer profitability Procuity is well-positioned to help grow your company’s top and bottom lines.

With almost 20 years in OLAP Business Intelligence Procuity can help you with your strategies, translate these strategies into plans, implement execution monitors and provide insights to help improve your financial and operational performance.