In 2006, a leading US energy provider performed an audit of spreadsheets and end-user computing applications and recognized the need to establish tighter IT controls. Many key spreadsheets used within finance and accounting operations were used in financial, regulatory and management reporting, and were considered in-scope for SOX 404 compliance. At the time, SOX testing for spreadsheets was a manual process evaluating access controls and security, documentation, change management and formula and link verification.
The Need for Automated Controls
Initial testing results concluded that although spreadsheets controls were adequate, they were very manual in nature and difficult to sustain. The director of internal audit and team lead for the project identified a variety of spreadsheet risks, including:
- Widespread use of spreadsheets
- Security access issues
- No audit trail for changes and management review
- Outdated documentation
- New users did not always understand the impact of changes made
- Manually intensive and error-prone review and approval processes
Business Drivers
Operating within a highly-regulated industry, the company had many compelling reasons to automate and improve spreadsheet controls, including mitigating operational risk, reducing audit cycles, and enabling compliance with corporate, regulatory and legal mandates. As a public company, they are subject to SOX 404, SEC and industry-specific regulations. They maintain an active operational risk program and are driven by continual process and quality improvements on a year over year basis. In addition, the company manages hundreds of contracts and has an aggressive M&A strategy. As such, automating controls over critical spreadsheets affected by these mandates represented an opportunity to take a proactive approach to sustaining compliance.
Adopting a Lifecycle Approach
To mitigate these risks, the director of internal audit and his team set out to establish a new methodology for spreadsheet and EUC control by leveraging best practices, the latest guidance from auditors, and software technology to make the new process sustainable. The new spreadsheet control lifecycle included creating a spreadsheet inventory, performing a risk assessment to identify critical spreadsheet tied to financial reporting, and applying automated controls to help track and manage changes.
As a best practice, the project team established risk assessment criteria to help categorize spreadsheets as financial, analytical and operational. Some examples include spreadsheets used in revenue accruals, journal entries (e.g. balance sheet flux analysis, income statement flux analysis, etc.), power controls for plant operations, and management reporting. In addition, the team evaluated spreadsheet complexity, including the number of formulas and spreadsheet size (in MB), number of external links or data sources, and any formula or structural errors.
Identifying Risky Spreadsheets
Risk assessment criteria included:
- Application or use of the spreadsheet
- Dollar amount impacted or controlled
- Number of formulas
- Complexity of the formulas
- Number and extent of external links
Any spreadsheets that were deemed critical became candidates for monitoring and control. Risk levels for linked spreadsheets were determined through a relational risk assessment process, where any dependent spreadsheets deemed critical also became part of the controlled spreadsheet population.
The Solution
To automate the spreadsheet controls environment, the company chose the Prodiance Enterprise Spreadsheet Manager (ESM) system, including Prodiance Spreadsheet Compare and Prodiance Spreadsheet IQ. “We selected Prodiance because of their robust set of tools, their credibility with industry analysts, and their responsiveness to meet our needs,” said the director of internal audit.
Prodiance ESM provided pervasive monitoring (24×7x365) of all changes to critical spreadsheets and automated change control through cell level audit trails and versioning. Prodiance Spreadsheet Compare was utilized by business analysts to compare changes between spreadsheet versions in a side-by-side fashion to help speed review and approval cycles. Prodiance Spreadsheet IQ provided automated spreadsheet diagnostics to help internal auditors accelerate spreadsheet error checking and the evaluation of links.
The Bottom Line
“By automating internal controls over critical spreadsheets with Prodiance technology, we have realized significant business benefits, including improved data integrity, fewer spreadsheet errors, reduced SOX testing of spreadsheets, reduced change control review, reduced remediation activity due to errors, reduced audit fees, and improved review and approval processes,” said the Chief Financial Officer for the company.
In a recent 
Complex spreadsheets have been used extensively in mergers and acquisitions. Although spreadsheets can provide rapid and immediate results to speed due diligence efforts, the potential for error is high and left undiscovered such errors can lead to disastrous results.
Eric Perry