How to calculate the cost of spreadsheet errorsby
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?
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:
- Phase one is the prevention phase, when verifying data at the point of capture would be equivalent to $1.
- 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.
- 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.”
You might also be interested in
AccountingWEB’s interim Editor in Chief has been with the site since 1999 and returned to the editorial hot seat in March 2020 to lead the hunt for a long-term successor... Send a DM if you're interested! When not tending to the needs of AccountingWEB members and geeking out on their technology habits, he devotes much of his time to his oddball...