Thursday, April 01, 2004

Can’t Model Uncertainty in Excel

You can’t model uncertainty in Excel! And that’s a problem! [Thanks to Martin Geddes for the link]

There are two ways that spreadsheets, as we know them, distort our thinking and lead to bad decisions. The first distortion is the use of point values and simple arithmetic instead of probability distributions and statistical measures. So far as I know, there’s no off-the-shelf spreadsheet product - certainly none in common use - that provides for input of numbers as uncertain quantities, even though almost all of our decisions rest on forecasts or on speculations.

In a world where our software development methods need to embrace uncertainty and cope with common cause variance, our project managers are trained to use a tool which does not understand the meaning of uncertainty or variance. Excel is the weapon of deterministic practices against the insurgence of probabilistic principles.

[Update April 8th] David Carter wrote to me with this…

I wanted to post a comment on your latest weblog entry on Excel & uncertainty, but can’t seem to do so. There are add-in products available for Excel (and MS Project) that let you model with uncertainty.  @Risk is one that’s been around for quite a while - I used it when I was working on my MBA from 1988-1991. http://www.palisade.com/html/risk.asp

[Update April 9th] Jack Vinson wrote…

I am familiar with Crystal Ball, which is an Excel plugin that lets you do Monte Carlo-type simulations. It lets you specific a distribution on just about any variable and then runs through simulations to give you distributions of the results. I haven’t looked at the product in many years, but five years ago they had a nice range of distribution models to choose and you could pick a different one for every variable.