All Excel spreadsheets should undergo spreadsheet testing to ensure that they achieve the purpose for which they were intended and provide the correct answers and information.
Testing should be performed on completion of new spreadsheets AND when changes are made to spreadsheets.
Where possible a financial modelling review tool should be used to test your spreadsheets. There are also other methods available and Excel has some built in tools to make the process easier.
When spreadsheet testing is performed, you must update the ‘Documentation’ sheet by providing the necessary information about the changes made and the tests performed. This will ensure that auditors can see the controls being applied.
On our free Auditing Spreadsheet models course we highlight where you can use Excel’s inbuilt tools and where it is advisable to look at other options. Below the types of testing you should look at.
How to test spreadsheets
Testing does not only mean verifying whether a model gives the correct output under ideal circumstances, but also whether the model can handle stress (loads of information), negative testing (using invalid values) and incomplete information.
Levels of testing to perform (on initial development and after changes)
A very quick check (usually just with mental arithmetic) that the formula gives a sensible answer. For formulas that are conditional, all branches should be considered. If this is difficult, then the formula is probably too complex and you should simplify it or break it down over a number of rows or columns. You can use the Trace Precedent tool in Excel’s Auditing Toolbar.
Involves checking (normally with a calculator) that the overall results of the module are reasonable, and that they change in a sensible way as the inputs to the module are varied.
Also look for any consistency issues with regards the formulas that are copied across rows or columns. It is particularly important to check all extreme or limiting cases at this stage, since this may not be possible during system testing. You can check for inconsistencies using Excel’s GoTo Special tool.
Confirm that all check totals are correct and that all key sensitivities appear to give reasonable results. System testing will generally be carried out with “real” data.
An Excel Auditing tool is generally required to do this efficiently.
User Acceptance Testing
User acceptance testing is to enable the business user to be satisfied that the initial model appears to conform to the original specifications. If the developer and user are the same, this step can be combined with the system testing.
Each level of testing builds on the previous level, thus all errors should be corrected before the next level of development is undertaken. This ensures a firm, relatively error-free base for the developments.
Creating a test plan is important to ensure completeness of the tests, as well as giving a structure to the process. Test plans give an overview of which developments were tested and who conducted the testing. This is an important control to minimizing the inherent risk contained in the model.
Important issues to consider when planning tests are:
- The significance of the decisions being made based on the results
- Is the output of individual use, department use or issued to external parties
The answers to these questions will determine the extent of testing conducted on the model. The test plan should also provide a structured way of recording errors found. The following details should be noted and recorded. A summary of the results should, at the very least, be recorded in the documentation sheet
- Who conducted the testing
- Date of testing and version of the model tested
- What was the aim of the test
- What was the desired result
- What was the actual result
- What is the suspected cause for the error
Suggested Tips when testing
- Use simple data, such as 0.1 and 200, so that you can immediately see whether results of formula are correct.
- Minimize problems by crosschecking for results separately e.g. using real data and compare the spreadsheet results against more than one calculated answer from previously solved problems. Never trust a new spreadsheet (or one where changes have been made to it).
- Run the model, just as actual business users would, by using the instructions provided and real input data.
- Besides our recommended Excel Auditing Tool, Excel has a number of inbuilt tools to help test models.