Panko: What We Know About Spreadsheet Errors

Ray Panko, “What We Know About Spreadsheet Errors,” Journal of End-User Computing (10:2), Spring 1998, pp. 15–21. Revised May 2008. Downloaded 2009.01.13 from http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htm.

Everybody uses spreadsheets. Everybody screws them up.

Spreadsheets are “scratchpad” applications, so what do you expect? You don’t subject your scratchpad activities to rigorous development methodologies. You don’t plan them out or document what you’re doing. You doodle and jigger and get what you need. That’s not a problem; that’s creative problem-solving.

The problem comes when people turn their “scratchpads”—in this case, their spreadsheets—and turn them into the basis for important business decisions. The other software we use to make big decisions has probably been through a more typical software development routine to check for errors. Not those spreadsheets.

Panko reviews several studies and finds spreadsheet error rates generally high. Panko finds the probability that a spreadsheet will contain an error to be in the 80-90% range, though he points to several studies and interviews reporting errors in almost every spreadsheet reviewed. The cell error rate doesn’t look nearly as bad: Panko finds a range in the low single digit percents, which compares reasonably with error rates in other tasks (0.5% for “simple mechanical tasks,” 5% for “complex logical activities” like writing computer programs). However, consider what happens when you plug even those low numbers into the following “bottom line error rate” formula:

E = 1 – (1-e)ⁿ

where:

• E = bottom line error rate (the probability of an error in the final result)
• e = task error rate (the probability of screwing up at each step)
• n = number of task steps.

Even a pretty good error rate of 0.5%, extrapolated over a sufficiently large spreadsheet, makes it a safe bet that your spreadsheet’s final numbers are wrong. (139 steps, each with a 0.5% chance of error, mean you have just better than 50% chance of an error in your bottom line.)

So, how not to screw up?

• “Four eyes forever”: Have someone else look at your work. Better yet, have a couple-three people check your work.
• Check your formulas: PV() or FV()? Are those the right arguments for IRR()?
• Document, document, document: Have you ever looked back at one of your spreadsheets and wondered, “What the heck was I thinking?” I have. So will the other people who work with your spreadsheet. Programmers comment their code; you should comment your spreadsheet.

You can do almost anything worth doing with a good spreadsheet. But to err is human, and spreadsheets, like any computer app, make it possible to err bigger and faster than ever before. Go ahead, use that spreadsheet for mission-critical decisions. Just check it, just like you would anything else.

Spreadsheets: “Dark Matter” of IT

Ray Panko heads an AMCIS 2009 mini-track on spreadsheets. In his call for participation, he refers to spreadsheets as the “dark matter” of IT:

Until recently, the spreadsheet was the Rodney Dangerfield of corporate IT. I got little respect. Spreadsheet applications were believed to be numerous but collectively unimportant from a strategic point of view. Recently, however, pressure from compliance laws have forced organizations to examine how their key business processes really use IT. What they have almost always found were spreadsheets—large numbers of massive, complex, and mission-critical spreadsheets—all developed by end users. They found that many processes use spreadsheets predominantly for their IT, and even processes that use packaged applications often use spreadsheets for the riskiest computations, such as end-of-period adjustments in corporate financial reporting. It now appears that spreadsheets are the dark matter of IT—larger collectively than traditional central applications in terms of computerized units of core business logic, yet invisible to IT (“It’s a business side thing”) and also to corporate management. Spreadsheets are also dark in the sense that research has shown that errors in corporate spreadsheets seem to be nearly universal. In addition, spreadsheets are an enormous liability for corporate efforts to protect personally sensitive information and trade secrets. At the same time, spreadsheets seem to be the dark energy of corporate IT, spreading information technology far beyond central computing to nearly every business function. IT professionals who believe that they can solve control problems by banning spreadsheets are at best misinformed [emphasis mine; Ray Panko, "Spreadsheets: The Dark Matter and Dark Energy of IT," AMCIS 2009 mini-track description, 2008.12.07, downloaded 2009.01.13 from http://panko.shidler.hawaii.edu/SSR/SSMinitrackAMCIS2009.doc]

In an interesting paper on spreadsheet errors, Panko refers to spreadsheets as “scratchpad” applications. They’re everywhere, on everybody’s computer. Did you really expect people not to be using it for all tasks great and small?

Your IT department might think spreadsheets are just Minesweeper with stats functions, a minor distraction for folks who aren’t real coders and IT managers. But all those people in your office who aren’t real coders and IT managers are busy using spreadsheets to work around the fancy, incomprehensible software IT installed last month and get their jobs done. Better know your spreadsheets!