Spreadsheet Nightmares
Spreadsheets are a Quick Fix
Spreadsheets provide business professionals with a quick way to get something done. There’s no waiting for committees to approve anything, no complex coding, no need to involve IT or specify requirements. It’s simple. Just fire up Microsoft Excel and start building.
Initially, spreadsheets are great. You see results instantly and can gradually add more and more as you go along. But as soon as your spreadsheet is shared, it stops being a personal spreadsheet and becomes an end-user developed application.
IT application development has matured to ensure high levels of quality, reliability, and accuracy. But when spreadsheets are shared or become complex they soon become unmanageable and error-prone — and should never be relied on for important business processes.
Yet organizations large and small, prestigious and humble, global and local do rely on spreadsheets to gather and process information — and have experienced the consequences…
Flaws Undermine Influential Economic Study
Harvard University
“Growth in a Time of Debt” by Harvard economists Carmen Reinhart and Kenneth Rogoff is an important study on the relationship between national debt and GDP growth. Leading politicians have cited this paper to justify their arguments for and against austerity measures.
Their spreadsheet contained a number of “coding errors”, specifically a formula to calculate averages for 1946-2009 omitted the first five countries in the alphabet.
University Loses $2,400,000 Projected Revenue
University of Toledo
An internal budgeting error meant that the University of Toledo had $2.4 million less than expected.
Instead of student numbers decreasing, “an increase mistakenly was shown in a spreadsheet formula that led officials to overestimate enrollment and therefore revenue.” explained William Decatur, SVP for finance, technology, and operations.
Two Finance Officals Resign Over $25M Error
Utah State Office of Education
Utah’s education budget was left $25 million short after a mistake underestimated the anticipated number of students.
State Superintendent Larry Shumway blamed the mistake on “a faulty reference in a spreadsheet”. Two official resigned over that mistake, including associate superintendent, Todd Hauber, who said “he didn’t feel he had adequate time to review each formula and spreadsheet.”
Loan Repayments Underestimated by $400,000
West Baraboo Village Board
West Baraboo faces $2 million of debt payments instead of the expected $1.6 million, after a spreadsheet error was discovered.
The village’s financial adviser said, “When we re-looked at the numbers we discovered a cell was not across the line being added correctly. So it understated the impact.”.
Examiners Mix Up Candidates’ Marks
Oxford University
Oxford University’s entrance process for prospective History students was delayed by mistakes transcribing candidate numbers into spreadsheets.
A spokesperson from Oxford University Press Office said, “Of course what we should have done was devote several hours to checking every single candidate number and set of marks between the big spreadsheet, the markers’ spreadsheets, and the original script cover sheets, to detect these errors.”
$1.26 Billion Taxable Property Overlooked
Kern County, California
Kern County issued Occidental Petroleum a property tax bill that excluded 25,000 acres of valuable oil field. If the mistake had not been spotted it could have cost taxpayers $12 million.
County Assessor-Recorder Jim Fitch blamed the mistake on a “clerical error by an experienced staffer who used the wrong spreadsheet to calculate Occidental Petroleum Corp.’s tax bill for property”
Error Causes $24M Power Overspend
TransAlta
Canadian power generator, TransAlta, bought more power transmission hedging contracts from US at higher prices than it needed.
CEO Steve Synder said it was due to“literally a cut-and-paste error in an Excel spreadsheet”
$6,000,000 Cash Under-Reported
Knox County Trustee’s Office, Tennessee
External auditor, KPMG, discovered that information supplied by Knox County’s Office “was significantly out of balance.”
One account with $6,000,000 cash was omitted due to an error in an Excel spreadsheet.
Knox County were required to pay additional audit charges and were expecting to receive a “significant deficiency” in the audit report.
Accidentally Buying 179 ‘toxic’ Lehman Contracts
Barclays Capital, Lehman Brothers
Following the collapse of Lehman Brothers, Barclays wanted to purchase some of Lehman’s contracts. They sent a spreadsheet for their lawyer to submit to the bankruptcy court.
“The Excel file contained 1,000 rows of data and 24,000 cells. Some of the trading contracts were marked as hidden because they were not intended to form part of Barclays’ proposed deal”
However these ‘hidden’ contracts were included when the spreadsheet was converted to PDF and approved by the judge as part of the deal.
$1.136 Billion Error in Shareholder Equity
Fannie Mae
While Fannie Mae updated their systems to meet new accounting standards, their Finance had to use spreadsheets to calculate the financial results. These results had to be quickly restated when a $1.136 Billion error in total shareholder equity was discovered.
Jayne Shontell, Fannie Mae senior vice president for investor relations, explained “There were honest mistakes made in a spreadsheet.”
Inadvertent Release of Confidential Data
AstraZeneca
AstraZeneca had to restate financial forecasts after it accidentally disclosed confidential planning information during “a routine consensus collection process”.
“Confidential company information was inadvertently embedded in a spreadsheet template sent to the sell-side analyst community that follows the company”.
Most Large Spreadsheets Have Multiple Errors
Academic Paper, University of Hawaii
Studies repeatedly show that large spreadsheets contain serious errors and that larger, complex spreadsheets have higher error rates. Over-confidence is widely reported as a significant factor.
The author concludes “the research done to date in spreadsheet development presents a very disturbing picture. Every study that has attempted to measure errors, without exception, has found them at rates that would be unacceptable in any organization. With such high cell error rates, most large spreadsheets will have multiple errors, and even relatively small ‘scratch pad’ spreadsheets will have a significant probability of error.”