ICAEW Excel blog: The real costs of spreadsheet risks

spreadsheet with dice on
istock_borisyankov_dls
Share this content
Tags

The ICAEW’s David Lyford-Smith discusses the issue of spreadsheet risk and how it can be identified and tackled.

This past month has been an interesting one for me at Chartered Accountants’ Hall. I have had several Excel discussions and meetings, including hosting a webinar, running a training course, and joining a meeting of our active volunteers on the Excel Community Advisory Committee. While all the topics have been different, there is one theme that always comes up in these discussions – and that is spreadsheet risk.

We have been talking about spreadsheet risk since the Excel Community was founded. I was a volunteer myself at that time, and the discussions (now around four years ago) eventually led three years ago to the publication of the Twenty Principles for Good Spreadsheet Practice that I summarise each month in this blog.

Reaching a definitive list of guiding principles for spreadsheet use wasn’t an easy task, but we persevered with it because we believe that poor spreadsheet use costs the economy a considerable amount. There’s little research on the specifics, but we all have encountered moments where we learned about a great tool just a little too late to avoid some hefty work, or encounter a mystifyingly complex spreadsheet that takes us hours to unpack and fix.

In large part, a lot of the issue with how much poor spreadsheet practice costs is how few people and organisations take it seriously. Many believe that spreadsheets aren’t a significant part of their systems, but my experience points to Excel as the first destination for middleware – either tidying up input data, or making quick analyses of exported information from the ‘official’ software.

Spreadsheets put powerful tools in the hands of non-programmer users, and that accessibility ensures their enduring popularity. But it also ensures that poor spreadsheet knowledge and habits continue to affect organisations of all sizes.

Various research has shown that users of all kinds make errors in individual spreadsheets cells between 3-7% of the time, which in aggregate leaves us with ~90% of spreadsheets containing errors, and often significant ones. Self-review only catches so many of these errors; working in teams and peer-reviewing is the only reliable way to significantly reduce errors, as well as training in good practice.

Even with the Twenty Principles going strong, ICAEW has more to do here to provide leadership on how these risks can be identified and reduced. It’s something that will keep us busy for years to come.

Tip of the Week highlight – FREQUENCY

This week, we are taking a quick look at the FREQUENCY function, based on a more detailed post in Excel Tip of the Week #183.

FREQUENCY is a function that can easily produce histogram-style data from a table, telling you e.g. how many items fall into each of a number of “bins” of given sizes. It’s a bit unusual, as it’s an array function – one that spreads over several cells. Let’s take a look at an example:

Frequency example

The function gets written in a special way – you highlight the range that will contain the function, then enter the formula, and the press Ctrl + Shift + Enter to confirm the array formula.

The syntax for FREQUENCY is as follows:

            =FREQUENCY(data, ends of bins)

You can see in the example above that the formula is written over one additional cell compared to the bins – that’s so that all the data which is too large for any bin can be left in the final overflow cell.

Principle of the Month

This month’s selection from the Twenty Principles for Good Spreadsheet Practice is #5:

Before starting, satisfy yourself that a spreadsheet is the appropriate tool for the job.

When all you have is a hammer, every problem looks like a nail. Excel is a massively powerful tool, with a great deal of flexibility. As a result, it can be tempting to default to it, and use it in any situation and for any issue. But Excel is a spreadsheet program and has its limitations – there are plenty of cases where other dedicated software will be more appropriate. A particular weakness of Excel is in printability – with rows and columns differing wildly in size, Excel struggles to reliably print in a legible and efficient manner. It’s also not a great word processor for similar reasons, with text wrapping being very variable depending on screen size and often differing between users.

Don’t struggle to make the thing you’re doing in Excel if there’s an alternative that would do it better – and keep Excel for the data- and analysis-driven applications at which it truly excels.

David Lyford-Smith is a technical manager within the ICAEW’s IT Faculty and blogs for the ICAEW Excel Community. The Excel Community is an Excel content hub that encompasses webinars, blogs, member Q&A, fortnightly bulletin updates, and more.  Find out more here   

About David Lyford-Smith

David Lyford Smith

David Lyford-Smith is the technical manager for ICAEW, where he works in the IT Faculty and manages and blogs for the Excel Community.

 

Replies

Please login or register to join the discussion.

avatar
14th Jun 2017 13:36

I've never understood the prejudice against spreadsheets. They are an excellent tool but anyone who treats them as "gospel" is showing their own limitations rather than the spreadsheet. I'm going to carry on using them (mainly for data analysis) but knowing full well I should have a rough idea of the results before I enter a single figure and I suspect that applies to the vast majority of normal accountants who don't have the time or money to spend a fortune on fancy bespoke software.

Thanks (2)
avatar
By JonnyT
to rememberscarborough
15th Jun 2017 10:41

Hi,
Structured cloud based system can be very beneficial to your company and ultimately save you money in the long run, removing risks from using spreadsheets.
There are many tools out there that would save you a lot of time when doing tasks but wont cost you an arm and a leg. For example I have found ProForecast to be very beneficial to me but is also inexpensive.
Thanks

Thanks (0)
avatar
to rememberscarborough
16th Jun 2017 13:46

"A particular weakness of Excel is in printability – with rows and columns differing wildly in size, Excel struggles to reliably print in a legible and efficient manner. It’s also not a great word processor for similar reasons, with text wrapping being very variable depending on screen size and often differing between users."

I wouldn't be concerned about the prejudice.

Thanks (0)
avatar
14th Jun 2017 21:19

It's a view. Best left at that.
Dave

Thanks (0)
avatar
15th Jun 2017 10:34

The first and most important consideration in spreadsheet design is to ensure that is complies with industry-standard 'Fast' principles. (Flexible, Appropriate, Structured & Transparent).
Basically, no formulas longer than the end of your thumb, and no formula should take longer than 30 seconds to explain.
Avoid overly-complex formulas, and invest time up-front on the design and structure.
Too often I've seen spreadsheets that fail to follow these principles. It always ends in tears!

Thanks (0)