Simon Hurst introduces the ‘Twenty principles for good spreadsheet practice’, and other key publications, to members who have recently joined the Excel community.
Summary
If you have joined the Excel Community recently, you might not be aware of the publications that the community has developed. This article is the first of a short series that will provide an overview of each of these publications. We start with the ‘Twenty principles for good spreadsheet practice’, a list of key spreadsheet considerations that fits on a single sheet of A4 but could save you hours of effort or help prevent a career-ending spreadsheet catastrophe.
The Excel Community publications
Over the last ten years, the community has worked on a series of publications designed to promote better spreadsheets. There are currently four publications in the series:
- Twenty principles for good spreadsheet practice
- Spreadsheet competency framework
- Financial modelling code
- How to review a spreadsheet
The full publications can be downloaded as PDFs from: www.icaew.com/exceltl
In this article we are going to look at the first of these publications: Twenty principles for good spreadsheet practice.
Why bother?
Perhaps the initial consideration is why anyone would need to consider principles for good spreadsheet practice. After all, most of us have been happily using spreadsheets for a wide range of applications over many years. The need to do spreadsheets better is often promoted in the context of the avoidance of catastrophic error. The European Spreadsheet Risk Interest Group (EuSpRIG) curates a list of spreadsheet ‘Horror Stories’ that show just how badly things can sometimes go, often involving eye-wateringly high sums of financial loss. However, in looking at setting out some principles for helping improve spreadsheet practice, the community was equally concerned with efficiency and productivity. Most members of the committee that helped develop the principles had come across multiple examples of spreadsheets where better design and procedures could have saved the developer and users many hours of unnecessary effort.
Accordingly, the twenty principles are designed to be straightforward, easy and practical ways to help spreadsheet users create spreadsheets more quickly and efficiently and make sure that those spreadsheets are easy to use and less likely to result in expensive and time-consuming errors.
The principles
The twenty principles are split into 3 separate categories:
- The spreadsheet’s business environment
- Designing and building your spreadsheet
- Spreadsheet risks and controls
Although the full PDF lists the principles and a brief explanation of how to implement each one, together with the benefits, one of the initial aims was to be able to fit the principles themselves on a single side of A4:
The spreadsheets business environment1. Determine what role spreadsheets play in your business, and plan your spreadsheet standards and processes accordingly. 2. Adopt a standard for your organisation and stick to it. 3. Ensure that everyone involved in the creation or use of spreadsheets has an appropriate level of knowledge and competence. 4. Work collaboratively, share ownership, peer review. Designing and building your spreadsheet5. Before starting, satisfy yourself that a spreadsheet is the appropriate tool for the job. 6. Identify the audience. If a spreadsheet is intended to be understood and used by others, the design should facilitate this. 7. Include an ‘About’ or ‘Welcome’ sheet to document the spreadsheet. 8. Design for longevity. 9. Focus on the required outputs. 10. Separate and clearly identify inputs, workings and outputs. 11. Be consistent in structure. 12. Be consistent in the use of formulae. 13. Keep formulae as short and simple as practicable. 14. Never embed in a formula anything that might change or need to be changed. 15. Perform a calculation once and then refer back to that calculation. 16. Avoid using advanced features where simpler features could achieve the same result. Spreadsheet risks and controls17. Have a system of backup and version control, which should be applied consistently within an organisation. 18. Rigorously test the workbook. 19. Build in checks, controls and alerts from the outset and during the course of spreadsheet design. 20. Protect parts of the workbook that are not supposed to be changed by users |
To accompany the publication, we also created an Excel template to help apply the principles in practice. This template can be downloaded for free:
Other publications
Particularly aimed at new members of the Excel Community, this series of articles will look at the other three publications over the coming weeks. The next publication to be covered, the Spreadsheet Competency Framework, addresses principle 3: Ensure that everyone involved in the creation or use of spreadsheets has an appropriate level of knowledge and competence.
Links
As well as a few specific links below, we have added a bookmark to our Power BI based archive portal that will display the list of articles relevant to the creation and use of the 20 principles. Just click on the ’20 Principles’ bookmark towards the top left of the main page of the main page to filter the display to those in the 20 Principles series. Click again to de-select and return to the display of all the archive posts:
The 'Open in full-screen mode' icon in the bottom right-hand corner of the embedded report should show the contents at a more readable size with the Escape key returning you to the post.
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.
