Editor in Chief AccountingWEB
Columnist
Share this content
How to correct NIC employment allowance claims under CJRS
iStock_Calculation error_RapidEye

How to calculate the cost of spreadsheet errors

by

Even as forecasting and analysis apps moved into the mainstream, Excel has retained its hold as the most frequently used piece of software within the profession. But what are the costs of this continuing dependency?

16th Jul 2021
Editor in Chief AccountingWEB
Columnist
Share this content

Spreadsheet dependence is an affliction that has a particularly strong hold on corporate finance departments, but antidotes are available.

Over the many years that AccountingWEB has surveyed accounting software use, the accountants clinging most steadfastly to their spreadsheets tended to work in industry and commerce. The tendency was most marked among larger corporate finance teams.

Finance data management specialist Konsolidator supported this view in a recent Industry Update on data quality and transparency, describing a typical situation where group CFOs often step in at the last minute to make a final accounting adjustment to figures that have been through several internal controls.

“The final adjustments may not be ready until the very last minute and maybe hand-carried through the books by that same group of people, who prepares the adjustments,” the company noted.

Konsolidator offers a cloud-based consolidation system, so it’s easy to see why it takes such an interest in spreadsheet weak spots. But the observations and lessons it offers to back its case are strongly rooted in experiences that will be familiar to UK accountants.

The 1-10-100 rule

The most obvious observation is that nobody is perfect. Whenever processes involve humans, there is a risk errors will happen. But if they arise within a system of financial controls, where and when the errors occur can have a huge impact.

To illustrate this point, the Konsolidator article explains the 1-10-100 data quality rule developed by George Labovitz and Yu Sang Chang in 1992, which reinforces the principle that it is cheaper to prevent a defect than to correct one.

This sequence breaks into three phases:

  1. Phase one is the prevention phase, when verifying data at the point of capture would be equivalent to $1.
  2. Phase two is the correction phase, when the cost of incorrect data rises exponentially to $10. The longer you leave it, the more expensive the impact becomes.
  3. Phase three is the failure phase. Poor data leads to poor decisions, so the impact of doing nothing to fix unchecked errors would be equivalent to $100.

Against this scenario, Konsolidator argued that finance managers should re-examine their spreadsheet-based controls to identify where errors can occur and create new processes to avoid potential errors in the future.

“We still see Excel embedded in many organisations and are staggered at how complex they are. It’s time to systemise the stuff that machines can do well and use Excel for the things it’s good at,” said Konsolidator UK country manager Lianne Gatti.

Technology isn’t always the fix

“Technology isn’t always the fix. You’ve got to think about the context, people and process around it,” she continued.

Gatti has been talking to a lot of mid-size UK businesses recently and found that many of them were reviewing their processes. A lot of those reassessments were driven by an increased sensitivity to risk and recent corporate changes.

“Some companies have done well during the pandemic and are looking at acquisition strategies. Or they’ve suffered and are having to sell assets. To gear up for operations post-Brexit, a lot of them set up European subsidiaries. Managing the foreign exchange calculations is getting a little difficult to do in a spreadsheet,” she said.

“All those things affect the consolidated results they have to present and they’re concerned about the cost of getting things wrong.”

While Excel is great for presenting the figures and a good entry point to Power BI, more robust software tools are available to automate the calculations, she argued.

“Don’t get rid of Excel. It’s cheap and accessible and still a hugely important part of the finance ecosystem. But use it in the right way.”

Replies (3)

Please login or register to join the discussion.

avatar
By AdamMurphy
16th Jul 2021 14:19

Ah more spreadsheet bashing. Anybody would think this site raises revenue by ads from cloud companies. Oh….wait…

Thanks (1)
Replying to AdamMurphy:
avatar
By Hugo Fair
19th Jul 2021 15:31

That's a bit of an unfair comment ... the 'bit' being the last 4 letters of unfair!
[I nearly wrote that as an Excel formula but thought it might be lost on some].

Thanks (2)
avatar
By Ron007
26th Jul 2021 10:36

Yes a simple spreadsheet is an invitation to errors. But if you follow one of the developed industry standards and build in some checks and balances, including people as part of a process, you can catch more errors, sooner.

! Best Practices In Designing Financial Reports (Zebra Bi) 65min 2016 03 17
https://zebrabi.com/financial-reports-webinar/
.
The Three Financial Modelling Standards P1of3 2021 05 30
https://www.fullstackmodeller.com/blog/the-three-financial-modelling-sta...
What are they and why do they matter?
If you’ve been around the financial modelling world for any length of time, you’ll probably have heard about modelling standards. While they might be everywhere, it’s not always clear what they are, how they’re different, and why you should use them.
The three standards?
. * FAST
. * Mazars SMART
. * ICAEW Financial Modelling Code:
.
Build the perfect error check into a financial model?
https://www.acuitymag.com/finance/how-do-you-build-the-perfect-error-che...
Embedding error checks in your spreadsheet design cuts down the workload by shouting out when there’s a figure you need to look into more closely.
.
The Ultimate Guide to Financial Modeling Best Practices
https://www.wallstreetprep.com/knowledge/financial-modeling-best-practic...
How to structure, format, audit and error-proof your financial model
Like many computer programmers, people who build financial models can get quite opinionated about the "right way" to do it.
In fact, there is surprisingly little consistency across Wall Street around the structure of financial models. One reason is that models can vary widely in purpose.

Thanks (0)