Alyssa Martin Executive Partner Weaver

Several hundred million copies of Microsoft Excel are in use worldwide, and it's easy to understand why spreadsheets enjoy immense popularity within corporations.  

Spreadsheets can be designed to analyze data, log and track information, evaluate "what if" scenarios, and perform calculations for financial reporting purposes. User-defined formulas, macros and other options provide further opportunities for individuals to customize spreadsheet functions. This functionality allows flexibility needed for people to perform their job duties efficiently.
Unfortunately, those same qualities also present considerable risk when spreadsheets are used for financial reporting processes.

Spreadsheets lack the imbedded data and logical security controls found in other applications. Proficiency in accounting principles and spreadsheet use varies widely among individuals, too, fostering inconsistent practices and potentially incorrect calculations.
In a typical small or mid-sized organization, hundreds of spreadsheets may be incorporated into planning and reporting processes. Financial reporting period closings can take much longer due to effort required to verify or correct all of that spreadsheet-generated data. Consequently, closing delays and opportunities for inaccuracies also jeopardize compliance with Section 404 of the Sarbanes-Oxley Act of 2002 for public companies.

Stakeholder expectations for enhanced accuracy and Sarbanes-Oxley compliance require confronting and mitigating potential risks. That entails addressing present spreadsheet use, taking appropriate remedial action and implementing best practices to continually reduce risk potential.

Examine Current Spreadsheet Use
Assessing present spreadsheet risk exposure requires inventorying all spreadsheets used within the organization. The inventory should determine:

-The purpose for each spreadsheet
-The department using each spreadsheet
-The file location for each spreadsheet
-The individuals who have access to each spreadsheet

Spreadsheets that support journal entries, transaction amounts, balances, disclosures and other financial reporting elements need to be segregated from spreadsheets used for management purposes. Spreadsheets supporting key financial reporting controls, and thus financially-significant spreadsheets, must also be called out and assessed for risk potential. Risk factors include:

Complexity: Spreadsheets deploying complex calculations, use of macros and formulas, query language code and/or multiple sources of input present greater risk potential due to those complex functions.

Frequency of Use and Updating:  Spreadsheets used regularly and frequently updated typically support crucial financial reporting processes and merit particular attention.  

Length of Use: Data accumulated in spreadsheets used for a year or longer. An initial error can adversely affect the data integrity of subsequent entries for months.

Number of Individuals Using a Spreadsheet: Risk potential increases with the number of spreadsheet users, and in particular with the number of individuals who can enter data, edit fields, or add or change formulas.
Each spreadsheet should be evaluated against those factors and assigned a risk rating of 1 to 3, with 1 representing low risk, and 3 signifying high risk. A spreadsheet used primarily to log information would rate a risk level of 1. A spreadsheet performing simple calculations to determine values or totals would rate a 2 risk rating. Spreadsheets containing complex formulas, multiple functions and multiple sources of input would rate a risk level of 3.

 

The combined risk rating and compensating control grade determine the testing level required for each spreadsheet. A spreadsheet with a 3C combined rating, for example, would require the highest testing level. That testing would include:

-Validating source input data
-Validating significant calculation accuracy and logic
-User access controls
-Check figures and other items used by the reviewer to validate the reports

All testing requires documentation on a standard applications controls testing template. Organizations can then devise appropriate remediation measures based on test results.

Address Remediation Needs
Spreadsheet use presents inherent risks, risk that can be lessened by reducing the number of spreadsheets in use. In many instances, existing report writing applications can gather and present information, rather than spreadsheets. Additionally, a report writer may be able to perform some basic calculations. This would allow the programmed calculation and report specifications to be tested and validated once. Then the report can be used on a recurring basis.

Data currently residing in some spreadsheets can be consolidated into secure databases. Converting high-risk spreadsheets to server-based applications based on Java or .Net provides automated control, too. Remediation for remaining spreadsheets should address:

Change Control: To maintain data integrity, any spreadsheet changes - including changes in formulas, format and function - need written approval, review, and acceptance. All control activities need to be documented.

Access Control: General IT controls should protect spreadsheets from unauthorized outside access. Low risk spreadsheets residing on a local drive require password protection. High risk and crucial spreadsheets belong in a secure file server directory. Access privilege changes require written request and  approval.

Input Control:  The spreadsheet preparer needs to verify original source data accuracy. Another individual also needs to trace inputs back to original source data. Such reviews should be documented with a tick mark and a legend that explains what was done.

Logic Inspection:  Someone other than the spreadsheet user should test the formula for correctness. Only one logic inspection per spreadsheet is required if other controls are working.

Such remediation steps provide a foundation for implementing best practices to continually reduce risk potential. Applying Software Development Life Cycle policies to such spreadsheets provides further oversight.

Implement Best Practices for Spreadsheet Use
Best practices are proactive measures that reduce risk potential throughout a spreadsheet's lifespan. Those practices begin with properly documenting the creation of any necessary new spreadsheets.

Each new spreadsheet needs an identification title, the designer's name, a description of its functionality, and explanations for reviews and tick marks. Input data needs to be segregated on different tabs from computations, and the spreadsheet must be entered into the inventory by the internal auditor.

Spreadsheet layouts should have distinct areas for data input, workings/calculation, and output. Assumptions, flexible inputs and variables should be kept apart from those areas. Constant values should be entered in only one place, and each spreadsheet should include a version tab for tracking changes. Those constant values should be kept on a separate input sheet, with formulas used to refer to them on other sheets.

Check totals and proof totals that calculate separately should be incorporated into the spreadsheet's design, too, with verification for computations. Cells containing formulas, assumptions and fixed inputs should be protected with an editor password to deter logic errors or unauthorized changes. 

Such practices promote consistency and enhance data integrity in documenting, developing and designing new spreadsheets. That makes it easier to verify and consolidate spreadsheet data. Using spreadsheet templates whenever possible further opportunities for errors or inconsistencies to occur.

Use Templates to Recreate Regular Spreadsheets
Templates do not require recreating formulas from month to month and should be used for spreadsheets that are recreated regularly. Templates should be designed so that the prior month's data can be easily removed and replaced with the current month's data, without copying and pasting information. Formulas or pivot tables should be used to keep data intact, and to create any needed variance analysis. Detailed information should remain intact on a separate sheet.

Such practices establish sound, consistent standards for spreadsheet development, and provide a basis for incorporating other best practices for spreadsheet use.

Incorporate Best Practices for Ongoing Spreadsheet Use
Best practices that address ongoing spreadsheet use further promote accuracy and consistency. Those factors are crucial, particularly for the use of complex spreadsheets and check figures.

A complex spreadsheet's general purpose should be documented on the input sheet housing all constant values. That documentation should also describe where the source data originated, where the output reports are located, and how often reports are generated. Using named ranges - such as salary or sales tax - rather than cell references, provides additional clarity. That makes it easier for auditors and other users to understand the spreadsheet's purpose.

Check figures verify that data copied or input is the same as the original source data. Check figures also determine the validity of calculations. A check figures worksheet for complex spreadsheets should be created on a separate tab within the Excel workbook.

Keep Spreadsheets Secure
Security concerns are another reason for reducing spreadsheet use. Converting high-risk spreadsheets to server-based applications based on Java or .Net protects data with automated access and modification controls. Such applications also automatically log all activity. Consolidating spreadsheet data into secure databases also provides general IT controls protection for that data.

A variety of security measures should be taken with remaining spreadsheets.  Access to financial reporting spreadsheets should be restricted to the appropriate financial or accounting personnel. Spreadsheet application controls should be activated to hide cell data and prevent formula changes. While low risk spreadsheets can be saved to a local drive with password protection, saving files to network drives ensures automatic backup and coverage within IT recovery plans. Significant historical spreadsheets should also be archived as "read only" files.

Review and Refresh Best Practices
Management should review check totals within complex spreadsheets each month. Complex and critical spreadsheets should be also be tested and reviewed regularly by the internal auditor. In addition, the spreadsheet inventory should be updated and re-rated annually.

Various application controls let individuals limit value ranges, audit formulas and take other steps to mitigate risk. Periodic training and refresher courses over spreadsheet usage and controls help individuals retain their focus on best practices, while discussion groups provide a forum for promoting continuous improvement.

The Residual Benefits of Examination, Remediation and Best Practices
Examining current spreadsheet use and evaluating the risk exposure illustrates needs for remediation. Best practices build on those remediation efforts, with proper documentation providing clear audit trails. Over time, best practices become habits. That leads to greater ease in meeting compliance requirements. That enhanced accuracy and transparency promotes confidence and trust among all stakeholders.