Partner The Knowledge Base
Share this content
AIA
Tags:

Analysis: ICAEW principles for good spreadsheet practice

by
4th Jul 2014
Partner The Knowledge Base
Share this content

Simon Hurst, one of the members who worked to assemble the publication, examines ICAEW's 20 principles for good spreadsheet practice.

For those that haven’t read it already, John Stokdyk’s article on the recent launch of the Twenty Principles is an excellent introduction and sets out the single page overview of the principles together with a link to the full document.

I don’t intend to go through the principles in detail or to try and justify or promote individual elements – the principles were presented for public consultation in advance of the launch as an exposure draft and some of the resultant discussions can be found on the ICAEW IT Counts site and AccountingWEB. Instead, I’m going to set out my view as to what the principles might achieve:

Objectives

My hopes for the principles are relatively modest. I’d like to think they would help individuals and organisations of all sizes and types avoid some career/business threatening spreadsheet errors.

I also hope that they might save business billions of pounds worth of time and effort wasted through inappropriate or inefficient use of spreadsheets, including making a significant contribution to the reduction in the UK’s structural deficit through improved spreadsheet use within government.

It might be nice to think that everyone who uses spreadsheets, or is responsible for managing the use of spreadsheets, will read the principles and consider adopting them completely and comprehensively, adding them to their existing methods for controlling the quality of spreadsheets.

At least in the short term, this is neither likely to happen, nor necessary for the principles to have a positive impact. The Twenty Principles are a bit like a talking dog: it's not what it says but the fact that it can talk at all that's significant 

How the principles could help

Some of the comments on the exposure draft expressed surprise that such a 'basic' set of principles would be of much use, suggesting that anyone who uses a spreadsheet should already know everything contained in the Twenty Principles.

One particularly good point was made by listerramjet: the 'presumption of competence' should prevent professionals working with spreadsheets without having first ascertained that they possess the necessary skills. With this in mind, there should be no more need for a set of guiding principles for spreadsheets than for auditing or tax.

The problem with the 'presumption of competence' is that you need to know enough about the subject to judge your own competence. A comprehensive system of exams and post-examination CPD exists to ensure this is a reasonable expectation for qualified professionals as far as tax and audit are concerned.

As far as I’m aware – and I welcome information to the contrary – none of the major professional accountancy bodies cover detailed IT skills in such a formal way. Accordingly, there is a risk that many of those using spreadsheets just don’t realise that they lack the necessary skills, and fail to assess their own competence, or lack thereof, correctly.

The mere existence of a set of principles issued by one of the main accountancy bodies could change this situation significantly. There is now at least a baseline against which professionals can start to consider their own competence.

In addition, principle three sets out basic examples of what constitutes an appropriate level of competence:

Ensure that everyone involved in the creation or use of spreadsheets has an appropriate level of knowledge and competence.

For anyone designing, developing or maintaining (as distinct from just using) a spreadsheet, this will include: awareness of the range of functions available; clear understanding of such basic concepts as relative and absolute cell references; and an appreciation of the importance of carefully checking the results of functions.

Of course, it might be that everyone already meets this requirement, but I’m pretty sure that this is far from the real situation. Rightly or wrongly, I have always considered that the relative and absolute cell references issue is a decent touchstone of basic spreadsheet competence.

As such, whenever I lecture or train on Excel, one of the first questions I ask concerns how many of the audience are fully confident in the use of such references. Initially I used to ask this question because I wanted to know the answer, pretty soon I started asking it in self-defence.

One of the problems of lecturing on Excel is the wide range of existing knowledge in most audiences. I hoped that the realisation that about 50% to 70% of their fellow audience members didn’t understand relative and absolute references helped others appreciate the need to cover the basics.

Having the weight of a major accountancy body behind the principles doesn’t just give them a bit more credibility.

Between them, accountancy firms and accountants in business, have the ability to influence a very large number of organisations of all sizes. Increasing the awareness amongst accountants of issues surrounding the use and misuse of spreadsheets could, therefore, be a very effective way of spreading awareness amongst a much larger audience 

Catastrophic error v inefficiency 

EuSpRIG has done a fantastic job of publicising the risks of damaging errors in spreadsheets, not least by highlighting public reports of spreadsheet errors. But I have always wondered whether smaller businesses in particular see these as particularly relevant to their own situation. I was pleased that the Twenty Principles allocated equal importance to errors and inefficiency.

Even if you don’t believe that you are likely to be affected by a life-changing spreadsheet error, you will probably have personal experience of time wasted through sorting out problems with spreadsheets or finding out too late that using a different structure or technique could have saved you hours, days or even weeks.

The Twenty Principles are designed to make spreadsheet use more efficient as well as more robust and, indeed, to avoid spreadsheet use altogether where there is a better solution available.

I’ve yet to come across any comprehensive research into the magnitude of financial loss caused by inefficient spreadsheet use in business and in government but smaller studies have suggested £millions in specific areas.

If the principles do nothing more than just raise awareness of the issues and make people think a bit more deeply about how they use spreadsheets they could help save substantial sums of money.

The future 

The ICAEW has already put a process in place to assess compliance with the Twenty Principles (with the modelling standard FAST being the successful guinea pig). This might lead to a range of products and services seeking recognition, with a consequent increase in the extent to which they address efficiency and reliability issues.

At the launch the ICAEW was keen to emphasise that they are keen to receive comments and suggestions for improvements. It might even be that there is a better overall approach to be discovered than Twenty Principles but, in producing something as a starting point, awareness should be increased and more debate provoked. 

Thanks

Following on from this personal view, I would like to acknowledge the achievement of the ICAEW in general, and the staff of the IT Faculty in particular, in putting together the group that worked on the principles and in choosing an excellent chairman who helped us reach agreement. I’m not sure that I was wholly convinced at the outset that a roomful of Excel experts could reach agreement with minimal bloodshed and in a comparatively short space of time. I sincerely hope it all proves worth it.

This is a personal and biased view. I am one of the members of the group that worked together to assemble the Twenty principles. I am sure that other members of the group will have quite different views on what the principles are for and what they can achieve. 

Tags:

Replies (4)

Please login or register to join the discussion.

avatar
By jimeth
10th Jul 2014 11:27

Agreed - a good article

Simon,

I agree with you.  I was not a member of the group but did contribute to the consultation on the initial draft.  It is a worthwhile set of principles.  Inappropriate and/or inefficient use of spreadsheets is commonplace.  Most users have had no training on spreadsheet use and many are unaware of even quite basic techniques which could improve their use.

How much the twenty principles will achieve will have to be seen - but at least they give a starting point and a benchmark against which to measure.

Thanks (0)
avatar
By posty133
10th Jul 2014 12:03

Type of user is the key

Fantastic document, the princaples have been tought to myself over the past few years by an experianced modeller. I became a convert along time ago, but i would stress it is not always necasary in simple work books! There is a risk of becoming conter productive and just following for the sake of it. It is just as important to identify the need as it is to follow these princaples when the are appropriate!

These will not be recived willingly by the masses that do not already follow this logic, it is hard to change years of practice.

The key bit in this document for me was simpley identifiying the type of user "designing, developing or maintaining" """22

The level of knowlage does vary greatly, but for me it is the end users that are the key to getting these princapals as best practise when needed.

Ultimatly they will be much more common and often end up updating or altering spreadsheets even if its not there main role, often without training. If they understand the concepts and can at least read the spreadsheets and formular prior to being in this sittuation to my mind you have removed the biggest barrier.

This would definatly be achived most effectivley as part of a qualification, it could be quite simply fitted in to an existing module or as part of the practical experiance.

This being said some profesional bodies could use some help with IT themselves, particluarly online! 

 

 

Thanks (0)
Simon Hurst
By Simon Hurst
18th Jul 2014 09:28

Thanks

Many thanks for the comments to date - very encouraging!

Thanks (0)
avatar
By Fastlane
24th Jul 2014 15:53

We have a long way to go
This debate is long overdue.
I agree with listerramjet, and concur with Simon's comment that "As far as I'm aware - and I welcome information to the contrary - none of the major professional accountancy bodies cover detailed IT skills in such a formal way. Accordingly, there is a risk that many of those using spreadsheets just don't realise that they lack the necessary skills, and fail to assess their own competence, or lack thereof, correctly." But it's not just accountants, accounting firms and their professional associations who need to lift their game. Ten years ago, accountants (both in practice and commerce) and their unqualified colleagues in Finance were the primary users of spreadsheets. Now, every man, woman and their dog are using them and for a significant proportion of their daily work - yet very few of these users (or their supervisors) recognise how badly and inefficiently they are using it because they just don't know what they don't know. Worse still, they and their employers, do not seem prepared to spend time nor money on training to improve their skills for such an significant aspect of their daily work. Instead, most learn from the colleague next door or by trail & error experience. (Mind you, that's how I learned, but I've now got nearly 30 years of it under my belt!)
Exacerbating this problem is that the development of guidelines, principles, or standards for spreadsheets is still in its infancy, and is fragmented. I'm aware of three sets of "standards" (FAST, SMART, and BPM) targeted at financial modelling (which I view as a significantly advanced form of spreadsheeting), but these vary in their detail and level of "prescription". Now we have the ICAEAW 20 principles added to the mix.
It is somewhat frustrating for me when I see the poor quality of spreadsheets being produced - even from professional accounting firms - but also gives me some comfort that this means there should be plenty of opportunities/work for me in the years to come.

Thanks (0)