What is Spreadsheet Risk?
Over the last thirty years spreadsheet applications have become one of the most important business tools for organizations of all sizes. They are (arguably) the most popular and powerful software tool used in business today.
Their popularity and widespread application can be attributed to the fact that they are easy to use, simple to modify, extremely flexible and can be rapidly applied to suit a variety of different business functions – data analysis, data calculations, data presentation, modelling, accounting, reporting, graphing data etc.
Microsoft Excel has been the dominant market player in this space for a considerable time. For many organizations Excel has become an essential tool that is embedded in some of their most critical business processes. Indeed, many business decisions are made based on results coming out of an Excel Spreadsheet.
Introducing Errors – How mistakes lead to risk (and loss)
Unfortunately, the key strengths that underline the power of spreadsheet applications are also the source of their greatest weakness – Errors.
Errors can take many forms but generally fall into three main categories: –
1. Data input errors
2. Calculation or Formula errors
3. Formatting errors
Like any business process, errors introduce an element of risk. Risks are magnified when errors are easy to introduce into a process and checking procedures are not performed (or are limited).
A single mistype in a spreadsheet can create an error that could dramatically alter the end result of a calculation and thus change a critical business decision. Some figures indicate that error rates in spreadsheets are as high as 88%!!!
So…what could be the impact of a small error in a spreadsheet?
Example 1: – Excelling the Economy
In January 2010 economists Carmen Reinhart and Kenneth Rogoff published an economic paper analyzing the effect of government and external debt on economic growth levels. They utilized historical data from forty four countries spanning nearly 200 years in their research.
At the time their paper was extremely influential as policy makers around the world sought the best approach in tackling high sovereign debt in a post Global Financial Crisis (GFC) environment. Their results assisted in the support of pro austerity measures that affected millions of people.
However, three years later Reinhart and Rogoff admitted that some of the historical data used in their calculations was inaccurate. The reason…a simple formula error in Excel led to several countries being inadvertently excluded from some summary results.
Although they reworked the numbers and maintained it didn’t affect their final conclusions the damage was already done. It had created a slight element of doubt as to the legitimacy of their results.
Example 2: – JP Morgan Chase
In 2012 JP Morgan Chase reported a $6.2 billion trading loss at its Chief Investment Office. The subsequent internal investigation found problems with the company’s Value at Risk (VaR) model which was being calculated in Excel.
The bank’s 129 page taskforce report stated “…the (VaR) model operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another.” (p.124)
In addition “…a spreadsheet error caused the VaR for April 10 to fail to reflect the day’s $400 million loss in the Synthetic Credit Portfolio. Because it was viewed as a one-off error, it did not trigger further inquiry.” (p.127)
Errors were also discovered in some calculations “…after subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended.” (p.128)
Causes of Errors
While the above examples indicate formula miscalculations were the main culprit there are numerous other factors that can cause spreadsheet errors. These include:
• Too many unknown and unorganized spreadsheet files or tabs
• Poor naming conventions of files and tabs
• Excessive and undocumented data linkages between files and sheets
• Poorly designed and presented spreadsheets
• Spreadsheet evolution (start off simple and become increasingly too complicated)
• Spreadsheet ownership (ownership changes without the transfer or knowledge)
• Manual data input (including cell by cell typing and copy & pasting large datasets)
• Too much data in a spreadsheet (remember the 64,000 row limit in Excel??)
• No versioning or change tracking
• No double-checking processes
• Overly and needlessly complicated formulas
• Plain old human error
Mitigating & Reducing Spreadsheet Risk
There are many ways and methods in order to mitigate or reduce the risk associated with spreadsheets.
Some advocate the use of automation in order to offset the risk of copy/pasting large datasets. This is a positive first step, however automation also introduces inherent risks in itself that need to be properly managed.
Others promote the idea that data should be stored in databases as opposed to spreadsheets. This is not always so clear cut as it would seem and a “right tool for the right job” principle needs to be applied. Although databases enhance certain aspects they do not totally eliminate data errors.
We believe that the most effective method is to have in place a holistic Spreadsheet Management System that seeks to address all the issues listed above. This can either be created in-house or utilizing third party software or consulting.
How do you use Spreadsheets in your Company?
We are always looking to assist organizations reduce their spreadsheet risk. In order to help us please fill out our really quick (less than 2 minutes) spreadsheet survey – http://goo.gl/V7KXww