Who is Raymond Panko… and why should you care?

In recent years, we have learned a good deal about the errors that people make when they develop spreadsheets. In general, errors seem to occur in a few percent of all cells, meaning that for large spreadsheets, the real issue is how many errors there are, not whether an error exists.

Most studies looked at errors at the end of the development stage, when subjects said that they were finished developing their spreadsheets. Cell Error Rates (CER) across these studies were similar, despite the fact that subjects ranged from novices to highly experienced spreadsheet developers. One study even compared undergraduate business students, with little spreadsheet developing experience, and MBA students with more than 250 hours of spreadsheet development experience. Their Cell Error Rates were very similar.

Even when a task was selected to be very simple and almost completely free of domain knowledge requirements, the Cell Error Rate was about 2%.

Respondents were keenly aware of the dangers of error and spent considerable time working to reduce errors, including the creation of cross-footings, doing spot checks of formulas with calculators, and having others look at output. However only 1 of the 11 interviewees said that they had done a complete code inspection of a spreadsheet; and in that case it was a spreadsheet given to that person by someone else!

Later, Hendry and Green did a similar study, this time with 10 spreadsheet developers. They added a phase in which the subject walked through a spreadsheet with an author. They found that subjects often had a difficult time explaining parts of the spreadsheet that they had themselves built.

They also found that some of the spreadsheet developers had run into serious problems trying to make parts of the spreadsheet analysis fit the row-and-column layout of spreadsheet program.

We are overconfident about the accuracy of spreadsheets, despite the large amount of information to the contrary. One study gave three spreadsheet development tasks to nine highly experienced spreadsheet developers, all made at least one error, not all of which were caught and corrected. In fact, 63% of the final spreadsheets overall had errors. Yet when asked about their confidence in the correctness of their spreadsheets, their median score was “very confident.”

Overconfidence is corrosive because it tends to blind people to the need for taking steps to reduce risks.

Whatever specific techniques of improvement are used, one broad policy must be the shielding of spreadsheeters who err from punishment because a climate of blaming will prevent developers from acknowledging errors. Quite simply, although the error rates seen in research studies are appalling, they are also in line with the normal accuracy limits of human information processing. We cannot punish people for normal human failings.

To read more, including the author’s suggestions for mitigation, please review the article >>>

What We Know About Spreadsheet Errors

By Lawrence Serven, from: http://blog.xlerant.com/Blog/bid/71742/Who-is-Raymond-Panko-and-why-should-you-care

Lawrence Serven is co-founder of XLerant, Inc. Lawrence, the author of Value Planning: The New Approach to Building Value Every Day (John Wiley & Sons) brings his passion for best practices to bear in helping organizations find solutions to their budgeting and planning challenges. He combines real world experience as a planning executive for Fortune 500 companies, and his consulting experience as a practice leader at Deloitte Consulting and founding partner at The Buttonwood Group, to bring innovation and subject matter expertise to developing financial software. His work has been profiled in The New York Times and Harvard Business Review, and is a frequent contributor to CFO Magazine and other leading business publications.