Before we learn about the Spreadsheet Review Process, some general tips on auditing or reviewing spreadsheets.
Don’t Work on the original- MAKE COPIES
First and foremost you should perform your reviews and comparisons on copies of the original spreadsheet. This way you will not accidentally cause an error while you are trying to eliminate them. Zip or compress the originals and then work on the copies. Change the name of the copies slightly so that you know which files you can work on.
The basics of a spreadsheet review process
The basics of a spreadsheet review are:
- Identify any obvious errors. Using the Excel tools there are a number of errors that, once you know where to look, will be either obviously an error or they will be correct. This includes overwritten formula, hardcoded numbers and sheets that should be copies of each other but aren’t.
- Next look at the high risk formulas and functions. In this course we highlight what can make a function high risk and ways to find them.
- Depending on how much time you have and how important the spreadsheet is, you should review the rest of the cells as spreadsheet errors can occur anywhere.
- Once you have completed this you should have an extremely good idea of the accuracy of the spreadsheet.
Documenting the spreadsheet review findings:
Now it’s all very well going out and finding the errors, but how do you document them, especially if you are doing the review on someone else’s spreadsheet. The last thing you want to do is have to re-review cells because you can’t remember if you have looked at them at all.
When you find cells that need to be checked, put them onto a seperate sheet or use the inbuilt features of a tool like ExcelAnalyzer.
We have found that a simple spreadsheet like this helps.
- On each row you can identify the spreadsheet (workbook name), the sheet name and the cells being referred to.
- Describe the issue or concern in sufficient detail e.g. ‘the formula looks are the prior year instead of the current year’ instead of ‘Inconsistent Formula’
- Have a space for follow up comments and explanations. Even if this is just for you, this column is useful.
- It is also very useful to attempt to rank the potential error found when you find it. You can come up with your own scale, but for us a:
- High risk is an error we are fairly sure about and it is currently affecting the numbers in the model i.e. the numbers are wrong as they are.
- A Medium risk is an error that is not currently affecting the numbers of the model, but will cause a problem if aspects or inputs of the spreadsheet are changed. These are also serious errors as spreadsheets are often used to quickly see the results of changes made. These medium errors mean that the model is not as flexible as you think and you should be cautious when making any changes to the model, be it inputs or formula.
- A low risk is normally just a presentation type issue
- (Query) In some instances it is not clear whether a cell contains an error or not. Here we raise queries so that the issue is not lost and at some point it needs to be explained and cleared from the report.