Have you ever spotted an error in a spreadsheet?
Errors in spreadsheets are common, with more than 90% of spreadsheets containing errors. These errors remain undetected as spreadsheets are rarely tested. Research has shown that 50% of spreadsheet models used in large businesses have material defects. Organizations rely on spreadsheets for complex data management and decision-making. It is important to be aware of the risks associated with spreadsheet errors. Errors can lead to million-dollar mistakes and public embarrassment and find ways to eliminate the risk.
Many businesses have experienced public embarrassment and financial loss due to spreadsheet and data errors. As a result, they’ve had to adjust their processes by getting proper training for their staff members. Here are 16 significant examples:
Spreadsheet Error by State of Virginia Overestimates Available Local School Finding by Over $201 Million
The Virginia Department of Education has acknowledged an error in a mathematical formula in an Excel spreadsheet provided to local K-12 schools. The mistake led to an overestimation of state funding by a difference of $201 million over two fiscal years. Charles Pyle, a department spokesperson, stated the mistake had no impact on actual funding and that school divisions are receiving all aid authorized under the state budget. Democrats in the General Assembly criticized the department for the error and expressed their desire to make school districts whole, while Republican House Appropriations Chair, Barry Knight, also expressed his displeasure. Solutions are being sought by the General Assembly and the Governor.
-From AP News
Public Health England: Incorrect Excel Usage May Have Caused Loss of 16,000 COVID Test Results
Public Health England (PHE) recently made a significant mistake with its Covid-19 test results. PHE set up an automatic process to pull the test results into Excel templates before uploading them into a central database. Without properly anticipating their needs, they chose XLS, an outdated file format that can only hold about 65,000 rows of data, a far cry from Excel’s 1 million row capacity. Unfortunately, a large amount of data was not preserved in the system due to insufficient storage, thus 15,841 positive tests were not reported on the official daily figures. This means that up to 50,000 potentially infectious people could have flown under contact tracers’ radar and weren’t instructed to self-isolate.
— From The Guardian
The Impact of a Minus Sign: How Fidelity’s $2.6 Billion Mistake Changed the Game for Magellan Fund Investors
Fidelity’s $2.6 Billion Minus Sign Mistake is a cautionary tale of how a small character can impact numbers and results. In late 1994, the multi-billion dollar Magellan fund of Fidelity incorrectly announced that they would make a $4.32/share dividend distribution when the true number should have been $0. The mistake was made when an in-house tax accountant failed to include the minus sign on a net capital loss of $1.3 billion, incorrectly turning it into a net capital gain and miscalculating the dividend estimate by a whopping $2.6 billion. Oops!
—From the Risks Digest
Fannie Mae Sees Unexpected $1.1 Billion Drop in Shareholder Equity After ‘Honest’ Mistake
In 2003, Fannie Mae, one of the largest mortgage financiers in the world caused concern among investors and regulators after announcing a $1.1 billion revision to its balance sheet. The modifications did not impact its income statement, according to the company. The changes led to a $1.7 billion growth in its mortgage portfolio, a $1.04 billion increase in its total assets, and a $1.3 billion rise in its unrealized gains on certain securities. This mistake resulted from an “honest error” made in a spreadsheet used to implement a new accounting standard.
— From NYTimes
How a Manual Excel Error Cost JP Morgan Over $6 Billion
The 2012 London Whale debacle at JP Morgan made headlines around the world at the time of occurrence. Trader Bruno Iksil was dubbed the ‘London Whale’ after accumulating vast quantities of oversized Credit Default Swaps (CDS) as part of a bank’s hedging strategy. When hedge fund insiders began to observe Warren’s aggressive trading habits impacting the CDS market, pandemonium broke out and caused an immediate loss of $2 billion for the firm. A few months later, the loss was reported to be above $6 billion. After investigations, it was discovered that JP Morgan relied on a Value at Risk model (VaR) to generate their hedging strategies. The manual process of copying and pasting data across spreadsheets caused the formulas to be incorrectly adjusted as they divided rates by their sum instead of their average. This unfortunate oversight had significant implications for the efficacy of the bank’s strategy. The London Whale debacle may have been far less damaging if caught at the right time. This caused volatility levels to appear lower than they should have, leading JPMorgan Chase to take on larger risks than they otherwise would have. While spreadsheet mistakes were not the sole cause of the whopping $6 billion loss, they were a huge contributor.
— From Baseline Scenarios
The $24 Million “Clerical Error” at TransAlta
In June 2003, TransAlta, a prominent Canadian power generator, made an unfortunate discovery – they had been the victims of a spreadsheet error that cost them an incredible $24 million. The mistake occurred when incorrect cut-and-paste work resulted in TransAlta buying US power transmission hedging contracts at higher prices than necessary.
At the forefront of this issue was TransAlta’s Chief Executive Officer, Steve Snyder, who noted that a simple “cut-and-paste error in an Excel spreadsheet” led to the mistake. Snyder reported that no one had double-checked the documentation before submitting it – an oversight that eventually cost the company dearly. His concluding statement summed up the incident aptly: “At the end of the day, it’s a simple clerical error.”
— From The Register
University of Toledo Loses $2.4 Million in Projected Revenue
In 2004, the University of Toledo (UT) discovered an internal budgeting error which has resulted in a projected revenue shortfall of $2.4 million. This mistake was caused by a typo in a spreadsheet formula that had led officials to overestimate graduate student enrollment and, consequently, revenue. 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. UT will pursue systemic changes to provide more safeguards in the future and will continue to work towards strengthening its budgeting and forecasting processes.
— From The Toledo Blade
RedEnvelope CFO Resigns, Stock Slides on Loss Forecast and Budgeting Error
In March 2005, shares of RedEnvelope Inc. lost more than a quarter of their value after the company announced that it expected to post a loss due to weak Valentine’s Day sales and a budgeting error that caused an overestimated gross margins. The announcement also included news that their chief financial officer, Eric Wong, had resigned.
Although the company claims that the budgeting error was an isolated incident, investors remain wary of how this situation will affect their future performance. This reminds other publicly traded companies that quality control is critical when reporting financial information. With increased government regulation and scrutiny, even minor missteps can have drastic implications for a company’s stock.
— From Marketwatch
Virginia Tech Research Think-and-Do Tank Admits Error in Regional Study Caused by Cut-and-Paste Error
The Center for Regional Strategies, a “think-and-do tank” housed at Virginia Tech, announced an errant cut-and-paste error in a study released in March 2005. The mistake caused one measure of the region’s level of educational attainment to appear worse than it actually is – instead of showing the true 20% of people over 25 with bachelor’s degrees or higher, it showed 11%.
“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
Data Quality Control Issues Lead To Revised Earnings Figures For Kodak
In November 2005, Kodak revealed a costly mistake, revising its earnings figures for the third quarter of 2005 by an additional $9 million. After making this announcement, Kodak’s chief financial officer, Robert Brust, identified the issue as an internal control deficiency that impacted the accounting for restructurings. According to Kodak spokesman Gerard Meuchner, the error was traced back to a spreadsheet with too many zeros added to one employee’s accrued severance pay. While no payment was ever made in regards to this mistake, it still serves as a cautionary lesson in ineffective data-quality control systems.
— From Marketwatch
Westpac Forced To Release Profit Briefing Early Due To Spreadsheet Error
Westpac, one of Australia’s leading banks, had to halt trading and deliver their annual profit briefing in a rush after they accidentally sent out the results by email to research analysts. Apparently, details of the bank’s $2.818 billion record profits were embedded in a template of last year’s results which could be accessed with minor manipulation of the spreadsheet.
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
How Disaster Was Avoided After A Ticket Mishap At The London 2012 Olympics
The 2012 London Olympics was one of the biggest sporting events in recent years, and tickets were highly coveted by Olympic fans around the world. What many did not expect was a mishap involving the ticketing company that could have caused major chaos if it had been left unchecked.
To the astonishment of many, thousands upon thousands of tickets were unclaimed when second-round ticket sales of the synchronized swimming competition began. After realizing something was off, it was revealed that an employee for the ticketing company made the simple mistake of typing “2” instead of “1”, resulting in 20,000 seats going on sale instead of 10,000. Fortunately, this error was identified early on and those who purchased tickets to what essentially amounted to non-existent seats were able to exchange them for other events within the games. Some customers even saw this as a blessing in disguise; they were upgraded from their original purchase and ended up attending higher profile competitions which would not have been possible otherwise. Overall, the debacle was more embarrassing than anything else – so much potential disaster avoided because someone caught a seemingly small typo at just the right time!
— From The Guardian
MI5 Mistake: 1,000+ Phones Wrongly Bugged by British Intelligence Agency
MI5 – the sister intelligence agency of MI6 (think James Bond) – wrongly bugged more than 1,000 phones. MI5 stated that a spreadsheet formatting error caused formatting to be applied on telephone numbers ending in 000 rather than the actual last three digits. Got to love the government!
— From DailyMail.com
Study Finds Professor’s Claim of Economic Slowdown at 90% Debt to GDP Ratio is Inaccurate
Two Harvard professors incorrectly concluded that economic growth slows significantly if the country’s debt exceeds 90% of the country’s Gross Domestic Product (GDP). However, it was later discovered a student that the average formula used didn’t cover the complete range of values in the column in an Excel Spreadsheet.
— From NBCNews.com
Excel Error Causes Barclays to Unknowingly Acquire Bad Assets from Bankrupt Lehman Brothers Post 2008
After Lehman Brothers crashed in 2008, Barclays stepped up to buy some of its assets and sent a spreadsheet detailing exactly they wanted to buy. However, instead of deleting the rows of assets they weren’t interested in, whoever made the spreadsheet hid them. All the rows were shown when the Excel document was later converted to a PDF. This led Barclays to buy 179 contracts they didn’t want.
— From ComputerWorld
Utah’s $25 Million Budget Miscalculation
Utah’s Office of Education understated the number of students who would enroll in the state’s public schools, which led to a $25 million budget shortfall. The culprit was a faulty calculation to determine the weighted pupil units, which determined a school district’s per-pupil funding. Two of the state’s top finance officials were forced to resign.
— From KCPW.org
Conclusion
These examples show how spreadsheet errors can have serious consequences, leading to incorrect financial reports, incorrect calculations, and erroneous results in important studies. The mistakes range from a simple formatting errors to incorrect formula usage, leading to incorrect information that can have significant financial or reputational consequences.
Processes and governance can reduce spreadsheet errors by establishing standard procedures for data entry, data validation, data reconciliation, and error checking.
Microassist’s Excel Mastery: Advanced Auditing Techniques for Error-Free Spreadsheets course can greatly reduce errors by familiarizing users with Microsoft Excel, its features and best practices. Training can help users understand the limitations and capabilities of Excel, and also help your team understand the consequences of their actions in order to prevent mistakes. For example, in the case of spreadsheet errors, training can teach users how to properly format, manipulate and use formulas to ensure accurate results. Additionally, training can highlight common errors and provide strategies for avoiding them. Investing in technology training can help organizations ensure that their employees are equipped to use technology effectively and avoid costly mistakes.
Subscribe to Monthly Training Updates
Receive monthly productivity and training insights, software tips, and notices of upcoming classes!