sarbanes oxley spreadsheet documentation control

Training Index Sarbanes Oxley Home Page

Spreadsheet Documentation (using only Excel's inbuilt tools)

General Rule on spreadsheet documentation

Each Sarbanes Oxley spreadsheet (all spreadsheets in fact) should have sufficient documentation to enable an independent person to understand and use the spreadsheet model. The documentation should give a clear indication of the history of the spreadsheet and significant events in the spreadsheets life cycle. This can be achieved by adding a worksheet into the spreadsheet, labeled ‘Documentation’, which should cover a number of aspects (identified below) and must be updated regularly.

This worksheet can then be printed out and kept as a hard copy if necessary and referred to by the auditors periodically to ensure it is being updated.

How to document spreadsheets

Insert a separate worksheet into all your spreadsheets (old and new alike) and document certain key information. See below for an explanation of the details required. Please note that more SoX critical/ complex spreadsheets would require a more detailed documentation page (or even a separate document!) but at the very least it should contain the information below. You can also view an example of a documentation page by clicking here

Minimum Requirements in the documentation page

Filename and path: The name of the file and where it is stored. The easiest way to achieve this is to put a formula in a cell which says (exactly as it appears here) =CELL(“FILENAME”) and it will automatically update itself with the current storage location and filename.

Date: The current date. The easiest way to achieve this is to put a formulae in the cell which says =NOW(). This is more for printing purposes.

Spreadsheet Owner: The name of the person responsible for the spreadsheet. This may include a history if the owner has changed over the years.

Overview: This sets out the objectives of the model, its basic structure, the sensitivities and scenarios that it does (and does not) perform, and any run-time performance requirement (e.g. iterations must be switched on, it operates on manual calculation etc).

How to use the spreadsheet: A description of how to use the spreadsheet. Any unusual features should be noted e.g. it has a purposeful circular reference; it works on manual calculation etc. Should also include a legend explaining the meaning of colours, abbreviations etc in the spreadsheet.

Output: Describes who the model is intended for, what the model is used for, what types of reports are generated and what information is on those reports at a high level. This may include what sections of the spreadsheet are irrelevant (they should be deleted but sometimes spreadsheets are so intricately linked that staff are scared to delete anything).

Calculations: This describes the functionality of each calculation module, not on a line by line basis but in sufficient detail for business users to understand how the calculations work. A flow diagram may be useful here.

Input: This lists the required input data e.g. databases, system owners / business users, files, user keyed data or messages with a full description of sources and formats of the inputs. Inputs also refer to assumptions made, and indicate the ownership of the data. For links to other spreadsheets you can use the ‘External links’ feature in Excel to identify them (watch the adjacent video clip or see the text explanation below on how to do it).

Initial Testing Log: Information required includes the date of testing, who performed it, what type of tests were performed (see testing explanation) and summary of corrections made.

Change Log: Changes made to the spreadsheet should be noted within the spreadsheet. In particular the items to be noted should be the date of change, version of the spreadsheet (full spreadsheet name as each significant change should result in a new version of the spreadsheet- see version control), person performing the changes, a brief rational for the changes, and confirmation of testing performed on the changes.

Watch the Excel's LINK video clip by clicking on it.

To see an example of a documentation page click here. If you prefer to read how to find external links (as apposed to watching the video clip above), then look below for text based information.

Useful Excel Built in Tools

If the spreadsheet is new to you, you may struggle to identify all the links into the spreadsheet. Excel has provided some tools that can help, namely the LINKS tool and the FIND REPLACE tool. See below how to use them or watch the video clips.

Video Clip: Identifying and Managing External Links in Excel spreadsheets

External Links:
Although it is nice to be able to trace relationships between cells over multiple workbooks, it sometimes arises that the spreadsheet has an external link that you cannot easily find.
The first trick is to identify what the link is referring to. Excel has addressed the issue by giving you the LINK feature. When you are in a workbook that indicates that there is an external link, do the following:

Click Edit > Links

This will bring up a screen that will indicate what other spreadsheets are being referenced to. It also allows you to automatically change the links in the spreadsheet to a new link, update links and the like.

However, it does not identify the actual cells that contain the link and this is sometimes required. Therefore we need to find another workaround.

TOP

Find and Replace:
The FIND/ REPLACE function has proved to be very useful in the auditing of spreadsheets. Along with its normal function of finding words, it can be used to find external links or particularly risky functions.

This is how:
When looking at the results of the LINKS function, find the external reference that concerns you and look closely at the path. Try and identify some unique components of the path e.g. special characters, unique names etc.

By using the unique set of characters you can search for them in the spreadsheet and quickly identify the cells that contain the links.

So in the picture example you would search for the words: "Other Workbook". For older versions of Excel you will need to perform this on every sheet. For Excel XP, by clicking on the OPTIONS button when you are in the FIND/ REPLACE menu, you can choose to search the entire workbook. If you do not find a formulae with the external link then look at any graphs you have and review the source data. As a result the combined use of LINKS and FIND/ REPLACE can make it easy to track down errant links.

Links to our other Sarbanes Oxley Spreadsheet Controls:

Documentation Control

Audit Trails (coming soon)

Change Control

Backup and Archiving

Quote of the page: Learn all you can from the mistakes of others. You won't have time to make them all yourself. - Anon

Free Microsoft Excel Training?

Visit our training pages.