How sad is it that (i) I love nothing better than tapping away on a spreadsheet and (ii) I OWN that mug?!!!

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

From: Francois Badenhorst, AccountingWEB’s business editor:

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 ticketsto 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.

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”.

See the full article here.