How to review a spreadsheet: Part 1

The full publication is available free to anyone regardless of whether they be an ICAEW member or Excel Community member.

• Visit the How to review a spreadsheet hub

The publication was originated in response to the challenge we all face in making business decisions that reply upon calculations from spreadsheets. How confident are we that the vital numbers are correct?

Ray Panko (from the University of Hawaii) researched error rates in spreadsheets and determined that as many as 90% contain a mistake. While the probability of error in any one cell is low, the fact that a spreadsheet consists of thousands, if not millions, of coded cells the chance of an error rises significantly. From time to time serious errors reach the press, most recently being COVID 19 data being mis-calculated by Public Health England during the pandemic.

Error avoidance starts by building the spreadsheet correctly. Therefore, it should be built by a person or people with the necessary experience and skills. Other publications by the Excel Community such as Twenty principles of good spreadsheet practice, Spreadsheet competency framework and Financial modelling code are all resources to help and guide you in the best way to build the spreadsheet (these publications are also available free).

Once built, a spreadsheet review should be carried out by a third party. Ray Panko found self-review only identifies 34% to 69% of errors. While there is no process to ensure the complete validity of a spreadsheet, the guide and this blog series can assist you to improve accuracy and ultimately confidence in decision making.

The Blog Series

The blog series will not repeat the publication, but focus upon the Excel aspects. We will provide examples together with step-by-step instructions for using some of the tools and functions listed.

The publication sets out five review stages and some post-review activities.

Initial Review – this is a big picture review covering the spreadsheet purpose, risk, author competence, controls etc. The guide describes the core areas of consideration that should be checked at the start of any review.  This section will not be covered in the blog series.

Structural Review – there are 11 areas to review in gauging confidence in the structure and logic. This section covers areas such as logic flow, separation of data (inputs), calculations and outputs, consistency of units, time series, the use of checks and assessing areas of complex functionality. Revealing flaws here should raise alerts about the robustness of the spreadsheet and corrective work may be require before investing further time on a more detailed review. This section will not be covered in the blog series.

Data Review – this is the process of ensuring the validity of the inputs to the spreadsheet. There are five areas of consideration and these will be covered in blog 2.

Analytical Review – a sense check that the numbers ‘look right’. A process of using charts (including sparklines), ratios and recalculation that provides signals of validity. Excel functionality to help with this will be provided in blog 3.

Detailed Review – a set of Excel tools and functions available to help in the review process these will be divided as follows:

• Design principles and Formula errors these will both be covered in blog 4
• The Formula Auditing Group provides a set of helpful tools to trap errors which will be covered in blog 5
• Other useful functions and tools will be covered in blog 6 and include the Watch Window, Hidden spreadsheet attributes, Inspecting the workbook and more.

Post-Review Activities - once a review has been completed the findings need to be documented and any changes implemented. However, as soon as a change has been made the validity of the review may become compromised. An additional review of the revised spreadsheet should be completed to ensure that in correcting one aspect there are no new areas that have become flawed. This section will not be covered in the blog series.

Screen shots in the blog series will be from Excel within Microsoft 365 = version 2019

Compatibility with Excel 2007 and previous will not be considered.  Excel 2010 version saw the introduction of Sparklines which are not backwards compatible.

The series of blogs is written by John Tennent who is a Chartered Accountant and Managing Director of Corporate Edge Ltd. He is a member if the ICAEW Excel Community Advisory Committee. He is the Author of ‘The Economist Guide to Business Modelling’ and both builds models for clients as well as runs training courses to help people build their own models. He can be contacted on john-tennent@corporateedge.co.uk