Spreadsheets can be a powerful tool, enabling you to process large amounts of data in an organized and highly visual way. Analysis is easier due to their flexibility – just think about pivot tables and other display features like bar graphs, line graphs, and scatterplots. You name it, it’s probably available in a spreadsheet. You can create a wide variety of reports to suit different audiences and purposes.
Most organizations recognize this. In an Oracle and Accenture study, more than 70% of the global executives surveyed used spreadsheets to track and manage financial reporting on a daily basis. Yet, despite all this functionality, spreadsheets could be more of a liability than an asset. In fact, in financial reporting and accounting spreadsheets can be risky to rely upon. In 2012, JP Morgan Chase made a trading loss of $6.2B as a result of a user error in a spreadsheet that wasn’t discovered until it was too late.
The Likelihood of Spreadsheet Errors is Greater than You Think
The problem is the approach taken when using spreadsheets. Users don’t seem to know how to safeguard against spreadsheet errors. In an information integrity research conducted by the University of Greenwich’s K. Rajalingham D.Chadwick, a KPMG report gives the reason behind many of these errors, “Our findings are disturbing, but they’re not surprising, as 78% of [spreadsheet] models had no formal quality assurance to ensure they were built to specific requirements and were fit for the purpose.”
So what is the extent of the spreadsheet errors being made? A 2016 PwC report on spreadsheet errors revealed that more than 90% of spreadsheets contained errors and more than 90% of spreadsheet users were convinced that their models had no errors. What is alarming is that the extent of spreadsheets errors seems not to be improving.
This high percentage of errors reduces the benefits of a spreadsheet. In a 2004 CFO.com article, Warren Green, CFO of One Call Medical Inc, summarized the spreadsheet problem:
“I spend more time building and managing the spreadsheet model, and making sure none of the links were broken, than I did managing the data and analyzing it to ensure it fit the strategic plan. Aligning spreadsheets was a nightmare. A simple change like someone adding an account threw the whole template of (operating expenses) out of whack. But the real drawback was my inability to do an analysis of data to make better decisions, to reforecast or otherwise plan accordingly.” – Warren Green, CFO of One Call Medical Inc
In order to understand where these errors are hiding in a spreadsheet, it is important to take time to understand where and how user spreadsheet errors occur. This is the first step to reducing them.
All user errors are categorized as either qualitative errors or quantitative errors. Qualitative errors don’t immediately affect the values calculated in the spreadsheet but they do increase the likelihood of misinterpretation. Quantitative errors, on the other hand, lead to incorrect values.
How do misinterpretations occur in a spreadsheet? This occurs when a user of the spreadsheet doesn’t uniformly format similar data, thus leading to formatting errors. For example, when using Excel spreadsheets, the cells default to General format which allows varying figures in decimal places. This makes it hard to identify incorrect values by a magnitude of 10 and above. To prevent this, businesses need to have a standard rule to specify cell format, decimal places, and (if appropriate) commas. However, this isn’t the only way to increase the chances of misinterpretation.
As a spreadsheet is being created, the user can make incorrect assumptions that lead to an intentional entry of an inappropriate piece of data. This leads to decision errors. For example, when converting the currency of money in a spreadsheet, the user can decide to put in a specific currency rate. However, since this rate is constantly fluctuating, the values obtained will be erroneous over time, thus leading to misinterpretations on the part of any other users.
Under quantitative errors, the easiest errors to detect are mechanical errors. These occur when the user either replaces correct values with incorrect values (overwriting errors) or when the user simply types in incorrect values (data input errors).
Another category of quantitative errors is logic errors. These are where the wrong formula is used because of a mistake in reasoning. He pointed out that logic errors tend to occur more often than mechanical errors. For example, in a table where the column’s total should be the same as the row’s total, a logic error frequently occurs where they are dissimilar. Such an error is difficult to detect because the spreadsheet has not been structured to show this as an error, so critical mistakes like this typically go unnoticed. This leads users to believe that the totals are similar when they actually aren’t.
The final quantitative error is omission errors. These occur when essential things are left out of the model that has been created. Again, Excel spreadsheets have low detection rates and often lead to misinterpretation of the situation.
Spreadsheets enable users to carry out calculations and analysis that would not have been possible half a century ago. However, despite these capabilities, they have proven prone to numerous errors if not properly planned and if data quality isn’t monitored. One way to help mitigate this is to understand the errors that occur and take the necessary measures to mitigate them. However, a better alternative to spreadsheets is to use automated, cloud-based accounting software to manage your finances. This technology not only reduces the risks of human error and tedious Excel formulae but also provides real-time visibility into every financial dimension of your business. Products like Accounting Seed, give you a 360-degree insight into your financial health that a spreadsheet just can’t offer.
Author Bio: Bakhita Kamau is a freelance finance, supply chain, and data analytics writer who specializes in creating content that attracts the right kind of customers who will help your business succeed. Find out more about her at https://bakhitakamau.com