Reports of the death of the spreadsheet are greatly exaggerated!
When you read the magazines devoted to Retail Technology, and visit the shows you could be forgiven for thinking that all retailers are using state of the art business intelligence software with perfectly architected applications and processes. However, if you try visiting a few retail head offices and take a look at what the majority of users are doing you will see that the most commonly used business intelligence tool by a long stretch is the humble spreadsheet. Finance staff are busy analysing store performance and creating budgets, merchandisers are working on their Weekly Sales, Stock and Intake plans (W.S.S.I.) and so forth. Functionally, the spreadsheets I see have changed very little over the 35 years or so since I first came across a copy of Lotus 123 on a colleague’s desk, although the results are, of course, much better presented. Why then is there this huge discrepancy between the marketing dreams of the major business intelligence vendors and the more prosaic reality often encountered at the retail coal face?
Information wants to be free
There is a variety of reasons but firstly, to borrow the hackers’ mantra, “Information wants to be free”. However much the strategic thinkers in a business may wish to see a single version of the corporate truth held in a central and sanctified repository, the people doing the real day jobs are being asked to pull disparate data together into meaningful, flexible and often irregularly shaped reports. To do this they have to appropriate data from a variety of sources and pull it together into a single medium that is easily shared – the requirement to liberate data pulls them inexorably towards the spreadsheet. Most of today’s Retail business intelligence tools are designed to deliver data in structured ways they simply don’t cut it when the anarchic reality of everyday life takes over. This forces analysts into using tools that allow them to deal with reality rather than attempting to comply with the idealised requirements of the IT consultants.
Analysts are data heretics
The heretical behaviour of today’s information analysts is not unlike what was seen when the European Reformation wrested control of the language of the church from the priests and the educated elite and brought it within the grasp of to the masses. Information will always find a way to propagate itself. Indeed with today’s pricing models, the democratisation of business intelligence is still a dream in many organisations. So what is it that makes spreadsheets superior to the other business intelligence tools in the eyes of these data heretics? Spreadsheets have long been the analyst’s tool of choice because of their inherent flexibility and the ability that they confer to present information in an attractive and easily comprehensible way. It doesn’t take a huge amount of effort to produce high quality dashboard like reports using software that costs £100 (take a look at http://www.exceluser.com/dash/startdash.htm if you want to find out more). The skill sets that allow this pragmatic and effective behaviour are also readily transferable from business to business with benefits to all concerned. This is one benefit of Microsoft’s hegemony in this area that is often overlooked.
What if (or what if not!)
Most users are charged with performing what-if analysis or creating plans and budgets. As I mentioned earlier, many of you retailers will still have quarterly or annual store P&L budgets and WSSI plans being created using a spreadsheet. I this context a huge range of OLAP tools have to be rejected straight away as alternatives to the spreadsheet. They either don’t have the capability of allowing data to be written, or they only offer it in such a clunky manner as to be next to useless. Compare this to the humble spreadsheet where even a novice can create a relatively complex scenario containing several assumptions and the associated results. Do analysts get given large budgets and extended timescales to prepare their projections? Not in most retailers I know! The result is that they often have little choice but to use spreadsheets to manipulate data and to provide the required answers.
A BI Backlash?
Having said this, as I hinted above, there are many reasons why this unstructured proliferation of data in spreadsheets is undesirable. First and foremost there is the issue of spreadsheet error. If you haven’t started to worry about the accuracy of all those hand crafted reports you are given, then take a quick look at http://www.eusprig.org/stories.htm. I particularly enjoyed the story about the $24 million clerical error! From a more strategic viewpoint you may not want to have the same data replicated across the business in different formats. You certainly don’t want different versions of what purports to be the “same” data flying around your boardroom. You may want to be able to share data, or to consolidate it across the business. You might want all calculations used in the business to be consistent. Another important issue is that when spreadsheets reach a certain level of complexity they become almost impossible to maintain – especially when the author now works for your major competitor and nobody trained them to document the applications that they wrote two years ago. So, when we think about all these issues, we come right back to the justifications that are often used for purchasing our expensive business intelligence systems. The conundrum that remains is how we can achieve a combination of the structure and robustness of a well architected business intelligence system whilst preserving the flexibility that is needed if we are to allow our analysts and planners to do their jobs effectively.
Meeting the needs of the users
The obvious solution is to integrate the data stored in our disciplined and properly organised business intelligence systems with the analysts’ favourite tool, the spreadsheet. Business intelligence tools that do this have been around for some time now, and most vendors now offer some sort of spreadsheet integration capability. Being able to replicate the standard two dimensional “pivot table” concept is simply not sufficient in an environment as complex as retail though. If we take the simple example of a retail range plan we will find a level of complexity that will defeat all but a very few business intelligence tools. Some data is held at total season level, some is held by month. Some data is held at total store level, other data is held by store grade. In spite of this every retailer that I have ever come across has managed to create some sort of spreadsheet to meet the needs of the user community in this area.
A middle way does exist
In order for a business intelligence tool to mimic the functionality that spreadsheets have now established as a base requirement it needs to be able to pull together data from multiple, differently dimensioned cubes into a single interface (just like we see in a standard range plan spreadsheet). Whilst a few vendors have created proprietary interfaces to achieve this, by far the most common approach is to do this via the medium of the spreadsheet. When this single interface to multiple data cubes approach works, it works extremely well. You can have a robust application with centralised data and calculation repositories. This allows data sharing and easy consolidation as well as ensuring that spreadsheet errors are minimised. In fact it can remove many of the headaches that spreadsheets engender altogether. At the same time the data is freed and it can be manipulated by analysts using a tool that they all understand and can get the most out of.
Long live the spreadsheet!
So it would seem that the spreadsheet is not dead or dying after all. What the business intelligence revolution of the last 10 years has achieved is to offer the analysts a far more flexible and powerful set of weapons than they had before. The trick to getting the most out of your systems investment is not to be dogmatic but pragmatic. Give your users orthodox business intelligence tools but allow them to interact with them using a tool that they understand. That way your analysts can spend more time thinking valuable thoughts about the information itself instead of wondering how