Ivars Peterson's MathTrek
At the same time, faulty spreadsheets and poor spreadsheet practices have been implicated in a wide variety of business and financial problems. An incorrect number in one cell throws off future revenue estimates, wreaking stock market havoc. An incorrect formula produces inflated salaries. Too many zeros are added to an employee's accrued severance statement. The results of a test are incorrectly sorted, mismatching names and scores. Fake data fill in for missing entries, jeopardizing a multimillion-dollar court case. A cut-and-paste error alters bid rankings, leading to contracts at higher prices than warranted. And so on and on and on.
"Spreadsheets have been shown to be fallible, yet they underpin the operation of the financial system," Grenville J. Croll reported last summer at a conference organized by the European Spreadsheet Risks Interest Group (EuSpRIG). Croll is managing director of Frontline Systems UK and has studied the City of London financial markets and their heavy dependence on spreadsheets for a wide variety of purposes.
"It is completely within the realms of possibility that a single, large, complex but erroneous spreadsheet could directly cause the accidental loss of a corporation or institution," Croll added. "If the uncontrolled use of spreadsheets continues to occur in highly leveraged markets and companies, it is only a matter of time before [some random] event occurs, causing catastrophic loss."
The situation isn't much better in the United States. However, the Sarbanes-Oxley Act of 2002, which set new standards for corporate financial integrity, has focused attention on the inherent risks of using Microsoft Excel and other spreadsheet programs for financial records, planning, and decision-making. Ironically, many companies are using spreadsheets, which may themselves be flawed, in trying to document compliance with the act's requirements.
Croll argues that, in general, even the simplest spreadsheet calculations aren't properly tested or checked in most companies. Untested spreadsheets are riddled with errors, he says, often because the wrong formula was entered in the first place.
It's not uncommon, for example, to find a 5-percent cell error rate.
Flawed mathematical models, the huge size and complexity of many financial spreadsheets, lax security, inordinate ease of access and use, and other factors all contribute to these vulnerabilities, whether through fraud or by accident.
"With the exception of quantitative finance professionals working as software engineers, there is almost no spreadsheet software quality assurance . . . ," Croll contends. "Spreadsheets built using well-engineered code libraries were inevitably tinkered with later by traders, salespeople, analysts, and other users in an uncontrolled fashion."
From his studies, Croll concludes that the "people who create or modify spreadsheets in the financial markets are almost entirely self-taught."
It all adds up to a recipe for financial disaster.
A possible antidote? Founded in 1999, EuSpRIG is a group of individuals in business and academia that promotes research on the extent and nature of spreadsheet risks, methods for preventing and detecting errors, and techniques for limiting damage (see http://www.eusprig.org/).
Copyright © 2005 by Ivars Peterson
Chadwick, D. 2003. Stop that subversive spreadsheet! European Spreadsheet Risks Interest Group. Available at http://www.eusprig.org/eusprig.pdf.
Croll, G.J. 2005. The importance and criticality of spreadsheets in the City of London. Eusprig 2005. July 7. Available at http://www.eusprig.org/tiacositcol4.pdf.
O'Beirne, P.R. 2005. Spreadsheet Check and Control; 47 Key Practices to Detect and Prevent Errors. Systems Publishing. See http://www.sysmod.com/scc.htm.
Peterson, I. 1996. Fatal Defect: Chasing Killer Computer Bugs. New York: Vintage.
The European Spreadsheet Risks Interest Group (EuSpRIG) has a Web site at http://www.eusprig.org/.
Spreadsheet Research, maintained by Ray Panko of the University of Hawaii, is a repository for research on spreadsheet development, testing, use, technology: http://panko.cba.hawaii.edu/ssr/.