Spreadsheets: 25 Years in a Cell

Now in their 25th year, spreadsheets ignore the uncertainty of forecasts and can distort our thinking if not considered carefully, Peter Coffee writes.

In this 25th anniversary year of the PC spreadsheet, we can be proud of the progress weve made in decision technology. We can also be appalled by the stagnation of our decision-making practices. The things we learned to do badly in 1979, upon the debut of VisiCalc, we mostly continue to do wrong today.

IT observer Stan Kelly-Bootle described in 1995 the impact of VisiCalc and its descendants: "The PC soon blossomed as the Uzi of creative corporate accounting," he wrote. "The What-If moved to Why-Not, indicting the spreadsheet as the chief culprit in the 1980s S&L scandal."

Kelly-Bootle was talking about the ease with which we slide our assumptions toward their optimistic limits, inching good numbers up and bad numbers down until we get the result we want—failing to admit that the result is based on multiplying a series of less-than-even chances.

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, theres 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.

There are add-on products that incorporate uncertainty into spreadsheets, and many of them are quite good. Products of this kind that Ive favorably reviewed over the years include DecisionTools Pro from Palisade and Crystal Ball Professional and CB Predictor from Decisioneering. Its not too hard to appreciate the difference between products that incorporate uncertainty and those that dont: On the one hand, youve got, "We predict a $1 million profit in the first year"; on the other, "The expected Year 1 profit is $1 million, but theres a 30 percent chance of losses for the first two years." These different statements will lead to quite different discussions.

The second distortion caused by conventional spreadsheets is more subtle. Its described in a 1980s paper, written by university researcher Jeffrey Kottemann and others concerning what they called "Performance, Beliefs, and the Illusion of Control." The paper described an experiment in which subjects were asked to perform a planning task using different tools, some of them with elaborate what-if capability and others without it.

The subjects whose tools invited them to imagine alternative scenarios believed they were doing a better job—even though statistical measures of their results showed no improvement in the actual quality of the forecasts. Those subjects did, however, take longer to perform the task. Isnt that the worst nightmare of those who must justify ITs return on investment—spending extra money on a more time-consuming product that yields absolutely no measurable improvement?

If we were starting from scratch, with the hardware power and the pervasive connectivity of today, I suspect wed build something that looks a lot more like the Projected Financials product from Whitebirch Software. You can try before you buy with a guest account at Whitebirchs site, but, essentially, the product deals with financial objects rather than individual numbers. A revenue stream, for example, has certain characteristics, such as when it starts and what trends it exhibits. A financial statement aggregates some numbers, such as monthly profits that sum to yearly profits, but reports others, such as accounts receivable, as levels rather than flows.

The higher-level approach of the Whitebirch product encourages higher-level thinking about assumptions and uncertainties, and it makes it easier to construct more complete and realistic models—ones that reflect seasonal trends, market saturations and other messy but important behaviors that ordinary spreadsheets only model with great effort. Such nuanced modeling encourages decision makers to spend more time acquiring better knowledge and less time trying to spin the straw of ignorance into the gold of brilliant foresight.

Spreadsheets were a remarkable innovation, the tipping-point product for putting PCs on professional desktops. Their familiarity need not breed contempt—but neither should it lead us to ignore the spreadsheets limits.

Technology Editor Peter Coffee can be reached at peter_coffee@ziffdavis.com.

/zimages/3/28571.gifCheck out eWEEK.coms Database Center at http://database.eweek.com for the latest database news, views and analysis. Be sure to add our eWEEK.com database news feed to your RSS newsreader or My Yahoo page: /zimages/3/19420.gif http://us.i1.yimg.com/us.yimg.com/i/us/my/addtomyyahoo2.gif