Spreadsheet testing (only using Excel’s built in tools)
All Sarbanes Oxley 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 tool such as Spreadsheet Professional 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.
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.
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.
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.
Spreadsheet Professional can be used to identify typical spreadsheet errors.
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 Spreadsheet Professional, Excel has a number of inbuilt tools to help test models.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.