Dear Analyst #47: Spreadsheet horror stories from the European Spreadsheet Interests Group
Podcast: Play in new window | Download
Subscribe: Google Podcasts | Spotify | Stitcher | TuneIn | RSS
The episode about how a rogue trader cost JPMorgan Chase $6.2B due to an Excel error struck a chord with folks. This episode explores three horror stories (and a recent one related to COVID) where people made simple spreadsheet errors and cost their companies and organizations millions of dollars. I don’t get too in-depth with the actual spreadsheet error in each story like I did with the JPMorgan Chase story, but do provide a quick analysis and lessons to be learned from each story. At the end of the day, these stories are not about the deficiencies of Excel itself, but rather human error and oversight.
EuSpRIG for the win
All the stories below come from the EuSpRIG’s website where some of the stories go back to the mid-90s. I came across the European Spreadsheet Risks Interest Group (EuSpRIG for short) watching a webinar about auditing Excel workbooks by Paula Guilfoyle. During the webinar, she mentioned these Excel “horror stories” on the EuSpRIG website, and low and behold there’s a rich archive of all these horror stories which the team has consistently been updating for what looks like to be over 20 years. You know the group and the content must be legit since the website still looks like a site from the late 90s:
In all seriousness, Patrick O’Beirne (chair of EuSpRIG) has created an amazing community and resource all spreadsheet users should peruse to learn from past spreadsheet mistakes. Nicole Kobie at Wired recently wrote a great story about these “Excel warriors,” and I think this quote from the story highlights the main issues all you analysts out there should heed:
Part of the challenge of this work is that spreadsheet defenders must not only be Excel experts but know the industry that they’re working in.-Nicole Cobie, Wired
From what I can tell, all the horror stories on the EuSpRIG website (and really any time you see a story in the media about a spreadsheet error) highlight something negative that happened to the company. These type of stories are the only ones that reporters pick up and lead to clicks, ad dollars, and that whole thing. Rarely do you see a story of masterfully crafting a spreadsheet formula that leads to a positive result for the company.
Story #1: $2.6B erased from Fidelity’s Magellan fund
This story originated from a thread in an e-mail listserv from 1995 called The Risks Digest. Stepping back for a bit, it’s amazing that these spreadsheet mishaps were “documented” this far back on Microsoft Excel ’95 (I learned Excel on version 2003 which seems lightyears ahead of 1995):
Story goes like this: In November 1994, Fidelity was planning on making a distribution from their fund in the amount of $4.32/share. Fidelity cancelled the distribution because a tax accountant forgot to put a minus sign in front of a $1.3B net capital loss, which resulted in a positive dividend estimate that was off by $2.6B.
Analysis & lessons learned
Can you imagine being the tax accountant having to tell your boss that you forgot to put a minus sign in a cell? This error made it all the way up to then Fidelity CEO J. Gary Burkhead who sent a personal email (or maybe a physical letter–heck it’s 1995) to all shareholders about the mistake:
We have taken several steps designed to ensure that this error should not happen again. We will subject initial estimates to the same rigorous verification process that we use in preparing the distributions that the funds actually pay. This will include a thorough review not only by our own fund accountants by also by the fund’s treasurer and independent auditors. In addition, estimates will be reviewed by each fund’s portfolio manager.
What’s missing from the story, naturally, are what systems Fidelity used to output the financial records of the fund into the file the tax accountant used. Why did the tax accountant need to calculate the net capital loss in a separate Excel file?
Not much of a lesson to learn here except that if you’re copying and pasting numbers from a PDF to a spreadsheet, make sure that negative symbols are preserved. Especially for financial data (where negative numbers are typically written in between parentheses), making a simple mistake like this can lead to you having to explain your mistake to the CEO.
Story #2: Misaligned rows results in 10% profit erased ($24M)
I really wish we could see what the actual spreadsheet looked like in this story. It’s April 2003, and TransAlta Corp. (an electricity power generator company based in Alberta) needs to submit its bids for purchasing May contracts in the New York power market. Once the bids are submitted, you can’t change them, based on the rules of the power market. Of course, the bids are submitted in an Excel file, and someone did a bad cut-and-paste job and rows got misaligned.
- The result: TransAlta bid higher for certain contracts they shouldn’t have, and ultimately overpaid for them.
- The kicker: TransAlta knew about the error for a month and could not say anything because if their competitors found out about the incorrect bids, TransAlta could’ve lost a lot more than $24M
It was literally a cut-and-paste error in an Excel spreadsheet that we did not detect when we did our sorting and ranking of bids prior to submission.-Steve Snyder, TransAlta president in 2003
Analysis & lessons learned
Copy and paste errors happen all the time. My guess is that the analyst who did the paste was doing this from another spreadsheet. Aside from doing error checks to ensure numbers add up correctly (and they match the originating spreadsheet), one idea this sparks is the type of paste you use.
For creating dashboards and models, you are typically doing a Paste Special Values, Formats, and Formulas. In this case, doing a vanilla copy and paste might have led the analyst to realized the error they were making. Let’s assume the source cells were really crappily formatted like the cells below. The empty cells to the right where you’re supposed to paste the bids have no formatting, so when you do a regular paste, it’s easier to see that you have misaligned the cells because you are copying over the source formatting when doing the paste.
The actual spreadsheet the TransAlta analyst submitted was much more complicated than this, so I may have completely missed the mark. But the lesson to be learned here is that cell formatting can give you a quick visual cue on whether or not you are doing the paste correctly.
Story #3: Overestimating graduate student enrollment leads to $2.4M less revenue
The University of Toledo is preparing for the 2004-2005 academic year. UT was already told that due to state budget cuts, they would get $1.5M less funding this year. It’s a tough position to be in as UT officials decide what their budget will be for the upcoming school year. Do they raise tuition? Get their donors to pitch in some more? An analyst in the institutional research office saves the day. He or she says that graduate student enrollment is expected to increase by 10% next year, even though official UT projections point in the opposite direction. With the extra tuition from these graduate students, UT can carry on with their plans for hiring more full-time faculty and other strategic initiatives.
The kicker: The official UT projections of a 10% decline in graduate student enrollment were accurate. The analyst may have not formatted the decrease correctly and officials believed it was an increase instead.
Analysis & lessons learned
This story shows the perfect confluence of a spreadsheet error, FP&A budgeting constraints, and a little bit of confirmation bias all mingling together at a party.
Imagine all the UT officials trying to figure out how they are going to manage a budget for the upcoming year and commit to all the strategic investments they want to make. You either cut costs or raise prices, and low and behold an analyst discovers a trend in the data that upper management failed to see. It’s not too crazy to think that management is not as close to spreadsheet formulas as the analyst is, and once they hear “10% increase in graduate enrollment = $2.4M projected revenue,” their eyes light up. All their problems are solved, and they can continue growing the university like they originally planned.
It’s hard to be the bearer of bad news and reveal that the unbridled optimism is unfounded, and that this projected $2.4M is not real. The budgeting team doesn’t want to hear it, neither to the UT officials. Truth hurts. Why investigate whether one cell in the spreadsheet is positive or negative when everyone is happy?
Moral hazard with a twist
I had to learn about ethics during a finance class at university. The professors talked about the concept of moral hazard and doing what’s right for shareholders and the community. The main lesson he gave:
Don’t do something today that you would regret reading about on the front page of the Wall Street Journal the next morning.
These spreadsheet errors are all (allegedly) “honest mistakes.” As I talked about in the JPMorgan Chase episode, these are examples of Hanlon’s Razor. Yet, these stories end up on the front page of newspapers because the media likes to show that simple human error can cause real financial harm and taints reputations.
Should analysts be held responsible for these mistakes like a stockbroker trading on inside information? Is it worth taking a risk knowing that at the end of the day, you can blame your spreadsheet or lack of proper tools and controls to get work done?
The UK story about COVID cases tracked in .XLS files
Related to the three stories above, a recent story about spreadsheets gone wrong made headlines since it had to do with COVID. Public Health England (the organization responsible for processing data about COVID cases) was not reporting positive COVID cases and contact tracing details correctly because they were using .XLS files (file format from Excel 2003, hooray!) instead of .XLSX. This older file format can only hold 65,536 rows per worksheet and PHE had way more to report than 65,000 cases including the contract tracing data.
My hot take: if the UK can’t even get their reporting right, imagine how other countries must be faring. Spreadsheet errors leading to losing millions is one thing, but to risk lives is pushing this issue to a whole other level. I came across this video from Matt Parker who describes the situation with great satire:
Other Podcasts & Blog Posts
In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:
[…] written about this aphorism before (see episodes #47 and #38) in relation to Excel errors. Via […]