Understanding spreadsheet risks
In an article originally published on Finance Week, Protiviti associate director Ewen Ferguson explains the significance of spreadsheet risk and how to put a risk management policy in place. This is a short extract.
Spreadsheets make it easy to perform analysis that otherwise would be difficult or time consuming - but managers are so familiar with Excel that they sometimes place undue trust in the integrity of the analysis performed using spreadsheets.
Spreadsheets (especially following the introduction of additional data capacity and processing efficiency in MS Excel 2007) are powerful analysis tools that, in many cases, are capable of delivering the same functionality as formally developed applications. Spreadsheets are often a viable and sensible alternative to IT-owned applications that are subject to lengthy software development cycles. As a result, they are a ubiquitous business tool.
1. Define the objectives of what you’re trying to achieve. For example, are you trying to mitigate operational risk or to comply with specific legislation?
2. Define a starting point: this could be a business unit or function that you want to review.
3. Build an inventory of the spreadsheets in use within your firm. Do you know who manages them? How reliable are their calculations? Who ensures the results they produce are valid?
4. Focus on risk: take a risk-based approach and look carefully at the parts of the business that place most reliance on spreadsheets.
5. Perform a risk assessment: spreadsheet risk should be considered in terms of the likelihood of an error occurring and the impact of an error on the organisation.
6. Focus on the process: consider if there are mitigating controls within the business processes in which the spreadsheets are used that would detect errors should they arise.
7. Identify controls: define an appropriate spreadsheet control framework that documents minim standards, idenifies risks and controls and provides a regular review mechanism.
8. Establish a baseline by assessing each spreadsheet's functionality - there is little point in controlling a spreadsheet that is not working in the first place.
9: Implement Policies and Procedures: Training programmes and monitoring processes will be required to achieve compliance.
10: Maintain: Ensure that the controls put in place can be relied upon going forward.
The US Sarbanes-Oxley Act and related regulations increased the level of scrutiny over the way spreadsheets are used and controlled in financial reporting and other critical business functions. With multi-million-pound errors and frauds attributed to spreadsheet use, this increased scrutiny is not surprising.
The past couple of years have also seen companies filing material weaknesses and deficiencies with the Securities and Exchange Commission (SEC) as a result of the lack of controls around the spreadsheets used for financial reporting. The recent change in the UK rate of VAT to 15%, too, caused headaches for many organisations that were using spreadsheets with the VAT rate ‘hardcoded’ in them.
What is the risk?
A simple search for spreadsheet errors in ExcelZone, the EuSpRIG site and on the internet reveals numerous examples, including budgeting errors, financial statement errors, pricing errors, fraud and bad decision making as a result of poor information. The financial impact of these errors can be significant (in some cases many millions of pounds) and the damage to a company’s reputation can be even worse.
Below are some recent errors identified by Protiviti:
- An undocumented workbook was inherited by a new user. Embedded assumptions and resulting errors led to exposures being incorrectly tracked and options being incorrectly priced and traded. This caused the company a multi-million-pound loss.
- An incorrect formula in a hidden row helped cause a $50m error in a company’s capital reserves.
- Improper use of a LOOKUP function resulted in a $1.5m error in a company’s cash flow statement.
- A formula accidentally overwritten with a hard-coded number caused a multi-million pound trading loss for a company.
The use of software solutions
Spreadsheets are here to stay and provide a wide range of critical business applications. They are by the far the most common end user developed tools, but it is crucial that there are adequate controls in place to mitigate potential risks. The last few years have seen an increasing number of technical solutions on the market that are aimed at helping companies manage the risk associated with using spreadsheets. Whether used in conjunction with Excel spreadsheets, Microsoft Access databases or other third party analysis tools, the same principles set out above should be applied to end-user applications.
As well as meeting regulatory requirements, spreadsheet control helps to reduce potential losses due to errors and can result in significant productivity and efficiency gains.