You can read all four publications for free; as a reminder, they are:

• Twenty Principles for Good Spreadsheet Practice, a high-level guide to what to do and not to do in spreadsheets, and which forms the basis of all the other guides;
• Spreadsheet Competency Framework, which explains the different levels of spreadsheet ability and our view of what skills belong to which levels;
• Financial Modelling Code, which applies the lessons of the 20P to the financial modelling context and builds a picture of what good modelling looks like; and
• How to review a spreadsheet, which is the subject of today’s post.

We are also hosting a launch webinar for the new publication today – check our webinars page to find the recording.

The history of the guide

When looking for the next guide that ICAEW could produce to help chartered accountants who work with spreadsheets, the topic of review was a popular one. Our other guides primarily talk about how to create spreadsheets and who does it – the biggest missing piece from that picture is the importance of review. And starting at the Twenty Principles, we have #4:

• Work collaboratively, share ownership, peer review.

And #18:

• Rigorously test the workbook.

These demonstrate the importance of reviewing to the process of creating a reliable and useful spreadsheet, but many finance users have to muddle through and create their own processes for doing this. So we decided to set out to help provide some structure and tools for how to perform a good review.

The document was initially written by long-time Excel Community volunteer and trainer John Tennent. John’s initial draft was reviewed by a list of experts who provided their own input and comments, from which we created an exposure draft that was published here earlier this year. Based on the public feedback on that draft, I then completed the document in its current form. Just like all ICAEW thought leadership, the guide is free to access; if you want to use it for commercial purposes or want to reference it in some way, please contact excel@icaew.com for details.

What does the guide say?

The guide is aimed primarily at finance users in small- and mid-sized organisations, dealing with typical spreadsheets. Extremely complex financial models, or other very high-risk spreadsheets, should be reviewed by specialists, either internally to the organisation creating them or externally hired professionals.

The first step in a review should be to focus on the big picture – rather than diving straight into formulas, get a sense for the spreadsheet’s purpose, how it is built, the risk that it carries, and the capabilities of the spreadsheet’s initial author. Even if the reviewer is not an expert, getting a second pair of eyes to look over a spreadsheet is a very effective way to catch errors that the original author might have overlooked.

Once this initial review is completed, the next step is to complete a structural review – an overview of how the spreadsheet is put together and laid out. You’re looking for a clear and methodical layout and clear labelling that explains what each part of the data in the spreadsheet is for. This is where the high-level lessons of the Twenty Principles are most relevant – for example, are inputs separated from outputs?

Then it’s time to test the data in the spreadsheet – is it accurate and well sourced? Do some basic checks, like setting all inputs to 0, and see if the outputs behave as you would expect. This is also a good time to check that any standing data, such as tax rates, is still up to date. From there you can move to an analytical review, checking if the outputs of the spreadsheet are reasonable from your expectations, and checking important indicators such as ratios and trends.

Finally, with all the high-level testing completed, you can move onto a detailed review – careful inspection at the individual formula and cell level. There are plenty of things to check in this stage, and the full document lists many of them, along with a catalogue of Excel tools to help you do it – but here are a few:

• Are there inputs which are not used?
• Are there any hidden calculations or sheets?
• Does the sheet contain circular references or error values?
• Are complex functions or formulas used without clear documentation and explanation?

Once the review is completed, it’s also important to document what has been done and any changes that have been made, so that the next time the spreadsheet needs reviewing, you are giving that future reviewer a head start.

Once again, I encourage you to read the full document if you want to find out more.

Previous post - Custom data types
TOTW index 
Next time – Revisiting useful macros and custom functions!