Optimal vs. Spreadsheets
90% of Spreadsheets have serious errors
Studies show that approximately 90% of spreadsheets have serious errors. The error rate is about 1% of the cells, so a 10 by 10 matrix will typically have an error. The larger the spreadsheet, the higher the error rate.
Most errors are the result of math and reference errors. Since spreadsheet math and cell references are hidden, such errors are very difficult for users to see.
Many studies of spreadsheets state the financial consequences of using spreadsheets for decision support are very expensive. Published examples of the cost of spreadsheets errors are in the billions of dollars.
OPTIMAL:
IT’S ALL ABOUT SAVINGS!
Optimal provides many advantages over spreadsheets. The most important advantage is that the solutions produced by Optimal are computed by a mathematical programming optimizer that, by definition, generates the best possible solution for each given scenario, i.e., the optimal solution.
Further, Optimal data are managed in a database environment to facilitate data management. Menus facilitate navigation through the application data tables. Formatted reports present many perspectives of solutions.
The result is measurable savings over spreadsheets.
Spreadsheets make guessing look scientific.
Decision support via spreadsheets is typically done by generating what-if scenarios until an acceptable solution is found. In effect, such scenarios are a means of guessing. Formatted output has a formal appearance that masks the process by which the information is generated. While such solutions may be acceptable, they are rarely optimal.
Articles Regarding Spreadsheet Errors and Consequences
Various studies report that nearly 9 out of 10 spreadsheets (88%) contain errors. A majority of these errors were from human error (and could have been avoided). This article lists famous examples of spreadsheet blunders…
- Vulnerable to fraud
- Susceptible to trivial human errors
- Difficult to troubleshoot or test
JP Morgan seriously underestimated the downside of its synthetic credit portfolio, which ultimately led to the bank to declare $6 billion in losses and could lead to another $600 million in fines. As James Kwak explains on Baseline Scenario, the errors stemmed from a combination of copy-paste mistakes and a faulty equation created to crunch the numbers.…
Various studies report that nearly 9 out of 10 spreadsheets (88%) contain errors. A majority of these errors were from human error (and could have been avoided). This article lists famous examples of spreadsheet blunders…
In 2005, The Center for Regional Strategies, a Virginia Tech Think Tank, made a cut-and-paste error in a graduation rate study of New Century Region… This spreadsheet error led The Center for Regional Strategies to report the New Century Region’s graduation rate of 11%, where the actual graduation rate should have been reported as 20%… We apologize for our mistake…
Emerson Construction Company made a cell reference spreadsheet error that caused a bid submission that was $3 million lower than intended…
Research: Bad math rampant in family budgets and Harvard studies “Spreadsheets, even after careful development, contain errors in 1% or more of all formula cells,” writes Ray Panko, a professor of IT management at the University of Hawaii and an authority on bad spreadsheet practices. “In large spreadsheets with thousands of formulas, there will be dozens of undetected errors.”
Given that Microsoft says there are close to 1 billion Office users worldwide, “errors in spreadsheets are pandemic,” Panko says.…
In 2010, economists Carmen Reinhart and Kenneth Rogoff released a paper, “Growth in a Time of Debt.” Their “main result is that…median growth rates for countries with public debt over 90 percent of GDP are roughly one percent lower than otherwise; average (mean) growth rates are several percent lower.” Countries with debt-to-GDP ratios above 90 percent have a slightly negative average growth rate, in fact…
As Herndon-Ash-Pollin puts it: “A coding error in the RR working spreadsheet entirely excludes five countries, Australia, Austria, Belgium, Canada, and Denmark, from the analysis. [Reinhart-Rogoff] averaged cells in lines 30 to 44 instead of lines 30 to 49…This spreadsheet error…is responsible for a -0.3 percentage-point error in RR’s published average real GDP growth in the highest public debt/GDP category.”
“When you looked at the spreadsheet ,it didn’t look like anything was missing,” said Stallard, who processed Betts’ reports. The amount taken was placed in hidden rows in her spreadsheets…
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. The quotes are taken from Ivars Peterson’s MathTrek Column written in back in 2005, but it still applies to day.
Audits done shows that nearly 90% of the spreadsheets contained serious errors. Code inspection experiments also shows that even experienced users have a hard time finding errors succeeding in only finding 54% on average…
Richard Cuthbert stood down as analysts warned the firm was in danger of breaking banking agreements on debt after it had to reduce full-year profits by more than £8.5million to below £6million. The mistake was not Cuthbert’s, however, but an outside firm of actuaries. It told the company on Wednesday that a spreadsheet error meant a pension fund deficit had been wrongly valued.…
…We found errors in 0.9% to 1.8% of all formula cells, depending on how errors are defined…
A $6 million reporting error that the Knox County Trustee’s Office made in October and eventually resolved will still end up costing taxpayers. It occurred when one account wasn’t correctly linked into an Excel spreadsheet…
The town mistakenly reckoned it had $1.5 million more in this year’s budget than it actually has… She said a figure went missing as staff managed “monstrous spreadsheets.”
Various studies report that nearly 9 out of 10 spreadsheets (88%) contain errors. A majority of these errors were from human error (and could have been avoided). This article lists famous examples of spreadsheet blunders… READ MORE
- Vulnerable to fraud
- Susceptible to trivial human errors
- Difficult to troubleshoot or test
READ MORE
JP Morgan seriously underestimated the downside of its synthetic credit portfolio, which ultimately led to the bank to declare $6 billion in losses and could lead to another $600 million in fines. As James Kwak explains on Baseline Scenario, the errors stemmed from a combination of copy-paste mistakes and a faulty equation created to crunch the numbers.…READ MORE
Various studies report that nearly 9 out of 10 spreadsheets (88%) contain errors. A majority of these errors were from human error (and could have been avoided). This article lists famous examples of spreadsheet blunders…READ MORE
In 2005, The Center for Regional Strategies, a Virginia Tech Think Tank, made a cut-and-paste error in a graduation rate study of New Century Region… This spreadsheet error led The Center for Regional Strategies to report the New Century Region’s graduation rate of 11%, where the actual graduation rate should have been reported as 20%… We apologize for our mistake…
Emerson Construction Company made a cell reference spreadsheet error that caused a bid submission that was $3 million lower than intended…READ MORE
Research: Bad math rampant in family budgets and Harvard studies “Spreadsheets, even after careful development, contain errors in 1% or more of all formula cells,” writes Ray Panko, a professor of IT management at the University of Hawaii and an authority on bad spreadsheet practices. “In large spreadsheets with thousands of formulas, there will be dozens of undetected errors.”
Given that Microsoft says there are close to 1 billion Office users worldwide, “errors in spreadsheets are pandemic,” Panko says.… READ MORE
In 2010, economists Carmen Reinhart and Kenneth Rogoff released a paper, “Growth in a Time of Debt.” Their “main result is that…median growth rates for countries with public debt over 90 percent of GDP are roughly one percent lower than otherwise; average (mean) growth rates are several percent lower.” Countries with debt-to-GDP ratios above 90 percent have a slightly negative average growth rate, in fact…
As Herndon-Ash-Pollin puts it: “A coding error in the RR working spreadsheet entirely excludes five countries, Australia, Austria, Belgium, Canada, and Denmark, from the analysis. [Reinhart-Rogoff] averaged cells in lines 30 to 44 instead of lines 30 to 49…This spreadsheet error…is responsible for a -0.3 percentage-point error in RR’s published average real GDP growth in the highest public debt/GDP category.” READ MORE
The town mistakenly reckoned it had $1.5 million more in this year’s budget than it actually has… She said a figure went missing as staff managed “monstrous spreadsheets.” READ MORE
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. The quotes are taken from Ivars Peterson’s MathTrek Column written in back in 2005, but it still applies to day.
Audits done shows that nearly 90% of the spreadsheets contained serious errors. Code inspection experiments also shows that even experienced users have a hard time finding errors succeeding in only finding 54% on average…READ MORE
“When you looked at the spreadsheet ,it didn’t look like anything was missing,” said Stallard, who processed Betts’ reports. The amount taken was placed in hidden rows in her spreadsheets..READ MORE
Richard Cuthbert stood down as analysts warned the firm was in danger of breaking banking agreements on debt after it had to reduce full-year profits by more than £8.5million to below £6million. The mistake was not Cuthbert’s, however, but an outside firm of actuaries. It told the company on Wednesday that a spreadsheet error meant a pension fund deficit had been wrongly valued…READ MORE
…We found errors in 0.9% to 1.8% of all formula cells, depending on how errors are defined….READ MORE
A $6 million reporting error that the Knox County Trustee’s Office made in October and eventually resolved will still end up costing taxpayers.
It occurred when one account wasn’t correctly linked into an Excel spreadsheet…READ MORE