Part 2- High level spreadsheet analyses are critical to spreadsheet maintenance
In the previous column we discussed the risks spreadsheets pose to organizations in general and organizations trying to meet Sarbanes-Oxley legislation of 2002 in the US in particular.
Some pundits suggest that the only way to remove the errors that spreadsheets often allow to creep into the organisation is to eradicate the spreadsheets themselves. What they neglect to consider is the flexibility and value for money that spreadsheets provide.
Ridding an organisation of spreadsheets in the interests of eradicating financial reporting error is similar to unplugging the network in the interests of security. In theory it’s a great idea; in practice it’s unworkable.
If the risks associated with spreadsheets are carefully considered, and the potential solutions to these risks explored, then it becomes clear that in many cases the financial gain of employing spreadsheets can readily be satisfied.
In exploring the potential solutions, it is important to take two factors into account: the primary question is: “What impact will an error have on the organisation should it occur?”; and the second is: “What is the probability that the error will occur?
Before an organisation can understand the impact of spreadsheets on the business they will need to know the extent of spreadsheet use throughout the organisation and what they are being used for.
Identifying and documenting every spreadsheet in the business is time-consuming and as soon as the report is printed, it is old news. A high-level spreadsheet analysis resolves this and is accomplished in part by referring to Y2K and security projects because they may indicate where spreadsheets are used and where difficulties lay in the past. The organization’s auditors should also be consulted, including external, internal and IT auditors, to ascertain which areas of organisational information are supplied in spreadsheets. The HR department can pinpoint senior and more qualified employees because these tend to make greater use of spreadsheets.
In terms of legislation, Sarbanes-Oxley in particular, financial statements should be reviewed and the source of each piece of information ascertained. These pieces of information may, for example, include provisions, contingencies and commitments, which are often spreadsheet dependent. In addition, for example, turnover figures may be derived from a formal IT system but then be adjusted in a spreadsheet prior to being combined with the rest of the financial results. Forward-looking information, such as pension liabilities, is particularly at risk as it is often based on spreadsheet calculations and there is generally very little to check it against.
By the conclusion of this process, which should not be lengthy, the organisation will have developed a good idea of where the important spreadsheets are used and, more importantly, which employees rely on spreadsheets.
Spreadsheet risks are really people issues so providing employees with the proper training and tools to more effectively use spreadsheets alleviates much of the risk.
Completing the high-level spreadsheet analysis also identifies those areas of the business that are more heavily impacted by spreadsheet use and a plan to tackle the risks can be employed, beginning where the highest impact is expected.
Probability of errors
Once businesses ascertain in which areas spreadsheets predominate and have considered where errors would have the greatest impact on the business, the probability of errors can be prioritised.
The likelihood that an error will occur increases with the following influencing parameters:
- The original developer no longer works at the company or in the department;
- There is little or no spreadsheet documentation;
- Spreadsheet results cannot be verified against another source, in the way that a bank reconciliation can be verified with a bank statement;
- Changes continue to be made to spreadsheets;
- Changes are made under time pressure;
- Little time is spent on up-front planning;
- Developer skills are in question; and
- The business issues are not clear to the developers
At the end of this you should have a high-level analysis of where your risks lie.
There will be some departments where the majority of staff use spreadsheets significantly and regularly. The only way to address these departments is to ensure that employees have the tools and training to limit the risks. You should also have a spreadsheet analysis review group with the skills to assist spreadsheets users, which will be discussed in greater detail in the third series of this column.
Where a few key spreadsheets are used in a department or division, attention can be paid to the spreadsheets and not the users. This is achieved by having the review group test the spreadsheets comprehensively and then ensure that the correct access controls are in place. This can be automated through available software that will also maintain an audit trail of which users access and make changes to the spreadsheet. Version control can be performed and changes between versions highlighted.
Inability to address these issues most often results in loss of money, regulatory compliance issues being raised or a damaged reputation. The effects range from minor to devastating.
US-based Tweeter Entertainment Group’s auditor said that its spreadsheet controls were “not sufficient” in the fourth quarter, reported the Boston Herald.
The Office of the State Auditor in Utah in the US uncovered a $2 million overstatement of the direct cost base in the Division of Wildlife Resources in the Department of Natural Resources. The error was due to a cell being linked to an incorrect spreadsheet for direct salaries and benefits. That resulted in the auditor reporting a breach of standards.
Understanding the risks and knowing where they lie ensures companies are a step ahead in achieving practical solutions and mitigating risks to acceptable levels.
* In part 3 we discuss the importance of establishing a spreadsheet analysis review group.
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.