Why your business is in danger and it’s your (Excel) fault?
When it comes to data processing we all tend to choose the same tool — MS Excel. That’s what we all know. That’s what seems the easiest.
However, if you operate your business basically off of Excel, you may be in trouble. In 2012 in the case known as “The London Whale” JP Morgan suffered $6 billion trading loss, majority of the fault being attributed to standard Excel flaws.* It turned out that the Value at Risk (VaR) model operated on a set of Excel spreadsheets that had to be filled by copy — pasting the data between files. Baseline Scenario’s James Kwak, an associate professor at the University of Connecticut School of Law, claimed that Microsoft’s calculator is partially to blame for the situation. Kwak wondered if the ease to use Excel that allows people with no programming experience create relatively advanced applications makes it dangerous to use in sensitive cases. To top it all of, majority trainings on Excel tend to skip the importance to emphasize the need for well-designed code leading to the legacy spreadsheets that are only understandable to their creators, who may have left the company years earlier.
I must confess. I am guilty of such a sin- the legacy spreadsheet. For one of the companies I have worked for, I created a reporting dashboard. Let me be clear. I do not know all the tools on the web. Neither the possibility to create a custom dashboard was on the table. Excel was the best option from between the ones that were available at that time that I could tailor to my needs. The ‘front-end’ of my dashboard looked something like that:
At first glance it seems like the report was nothing too fancy and was easy to create. Maybe. But the difficulties start to emerge when we look at the ‘back-end’ of my structure. The report relied on a few tabs, some of them hidden. I had to apply unusual rules to keep the charts moving in a consistent way and every time I updated the numbers I had to double-check to see if the graphs stayed in place. For bigger reports, this was a time-consuming challenge. I was aware that even bigger challenge with the dashboard would be on the day when I no longer was with the company. The complexity of the ‘tricks’ that I had to use to create a dashboard that looks like the one above was not easy to comprehend, even for me later on when I worked on adjusting it to other projects. Every attempt to do so required some time to remind myself how I did it in the first place. Even though I did my best, I was not proud of what was ‘under the hood’. I was the only one to understand it and I was the only one able to updated the report from different sources. I was most certainly not the best (manual) way to crunch the numbers huh?
The fact that Excel can be “programmed” by people who would be using it may impose serious challenges. Whereas I was able to control all the input and output data in my dashboard, spreadsheets that are updated by many people shoving numbers from various systems like SAP or Oracle and between spreadsheets, are extremely vulnerable. The problem is that everyone can create spreadsheets and do it badly, without proper documentation and well-designed methodical way. Everyone can input data and leave no trace to the source. Sometimes, like in case of my dashboard the capabilities of Excel were overgrown by the needs and had to be ‘flexed’ to create the tool I needed.
Because it’s so easy to use, Excel is everywhere in business world. Especially in areas that heavily relate on processing numbers as marketing, sales and finance . Also because it’s so easy to move data Excel spreadsheets fail and the error very often goes unnoticed.
JP Morgan was not the only one to suffer because of mistakes made with Excel. Harvard professors Carmen Reinhart and Kenneth Rogoff admitted to making an Excel error in their 2010 paper that became a powerful data point for those who oppose government spending to retrieve economy. The paper stated that when a country’s debt hits 90% of GDP, growth slows dramatically. They claimed such economies shrink 0.1% a year, when in fact, based on the historical data their Excel calculations missed, they grew 2.2%.
The acquisition of the collapsed Lehman Brothers by Barclays didn’t go very smoothly either. The creators of the spreadsheet that listed the assets that Barclays was willing to buy, instead of deleting a few columns, hid them. When a junior law associate converted the file to a pdf. and e-mailed it over to the bankruptcy court the hidden cells reappeared and as a result Barclays was made to cover losses on additional 179 toxic deals it never wanted to acquire from Lehman’s assets.
“With great power comes great responsibility”
Some organizations realize the need to move some or all of their processes to another tool. Many oppose fearing the painful process of moving to another too. Reportedly, a year before the JP Morgan loss occurred, an outside consultant determined the need for implementing controls over spreadsheets. The policy to fix the issues was never implemented.
According to some research, 88% of spreadsheets have errors. That’s a big number.* I am not arguing you should get rid of Excel completely. However, do establish the strategy and evaluate the need to use it for your processes. See how many users input data, save, download, update and upload your excel files in any given process. If there is too much manipulation with the numbers, your data should not be in Excel.
How then should you protect the business from losses incurred by spreadsheets? Besides the standard practices like training the staff and implementing good documentation standards while processing the data, moving to a secure, audit-able, automated and comprehensive applications that eliminate the, extremely prone to errors, process of cutting and pasting the data, might save you millions. Or if you are a JP Morgan, billions.
If you know someone whose business suffers from malfunctioning data management processes and systems, I would be more than grateful if you could share this article with them.
If you would like to know more about custom-made applications that replace Excel and other software in the areas where processes are too complex and exceed the capabilities of currently used software please let me know at: contact@tailored-code.com
Further readings:
