sarbanes oxley spreadsheet testing controls

Training Index Sarbanes Oxley Home Page

Spreadsheet testing (only using Excel's built in tools)

General Rule on spreadsheet testing

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.

		Video Clips
	Levels of testing to perform (on initial development and after changes) Formula Testing: 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.	Auditing Toolbar How SP will make it easier (coming soon)
	Module Testing 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. Spreadsheet Professional will assist to perform these tests	Auditing Toolbar (as above) GoTo Special Finding external links How to test without Spreadsheet Professional (coming soon)
	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.	As above
	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.	As above
	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.
	Test Plans 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. See below for text based assistance or view the video clips available in the adjacent column at the top:

Auditing Toolbar:
To load you auditing toolbar perform the following:

Click on View > Toolbars > Formula Auditing

Note:
If you don’t see the Formulae auditing option, look in the customised option. In some versions of Excel it is referred to as the Auditing Toolbar.

What should pop up on the toolbar is shown below (or similar depending on which version of Excel you are using). I highly recommend that you anchor the toolbar amongst the other permanent toolbars as this will be a vital component of any spreadsheet work (development or review).

The benefit of this tool is that it visually identifies and can trace links across different sheets and even different workbooks. The only requirement is that the workbooks are open at the same time.The meaning and use of the buttons are as follows:

Trace Precedents:
Visually identifies the cells used in the formula being reviewed. If the cell is on a different sheet or workbook, a dotted line will appear. Double click the dotted line and chose the reference you want to see and you will be taken there.
Trace Dependents
Visually identifies the cells that depend on the current cell. As with the Precedent button, this can span across a workbook to other workbooks as long as all related workbooks are open. This can be used when deleting cells to ensure that the cell is not used elsewhere in the spreadsheet.
Remove arrows:
Eventually it will become confusing with all the arrows on the spreadsheet. Use this button to remove all the arrows.
Comments tool:
Allows you to quickly insert a comment.

TOP

Go To Special:
An underutilised tool in Excel is the GO TO feature and particularly the button marked SPECIAL. By clicking EDIT, then GO TO, then SPECIAL, the following will appear. (for training via video follow this link)

By clicking on the desired option and clicking OK, Excel will highlight all the cells on the sheet (or the selection) that meet the specified criteria. Below is a description of each function:

Comments- will highlight all cells containing comments (useful to delete unwanted comments)

Constants- will highlight all cells containing constants of the kind required (number, text, logical, error)

Formulas- will highlight all cells containing formulae of the kind required (number, text, logical, error).

Blanks- will highlight all blank cells

Current region- will highlight all cells in the current region

Current Array- will highlight all cells in the current array.

Objects- highlights all objects on the sheet (e.g. buttons, pictures etc)

Row/ Column Differences- will highlight which cells in a selection contain different formulae.

Precedents/ Dependants- will highlight the precedence or dependents of a cell, either to one level or all levels.

Last cell- will identify the last cell in a sheet (useful to find stray cells).
Visible cells only- highlight only cells that are not hidden (useful to make changes to what you can see without affecting hidden cells)

Conditional formats- highlights cells that have conditional formatting on them (either any conditional format or the same format of the current cell).

Data Validation- highlights cells that have data validation on them (either any validation or the same validation as the current cell).