Operating Effectiveness Testing for Spreadsheets 1369
I am looking for guidance regarding testing of change controls over spreadsheets. The scope of testing could range from verifying that there is a tab in the workbook that records changes right up to reviewing old versions of spreadsheets to ensure that any changes have been recorded, verifying the testing of the changes, and reviewing evidence that the test results were reviewed by a manager and approved for implementation.
What are the auditors saying is adequate operating effectiveness testing for change controls over spreadsheets?
Thanks. Brad Mundy
Check out the info already posted in the Forum…
Milan, I had already read through the entire thread you referenced and did not find anything that answered my question about operating effectiveness testing of spreadsheet change control.
Thanks anyway. Brad
Regarding Spreadsheets and Change Management, many external auditors consider spreadsheets to be like mini-applications. For this reason, general controls including change management procedures, versioning, etc., may be used to manage spreadsheets that have impact on financial reporting.
One example: Grade spreadsheets based on Change and Version Control Components. For example, Grade A might be assigned if workbook changes are documented and the workbook is retested by an independent individual with the results documented. The active version is indicated clearly in file name.
In a less controlled environment, Grade B might be assigned when changes are documented and the workbook is retested by user and results documented. The active version is indicated clearly in file name.
Still less…Grade C might be assigned to a workbook that is retested by the user. Controls are not in place to maintain version control.
From Page 5 of the PwC document on Use of Spreadsheets,
Spreadsheets used as part of their financial reporting process should be treated as manual processes and tested accordingly. Examples include:
A company can maintain two copies of a spreadsheet, with changes made to both spreadsheets by separate individuals and the results compared.
A company can use cell protection to restrict access to a spreadsheet, and management can test a sample of cells to ensure passwords are assigned for their protection.
A company can employ standard naming conventions to ensure the use of the current spreadsheet version, and management can inspect a sample of spreadsheets to confirm that they follow the standard naming convention. If the standard naming convention requires that the spreadsheet name include the date and time of the modification, management would test that the spreadsheet name corresponds to the modification date.
Well-developed spreadsheets often contain a separate tab that is used to document significant changes to the spreadsheet. In an automated system, the log is maintained by the system and not accessible to users. One product, SOX-XL, that I am familiar with, has this feature. I’m sure other Spreadsheet SOX Compliance Tools might also contain this feature.
In short, the tab captures all changes (except formatting changes to cell) to the spreadsheet, recording the spreadsheet user’s name, time of change, and change accomplished. To test the operating effectiveness of the change controls, one only need examine the log and assess that changes are captured and persons making changes are authorized.
If a tool is not used, it is possible to insert a tab and maintain it manually to log significant program changes to the spreadsheet. Formulas and static data would be locked to prevent unauthorized changes.
Sorry, but I had not realized that you had seen the earlier posting.
The link below is a pdf presentation on the use of spreadsheets and identifies some spreadsheet tools that might be helpful.
Hope this helps,
It might also be helpful to note that auditors assess the reliance on spreadsheets to compile the financial statements. With this in mind, a typical management letter comment (MLC) and/or common deficiency found addresses a company’s ‘Pervasive Use of Spreadsheets’, or ‘Inadequate Training over the Use of Spreadsheets’. Either way, effective change controls might be in place, but excessive reliance on the use of spreadsheets could lead to the MLC.
The external auditor’s recommendation is then, ‘Build controls around spreadsheets and educate users.’ Logically so.
The following is boilerplate text that might be used by an auditor to address problems identified over the use of spreadsheets:
During our audit, we noted substantial errors in reports and spreadsheets that were produced by the accounting department. Discussions with accounting personnel have led us to believe this is due to ineffective training and inefficient use of spreadsheet and word processing files.
Used properly, these programs can be extremely beneficial and save substantial time in assisting in the accounting and finance area. Under current conditions, however, the improper use and inaccurate spreadsheets will simply continue to cause errors in the financial information, reports, and financial statements.
We strongly suggest that this situation be corrected as soon as possible with improved training for all accounting department employees on the use of such software. Also, xyz company may consider engaging a consultant to review the application and use of this type of software, to establish a consistent pattern of use that best meets accounting department needs. Substantial benefits in efficiencies and cost savings will result from an improved use of these types of software.
This is good guidance, Milan, and much appreciated. I will draft my test plan for spreadsheets and will be running it by Internal Audit. Once I get comments back from them, I will post them on this thread.
Chhaava last edited by
Folks, I drafted a RACM and policy on the Spreadsheet process. If you need it I can provide one.
calvin last edited by
Some of the compiled items from another listserv fo spreadsheet controls:
- Change Control - Maintaining a controlled process for requesting changes to a spreadsheet, making changes, and then testing the spreadsheet and obtaining formal sign-off from an independent individual that the change is functioning as intended.
- Version Control - Ensuring only current and approved versions of spreadsheets are being used by creating naming conventions and directory structures.
- Access Control - (e.g. Create, Read, Update, Delete) Limiting access at the file level to spreadsheets on a central server and assigning appropriate rights.
- Input Control - Ensuring that reconciliations occur to make sure that data is inputted completely and accurately. Data may be inputted into spreadsheets manually or systematically through downloads.
- Security and Integrity of Data - Implementing a process to ensure that data embedded in spreadsheets is current and secure. This can be done by ‘locking’ or protecting cells to prevent inadvertent or intentional changes to standing data. In addition, the spreadsheets themselves should be stored in protected directories.
- Documentation - Ensuring that the appropriate level of spreadsheet documentation is maintained and kept up-to-date to understand the business objective and specific functions of the spreadsheet.
- Development Lifecycle - Applying a standard Software Development Life Cycle to the development process of the more critical and complex spreadsheets covering standard phases: requirements specification, design, building, testing, and maintenance. Testing is a critical control to ensure that the spreadsheet is producing accurate and complete results.
- Back Ups - Implementing a process to back up spreadsheets on a regular basis so that complete and accurate information is available for financial reporting.
- Archiving - Maintaining historical files no longer available for update in a segregated drive and locking them as ‘read only’.
- Logic Inspection - Inspecting the logic in critical spreadsheets by someone other than the user or developer of the spreadsheet. This review should be formally documented.
- Segregation of Duties - Defining and implementing roles, authorities, responsibilities, and procedures for issues such as ownership, sign off, segregation of duties and usage.
- Overall Analytics - Implementing analytics as a detective control to find errors in spreadsheets used for calculation. However, analytics alone are not a sufficient control to completely address the inherent risk of financial amounts generated using spreadsheets.
Additionally you may would like to classify the spreadsheets before your define the testing. They may vary widely in design and usage.
Calvin - That’s an excellent list When financials are tracked on spreadsheets, the good old days of the accounting manager simply maintaining changes on their own may be over. It appears there’s a need for a more formalized workflow system, versioning and even autonomy controls to promote changes, more secure directories, etc.
ucjjg last edited by
Soxer’s post above is very good and has many of the requisite steps to address spreadsheet controls.
I’d only add one more important item: Ensure that factors that should remain consistent from spreadsheet to spreadsheet actually are. For example, each year it is common for companies to assume tax rates, useful asset life, interest rates, rates of return, customer default rates, etc. In the event that those assumptions are not consistent across various spreadsheets, the accounting treatment for like-transactions is inconsistent and therefore erroneous.
Such accounting errors are control deficiencies, the magnitude of which could rise to that of a significant deficiency or worse.
Thanks ucjjg for sharing what should be the primary qualitative objective, and calvin’s great list tells us how to get there
Indeed, the top priority is accuracy and consistency between monies, rates, and other financial factors from spreadsheet-to-spreadsheet or input-source-to-spreadsheets, where these factors should be the same. Inspection, accuracy and balancing controls are indeed essential.
This focal point can prevent embarrassing situations in an audit, e.g., or as Desi used to tell Lucy – ‘you got some ‘splaining’ to do’
jasoncrater last edited by
What steps are taken now to test spreadsheets that are used to help calculate information that feeds into the financial statements?? My company has several extremely complicated spreadsheets (i.e. 30,000 formulas). PwC is asking us to test these spreadsheets for logical formulas, as well as testing the inputs by tracing them to source documentation. Does this seem to be the standard? We recently purchased a spreadsheet audit tool called X-Checker and I am in charge of implementing the testing process. I want to make sure we don’t do too much or too little.
Hi Jason - It sounds like your approach is on the right track, as you all are:
- Working closely with Audit
- Identified the computational aspects of the spreadsheets
- Acquired a software tool to help audit the spreadsheets
I can definitely relate to the use of complex multi-tab spreadsheets, as during the past year I’ve learned things in Excel that I never knew you could do in this environment.
Obviously, you can’t test all 30,000 formulas, as most likely some of the calculations might be volatile in changing from quarter to quarter as well. As an IT person I don’t have the SOX expertise on this and would suggest the following:
- Ensure you have change control, versioning, automony levels, and security on the Excel spreadsheets themselves. This will ensure integrity as part of meeting this key control requirement (and this should be briefly assessed during testing)
- Continue working with the external auditors. Hopefully you all can develop a framework, where you might be able to randomly sample and test pieces of the 30,000 calculations rather than trying to prove out the whole.
- If you can conduct tests based on much smaller random samples, it might help you all meet the recommendation to ‘test these spreadsheets for logical formulas, as well as testing the inputs by tracing them to source documentation’. This approach of working out results manually and testing against the spreadsheets seems reasonable, if you can test smaller subsets of data.
jasoncrater last edited by
Thanks Harry for your help.
ucjjg last edited by
A slight tweak to Harry’s path forward…
I would advocate that you work with PwC to stratify the spreadsheets based on how they effect the financial statements. Clearly not all 30,000 of your spreadsheets are created equal. For example, the spreadsheet that is used to calculate the loss allowance (critical accounting estimate) is more critical than the spreadsheet used to calculate sales commission for brokers (an amount and/or account not likely to be material). Once you have tiered the spreadsheets based on their impact to financial statement accounts, you will need to spend a significant amount of time with in depth testing of the controls associated with those spreadsheets that have a material or significant effect on the financials (likely NO SAMPLING for these spreadsheets). Your effort level in the remaining buckets can become decreasingly less detailed - and that’s likely to be acceptable to PwC.
To the extent that you can adopt common controls and a common process (ideally with common people running that process) across spreadsheets, the concept of sampling has greater plausibility. Sampling across a multitude of non-common items does not provide an external auditor with much comfort about the population.
Hope that helps you to zero in further on the target. Let me know if you have any further questions.