Spreadsheet Furore

Every so often, we get a news tempest about an error in a spreadsheet. What seems odd to me is that no one points out that there is a fundamental error in all spreadsheets, a gaping hole that is far more serious for Decision Excellence than the errors people sometimes make.

Spreadsheets don't tell you what you need to know.  

And even if they are correct, they are lying.

All right, I accept that in some circumstances this may not be the case. If you are talking about things like your bank balance, where there is complete information about all the quantities involved, then they can be honest and useful.

The trouble is, they are used to take decisions, and this is where they are letting us down, badly. 

Every decision is a bet, and what is the first thing you need to know before you place a bet? It's the odds, the probabilities, or the term I prefer, the predictability.  

You know very well that it would be a total miracle if the actual number came out to be the same as that in the forecast. So why doesn't the spreadsheet give you information on predictability - the chance that each different possible outcome will come to pass?  For example, the chance that the costs will be greater than the benefits.

It really hurts my head that it is taking SO LONG for businesses to get the capability to manage predictability.  The mathematical methods have been around since the 1950s, and scientists and engineers use them all the time.  

Why is this?  I can think of several reasons. 

First, we don’t tell our children the truth.  We teach them that 2 + 2 is always 4.  But 2 + 2 = 4 is a special case; it only holds when the symbols represent definite quantities.  When they represent indefinite quantities, when there are ranges of possible values for each quantity, 4 is the wrong answer!

Suppose the first “2” in your business case represents infrastructure costs, and that quantity is in the range 1.9 to 2.4 and the second “2” represents application costs, and it has a range 1 to 6.

Then the answer 4 is wrong both numerically and conceptually.  Numerically, 4 is not the most likely sum, and conceptually, it is the range of the sum we need, not just one value from that range.

The second answer is we let people who don’t know this basic truth design our IT systems.  As a result, our databases and spreadsheets all have little boxes in them, and those boxes can only handle definite quantities.

Then to top it all off, we don’t educate our accountants about this. We set them up with bad mathematics, and bad tools, and then we put them in charge!

So we’re stuffed – there’s no way, with the data we have and the tools we have, to get the answers we need! 

Actually, there are tools out there that can do the job, they are just too hard to use. The good news is this can easily be fixed, using Applied Information Economics. Let me explain how.