Controlling your spreadsheets
Spring 2007
Spreadsheets are an integral part of the information and decision-making framework for companies. Accounts departments of all sizes rely heavily on spreadsheets to perform vital bookkeeping and management functions.
Research indicates that over half of financial management reporting is performed with spreadsheets. Despite this, a disparity exists between the importance of spreadsheets to business processes and the way that businesses protect and develop their spreadsheets.
Spreadsheets offer the flexibility and ease of use of a desktop application, combined with the power to perform complex data analysis, yet are open to user error and manipulation. To ensure that the information produced is accurate, secure and properly maintained, you need to focus on: preventing unauthorised access; managing and monitoring spreadsheet changes; retaining and archiving key spreadsheets; and developing robust spreadsheet models.
Not every spreadsheet that a business uses is important and so you need only check on the spreadsheets which have a direct impact on the statutory accounts or management information. You should pay special attention to spreadsheets that use complex formulas and calculations, make many connections to multiple worksheets, or employ macro or other code.
Once you have identified the key spreadsheets, you need to put in place controls to make sure they are as rigorously managed as other areas of the business.
Risks
1. Unauthorised access
2. Unauthorised or erroneous modification
3. Errors in formulas leading to errors in output
4. Lack of knowledge about how to use spreadsheets
Controls
1. Save to restricted area and ensure regular back up.
2. Once reporting period finished convert to read only and securely archive for later retrieval.
3. Checks on formula
- a. Test formula before use.
- b. Re-test on regular basis.
- c. Lock formula to prevent inadvertent changes.
4. Document elements such as input cells, formula cells, output cells and data sources and calculation methodology
