The Risky Business of Spreadsheet Errors

Spreadsheets create an illusion of orderliness, accuracy, and integrity. The tidy rows and columns of data, instant calculations, eerily invisible updating, and other features of these ubiquitous instruments contribute to this soothing impression.

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.

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/).