88% of the Excel spreadsheets have errors

Microsoft Excel is currently one of the tools that companies use the most worldwide. Excel continues to prevail despite the new incessant and speedy launch of new programs.

 

The software can be applied to different applications: spreadsheets for KPI follow-up, inventory control, financial reports, project management... Excel’s ability to organize and handle data, creating tables, charts and diagrams, and build different formulas and relations between data has turned it into the corporate world’s favorite tool.

 

Over time, spreadsheets for simple calculations and quick analyses gave way to large spreadsheets with complex models, different tabs, countless charts, thousands of formulas, and a gigantic amount of data. Its applications became increasingly sensitive, being used to determine company budgets, allocate its resources, and analyze return on investments, for example. Excel analyses are now used for decision-making processes worth billions of dollars.

 

 

With such importance, knowledge in Excel became a mandatory item in résumés. Mastering this tool is now required in virtually all company levels, even if, many times, such mastery is not proven by job applicants. As a user friendly and good documentation tool, most users have basic knowledge and training, despite considering themselves advanced users.

 

Considering the complexity of Excel spreadsheets and the lack of mastery by many users, we have come to the title of this post: more than 80% of the Excel spreadsheets have errors. Ray Panko, a University of Hawaii professor, discovered that, on average, 88% of the Excel spreadsheets have 1% or more errors in their formulas.

 

According to his studies[1], the frequency of errors generated while developing spreadsheets is similar to that of the development of programming codes. However, the latter go through many more tests and validation processes before they are officially used, while most spreadsheets are used soon after the first draft is developed.

 

 

Panko classifies the spreadsheet errors in 3 types: mechanic, such as number typing errors, which are usually quickly detected and corrected; logical errors, such as incorrect use of formulas, which are the most frequent, and omission errors, where data is left out of formulas. This last type of error is considered the most severe, due to the fact that it is more difficult to detect.

 

 

A famous example is the case two renowned Harvard economists, Carmen Reinhart and Kenneth Rogoff, published a study in 2010. This study identified a critical state or government debt limit.[2] In cases the debt surpassed 90% of the GDP, there would be a negative economic growth (-0,1%). Several politicians used this study as grounds to create austere and debt reduction policies. Some students requested access to the data used by the authors, since they were not able to replicate the results using the methodology detailed in the studies. The result appeared in 2013: it was an error in an Excel formula, which left out the results 25% of the countries considered. With the correction, not only the economic growth for the cases the public debt exceeds 90% of the GDP was no longer negative, but it also started indicating what is considered a high value (2.2%).

 

According to Panko, the alleged main causes for these errors are: lack of documentation and poor spreadsheet formatting, aligned with a badly developed design.

 

The fact is, in several companies, complex and important analyses began to be processed on Excel, with a use that borders program limitations, developing spreadsheets with errors and leading to incorrect decisions, resulting in financial and time losses.[3]

 

Mathematical optimization models are also customarily used to assist in complex decision-making. Models built on Excel have several limitations that may impact their developments and uses, such as the amount and characteristics of variables, memory use and low performance.

 

It is crucial to use more adequate tools for complex analyses, such as appropriate languages for model development, more efficient solvers and user-friendly interfaces. At Cassotis, we use the AMPL language combined with the optimization platform, which enables intuitive creation of mathematical models and the quick development of user-friendly interfaces, also offering several features that allow to easily test different scenarios and their comparisons, analysis of sensitivity, and easy data edition and viewing.

 

What about taking advantage of this opportunity and getting to know these tools, so we can jointly complete the path of achieving rationality in decisions?

 

References

[1] PANKO, Raymond R. What We Know About Spreadsheet Errors. 

[2] KRUGMAN, Paul. The Excel Depression.

[3] SOTO, Christiane. 12 of the Biggest Spreadsheet Fails in History.

 

Cassiano Vinhas de Lima - Consultant at Cassotis Consulting