Laptop screen showing Vena blog, over a grid background.

The Mistake Everyone’s Making About Spreadsheet Errors

November 4, 2015 Mitchell Buchanan  
LinkedIn Twitter Facebook

To err may be human, but for some financial software vendors, to keep using Excel is unforgivable.

The latest salvo comes in the pages of Capitalism’s Dirty Secret, a report from U.K.-based financial consulting firm F1F9, which says 33% of large businesses report poor decision making due to spreadsheet problems and nearly one in five large businesses have suffered direct financial loss due to poor spreadsheets. As the managing director of F1F9 told the Telegraph:

“More often than not just one person in a company has the knowledge of how the financial spreadsheet models are constructed. Other people are unable to understand and therefore check the analysis. The potential for errors is massive.”

The Truth about Errors in Financial Spreadsheets

What might get lost in all this is the fact that F1F9 isn’t advocating companies abandon spreadsheets (one of the headings in the last chapter of the report is “Spreadsheets Are Here to Stay”) but provide better training and best practices around using them. The first step may be admitting you have a problem. In July, for example, an academic paper from the University of Hawaii on spreadsheet errors showed that our brains aren’t always ready to face the truth:

“Humans have many intuitions about errors, but the way our cognition works means that we are conscious of very few our errors. Consequently, we tend to believe, among other things, that few spreadsheets have material errors, that we make few errors, that anyone can make only rare errors and catch almost all errors if they just tried harder and spent more time.”

As the Financial Times recently observed, however, research has shown there is a difference between poor usage and mistakes that cause major repercussions:

“What qualifies as an error in spreadsheet land is often subjective. For example: putting a number (rather than a cell reference) directly into a formula. Such “hard-coding” is almost always bad practice, especially if there is any chance whatsoever that the value of that number will change — as with, say, an inflation or interest rate. But if the number doesn’t change, it won’t lead to a wrong result. So is it an error?”

What Automation Brings To Excel

Besides standardizing the use of spreadsheets and educating users appropriately, finance industry observers also fail to consider how automation can help mitigate or limit the human propensity for error. Centralizing the underlying data with workflow and rules around acceptable values, reviews and approvals is probably more productive than creating another laundry list of Excel spreadsheet errors and horror stories.

To be sure, virtually every typical error in corporate spreadsheets can be attributed to manual-intensive data entry and consolidation, a challenge that can easily be overcome by incorporating into Excel functionality such as:

  • Centralized Data: so Excel is the interface into a “single version of the truth”
  • Workflow: laying out a clear flow of data from contributor to reviewer to approver
  • Version Control: including the ability to revert back to previous versions
  • Permissions and Business Rules: on the visibility and ability to edit data, right down to the individual user and cell levels
  • Detailed Audit Trail: detailing, time and date stamping every change made at the transactional or cell level across every corporate spreadsheet.

Yes, mistakes using Excel will happen. But the steps or features required to minimize – or even eliminate – these errors are both readily available and easy to implement. Indeed, to ignore the solutions that improve the accuracy of financial spreadsheets could be the biggest error of all.

Recommended Posts