Trick or treat! Sometimes spreadsheets don’t wait for candies and instead play tricks on you right away. Here are some scary spreadsheet horror stories to put you in the Halloween spirit.

We’ve all experienced this: either tried to blame the software for a mistake we made or witnessed someone else do it. We even have a Reuters columnist who once wrote a piece asking if Microsoft is the quiet villain of global finance, pointing out weaknesses across the entire Office suite. 

Though it was written with tongue firmly in cheek, with the author ending off by observing that:

It would help if financiers deployed Microsoft’s tools with common sense rather than trying to use them as a substitute (for common sense).

Because unless you can attribute a mistake to a genuine programming flaw, any errors in your Excel spreadsheet intended for financial planning, data analysis – or whatever you happen to use spreadsheets for – is always the fault of one of the people working on it. 

And the more people you have contributing to a single sheet, the greater the chance of errors creeping in.

The London Whale Incident

Bruno Iksil is a former JP Morgan trader. He was saddled with most of the blame for the flaws in a new VaR (Value at Risk) model introduced to their Chief Investment Office at the start of 2012. The $6.2-billion accumulated trading loss that followed 12 months later was quickly investigated, and the JP Morgan Task Force Report released in January 2013 revealed that:

[…] the 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.

The pressure to speed up the model review possibly resulted in overlooking operational errors in some calculations. Just a single formula error led to an understatement of the volatility and a lower Value at Risk. And a significant loss.

There is little doubt that depending on manual copy and paste operations played a part in this error. It also became clear that having a review process in place is only effective if it isn’t rushed.

Unfortunately for JP Morgan, it wasn’t the only spreadsheet-related error that came to light in 2013. 

A US Securities and Exchange Commission investigation revealed a spreadsheet that contained details of certain well-connected hires – usually from prominent families with political influence – including how these hires were linked to certain deals pursued by the bank.

The suggestion was that many of these hires had little – if anything – to do with actual skill and happened purely as a step towards acquiring business deals. 

While favoring who you know and who you’re related to over actual skill for career progress happens in most industries, this is one of the first times an organization has been found to foolishly keep track of these connections. Using a spreadsheet, no less.

Not so Convivial Hangover

In the first quarter of 2018, the drinks group Conviviality collapsed following several financial mistakes. 

They first announced expected profits of £70 million, then dropped this to £56 million, before shedding a further £30 million less than a week later. The £30 million cut was blamed on a tax liability they forgot to budget for. But £5.2 million was supposedly the fault of a spreadsheet error made by a single member of the finance team. 

But as shown with the JP Morgan debacle, having proper systems and controls in place is essential for reducing or eliminating spreadsheet errors. And Conviviality was criticized for a lack of systems and controls as the profit warnings were made and corrected.

Doubting Reinhart–Rogoff

American economists Carmen Reinhart and Kenneth Rogoff published an economics paper, Growth in a Time of Debt, in 2011. Pro-austerity individuals quickly embraced the arguments put forth in the paper. Especially in light of the fact that the paper came out just as many countries were still dealing with the aftermath of the 2007–2008 financial crisis.

But three other economists, who received access to the data sample used by Reinhart–Rogoff, found “coding errors, selective exclusion of available data, and unconventional weighting of summary statistics” in the spreadsheet used. 

The result?

When properly calculated, the average real GDP growth rate for countries carrying a public-debt-to-GDP ratio of over 90 percent is actually 2.2 percent, not −0.1 percent, as published in Reinhart and Rogoff.

This affair reminded many that aside from calculation errors that can easily slip through undetected, spreadsheets errors can also happen when you intentionally or carelessly exclude data, leading to very different results.

The European Spreadsheet Risks Interest Group (EuSpRIG) has been collecting spreadsheet horror stories for a few years now, with an archive going way back to 1995. And while not all of the stories that make it into the public domain involve an immediate financial loss, those that result in a loss of investor confidence or reputation damage can ultimately also cause a less visible financial loss.

Conclusion

Phil Bewig noted in his 2005 paper, How do you know your spreadsheet is right? Principles, Techniques and Practice of Spreadsheet Style:

You can’t eliminate errors from the spreadsheets you develop, but you can reduce their number.

One of the first practices he recommends is to consider alternative software tools. And these don’t have to only be databases or business intelligence systems. Spreadsheets – whether through Excel, Google Sheets, or some other program – are not fundamentally at fault. One reason even large organizations like JP Morgan choose them is because they’re familiar and fairly easy to use. 

Alternative tools could also involve document exchange or secure file transfer systems, helping ensure data accuracy. 

But avoiding errors – and your own spreadsheet horror stories – also depends on one other thing: finding the processes and techniques for reviewing critical spreadsheets in your organization. 

And that part depends entirely on what works for you. Whether it’s following uniform design guidelines, like having the entire spreadsheet on a single page rather than split over multiple tabs, or introducing security measures like limiting the use of links to other spreadsheets or external data sources. 

Not forgetting the reduction of the amount of data you need to insert manually, along with monitoring copy-paste functions, and a robust audit trail system to record all changes made.

What processes have you implemented to continue using spreadsheets or data grids for even the most complex data?