In January 2010, Harvard economists Carmen Reinhart and Kenneth Rogoff published a controversial and influential paper entitled “Growth in a Time of Debt.” The paper studied economic growth at varying levels of debt across both advanced and emerging markets and concluded that countries experienced significantly lower growth when debt levels exceed 90 percent of gross domestic product.
Over the last several years, that relatively simple finding has been reported extensively by notable publications and has been cited by policymakers across the globe to justify strict austerity programs.
Three years (and countless protests) later, that conclusion was all-but invalidated when a student at the University of Massachusetts Amherst attempted to replicate the findings of the study. After trying to independently recalculate the results of the paper, the student was granted access to the original Microsoft Excel spreadsheets used by Reinhart and Rogoff and quickly discovered that a simple spreadsheet error had substantially distorted the analysis.
This is not the first time that a simple spreadsheet error has been the cause of major problems.
In 2012, an Excel formula error contributed to more than $6.2 billion in trading losses for JP Morgan Chase & Co. in what has come to be known as the “London Whale” incident. Surprisingly, the error wasn’t related to the use of a complex financial formula with multiple variables. Quite the opposite: The spreadsheet in question incorrectly divided the difference of two rates by their sum instead of their average.
Nor are spreadsheet errors uncommon. Raymond Panko, a professor at the University of Hawaii, published a paper on the topic of spreadsheet errors – compiling the results of multiple independent studies. The results of the meta-analysis indicate that spreadsheet error rates are as high as 88 percent.
Excel is flexible, powerful, and ubiquitous. Organizations across the globe use the spreadsheet software for just about everything – from external financial reporting to simple bar graphs. While some of these activities are inherently riskier than others, they are not outside the capabilities of Excel when used properly.
Therein lies the problem: Most organizations are not good at managing spreadsheet risks – or teaching employees how to properly use Microsoft Excel. Here are four straightforward ways to help reduce the risk of spreadsheet errors:
- Assess Your Exposure to Spreadsheet Risk: Create an inventory of your organization’s key spreadsheets and categorize based on the frequency of use and inherent risk. Consider all of the potential consequences of an error and the magnitude of errors to understand your exposure.
- Document the Use of Spreadsheets in Process Documentation: Documenting the use of spreadsheets in process flows across the organization can help shed light on the specific areas where spreadsheets are relied upon – and provide a guide to bolster controls and reduce the risk of process escapements due to spreadsheet errors.
- Avoid “Hardcoding” Spreadsheet Data: Hardcoding data – the practice of removing formulas and replacing with static data – may make it easier to manipulate data, but it eliminates transparency and makes it harder to validate that the spreadsheet operated appropriately.
- Teach Employees Practical Tips to Reduce Spreadsheet Risk: While there may not be a “catch-all” solution to prevent spreadsheet errors, there are several simple data validation formulas and tools that can significantly improve the accuracy and reliability of spreadsheets. These tools and techniques should be a prominent topic in any Excel training courses.
In addition to the above, there should be a renewed effort to improve Excel-based instruction in the workplace. As stated previously – Excel is a powerful and flexible tool. By understanding and managing spreadsheet risks – and improving our understanding of the program, we’ll hopefully see fewer incidents like Reinhart-Rogoff in the future.