10 May TACKLING SPREADSHEET RISK – WHAT YOU CAN DO RIGHT NOW
You are worried about spreadsheet risk – and if you have any doubts see the previous post “Spreadsheet Risk is not going away”- or look at British Telecom (BT), which recently announced a £500 million error in its accounts, due to an error in calculating pension liabilities. Problems with its Italian unit the year before cost the company £530m and BT’s share prices plummeted by 19%, removing roughly £7bn from its value.
But it is a big deal to organize a comprehensive spreadsheet risk project across the business. It needs board level support and many people in the business will underestimate or dismiss the risk.
What you can do is get your own house in order? We are going to look at what you can do right now to start addressing the risk, without launching a big project, without a big budget and just doing things that are within your control.
Typical spreadsheet error
Let’s start by looking at a typical spreadsheet error. If you are clear about what could go wrong, you have a better chance of avoiding errors.
A typical spreadsheet application starts with some important data, most likely for financial or regulatory reporting. It will provide some numbers that feed into the accounting process. The spreadsheet will have been built to plug gaps in existing IT systems. It will have been created by whoever deals with those numbers regardless of their spreadsheet expertise. It will have been built in a hurry and will have no documentation, testing or formal controls.
The original author probably made heroic efforts to check the numbers. Maybe the first time it is used there will have been a detailed review to make sure they look ok. – after that it just becomes another spreadsheet in the regular monthly process. If that process is weak then errors will happen.
What could go wrong?
In our experience the most common errors are:
- Formula errors – when the spreadsheet was developed or changed someone inadvertently creates a logic error. This could be an actual error in the logic. However, a common issue is that the user makes temporary changes to the spreadsheet to work around a problem and forgets to remove them.
- Data input errors – the user puts the incorrect or incomplete data into the spreadsheet. As the controls over the data input are weak this is not noticed and even though the spreadsheet logic may be fine if you put bad data in you get bad data out.
- Process errors – there will likely be dozens of copies of a particular spreadsheet. The user makes a mistake and opens the wrong one. If they do not notice, then errors are very likely. Or you wanted to roll-over a spreadsheet using save as.. and overwrote the live spreadsheet instead.
What can you do about it?
If you look at a typical spreadsheet control policy you will see a long list of controls that should be carried out. Many of these are time consuming and not under your control. We have listed out here the minimum that you need.
Training / Competency
Arguably Spreadsheets errors are all human error – training your staff is one of the most important actions you can take. The objective is to make sure that spreadsheet users have the right competencies. The ICAEW has produced a Spreadsheet Competency Framework which defines the skills needed for different types of users. (Full disclosure Finsbury staff are members of the ICAEW committee that produced the Framework).
Validation is the process of establishing that the spreadsheet does what it is supposed to do. If it is a complex spreadsheet with nothing to check it against, this may be a difficult undertaking and you may need tools to help you do the validation. However, for many spreadsheets you can check the spreadsheet against some trusted source. For example, if you have downloaded the spreadsheet from the general ledger you can check the totals to the ledger. This is not just evidence that the data is correct, but also that the formula logic in the spreadsheet is correct as well. Likewise, if the spreadsheet is small enough you can check it manually.
All IT systems including spreadsheets require controls to be in place to make sure the operate properly. Controls can be confusing because they seem to have almost unlimited scope. Should you worry about security and encryption if you are not even sure if your numbers are correct? We have chosen two controls – input and process controls – that are essential.
- Input controls – These determine that the data in the spreadsheet is complete and accurate. You can tick off data by hand, but by far the most common control is to agree controls totals in your data to some third party source of the data. This type of control is typically quick and easy to perform.
- Process Controls – Many spreadsheets have macros that must run for the output to be correct, even if it is just remembering to hit the F9 key.
- Review – this can take many forms from an informal review of the outputs to a detailed analytical review with a full explanation of changes. The key point is that many errors happen because no one looked at the output before publishing it.
If you have completed these controls then it is not everything, but you have done the basics and tackled the common reasons for data errors.
There is no doubt that spreadsheet risk is here to stay.
If you would like to learn more please register