London Whale, Austerity, and Excel Errors.  Oh My.

Excel in the News for the Wrong Reasons

A number of friends and readers have been sending me links lately about some high-profile mistakes made with Excel.  I particularly like the one above where Excel is shown as a massive meteorite smashing into the Earth.

Since I am sure some of you are getting sent the same links, and sometimes in a not-so-subtle, snarky way, I thought it would be good to provide my opinion – maybe it will give you something constructive to say in response to them.

1) There’s a BIG Difference Between Analysis/Reporting vs. Simulation

Excel Simulations:  Heavy on Formulas.  Light on Data.

Simulation-Style Workbooks Operate Like This: Small Amount of Data, Lots of Formulas

 

It’s funny:  Excel is used for a BROAD array of purposes but we tend to lump them all together as “Excel.”  To be honest, even I am guilty of this.

One of the articles in question (the one about the London Whale) relates how megabank JP Morgan lost a bazillion dollars on a bad bet.  It turns out that some of the finance whizzes at JPM incorrectly divided by a sum rather than an average in one part of their model.  That mistake led them to underestimate the risks involved with placing a billion dollar bet in the markets.

The purpose of that “whale” workbook was to evaluate profit/loss risks associated with bets that they COULD make.  It was NOT built to measure whether or not their existing bets were working.

And that is a crucial distinction.  Here, let’s “redo” that same diagram from above, this time for reporting and analysis:

Excel Reports and Analysis:  Heavy on Data.  Lighter on Formulas.

Reporting and Analysis Workbooks Operate Like This:  “Large” Amounts of Data,
Small Amounts of Formulas

2) The Opportunity for Mistakes is MUCH Greater in Simulations

Generally speaking, there aren’t any “moving parts” in data.  The risk that a mistake occurs in the data itself, while not zero, is very small compared to the risks incurred when you write formulas.

Research has shown that no matter how good we are, we tend to make mistakes about 1% of the time – the article referenced here (which is fascinating reading) shows a rate somewhere between 0.87% error rate and 1.79% error rate – per cell!

Let that sink in for a moment:

In a tool like Excel or a programming language like C++, even the best of us make 1 mistake out of EVERY 100 steps/actions we take.

And what’s an action?  Basically everything is an action:

  1. Writing a formula
  2. Editing a formula
  3. Filling down a formula
  4. Copy/pasting a formula
  5. Repositioning input cells
  6. etc.

A simulation model like the “whale” model involves thousands of such steps, which means there’s a virtual certainty of multiple errors!  An analysis or report, by contrast, may only involve 20 or less such actions.

3) PowerPivot Reduces the Number of Actions Dramatically Even Compared to “Traditional” Excel Reports

OK, so reporting/analysis involves far fewer formula-related steps than simulations.  Great.

But PowerPivot reduces those steps even further, because when you write a PowerPivot formula (particularly a measure), that formula can be re-used all over the place without having to copy/paste or modify it.  PowerPivot formulas are “portable,” in other words.

I’ve written specifically about portable formulas before, so check out the article if you haven’t seen it.

So let’s recap, again graphically:

Excel Simulations/Financial Models:  Lots of Formulas Means Lots of Surface Area for Mistakes.

Very Little Safe, Lots of Unsafe

Traditional Excel Reporting:  Much LESS Surface Area for Mistakes, But Still Some.

Much Less Red (Unsafe), Much More Green (Comparatively Safe)

PowerPivot Reporting and Analysis:  Much More Reliant on Centralized, Re-Useable, "Portable" Formulas.  Far Less Mistake Surface Area than Even Traditional Reporting/Analysis.

PowerPivot Swaps Out the “Red” From Traditional Reporting and
Replaces it With “Green” Portable Formulas

4) PowerPivot Mistakes Don’t “Hide” Like Traditional Mistakes

All right.  Reports have fewer moving parts than simulations.  And a PowerPivot report has still fewer moving parts than a traditional Excel report.  But we’re not finished!

In PowerPivot, we still make 1 mistake out of 100.  (And since it’s new to us, we probably make more than that.)  But those mistakes tend to be immediately apparent.

The “London Whale” mistake lived for many months (or maybe even years) undetected.  But the average “lifetime” of a PowerPivot mistake tends to be mere seconds or minutes.  Why?  A few reasons:

  1. Measures are inherently tested, broadly and immediately, as soon as you write them.  When you write a measure, PowerPivot slaps it on a pivot.  And that means your formula gets applied in many different contexts all at once.  You see multiple “answers,” immediately, and can instantly scan and compare them.  Most formula mistakes will not survive this initial testing, because it’s actually quite rigorous.
  2. PowerPivot is a lot “stricter” about your assumptions.  If you assume that there’s only ever one value for a variable, and sometimes there are multiple values, your measure is just going to return an error.  At first this seems like a nuisance, but over time this strictness pays off.
  3. If a mistake “escapes” those first two “traps,” it still will very likely be detected as you manipulate your pivot or create new ones.
  4. Inspecting PowerPivot formulas is inherently a more centralized and readable process than inspecting/auditing traditional Excel formulas.  Compare these two:

Spot the formula error.  Ugh.

Can You Spot Mistakes in This Sea of Formulas?
It Even Uses a Few Named Ranges!  But Still…  Eww.

There Are Just as Many Formulas In This Screenshot as the Previous Screenshot, but You Can Inspect them One at a Time, Centrally, And Every One of Them is Both Named AND Uses Strictly Named Reference in the Formulas!

There Are Just as Many Formulas In This Screenshot as the Previous Screenshot, but You Can Inspect them One at a Time, Centrally, And Every One of Them is Both Named AND Uses Strictly Named Reference in the Formulas!

Even in Simulations, the Benefits Still Outweigh the Risks

Last parting shot.  Several of the articles about the Whale reference a post by James Kwok.  In that post, James says the following:

"Excel-Negative” Articles Cited This Post by James Kwok as Evidence that Excel Sucks.  But James Kwok LOVES Excel.  As he should.

“Excel-Negative” Articles Cited This Post by James Kwok as Evidence that Excel Sucks.
I Guess They Only Read the First Three Paragraphs Smile

Well said James.  Well said.  Which is worse:  the capability to build incorrect simulations, or lacking the capability to do simulations at all?

I really think that last sentence nails it, in particular.

The Articles

If you’d like to read some of the original articles, here are the links:

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 11 Comments

  1. Excel doesn’t destroy worlds, people misusing Excel do! 😉

    Watch for: mandatory Excel user registration, licenses required to Excel, Excel Free Zones around schools and universities, mandatory Excel locking devices in every box (or download), limits on how many formulas can be loaded in a spreadsheet, and daily limits on how many worksheets a user can create.

  2. Firstly, the particular spreadsheet software used in the London Whale story is irrelevant. The result would have been the same whether it was MS Excel, Google Spreadsheet, LibreOffice Calc, etc.

    Secondly, blaming the error on spreadsheet software is akin to blaming a similar error 50 years ago on the pencil. Humans need to use tools at their disposal correctly. It’s not the car’s fault if you fall asleep driving and plow into a tree.

  3. I’ve been sent these articles too, but I find the arguments against Excel weak at best. Several people kind of beat me to the punch, but blaming Excel for mistakes is like blaming a spoon for fat people, or blaming chisels for bad sculptures. It’s not like the analyst told Excel to divide by a sum, but Excel decided on its own to divide by an average (or whatever) – Excel did exactly what it was told to do, and an analyst made a mistake.

    If Excel acts on it’s own volition, then we should be preparing for Judgement Day anyway (August 29th).

  4. How could Rob “Movie Quotes” Collie neglect the first quote regarding PowerPivot and Excel? “With great power comes great responsibility.” Yes, PowerPivot can do so well, but looking at the data gathered from drug use and UFO’s, sometimes having too much data – or the wrong data, leads to the wrong answers. PowerPivot won’t cure it, but will sure make it look interesting. Also, using DAX (albeit marginally) I’ve come up with some great answers. Not saying they were always the RIGHT answers, but they looked great. Syntax.. why did it have to be syntax. I hate syntax. Like other comments, GIGO. Can PowerPivot help? Yes. Can PowerPivot hurt? Yes. But I like what you have to say. If we can all use it for the forces of good (and proper techniques), it should help us .. in the end. I just hope I don’t go down the path of the dark side.

  5. The enemy of accurate financial models* is a lack of transparency and propensity to be easily checked. Generally the culprits are my finance colleagues who seem incapable of applying best practices when building models.

    The irony of the choice of Excel model to demonstrate the point is that it a great example of best practices! It has consistent formulas across rows, judicious use of named ranges, it indicates a clear Inputs>Calculations>Outputs structure and crucially it features a slow build of the numbers avoiding mega formulas so I can easily check it using a pencil and a calculator. To the seasoned modeller it’s a bit like the Matrix, I don’t even see the code.

    In same way that I am reluctant to use array formulas or even VBA in a model, I would struggle to use a DAX formula that was further up the ‘Spicy Scale’ for this very reason.

    Having read the detailed story on the Reinhart/Rogoff issue it sounds more like a conceptual problem and a shaky methodology rather than an excel problem.

    *I use the term model to mean simulation in Rob’s parlance.

Leave a Comment or Question