The ICAEW IT Faculty has published for public comment and debate a manifesto containing 20 principles for good Excel practice.
For several months, IT Faculty technical and development manager Paul Booth compiled suggestions and moderated debates between a dozen or so spreadsheet experts to come up with the outline 20 principles document.
But that was only a starting point. The faculty is keen to promote a wider debate around the document to strengthen its core message and broaden the adoption of the principles within the profession. The principles are listed below and the full document available for download to any interested party, so AccountingWEB asked Booth to explain what the project is trying to achieve and how far along that road it has progressed.
20 spreadsheet principles
1. 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.
5. 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 short and simple.
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.
17. 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.
Source: ICAEW IT Faculty, 20 principles for good spreadsheet practice.
Q: How did the 20 principles project come about in the first place?
PB: The IT Faculty committee made spreadsheet standards one of our priorities for 2013. At the risk of stating the obvious, the objective was “‘to improve the efficient use of spreadsheets and minimise spreadsheet errors”. The IT Faculty has always been about promoting best practice in the use of IT – not only by chartered accountants but in the business community more generally.
The humble spreadsheet epitomises so much of what can be good or bad about IT systems. It’s a great time saver; yet it’s a huge time waster as well. As the spreadsheet has grown in functionality, and people have learned ‘just by doing it’, they have learned bad habits. There are hundreds of spreadsheet cock-up stories, many of them massively expensive, embarrassing and high-profile. Barclays accidentally bought more of Lehman assets than they intended because of a few hidden spreadsheet rows and another that springs to mind is the great West Coast mainline bidding debacle.
So we recognise that the spreadsheet is ubiquitous, is a tool that’s used for a wide variety of purposes in many businesses. And it’s a tool that is often used badly – sometimes disastrously so. A set of ‘standards’ – statements of what we in ICAEW recognise as best practice – seemed long overdue.
Q: What is the faculty setting out to achieve?
PB: It soon became apparent that there were already lots of spreadsheet standards already in existence. Many of them are very detailed and run to over a hundred pages. There are some that are publicly available, some that are proprietary to particular organisations. They differ in emphasis, according to their respective ownerships and target markets, and most contain pretty sound common sense. But, more to the point, they are largely ignored! There seemed little purpose in developing yet another competing standard which would risk getting buried, along with all the others.
Much of what is contained in traditional spreadsheet standards is arbitrary: on colour and formatting, for example, one might say that input cells should always be orange while another might say they should always be green. Rather than invent yet another standard saying they should be blue, we are going for the underlying principle: “Adopt a standard for your organisation and stick to it.” Our aim is to agree and promote an overview level set of principles that will attract widespread agreement, and which developers and end-users can quickly absorb. One member of our group suggested we should get them all onto a single side of A4, which could be turned into a laminated plastic notice to be kept on your desk as a constant reminder. In the event, we have indeed got our principles onto a single side of A4 – but we have also provided a few pages of commentary, explanation and illustration. So a project that started life as ‘spreadsheet standards’ has become ‘spreadsheet principles’.
Q: Can you tell us a bit more about the experts who contributed to the project so far?
PB: It would be invidious to name just a few of them but not all. Suffice it to say that they are, at the latest count, 16 in number, and they embody a very impressive variety of expertise. They include modelling specialists from large accountancy firms, a senior representative of an established spreadsheet standards organisation, leaders of spreadsheet training organisations, at least two published authors on spreadsheet modelling best practice – and at least two regular contributors to AccountingWEB.
Q: Were there any particularly heated issues that required moderation in the internal debates?
PB: I’m delighted to report that nobody ever came to blows. When you are trying to boil down our message into brief statements with short, pithy explanations every word and nuance matters. It is only to be expected that one who specialises in sophisticated, large-scale, heavy-duty financial modelling will have a different perspective, and different priorities from one whose field of activity is among small accountancy practitioners who prepare accountants and tax returns for micro-business clients. Our aim has been to express the principles in ways that are relevant and meaningful to spreadsheet users right across this broad spectrum.
We started off with significant differences of emphasis: some saw the principles very much from a security and risk-control perspective, while others focused more on the details of style and layout within a worksheet; some thought the principles should include matters, such as training, that pertain to the business environment in which the spreadsheet exists, whereas others – while recognising the importance of training – did not regard such matters, that are ‘external’ to the spreadsheet itself, as relevant to the project in hand. But we did succeed in moulding a consensus that was acceptable to all.
Q: Are there particular areas or questions you would like AccountingWEB & ExcelZone members to respond to?
PB: Yes, this is very much an exposure draft. It is work in progress, and not (yet) perfect! I’m sure it will generate debate, and I certainly welcome that – even if the consequence is to send me back to the tricky job of interpreting the comments, moderating between them and further tweaking the words in order to achieve consensus. We want the final version to command widespread acceptance, but it must still present clear unequivocal statements of best practice, not anodyne compromise.
Here are a few points to debate:
- We say that inputs, workings and outputs should be kept separate, but we are deliberately non-prescriptive about how separate: always different worksheets, or does it depend?
- Should there be other categories mentioned – for example, distinguish between imports and manual inputs?
- Have we got the right balance between principles that are about the nitty-gritty of what goes into spreadsheet design and those that are about how you go about monitoring, controlling and preserving the spreadsheets that you’ve got in your organisation?
- Do the examples and illustrations adequately explain each principle, or are any of them misleading?
Doubtless AccountingWEB readers will find many other points to ponder, or even to dispute. I look forward to hearing from them.