Some say that Microsoft Excel might be the most dangerous software on the planet. At Sclable we see spreadsheets at all those places where business applications reach their limits. Spreadsheets just perfectly seem to fill “the gap”. So where is the danger? For our hit list of spreadsheet horror stories read on…
To soften the headline of this article I’ll start of with some really good things I have to say about spreadsheets. First of all, spreadsheets can be really useful. I use them myself to some extent. Spreadsheets have pretty much become my notepaper+calculator replacement.
Calculation Logic Prototyping
Also I have seen quite useful spreadsheets when it comes to capture the calculation logic of business applications. The main benefit is, that sales or finance people can do them, there is no need to involve a software engineer when you just want to break down some figures.
The Good, the Bad and the Ugly
So far so good. Sometimes especially non-engineers tend to oversee some risks. Which is bad. The results can be so devastating that they can indeed ruin your business. A few highlights over the last two years may show you how ugly it can get.
Highlight: The Keystroke Error
January 2012. The London Organising Committee of the Olympic and Paralympic Games (Locog) found out, that synchronised swimming was overbooked by 10,000 ticket sales.
“Locog said the error occurred in the summer, between the first and second round of ticket sales, when a member of staff made a single keystroke mistake and entered ‘20,000’ into a spreadsheet rather than the correct figure of 10,000…”
Highlight: The Date Conversion Error
October 2012. An engineer working on the Cancer Genome Atlas Project at the National Human Genom Research Institute discovered that Microsoft Excel would automatically convert the names on genes such as MAR1, DEC1, OCT4 und SEPT9 into date information.
“When processing microarray data sets [with Excel], we recently noticed that some gene names were being changed inadvertently to non-gene names…”
Highlight: The Copy and Paste Error
January 2013: JPMorgan reports how the company lost more than 2 billion dollar on the capital markets in 2013. Reason was their VaR (Value at Risk) Model which was operated through a series of excel sheets. Here is what happened:
“After subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR . . .”
Originally the intention of the creator of the VaR spreadsheet model was to blueprint a calculation engine that should have been properly modelled. Which unfortunately never was.
Highlight: Aggregation ranges do not adjust automatically
April 2013. A review by a PhD student of the analysis performed by leading Harvard economists in an influential paper on Government debt to GDP ratios found basic mistakes in their spreadsheets: Some rows have been excluded accidentally from an average calculation. The controversy was enormous. As a result the reputation of those economists, Harvard and governments citing the paper was damaged severely.
“The paper in question is [the] study “Growth in a Time of Debt”, which found that economic growth severely suffers when a country’s public debt level reaches 90 percent of GDP. That 90 percent figure has often been cited in the past few years as one big reason why countries must trim their deficits — even if their economies are still weak…”
Excel as Your Database
After this, who still wants a spreadsheet as a database? I was really surprised to find a book by the title “Excel as Your Database”. A quick check at Amazon gives a rating of one star out of five. Allright, that seems appropriate.
SpreaDsheet Risk Managment
Honestly “Spreadsheet Risk Management” is a liable term by now. If you have the slight fear of running into some sort of problems described above, you may consult the European Spreadsheet Risk Interest Group.
How Do Business Applications Avoid Such Issues
A properly modelled business domain saves you from adding to those spreadsheet horror stories in many ways. From data types and input format restrictions to solid aggregation models, enterprise grade business applications not only organize the way data is processed and figures are calculated. More than that your business application gives you full control when it comes to collaboratively develop and define KPIs, produce and verify reports or implement internal audit and approval processes.
It is a bit harsh to forbid the usage of spreadsheets once and for all like some companies and governmental organisations state in their policies and guidelines.
My general advice is to have a close look onto the creative ways how spreadsheets are used within your organisation. You might have a secret, unguided and potentially dangerous software project running. Add a software engineer and proper technology and you can get a tool with a high value. If not: see above.