Spreadsheet Mayhem

Staff may love them, but an Excel package on every desk means many versions of the corporate truth. The answer is smarter business intelligence.

by Ron Condon
Last Updated: 31 Aug 2010

This March, the UK's Office of Government Commerce was forced to delay the launch of a slick new red-tape-busting procurement programme when it discovered an error in a spreadsheet. Companies applying to become accredited suppliers were told: 'Unfortunately, (we are) not, as we had hoped, in a position to accept your tender at this time. This is because an error in the original evaluation spreadsheet has been identified, necessitating re-scoring of all tenders for this project ... This error has been corrected and this has caused a small number of changes to the original award decision.'

In June 2003, TransAlta, a Canadian power generator, announced that it had spotted a $24 million hole in its accounts, caused by what its chief executive Steve Snyder described as a 'simple clerical error'.

The company had accidentally underbid in a contract to supply New York's electricity, and the contracts could not be reversed. Snyder admitted that the company's computer spreadsheet contained errors that had undermined the bids.

The first of these spreadsheet errors was merely very embarrassing, the second was both embarrassing and extremely expensive. They are the stuff of management nightmares, shining examples of the kind of data disaster to which vendors of business intelligence software allude when exhorting you to buy their products. Standardise your data, goes the pitch, and these kind of basic errors simply can't happen.

But the staff on the ground still love their spreadsheets, and one program - Microsoft Excel - in particular. Excel sits on around 300 million personal computers around the world, and is so well liked that what started as a handy little program for doing the expenses and departmental budgets is being put to work on the kind of high-level strategic number-crunching it was never really intended to perform. Excel is now widely used to model real-life business problems and the results obtained are relied on for making critical decisions.

It's a very risky business - the people who write the spreadsheets may be experts in hedge fund management or risk analysis, but they are not computer programmers. They are usually in a hurry to get the answers they need, so they tend to skirt round some of the boring practices of the professional IT world - such as testing your code before use.

In a perfect world, such analysis would be fulfilled by the more established vendors of business intelligence (BI) systems such as Cognos, Hyperion and Business Objects. Indeed, they and other BI companies spent years evangelising about the concept of a 'single version of the truth' - the data warehouse. The argument was, and still is, that the warehouse holds the one true picture of how the company is doing, and should be the source material for any reports, modelling or decisions taken by the business. But people in need of a quick numbers check that they can use now have flocked to spreadsheets in droves.

'Users should never have been given spreadsheets on their desks but, unfortunately, we can't go back now,' says Alys Woodward, an analyst with research company Ovum. 'People built applications in Excel because the IT department could not give them what they wanted in time.'

That's not to say that the data warehouse model doesn't work - some implementations have been very successful in identifying trends and, for example, supporting retail loyalty schemes like Tesco's Clubcard.

But BI tools rarely manage to penetrate beyond 15% to 20% of decision-makers in organisations, partly because of their high cost, but also because they are often complex to learn.

The other drawback of the data warehouse model is the 'rear-view mirror' picture it provides to users. Data is usually fed in from transaction systems at the end of the working day and then fed in to update the data warehouse for analysis the next morning. The warehouse data is kept separate to avoid analysis work disrupting or slowing down the bread-and-butter systems of the business.

That basic design was created some years ago. But like the rural fellow in the old joke who replies, when asked for directions, 'Well, I wouldn't start from here', some people are now viewing the cumbersome data warehousing model in the same way.

'The data warehouse is just a system of record; it is too difficult to keep up-to-date,' says Charles Nicholls, a veteran of the BI world and chief executive of SeeWhy Software. 'We wouldn't start from here. The data warehouses we use today, which take a snapshot from live systems, do so to avoid damaging the performance of those transaction systems.

BI reports are just reference documents. They arrive too late to be useful.'

Alison Whitby, a consultant with Diagonal Consulting, agrees. 'Typically, BI has been rearward-looking and not very timely. People spend 95% of the time getting hold of the data, making sure it is the correct data, rather than analysing it to support decision-making.'

The challenge for BI is to reach out into the organisation and help the other 80% to 85% of people to make the right decisions, rather than just the privileged top managers. The industry calls this 'operational BI' and has adopted several methods to achieve it. For some BI vendors, it is enough to get the system to generate e-mails to users with reports tailored to their needs.

But SeeWhy's Nicholls argues that with today's cheap computers, it is possible, and more effective, to process volumes of data in real-time so that they can be acted on immediately. This 'event-driven processing', as he calls it, 'doesn't just tell you how many tins of beans you've sold, but also how many you should have sold given that it's now 5pm on a Friday.'

Instead of giving reports to shop workers who would not read them anyway, he says, 'you have a constantly updated flight departure board at the door from the stockroom to the sale floor showing "these are the items that are out of stock".'

He has also put his theories into practice, helping Diageo streamline deliveries of Guinness from Dublin to the US. Before adopting his system, he says, the company had been in 'spreadsheet mayhem', employing people to copy data from transport websites to keep track of deliveries and fewer than 50% of deliveries arriving in the US on time.

Using several years' historical data as a reference, the system tracks each event in the delivery and raises an alert when anything slips. The result is that 80% of US deliveries now arrive on time. The beer is fresher, they have less stock in the channel, and they have more control.

But what about all the companies that have built data warehouses? They still need to get the best from their investments, and that means driving usage right through the organisation. Many are realising that this increasingly involves finding an accommodation with Excel users.

'It is important to distinguish Excel as front-end tool from Excel as engine,' says Frank Buytendijk, vice-president for corporate strategy and BI specialist with Hyperion. 'As an engine, Excel is really bad - it is playing with fire, particularly if you are a publicly traded company.'

And this could end up being the most potent argument for killing off a lot of spreadsheets. As Ovum's Woodward says, many companies have large long-standing spreadsheets that seem to work well but which no-one really understands. In other words, they cannot be audited.

The trend, therefore, is to keep data centrally and to let Excel fans have their wish by downloading the information to play with. Most of the BI vendors have now resigned themselves to living with Excel.

And they need to move fast - Microsoft has noticed the trend itself and is threatening to eat the BI firms' lunch next year with the launch of Office 2007. This will introduce much tighter integration of Microsoft's SQL Server database with Excel, and therefore make it easier for companies to control spreadsheet usage.

To cap it all, there's another technology that could still further change the face of BI: the search engine. Most of us have quickly come to use search engines (usually Google) for even the most trivial of enquiries - who won the FA Cup in 1954? Who sang American Pie? No more combing through reference books - the answer is there in an instant.

With so much company information now lying in unstructured form - in documents, e-mails, online feeds, sound clips - we need a new way of getting to what we need. And a spreadsheet will not do it.

Recognising the need, many of the established BI vendors have formed partnerships with Google and other search engine providers to open up unstructured data for internet-style searching across a company's internal servers. This development is likely to fuel much of the interest and growth in BI in the near future. Business intelligence - and its bright young cousin, corporate performance management - needs to go beyond the bread-and-butter of raw numbers. To keep adding value, it must build sensitive analytical antennae to pick up changes and trends that may affect the business - such as adverse stock market comment or unfavourable reviews. The search engine capacity to answer unpremeditated questions offers the best way to get that level of flexibility.

For the moment, though, the spreadsheet seems to have won its battle for survival as the business person's favourite tool for analysing and modelling data. Like fast cars, spreadsheets may be dangerous and cause the odd crash, but we love them too much to let go.


Like many a small family firm, Bristol-based oil-fuel supplier Thomas Silvey once managed to get along nicely without taking budgets too seriously. But the company had expanded beyond its core business to include filling stations and oil depots for commercial and domestic customers. Turnover was up to £36 million, but with oil prices swinging wildly, the firm needed to get a tighter hold on budgets.

The arrival of financial controller Mark Woodward (right) last November proved a catalyst for change. 'They used to do budgets on an annual basis, although two years ago they didn't bother, and last year they were completed several months late.'

Woodward found himself trying to manage the business through 38 linked Excel spreadsheets. 'I'd have shot myself if we'd carried on that way,' he recalls.

Instead, he called in a few BI vendors and admits he chose the cheapest quote from Surrey-based Inca Software, for a centralised planning system that would bring a consistency of data across the business, while allowing the data to be analysed in Excel.

Inca Planning took just four weeks to go live, and Woodward says he now has much better control. 'We can understand and act upon the impact of changes in oil prices in minutes. This would have been impossible using our old system,' he adds. 'Our next challenge is to go from monthly P&L to a daily cashflow. Cash management is key in this business.'

He admits that he struggled with getting the company owners to approve the £9,500 investment, but says they have been won round. He expects it to pay for itself in less than a year - outstanding value for money.


With lots of expensive motors in the pipeline, DaimlerChrysler Retail - the importer of Mercedes cars into the UK - has to be on top of the finances. In the past year, the company has started using business intelligence technology to help management at all levels focus its efforts in the right areas.

Drawing data every night from its dealer management system, the company runs the information into an SQL Server database, then each morning its BI system Hyperion Intelligence presents it in a dashboard format.

Previously, managers could generate only standard reports from the software. The new system is based on a traffic-light model that lets them know which areas require attention and which are running well.

Says business systems manager Jane Templeman: 'We had a lot of plates spinning in the business, and we just needed to know which ones needed spinning today. It helped the management team do that.'

Management accesses the information it needs via the company intranet. The managing director gets a high-level view of key parts of the business and is able to drill down to further detail if needed. Other managers get a restricted view of the data, but the information is presented in the same traffic-light format.

The system has had a direct impact on profitability, says Templeman, by allowing managers to spot problems early, before they cost money. 'If the MD sees a red light indicating a high level of over-aged stock, he can drill down to see which outlets are not shifting vehicles as well as they should be.

'And it enables the local managers to be on the ball as well. Everyone is looking at the same data. There is a single version of the truth.'

Find this article useful?

Get more great articles like this in your inbox every lunchtime