When the Spreadsheet hits the Fan

Spreadsheets are easy to use and so they get used a lot and for all sorts of things.  However, they can’t stop you using the wrong formulas or referencing the wrong cells or adding the wrong items together.  They are therefore quite risky if the outputs are relied upon for anything really important.

The European Spreadsheet Risks Interest Group (yes, it’s a real organisation) estimates that 90% of all spreadsheets contain errors, and around 24% of spreadsheets that use formulas contain maths errors.

An investigation into the failed Texas energy trader, Enron, in 2001 led to the publication of half a million emails from within the company.  Nearly 70,000 of them related to spreadsheets (a lot of them about version control) and 15,770 spreadsheets were actually included in the release.  This provided insights on spreadsheet usage, such as: nearly half of them contained no calculations (so why did they use a spreadsheet?) and those that did contain calculations contained an average of 585.5 mistakes each.

J.P. Morgan was considered a world leader in risk management as they had invented a number of risk management practices and tools including VaR – Value at Risk that tells financial traders the amount of risk they are running (the potential loss) on any one day against a limit set by the firm’s risk appetite.

J.P Morgan’s Chief Investment Office based in London was a paragon of risk management virtue, apparently.

However, in early 2012 that Chief Investment Office admitted losing $5.8 billion in just three months.  

The office was run by a trader variously nicknamed “The London Whale”, “Caveman” and “Voldemort” so we can assume he was no shrinking violet.

The investigation into the losses found that the investment portfolio “grew to a perilous size with numerous embedded risks that the team did not understand and were not equipped to manage.”

The “model” they used to stay within appetite was a set of spreadsheets that were not understood by anyone in the team.  The investigation reported that one calculation added two numbers together instead of averaging them and that this significantly lowered the value at risk being reported, allowing traders to take on far more risk than they should have.  Not only did they not understand the risks they were taking – and understanding is key to successful risk management – but also the spreadsheet was telling them that everything was fine.

Data Sources: The Financial Times; Humble Pi – Matt Parker

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close