Spreadsheet typos and oversights can wind up costing your company millions. Here’s a look at eight big mistakes, and tips on how to prevent them from happening at your company.
Spreadsheet errors can happen to the best of us. As a result, many public companies and government organizations are trying to wean themselves off their reliance on spreadsheets for complex and critical financial transactions.
Of course, to achieve such a goal, organizations need all the help they can get. Most businesses today rely on spreadsheets in some way. The multi-celled document is used heavily for finance and accounting, as well as supply chain, customer relationship and sales functions.
However, recent financial regulations, such as Sarbanes-Oxley requirements, have had a huge impact on how companies manage changes and controls in financial documents, such as spreadsheets. Because of their preponderance and the amount of digital fingertips that can touch these documents, spreadsheets have come under a lot of fire. In particular, companies lack the appropriate controls and repeatable processes to mitigate the risks.
This isn’t about software defects within the applications, such as Microsoft Excel or OpenOffice. The problems associated with a spreadsheet ordinarily do not reside in the software program itself. It’s those imperfect human beings who are using the applications: inputting data, copying and pasting numbers from row to row and column to column, and writing inaccurate formulae.
Research abounds on the prominence of spreadsheet errors. One project found that 80 percent of spreadsheets contain significant errors. “That means that of every five spreadsheets, at most one will give the correct results,” writes Louise Pryor, an actuary and consultant who specializes in software risk management. (A PDF document by Pryor contains tips for managing and preventing spreadsheet problems.)
The European Spreadsheet Risks Interest Group (EuSpRIG), a consortium of academics, researchers and professionals who examine spreadsheet risks and develop methods for prevention, holds a conference each year to talk about the spreadsheet’s inherent dangers to organizations. (Last July’s get-together was titled “Enterprise Spreadsheet Management: A Necessary Evil?”)
“Research has repeatedly shown that an alarming proportion of corporate spreadsheet models are not tested to the extent necessary to support directors’ fiduciary, reporting and compliance obligations,” says the EuSpRIG website.
“Uncontrolled and untested spreadsheet models therefore pose significant business risks. These risks include: lost revenue and profits; mispricing and poor decision making due to prevalent but undetected errors; fraud due to malicious tampering; and difficulties in demonstrating fiduciary and regulatory compliance.
“These risks are ignored due to a widespread failure to inventory (keep records of), test, document, backup, archive and control the legions of spreadsheets that support critical corporate infrastructure,” says the site.
In addition to research and prevention duties, EuSpRIG pulled together an extensive list of more than 80 public reports of spreadsheet errors from business, government and academia.
For both your sinister viewing pleasure and as a warning of what can go wrong without proper controls, here’s a chronological list of eight of the more infamous spreadsheet transgressions, compiled from EuSpRIG, media reports and public documents, which are linked to at end of each entry.
1. Fidelity’s “Minus Sign Mistake”
Lesson learned: Be sure to differentiate your gains from your losses, and have another employee review the work.
January 1995
“There was a big flap recently over Fidelity’s Magellan fund estimating in November that they would make a $4.32/share distribution at the end of year, and then not doing so. A letter of explanation was sent to the shareholders…from J. Gary Burkhead, the President of Fidelity, including the following pertinent items: During the estimating process, a tax accountant is required to transcribe the net realized gain or loss from the fund’s financial records (which were correct at all times) to a separate spreadsheet, where additional calculations are performed. The error occurred when the accountant omitted the minus sign on a net capital loss of $1.3 billion and incorrectly treated it as a net capital gain on this separate spreadsheet. This meant that the dividend estimate spreadsheet was off by $2.6 billion…”
—From the Risks Digest
2. The $24 Million “Clerical Error” at TransAlta
Lesson learned: Have another employee double-check the documentation.
June 2003
A simple spreadsheet error cost a firm a whopping $24 million. The mistake led to TransAlta, a big Canadian power generator, buying more US power transmission hedging contracts in May at higher prices than it should have.
In a conference call, chief executive Steve Snyder said the snafu was “literally a cut-and-paste error in an Excel spreadsheet that we did not detect when we did our final sorting and ranking bids prior to submission,” Reuters reports.
This looks like a career limiting move by the person who made the cock-up and the people who failed to spot it. Snyder said the company would “deal with the individuals in the appropriate fashion if there is anything found. At the end of the day it’s a simple clerical error.”
—From The Register
3. Fannie Mae Discovers $1.3 Billion “Honest” Mistake
Lesson learned: When billions are at stake, it’s best to have a financial peer review the documentation.
October 2003
Fannie Mae, which finances home mortgages, stated in a news release of third-quarter financials that it had discovered a $1.136 billion error in total shareholder equity. Jayne Shontell, Fannie Mae senior vice president for investor relations, explained in a written statement, “There were honest mistakes made in a spreadsheet used in the implementation of a new accounting standard.”
—From PC World
4. University of Toledo Loses $2.4 Million in Projected Revenue
Lesson learned: Future-looking financial statements should have extra scrutiny and review. User training would also help prevent these formula-based errors.
May 2004
Already facing significant state funding reductions for next year, UT officials have discovered an internal budgeting error that means they will have $2.4 million less to work with than anticipated. The mistake—a typo in a formula that led officials to overestimate projected revenue—was found Tuesday…. The budgeting error discovered this week was made in the institutional research office by an employee whom officials refused to identify. While official UT projections call for a 10 percent decline in graduate student enrollment, an increase mistakenly was shown in a spreadsheet formula that led officials to overestimate enrollment and therefore revenue, Mr. Decatur said.
[President Daniel] Johnson said no job action will be taken against the employee who made the mistake, who has a good performance record. Officials will, however, pursue systemic changes to provide more safeguards in the future.
“We have very competent people,” Dr. Johnson said. “I do think that the continuing fiscal pressures on universities have forced us to a level of staff support where there is little or no redundance in the process.”
—From The Toledo Blade
5. RedEnvelope Skids on Loss Forecast and Budgeting Error
Lesson learned: Quality control is king, especially when reporting to The Street.
March 2005
Shares of RedEnvelope Inc. lost more than a quarter of their value Tuesday after the company warned of a fourth-quarter loss due to weak Valentine’s Day sales and a budgeting error that resulted in an overestimation of gross margins. [The company] said its chief financial officer, Eric Wong, had resigned.
“While the concurrent preannouncement and Wong’s departure may suggest management believes Wong’s replacement remedies the situation, we are not yet convinced the weaknesses are solely related to Wong,” analyst Rebecca Jones Kujawa wrote in a research note.
News outlets reported that RedEnvelope spokeswoman Jordan Goldstein said the budgeting error was simply due to a number misrecorded in one cell of a spreadsheet that then threw off the cost forecast and was unrelated to the CFO change.
—From Marketwatch
6. “Think-and-Do Tank” Flubs the Math
Lesson learned: Have another employee double-check the work.
May 2005
The Center for Regional Strategies recently confirmed that a researcher’s errant cut-and-paste from a spreadsheet caused one measure of the region’s level of educational attainment to appear a lot worse than it is. Specifically, in a study released in March by the Center for Regional Strategies, a self-described “think-and-do tank” housed at Virginia Tech, the center reported that a dismal 11 percent of the region’s population older than 25 had bachelor’s degrees or higher. That number should have been 20 percent.
“It was just a simple cut-and-paste error,” said Stuart Mease, a spokesman for the Center for Regional Strategies. “I don’t know how it happened, but it did. We apologize for our mistake and want to correct it.”
—From The Roanoke Times
7. Kodak Restates, Adds $9 million to Loss
Lesson learned: Lack of data-quality controls can have a negative outcome.
November 2005
Robert Brust, Kodak’s chief financial officer, said that the severance-related error stemmed from miscalculating severance pay accrued by just one employee, revealing what he called “an internal control deficiency that constitutes a material weakness that impacted the accounting for restructurings.” Brust said the company expects to fix the problem by year-end.
Kodak spokesman Gerard Meuchner said the hefty $11 million severance error was traced to a faulty spreadsheet. “There were too many zeros added to the employee’s accrued severance. But it was an accrual. There was never a payment,” he said.
—From Marketwatch
8. Westpac Jumps the Gun on Profit
Lesson learned: You can never get enough spreadsheet and data-handling training.
November 2005
Westpac was forced to halt trading on its shares and deliver its annual profit briefing a day early after it accidentally sent its results by email to research analysts. Details of the $2.818 billion record profit result for the 12 months to September 30…were embedded in a template of last year’s results and were accessible with minor manipulation of the spreadsheet. (Some news reports indicated an employee had thought that a black cell background fill would hide black text.)
Westpac CFO Philip Chronican said, “It is not just one error, it is a compounding of two or three errors…. We will obviously be conducting a full inquiry to make sure it doesn’t happen again.”
—From The Sydney Morning Herald
Can Software Trump Wetware?
Microsoft’s Excel is the most widely used spreadsheet application. Chris Caren, the general manager of Office business applications at Microsoft, says Microsoft Office (which includes Excel) can be found on some 450 million desktops today.
Caren says he’s aware of the risks of spreadsheet mistakes—what he terms “errors of input.” He says Microsoft has tried to address these with new features in Microsoft Office 2007 and the Microsoft Office PerformancePoint Server. For example, instead of a sales manager having to manually cut and paste data from document to document, he can now have an application do it for him. “It’s relying a lot less on the individual, especially when he is working with complex formulas,” Caren says.
According to Caren, historically, CIOs’ main gripes with using Excel spreadsheets for critical financial data could be summarized with a couple of reasons. First, CIOs worry about access to confidential information, either on a laptop that is not safely stored on a server or as data in a spreadsheet (such as payroll information) that is freely available to other employees. Also, because users can have different snapshots of data in the spreadsheet, “They all think they’re operating off the same thing,” says Caren—but each user is looking at a different version.
Caren notes that Microsoft has made significant investments in fixing those problems, and the recently released Microsoft SharePoint and Office 2007 have substantial version-control features.
If you’re sweating after reading this, first, take a deep breath, and second, initiate a conversation with your CFO about what controls are in place to make sure these kinds of events don’t happen to your company.
Courtesy: cio.com