Controlling Spreadsheets

April 11, 2011 at 4:39 pm

This is a Guest Post by Bob Boda:

 A significant amount of companies use spreadsheets as an integral component of their financial reporting process. This causes a problem within the auditing community since the spreadsheet is largely viewed as a program. The problem is that the designer, programmer, tester and end user is often the same person, there is no real audit trail given in the spreadsheet and any version control is a manual process controlled by the person “responsible” for the spreadsheet. Before we address how to control a spreadsheet, let’s take a look at how an auditor views controlling a program (since that’s their view of a spreadsheet).

First of all there usually users specifications that have the expected requirements of the program. These specifications are usually signed off by the end-user. A review and approval process usually follows since budgeting dollars are needed to pay for the code to be developed. Testing specifications are developed by the designer to ensure the code that was developed properly. The program is “unit” tested by the developer and then given to a testing team to ensure the functionality is working properly. Once tested, the user signs off on the program is working properly and a management approval is usually required to move the program into the production environment.

Now think about excel. The user develops the spreadsheet in an iterative fashion until they are happy that it is functioning in the manner they wish. They declare it “production ready”. Any audit trail or approval process is very manual with a high degree of trust.

An auditors modeo is “trust, but verify”, as you can see managing spreadsheets gives the auditor little if anything to “verify”. The AICPA has released guidance in 2006 on how to deal with spreadsheets in a financial regulatory environment. Note this guidance is NOT for all spreadsheets but only those identified as key:
• Used to calculate significant account balances, or
• Serve a role in the financial statement closing process, or
• Interface between critical financial IT systems or databases.

The company must identify, document, test, and control selected key spreadsheets. Spreadsheets used in the same manner as a Financial Applications require the same SOX General Controls as an IT Financial Systems application. Most spreadsheets will not qualify as key. Spreadsheets that do qualify as key must be managed and controlled following the same IT General Controls process as any other Financial Systems application. The controls and protection include the following:

1. Version Control
Because of the ease with which an average user can edit or revise formulas and data in a spreadsheet, each version of a key spreadsheet should be controlled as though a separate program. From an auditor’s standpoint each version is a separate program. Master versions of each key spreadsheet should be identified as such.

2. Backup
An exact copy of each master key spreadsheet should be saved to protect against loss. Typically these spreadsheets should be saved on a networked drive within your business unit. Backing up each spreadsheet includes saving all formulas, formats, data, and linkages intact in the spreadsheets. An auditor or other 3rd party must be able to load the spreadsheet and independently review all processes and calculations that the spreadsheet performs.
All transaction spreadsheets should be archived with meaningful names to facilitate identification for up to seven years.

3. Validation
Periodically, the calculations and operations that are performed by key spreadsheets must be validated. Guidance from the AICPA and PCAOB suggests that such validation must occur at least every two years assuming that formulas, computations, and operations performed by the spreadsheets have not changed over the two year period.

Validation is required every time a critical computation or formula is changed in the spreadsheet. This validation does not require that all aspects of a spreadsheet be re-validated or tested. However, any changed functionality should be validated for accuracy when the change is made. Every two years all functionality of the spreadsheet must be validated.

4. Change Management
The objective of change management is to prove that formulas, data and other functions are changed only with appropriate management authorization and provide an audit trail of the changes made in such a way that auditors can track and validate them. All changes and revisions to key spreadsheets should be logged by the person making the change and periodically reviewed by management. Change management logs may be configured within the key spreadsheets themselves or maintained as a separate log which must be archived with the key spreadsheet. All changes to the spreadsheet should be tracked. Change management logs should include:
• Date of change
• Name of person making the change
• Brief description of the change made
• Name of person approving the change, and
• Brief recap of the validation test to assure change is appropriate and correct
At least annually, all changes to each key spreadsheet should be reviewed by a separate member of management.

As you can see, this is a very manual process and requires a tremendous amount of management oversight by people who are usually very taxed at month, quarter or year end when changes are identified.

What is needed is a tool that will be able to compare multiple versions of Excel (the base against the newest version), provide an audit trail of all changes for review and then upon “approval” have the new version moved into “the production environment”, usually a protected folder.

Bob Broda is a managing partner at Visage Solutions. He has helped numerous clients and software vendors design, implement, and bring to market various technical solutions to mitigate risk and solve regulatory challenges.

Postscript from Bill Jelen: If you have to deal with change management in your Excel workbooks, check out Litera’s Change Pro for Excel.


Entry filed under: Uncategorized.

Learn Excel – Rank Rank: Podcast #1380 Learn Excel – Quarter Hours: Podcast #1381

Learn Excel from MrExcel

Learn Excel Podcast Splash Screen

The Excel Beginners Book:

"Don't Fear The Spreadsheet" - by Tyler Nash, Bill Jelen, Tom Urtis and Kevin Jones

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 738 other followers

Live Lessons Excel 2010 DVD

"Live Lessons Excel 2010" DVD

RSS MrExcel Recent Articles

  • An error has occurred; the feed is probably down. Try again later.

Recent MrExcel Tweets

MrExcel Podcast Archive

%d bloggers like this: