Part 5- How to control identifiable spreadsheets
In the previous column we discussed the method of preparing an inventory of spreadsheet risks once you have completed your high-level analysis and the spreadsheet review group is established. The aim of spreadsheet risk management control is to improve the quality of the spreadsheets used and thereby reduce the attendant risks.
Applying some form of spreadsheet controls to the spreadsheet environment is the most important area. On a broad scale, risks can be split into identifiable spreadsheets and key spreadsheet users.
Identifiable spreadsheets can be broadly defined as spreadsheets that have a long life, where the structure doesn’t change often and they often support operational aspects of an organisation such as budgeting templates that are used year after year.
Other spreadsheets are often ad hoc, designed and used for a brief period – as in a decision-making process. Due to the number of these spreadsheets that are built it is more practical to identify the regular builders and ensure that they understand the controls that are required.
Identifiable spreadsheets that are considered important to the organisation should be treated in the same way as a formal IT system. The first question should always be whether or not this function should be in formal IT development. In an ideal world, all operational issues would be controlled by formal IT developments, but practically the flexibility and feasibility of spreadsheets means that some operational aspects will still run on spreadsheets. If the organisation is happy to continue using the spreadsheet, then there are a number of issues that need to be taken into account.
If it hasn’t already been done, detailed testing must be performed on the spreadsheet. It is preferable to use a spreadsheet auditing tool like Spreadsheet Professional to perform the testing. The spreadsheet review group should be involved in the testing. Once tested and corrected, future changes to the spreadsheet must be controlled. At the very least, Excel’s built-in password protection should be used. Other software also exists which allows you to provide better access controls and password protection on any Excel spreadsheet and, importantly, generates an audit trail of changes made.
At this point you should ensure that there are the appropriate backup procedures for the spreadsheet. Ideally you will have documentation to support the spreadsheet as well. Then on an annual or biannual basis spreadsheets should be revisited, comparisons run on the tested versions and the audit trail reviewed to ensure that it is still functioning as required. Needless to say, any significant changes made to the model should be carefully considered, documented and re-tested at the time the changes are made.
In this way you will at least be moving in the right direction of spreadsheet control with regard to the requirements of regulations such as Sarbanes-Oxley.
In some cases it is not the spreadsheet, but the business’s employees that are the risk factor. People who work with spreadsheets seldom maintain only one; instead they generate multiple spreadsheets, often for important areas of the business and it would be impossible, impractical and unfeasible to test every spreadsheet by independent sources. Simply taking a detailed inventory of the spreadsheets these people use would prove fruitless because as soon as the report was generated it would start becoming outdated as a result of the changing information on the user’s PC.
A more effective approach is to train these people and give them the tools they need to reduce the risks posed by spreadsheets. By ensuring that all key spreadsheet users are familiar with easy-to-use software and processes to reduce the risks, businesses can improve spreadsheet controls from the bottom up, instead of solely relying on a central group.
At its heart spreadsheet risk is a people issue and the only way to reduce this risk is to ensure that all employees are aware of the risks, have completed the required training and have the required software to start mitigating the risks at a personal level.
This concludes the series. If you have any questions/suggestions please feel free to e-mail me on info@AuditExcel.co.za .
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.