Save content
Have you found this content useful? Use the button above to save it to your profile.
Vincent Colin/iStock/Thinkstock

IT Faculty issues draft Excel good practice code

by
3rd Dec 2013
Save content
Have you found this content useful? Use the button above to save it to your profile.

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.

Tags:

Replies (5)

Please login or register to join the discussion.

avatar
By Cantona1
05th Dec 2013 12:23

Barclays accidentally bought more of Lehman assets than they int

That is hilarious!

I think we should start from the very basic premise:

A spread sheet, in it is very basic form is not more than a scientific calculator.

If I want to get a result of 75, it is 100 minus 25, but if I mis type one of the inputs, I get a wrong result. If I think my homework is very important, I will ask some else to double check my work.

I wonder how many people who worked on Barclays’ spread sheet checked the calculation before it was sent to the decision makers.

Thanks (0)
avatar
By Paul Booth
05th Dec 2013 13:07

If it's important, ask someone to check my work

Indeed so! That's Principle No 4: 'Work collaboratively, share ownership, peer review'.

Unfortunately there are many more ways of causing spreadsheet errors besides mis-keying a figure. That Barclays error was (with the benefit of hindsight, and for those of us looking on from the outside) ridiculously simple, but it was no doubt part of a large, sophisticated spreadsheet.

Thanks (0)
avatar
By reggiep
05th Dec 2013 15:51

London Insurance Company

I produced a detailed risk reporting workbook for them. Someone linked to it with a pivot table and used the result to produce some of the data for their so-called "board pack". Of course the number of rows expanded somewhat, but the range this fool used did not. Nobody noticed until he asked me about it, and he never told anyone. I had been paid long before, so nobody lost (?).

How any more examples would you like? I have plenty.

Thanks (0)
avatar
By listerramjet
13th Dec 2013 16:36

a topic worthy of debate!

I agree with Cantona1 - and I would postulate that 99% of spreadsheets are no more than scratchpads for calculations, letters, doodles, or other day to day stuff.  And unlikely to require any consideration of spreadsheet risk whatsoever, beyond the normal human condition of idiots doing stuff for which they are not qualified - or perhaps just having a bad day.

But for the ones that don't the question is, what does 20 principles offer that is actually useful?  I would suggest not much. After all - Don't see the institute offering 20 principles for auditors, or tax specialists.  There is a presumption of competence, which surely should apply equally to accountants using spreadsheets.  And equally there is a requirement on accountants to only undertake assignments for which they are suitably qualified.  Surely woe betide any accountant producing a spreadsheet for a financial forecast if they don't have the necessary spreadsheet skills?

Having said that, I do welcome the debate.  I do think the list of 20 is a mixture of policy and systems development best practice, and I think it would be better so split into two accordingly.  I also don't think spreadsheet design is something that lends itself to "best principles".  If you read these and learn something new, then you should probably not be designing spreadsheets!  But development principles - such as get (SMART) requirements, test, Keep production and development separate, etc are usefully presented like this.

Thanks (0)
avatar
By Fastlane
14th Dec 2013 15:08

A Condundrum

The spreadsheet is such a versatile tool and used for a myriad of applications (I often liken spreadsheets to a whiteboard or artists canvas - a medium upon which to create whatever you like) that it is difficult to be prescriptive for all situations. Counter-intuitively, it is easier (more appropriate?) to be prescriptive about multi-sheet/multi-component structured financial models(e.g. capital project evaluation) than it is for a single sheet spreadsheet. Sure some principles/standards will be appropriate for both applications, but many will not suit the smaller one. However, as spreadsheets are now ubiquitous in most business organisations, and having seen most users learn from the person sitting at the desk next to them, I do support the development of a set of standards and/or documenting of best practice - but in doing so there has to be a recognition that there are horses for courses.

Thanks (0)