Save content
Have you found this content useful? Use the button above to save it to your profile.
Spreadsheet
iStock_Matejmo

Spreadsheets are risky, mistake prone - and they're going nowhere

by
11th Apr 2018
Save content
Have you found this content useful? Use the button above to save it to your profile.

The spreadsheet has been declared dead more times than Bitcoin. But the tool, specifically Excel, marches on.

It’s difficult to think of another piece of software which has enjoyed the longevity of the electronic spreadsheet. The tool, specifically Excel, has been a steady companion for AccountingWEB members.

The spreadsheet’s enduring popularity hasn’t come without controversy: it has been under sustained PR assault for as long as I can recall. The criticism seems to stem from a reasonable source: the never-ending stream of spreadsheet errors which have caused all manner of calamity.

The London Olympics famously sold 10,000 too many tickets to the synchronised swimming events thanks to a single, mistaken keystroke. A combination of copy-paste mistakes and a faulty equation contributed to JP Morgan Chase’s $6bn trading loss in 2013.

And most recently, Conviviality’s £5.2m “spreadsheet arithmetic error” was the bellwether for a nightmarish March, culminating in the business’s administration and acquisition by C&C.

Interestingly, C&C has its own chequered past with spreadsheets: the Irish drinks business’s shares fell 15% after reporting an increase in sales when it had actually fallen. C&C’s group finance director explained the error resulted from data which was incorrectly transferred from an accounting system used for internal guidance to a spreadsheet.

What to make of the spreadsheet, then? The comments on the Conviviality article by AccountingWEB members show opinion hasn’t changed: there are no bad spreadsheets, just bad practice.

“I have been using spreadsheets since the early days of Lotus 1-2-3 and have learned to build in cross-checking methods into all my calculations,” wrote Agutter Accountants. “And quite frankly, something that is wrong simply looks wrong to experienced accountants.

“As a management accountant by background I have always been a cautious forecaster. Understate potential income and anticipate all likely costs and generally speaking you stay out of trouble.

“Good method, and rigorous, critical review of your figures before going public keeps you out of bother in most cases. In this case I detect the whiff of accounting sloppiness and complacency.”

AccountingWEB member Lytton Wolf’s offered a particularly grim example: “I joined a small manufacturing/assembly business a few months ago. I am still finding errors and unfathomable 'logic' in their management accounts spreadsheets.

“Tabs along the bottom: 37. Links to external spreadsheets: 18 (around six or seven active). Seconds taken to open up spreadsheet: 16 (it’s 4.8MB!). Cells in use in spreadsheet: 132,385 (this does NOT include any great listing of stock/debtors/creditors – they are in other spreadsheets).”

It’s not difficult to see how these errors occur. The European Spreadsheet Risks Interest Group (EuSpRIG) maintains a grisly catalogue of spreadsheet horror stories. But it’s not just about schadenfreude, EuSpRIG is attempting to alter the culture around spreadsheets.

First and foremost, EuSpRIG’s Patrick O’Beirne told AccountingWEB is confirmation bias. “People don't actually test their speadsheets but accept an answer if it looks like what they expect or want.”

Errors like Conviviality’s are “common”, according to Simon Thorne, a lecturer in information systems and an expert on spreadsheet risks. “It really comes down to poor management when considering the validity of the basic work. If there had been testing or planning, it's likely that the mistake would have been seen before this point.”

EuSpRIG’s website has a handy list of best practices to follow.

But can’t all of this be fixed by abandoning the spreadsheet altogether? Arguably, you can fight the risks inherent to spreadsheets, or you could transition to a specialised forecasting solution.

“Excel as a tool is great,” said Kevin Philips, CEO of IDU, a South African based software house that produces budgeting, forecasting and software tools. “I’m an accountant and I love it as much as the next person. The problem comes when you try to use it as a distributed toolset. One small mistake in a formula could have a catastrophic effect on the end result.”

A tool like Excel also struggles with user friendliness. While a seasoned accountant can easily understand a spreadsheet, a non-financial stakeholder will struggle. “A budget only works if the people spending the money buy into the budget,” said Philips.

The spreadsheet, he said, has become a victim of its own success. When the tool becomes overburdened - either by the number of users or the complexity of the business - it’s prone to breaking. And as non-financial people contribute to it, version control is cast aside.

A forecasting solution is rigorous and specialised. Lacking Excel’s pliability perhaps, but avoiding the costly errors. But the flexibility is precisely why Excel is popular in the first place, said Nick Levine, the advisory lead at Propel by Deloitte.

“What I’d say, in terms of flexibility if you want something completely bespoke, Excel is the number one tool. There are many software add-ons, they all promise a great future, but they’re limited in their functionality.

“The specialised forecasting tools offer less chance of a human error and close to real-time data from the cloud accounting software. But they can’t provide anything as bespoke or focused as Excel.

“We’re all waiting for this perfect cloud solution, we’re willing it on. I want to get rid of Excel, but I can’t. There’s nothing that offers that level of bespoke functionality.”

People have been predicting the death of the spreadsheet since the 90s, according to David Lyford-Smith, a technical manager at the ICAEW. “It’s been about to die for 25 years but spreadsheets are still great at doing lots of things,” he said.

“All the information we have shows our members use Excel a huge amount. It’s such a flexible and accessible tool. You can make Excel do anything. It’s a very popular middleware for dedicated accounting applications.”

For Lyford-Smith, it’s clear that spreadsheets still have a place. The aim now is to instill good practice. And perhaps there’s too much focus on the tools, anyway.

“Producing a good forecast is not about selecting the right tool, rather than the right methodology and data on which you will build your forecast,” said Hugh Johnson, an AccountingWEB contributor and vice president of Suntico, a financial data analysis platform provider.

“It is important to consider the lifecycle of your forecast. Are you producing a one-off static forecast that has validity on the day it is produced and quickly decays, or are you producing an automated rolling forecast that is updated every day? This will influence your choice of toolset.”

“Whether you are using Excel, Power BI or any other tool to produce your forecast, it is important that everyone understands the basis of that forecast if not the maths behind it.  Without that understanding there is no basis for trust.”

“In my opinion, a typical forecast has a half-life of no more than 25% of the forecast period.  In other words, unless refreshed, the usefulness of the month’s sales forecast halves within a week of producing it.”

“If you understand the basis of a forecast, you can make your own judgements as to how you should use it”.

Tags:

Replies (21)

Please login or register to join the discussion.

avatar
By rememberscarborough
11th Apr 2018 16:58

It's pretty much like trying to ban the car...

Yes, there are plenty of idiots out there that misuse them but when looked after they provide a useful tool to get from A to B. We'd all prefer to have the financial "helicopters" to do the job but for most of us it's not practical so until a cheaper better alternative is provided Excel, like the car, is here to stay!!

Thanks (1)
blue sheep
By NH
11th Apr 2018 17:10

The European Spreadsheet Risks Interest Group (EuSpRIG)?
That sounds like a fun job, and they say accountants are boring!

Thanks (3)
Jennifer Adams
By Jennifer Adams
11th Apr 2018 19:32

A very timely article. I have just spent a day reworking a clients bookkeepers spreadsheet trying to make some sense and pulling together some sort of accounts.
I'm tired and fed up.
And no.. I couldnt do a bank import into my own software as it would have taken just as long to load, create rules and work through the 4 bank accounts, 3 credit cards, Transferwise account - Euro, dollar and pound going over 3 years.
I'm off for a drink to get myself together to face the final loading onto Taxfiler.

Thanks (2)
avatar
By roycox
12th Apr 2018 09:44

It's not Excel, it is the people that use it! Also, as in everything there are so called Experts who should be deemed amateurs. No Employment Agencies or companies that I know of have any idea how to rate a person's Excel skills.

Thanks (3)
avatar
By johnjenkins
12th Apr 2018 09:54

From an Accountant's perspective the spreadsheet is an electonic copy of a cashbook, TB and, of course from a TB a set of accounts.
People used to make mistakes adding up freehand, that's why we use cross - casting. In spreadsheet we use formulae.
Unfortunately the technical world has advanced so rapidly that a lot of people can't catch up and never will. This is evident now with all the problems that beset our techno advancement. We need a period of consolidation before we move further into unchartered (not always for the good) waters.
So the spreadsheet will stil be with us when we meet the aliens.

Thanks (2)
Replying to johnjenkins:
avatar
By Brend201
12th Apr 2018 11:32

xxx wrote:

before we move further into unchartered (not always for the good) waters.

Sorry, but it is one of my pet peeves because it is so common, even if in this case it is considered an ironical pun. Uncharted waters, i.e. not mapped.

Thanks (1)
Replying to Brend201:
avatar
By I J Lessels
12th Apr 2018 13:43

But in this context "unchartered" is almost priceless. Maybe that's what he actually meant?!

Thanks (0)
Replying to I J Lessels:
avatar
By johnjenkins
12th Apr 2018 16:11

I won't repie to that.

Thanks (0)
David Ross
By davidross
12th Apr 2018 10:43

Of course the spreadsheet is always being knocked - by those who want to sell other 'solutions'.

Are we to suppose that decimal points don't get misplaced in other software?

.... and what alternative do the knockers suggest for the informal, flexible workings that accountants will always need?

A non-story

Thanks (2)
avatar
By unclejoe
12th Apr 2018 10:43

I was once working on a problem investigation that required a lot of spreadsheet work. I was assigned a new temp assistant and told that he had excellent Excel skills. But he proved to be sooo sloooow! He was compiling a list of figures and then typing the same figures into calculator to get the total which he then typed at the bottom! He was astonished when I pointed out the SUM function. This sort of thing was common, although this instance was extreme. I suggested that I devise a simple Excel test that we could ask new recruits to complete as part of recruitment, but HR vetoed it saying that such action could land us with legal difficulties - I never did find out just why. In my experience almost every Excel user overestimates their competence. It is only when one gets into the advanced elements of macro programming, complex database functions etc that you realise this. And the worst offenders are senior management who seek to tell those that do know what they are doing how to do it - badly!

Thanks (0)
avatar
By KH
12th Apr 2018 14:23

It's not just spreadsheets ... back in my old days I remember being on a team of two to check the bid price on a unit trust before the price was put out to the market ... it was not a spreadsheet, it was a simple hand-written table with helpful things like "Add this figure to the previous subtotal" or "subtract the resultant figure from such and such a subtotal", something any accountant could do in the dark without a torch whilst eating his 25p luncheon-voucher sandwich ... but the senior guy (I was merely a young innocent nerd back then ... those were the days!) still just followed exactly what the unit trust manager had done, which was subtract one figure rather than add, despite the clear message that this figure had to be added ... the bid price calculation was duly given the go-ahead, the big Four firm charged its massively inflated fee, and two days later got embroiled in one unholy row when the mistake was spotted. So who needs spreadsheets to create errors?...?!

Thanks (1)
Hugh Johnson
By Hugh Johnson
12th Apr 2018 16:20

The thing about spreadsheets is that they are just so easy to start using. By anyone. With no training. Yet they are so powerful. What makes them great is also their achilles heel. They have a very natural tendancy to keep on propagating, and once taken root to grow in complexity. Getting rid of them is not the answer because they are so useful and it won't happen. Blaming the user / creator will not fix the problem either. What is needed perhaps is a way to progress from the spreadsheet once it becomes too critical or complex. I think that the very tight integration between Excel and Power BI could be a step in the right direction. It provides simple ways to manage spreadsheets and their data more robustly as well as migration of linked sheets and PowerPivot models. It is early days and may not solve all spreadsheet issues, but I think it is a very welcome step in the right direction.

Thanks (1)
Man of Kent
By Kent accountant
13th Apr 2018 11:25

Spreadsheets!! Bloody hell what is this 1999?!

Get some decent software and bin the spreadsheets!

Thanks (0)
Replying to Kent accountant:
avatar
By johnjenkins
13th Apr 2018 12:05

Yer and bin books, cards. Don't send letters. etc. etc. Oh yes definitly ban the "wagon wheel"

Thanks (0)
Replying to johnjenkins:
Man of Kent
By Kent accountant
13th Apr 2018 14:08

No!!!

EAT wagon wheels don't ban them!

Thanks (1)
Replying to Kent accountant:
avatar
By johnjenkins
13th Apr 2018 15:32

Never liked them or milky ways. Yes I'm a mars bar addict.

Thanks (1)
avatar
By martinengland
13th Apr 2018 14:31

This article is ideological gibberish!

For as long as there is an array of data that needs exporting from one database to another database, a spreadsheet will always needed to do it manually. It requires human intelligence to remove the garbage from one system before the next system eats it.

For as long as the software industry continues to sell garbageware badly, then spreadsheets are the only useful tool available to the accountant in business and/or in practice.

Even to do away with the spreadsheet in the book-keeping department of a Mittelstand business would require a standardised taxonomy and common API between ALL software vendors - ledgers, billing, timesheets, expense claims, payroll, HRM, inventory, assets, debt management, procurement, authorisation/workflow, document management systems, etc.

And, of course, all of these vendors jealously protect their proprietary databases in the hope that the database design itself locks their business customer "in".

So the common taxonomy and API ain't gonna happen in my lifetime! If ever...!

And thus the spreadsheet will remain the manually-retyped thread that glues fragmented-by-design systems together.

Thanks (1)
avatar
By [email protected]
27th Apr 2018 19:27

We would concur that recent reports of Excel's demise have been greatly exaggerated. While there are better tools out there for very specific workflows, spreadsheets will continue to be used for anything and everything that cannot easily be done with something off the shelf. Also, IMO the desire for something new with as much power and flexibility as Excel but without the possibility for error is a contradiction in terms - power and flexibility (in any product) are precisely what makes errors feasible.

There is much discussion about best practices, and that's good. But even perfectly authored spreadsheets will cause chaos, the moment they are treated as an enterprise solution and shared with many end-users.

We see several very common use-cases: one is the use of spreadsheets as pricing models. This really falls into the CPQ (configuration-pricing-quoting) category, but while there are some great CPQ solutions out there (like SF.com CPQ), their flexibility cannot match Excel. Companies that sell highly tailored products and services (think insurance, manufacturing, among many others) frequently use Excel for pricing and quoting - nothing else will do the job.

But therein lies the distribution and version control problem some of the comments have alluded to - how do you enable an entire sales team to use a master version of a spreadsheet? There are solutions to this problem, and we (EASA) are one of them. Without turning this into a sales pitch, if interested have a look at some of our case studies here - http://easasoftware.com/case-studies-management-control-excel-configure-....

Our basic approach is to use a thoroughly tested spreadsheet as a logic engine for a web app, built in a no-code environment. This eliminates concerns about version control, security, and audit trail. It also enables data storage - NOT in Excel files on multiple hard disks - but in a database, where it should be.

To conclude - if there is a better tool than Excel for your specific task, use it. If not, and you must use Excel, then search for solutions to at least mitigate the risk.

I forgot to mention ClusterSeven, another excellent risk mitigation tool that works well with EASA.

Thanks (0)
Replying to [email protected]:
David Ross
By davidross
05th May 2018 09:32

"I forgot to mention ClusterSeven"

No you didn't !

Thanks (0)
avatar
By AndrewV12
14th Sep 2018 11:35

There is nothing wrong with spreadsheets, though I always advise people, check, check and recheck your formulas.

The fault nearly always lies with the person using Spreadsheets, normally big errors smoke out big idiots out of their depth.

Thanks (0)
avatar
By AndrewV12
14th Sep 2018 11:37

If anyone moans about un-picking errors on Excel, try to un-pick errors on Sage, Quickbooks and the like, double trouble if Vat related errors.

Thanks (0)