Spreadsheets are everywhere – whether they’re supporting day-to-day accounting tasks or propping up swathes of the banking industry. But research shows that spreadsheet errors are extremely common, and that spreadsheet authors reviewing their own work will only get us so far. So a new free publication from ICAEW’s Excel Community aims to help you plan out how to review spreadsheets efficiently and with confidence.
“How to review a spreadsheet” is available to read now and will be officially launched in a free webinar on 3 November. The guide lays out the essential steps for getting confident that a spreadsheet is right, along with some practical tools for carrying out your review that will save time and effort. We also discuss some common sources of error and how to look for them, such as incorrect input data, wrong formulas, misleading layout, and more. And the guide finishes with the steps you should take after your review to document what’s been done and give the next user of your spreadsheet a leg up.
Initial review
The first step in the review process is to perform an initial review – a high-level inspection of what you’re looking at and how it’s put together. Ask yourself if the spreadsheet is tackling the right question, or if it’s fallen prey to scope creep. Check the creator’s experience and consider if there have been any earlier reviews that you can rely on. Only after getting a sense for the basics can you move on.
Structural review
Next, carry out a structural review, to check the fundamental building blocks of how the spreadsheet is built and which parts link together. Keep an eye out to make sure that commonly confusing things like sign convention and separation of inputs and outputs are being thought about. Get a sense for where the key sheets and calculations are, and if the workbook has any internal checks and flags you can use.
Check the data and analytical review
Then it’s time to check the data – where has it come from? Is it up to date? Try doing a simple check like setting all inputs to zero – if the outputs don’t also go to zero, it might be a sign that there’s a hard-coded value somewhere. This leads into the fourth stage, an analytical review, in which these kinds of tests get more sophisticated as you check the reasonableness of what the spreadsheet is telling you against your expectations.
Detailed review
Finally, it’s time for a detailed review. What’s necessary will depend on what you’ve done so far and what the risk level of the spreadsheet is, but our guide contains a bevy of ideas for tests and issues for you to consider. These range from in-built tools in packages like Excel, to simple formula checking techniques, to best-practice principles that you can compare to. Every spreadsheet is different, and knowing when to dive deeply into yours – and when to call in the experts – is a vital skill.
This guide is a product of a team of volunteer spreadsheet experts and financial modellers, lead by long-time Excel Community mainstay John Tennent and myself, David Lyford-Tilley. It was substantially improved thanks to your comments on an exposure draft earlier in 2021. I’m very proud of the finished product (which you can read here: How to review a spreadsheet) and hope you find it useful. And if you want to hear more, please do join us for the official launch on 3 November.