Can a single spreadsheet mistake in one cell add up to millions of dollars, a fired CEO, and other calamities? Sadly, yes. These and other tales of woe come from several sources that have a bit of vested self-interest, such as the European Spreadsheet Risks Interest Group (EuSPRIG) or websites of auditing tools such as Mazars that purport to help you catch your mistakes.
EuSPRIG has been cataloging its “spreadsheet horror stories” on its site for more than a decade and has amassed quite a collection from all the over the world. Mazars has published earlier this year a “Dirty Dozen” document about 12 examples of what not to do, culled from these and other news reports.
Let’s take a look at some of the more egregious errors.
One of the best examples is how a CEO lost his job over a single cell’s entry. British outsourcing staffing company Mouchel’s CEO is Richad Cuthbert, and he didn’t even make the mistake. Instead the firm’s outside financial actuaries had the wrong entry for the value of one of its pension funds. Cuthbert resigned and the firm had to write down profits by £4.3million.
Sometimes the mistake is too much information that gets accidentally included. According to an article in Reuters, Britain's second largest drug maker AstraZeneca sent confidential company information inadvertently, when it was embedded in a spreadsheet template sent to one of the analysts who follows the company. Apologies and retractions ensued to great embarrassment.
And while we are in merry olde England, remember the 2012 London Olympics and how hard tickets were to obtain for several events? While certainly part of the issue was the high demand that usually accompanies any Olympics, some of this was a spreadsheet error too. The Olympic Committee oversold a couple of swimming events. They said the error occurred 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 remaining tickets. The error was discovered when they reconciled the number of tickets sold against the final layouts and seating configurations for venues, and realized that they were going to have thousands of unhappy attendees, most of who had bought their tickets months in advance. They had to contact ticket holders and try to placate them with seats to other venues. Of course why they had this critical variable under such loose controls is another matter of inquiry.
Let’s move across the pond to the USA. A simple spreadsheet cell reference error led to a $25 million budget shortfall for the state of Utah's education budget. Two top finance officials submitted their resignations as a direct result of the blunder. State superintendent Larry Shumway said, "The actual error was never identified and that’s probably a more serious problem than the error in the first place." You could say that.
And another local government debacle in Tennessee cost millions. Knox County Trustee John Duncan made a simple clerical error, which resulted in understating the cash balance in one of his audit reports. The total cash on hand for June 30 that the office should have reported was $128.9 million, he said, and not $122.7 million, which was what was sent to the auditor. It occurred when one account wasn't correctly linked in an Excel spreadsheet.
AXA Rosenberg is an investment firm based in Orinda, Calif. In 2011, two officers concealed a computer modeling error that had the eventual result of a whopping $25 million fine by the Securities and Exchange Commission. The error came into the computer model in April 2007. It was eventually corrected, but the CEO of AXA Rosenberg Group wasn't told about it until November 2009, and he continued to hide its existence from his clients for several more months. Once again, the cover up was worse than the actual criminal act, and the first time the SEC had fined any investment company for spreadsheet obfuscation.
Here is one for the books: a simple omission of a minus sign led to over-stating of $2.6 billion in capital gains at Fidelity Magellan Fund back in the 1990s. Yes, just a single minus sign mattered. The president had to write to all shareholders, fessing up: “The error occurred when the accountant omitted the minus sign on a net capital loss of $1.3 billion and incorrectly treated it as a net capital gain on this separate spreadsheet. This meant that the dividend estimate spreadsheet was off by $2.6 billion.” That’s real money, even for fund managers.
So what are some key takeaways from all these disasters? Clearly, many of these errors were simple keyboard mistakes that careful proofreading could have found, perhaps with a second pair of eyes to examine the application logic and basic math behind the formulas. However, that is easier said than done, because reading spreadsheets isn’t like reading computer code, and everyone has a slightly different way of building their models. One suggestion is to make use of the FAST Standard: this encourages a Flexible, Appropriate, Structured and Transparent approach that creates understandable spreadsheets and reduces errors. A number of consultants are standing by to help here. But a better idea might be to make use of QuickBase or other online tools that build in collaboration and make keeping track of current versions and model structures easier.
Another suggestion: clearly identify your assumptions, perhaps with the use of sample data that can illustrate them clearly.
Finally, divide and conquer. It is best if the person responsible for performing commercial analysis and review is different to the person designing and engineering the spreadsheet itself, as Mazars suggests in their report.
None of these things are rocket science, or even data science: just good common sense.
Get a sneak peak and register for our upcoming Webinar, October 8 at 2:00 PM EDT - F.A.S.T . Approach to Improving Operations with Inc. Top 100 Leadership expert, Gordon Tredgold.
Photo Credit © About.com