Rather than simply providing incorrect input, an error could occur because of lack of communication or a faulty design. By making use of a shared set of spreadsheet guidelines and adopting a number of good practices in spreadsheet usage, you can properly manage these risks.
The Human Factor
Excel has become the most widely used spreadsheet application in the world. Everything from simple calculations at home to complex financial management by large corporations is being carried out through Excel. For a large part, this is due to how easy it is to use. However, a low threshold in usability does come with an inherent drawback. Excel allows for a large margin of human error.
The risk for error shifts from “are there any errors?” to “how many are there?”
RESEARCH in 1998 on spreadsheet usage by RAY PANKO, a professor in IT-management, showed that out of 88 different spreadsheet documents, about 94% contained errors. Per document, the average amount of cells containing errors ranged from 1.2% to 6.9%. These documents were all used by large American corporations, for whom an error margin of 1.2% could potentially translate to a financial loss of millions of dollars.
As long as Excel sheets are created and maintained by human users, there will always be a risk for human errors. If the Excel sheet is particularly large or complicated, the question that defines this risk shifts from “are there any errors?” to “how many are there?” Being aware of this inherent risk allows you to address potential errors beforehand or monitor any situations that are known to have a heightened risk for mistakes. In other words, awareness allows for risk management.
What Causes Errors in Excel Sheets?
Errors can be broken down into different types. For instance, errors can be caused by either design flaws of the spreadsheet itself or by incorrectly processing information in what is otherwise a solid design. Some errors are caused by circumstance, such as a high workload at a finance department leading to accountants making mistakes as they didn’t have enough time to properly correct their work.
A major factor for spreadsheet errors comes from the lack of proper company protocol.
Others are the result of users being unaware of how to properly work with a spreadsheet, either by incompetence or by inexperience. Other than that, there are off course mundane types of errors, like typing mistakes or accidentally clicking the wrong cell. A major factor for errors has to do with the lack of a proper company protocol for spreadsheets. If guidelines aren’t properly set up, even the most experienced spreadsheet user will make mistakes, as there isn’t any system in place to explain what’s being done incorrectly. If company guidelines are set up but aren’t properly adhered to, that risk would obviously remain present.
Good Practices for Risk Management
In 1998, Ray Panko admitted that there was no such thing as a 100% foolproof method to completely rid spreadsheets of errors. Close to twenty years later, there still isn’t such a method, as the human factor remains. However, there are a number of principles and good practices that enable risk management. While some of these are related to the design and usage of spreadsheets, others have to do with the workspace environment rather than the actual document itself.
As long as Excel sheets are created by human users, there will always be a risk for human errors.
Some of these practices are as follows:
- Adopt a protocol for everyone using the spreadsheet to adhere to. By laying down some ground rules, every user will be working along a shared strategy. This enhances consistency of spreadsheets and lowers the risk for mistakes.
- Make sure everyone involved is informed and competent. All users who are responsible for maintaining and updating the spreadsheets need to both be fully able to work with Excel and have a complete grasp of the company protocol we just discussed.
- Share ownership. Instead of limiting responsibility for a certain spreadsheet to just one user, involve multiple users. This allows for control methods like peer review.
- Set up a consistent system for back ups and version control. Every new version of a spreadsheet should be sequentially numbered. You should also take care to create a division within your system for spreadsheets that are either obsolete, currently in or currently being designed for future use.
- Make a schedule for regular inspections. By using peer reviews or thorough checkups of the spreadsheets, any present mistakes can be addressed, allowing for better risk management.
PerfectXL makes use of a Risk Validation feature, which allows you to efficiently scan your spreadsheets for different types of risks and mistakes. Check out the Risk Validation page for more information on the types of risk we’ve currently identified.