Excel is a victim of its own success
Excel is a tool widely used by analysts of all levels of experience. The combination of availability and flexibility, coupled with a widespread lack of training can mean Excel models are prone to errors.
There are examples of Excel error contributing to losses in the Billions of dollars. In virtually all cases, an appropriate approach to risk management could have prevented these substantial losses.
‘Those who do not learn from history are doomed to repeat it.‘ – George Santayana, Philosopher.
Almost all spreadsheets contain errors
A 2008 study carried out by the University of Hawaii found that almost 90% of spreadsheets contain errors.
“Spreadsheets, even after careful development, contain errors in 1% or more of all formula cells…In large spreadsheets with thousands of formulas, there will be dozens of undetected errors.” – Ray Panko, University of Hawaii.
To avoid repeating errors in the future, let us take a look at 3 of the highest-profile Excel errors from the past and see how steps can be taken to avoid a repeat.
JP Morgan’s $6bn trading loss.
In 2012, JP Morgan was using Excel spreadsheets to create VaR (Value-at-Risk) models. A simple copy and paste user error led an employee to copying the wrong information from one spreadsheet to another. The model subsequently underestimated JP Morgan’s risk which contributed to their $6bn trading loss.
It was revealed that an email was sent to the modeller responsible for the error by the trader who the modeller reported to. It was written that the modeller should “keep the pressure on our friends in Model Validation and Quantitative Research.” The evidence of added pressure to accelerate the review process undoubtedly contributed to the models flaws. This will have also made the team less focused on finding operational flaws in the approval process.
The takeaway from this story is that adding too much pressure onto the model review team can cause critical errors. Had the modeller had the time to check the model over for potential faulty equations, this would have helped to minimise their modelling risk.
Fidelitys $2.6bn “minus sign” error
Fidelity’s ‘Magellan’ fund estimated that they would make a $4.32 per share distribution at the end of 1994. This incorrect forecast happened because an in-house tax accountant missed out the minus sign on a net capital loss of $1.3 billion. This made the net capital loss a net capital gain. This caused the dividend estimate to be off by $2.6 billion.
Former Managing Director J. Gary Burkhead said, “While many of our processes are computerized, the requirements of the tax code are complex and dictate that some steps be handled manually by our tax managers and accountants, and people can make mistakes.”
Excel errors are never caused by a software malfunction. An appreciation of the risk of human error and implementation of robust financial model audit processes will protect your company’s finances, whilst keeping you out of the headlines.
Utah Office of Education’s $25m budget miscalculation
In 2012, the Utah office of education caused a shortfall of $25m in their budget by underestimating the number of students who would enroll in the whole state’s public school system in their spreadsheet. This was caused by a miscalculation in determining the weighted number of pupils, which then determines each district’s budget per pupil.
Former State Superintendent of Public Instruction Larry Shumway noted, “This error is of such magnitude that we are not comfortable that we can handle it simply within the margins and the typical practices and the authority that we usually have… Both our offices are working to ensure there are additional steps in place to have additional reviews.”
Republican Ken Sumsion, R-Lehi suggested to the state committee that the blame for the error should be shared due to constant tinkering with the office of education’s requirements in state statute. “In fairness, part of it is we make it pretty complicated“.
Key takeaway
The takeaway from this story is to keep things simple! Also, a lack of clarity of ownership and frequent tinkering with models by multiple parties dramatically increases the likelihood of error.
We cannot emphasise enough the importance of minimising spreadsheet risk by taking the appropriate precautions in your business. User error will remain unless checked. Rushed review processes will naturally lead to faults. And data which is regularly changed and influenced by external parties is prone to error.
