Audit Trails for Excel Spreadsheets

Most companies use spreadsheets as an integral component of their financial reporting process. This means that Auditors and accountants have the responsibility to ascertain the accuracy and validity of these spreadsheets.

Spreadsheets are problematic in the finance and auditing communities because, for the most part, they are viewed as a program. Often times the designer, programmer, tester and end user are same person, there is no real audit trail in the spreadsheet and any version control is a manual process controlled by the person “responsible” for creating and modifying the spreadsheet.

Before we address how to control a spreadsheet, let’s take a look at how an Auditor views controlling software (since that’s their view of a spreadsheet).

Before software is created, typically, the end-user’s specifications and needs define the expected requirements. These specifications are usually reviewed and approved by the process owner. Next, budgets are approved for software development. Then testing specifications are established by the designer to ensure the code is developed properly. Finally, the program is unit tested by the developer and given to a QA or testing team to ensure the functionality meets the requirements. Once final confirmation of functionality is achieved, management approval is usually required to move the program into production.

Now, think about an Excel spreadsheet. Instead of multi-step review and approval, the user develops the spreadsheet in an iterative fashion until they are satisfied with the functionality. They declare it “production ready”. Any audit trail or approval process is manual with high potential for errors and mistakes. In some environments, the user/developer walks through the changes made to the Excel spreadsheet with their supervisor or in the best case an independent third-party.

An Auditor’s motto is trust, but verify, however because of the lack of audit trail or a true approval process, managing spreadsheets gives the auditor little, if anything, to verify.

In 2006, The AICPA released guidance on how dealing with spreadsheets in a financial regulatory environment. This guidance is not for all spreadsheets, but only those identified as key spreadsheets and being 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 financial applications require the same SOX general controls as an IT financial systems application. Most spreadsheets will not qualify as key.

Spreadsheets that 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 network 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 3^rd 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 a

• 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.

In Conclusion:

As you can see, maintaining compliance is a highly tedious, manual process that requires a tremendous amount of management oversight by individuals who are usually very busy at month, quarter or year end when changes are identified.

Businesses need a reliable tool to compare the original version of an Excel spreadsheet against the modified version.  Ideally, this tool will provide an audit trail of all changes for review, identify them as needing approval and once approved, designate those changes as such.

We looked at several programs but found that all but one do not show changes in a meaningful way to achieve the goals of “Control” & “Verify.

In our opinion, Change-Pro for Excel® by Litéra provides the most reliable audit trail solution on the market. In addition to ease of use, Change-Pro for Excel has the flexibility to show a true redline with changes to values as well as formulas. The formula changes are designated as those that affect values, those that affect a blank cell and those auto-adjusted by Excel. The ability to view a cell by cell change report becomes invaluable in the approval and audit process.  Lastly, Change-Pro for Excel removes the cost, frustration and unreliability of manual comparisons.

Contact Visage Solutions today to see how we can assist you with this and other compliance matters.

_________________________________________________________________________

About Visage Solutions – www.VisageSolutions.com

Visage Solutions is a consulting company operating in the areas of regulatory compliance, risk assessment, information security, risk management and compliance processes. Utilizing our proprietary SingleVue™ and OpsAudit™ methodologies, the company focuses on assisting business entities in mitigating operational risk. Visage has provided solutions to a client base ranging from private, entrepreneurial companies to large multinationals. Our team is comprised of experienced executives, managers and consultants who can assist clients with the development, implementation and execution of their risk management and compliance strategy.