We trust our spreadsheets until they let us down. Then, we faithfully check all the values and formulas to find the error which is easier (or harder) to discover depending on the complexity of the spreadsheet, the importance of the information it contains, or how much time we don't have to troubleshoot the problem. Undiscovered mistakes lurk in every spreadsheet.
Cautionary tales about misadventures with Excel spreadsheets abound. Whether we find our errors using a computer-assisted audit tool or we check a spreadsheet line for line, we seek perfection.
To help achieve perfection and save time in the future, here are several tips to help avoid errors and time lost in troubleshooting spreadsheets. This list was compiled using Excel 2002.
Most spreadsheet errors are a result of not understanding the business issues or other underlying purpose of the spreadsheet. Three questions to ask are:
- What is the business issue modeled in the spreadsheet?
- Are there any design details that you may not understand that may help you prevent problems later?
- Can you explain how the model works to others?
The complexity of calculations used in any spreadsheet varies. Understanding source data is also a concern. Three questions identifying errors at this level are:
- Are the correct formulas and functions being used?
- Are you using the correct interest or tax rates?
- Are other source values being called correctly in your calculations?
Some companies have a policy of not using built-in functions to limit calculation problems, however, it can be difficult to disable automatic functions, especially when importing data from spreadsheets created by others. There are several financial functions built into Excel 2002 that may not fit your needs. For example, a limitation of the Excel function ACCRINT (accrued interest for a security that pays periodic interest) is that it is designed exclusively using US accounting rules. If you are calculating for a foreign security using this function, you may want to consider building your own calculations to ensure accurate calculations. Three questions to help determine if errors occur within automatic or custom calculations are:
- Do you understand how the functions you intend to use in your calculations work?
- Do you need to build your own calculations to ensure an accurate, problem-free spreadsheet?
- If you built your own calculation, did you test it comprehensively?
User error is a possibility even for the best of us. Unintentional formatting can prevent a spreadsheet from calculating correctly because Excel cannot perform math on numbers formatted as text. Protecting your spreadsheet with a password may be helpful in keeping track of any changes to its content. Four questions identifying formatting errors are:
- Have you formatted any numbers as text?
- Are any formulas overwritten with a number?
- Have you password protected the spreadsheet before passing it onto your users?
- Is the password kept in a place where you can find it again?
We all know that perfection is realistically impossible but it is reasonably closer if you have considered these items. A spreadsheet is only as good as the time taken to check it for errors.