Digital Transformation » How to avoid common data mistakes

In the news, we’ve all read about the corporate scandals caused by some pretty basic spreadsheet blunders.

Who can forget the time a poor Fidelity employee accidently omitted a minus sign, meaning their dividend estimate spreadsheet was off by $2.6 billion? Or the TransAlta cutting and pasting issue, which cost the firm $24 million? What about the time Westpac inadvertently used a spreadsheet template from the previous year? And the time a Kodak employee added $9 million to the company’s losses by adding too many zeros to a value?

Of course, these were all honest mistakes with no malice or intent, but the frequency and impact of these easy-to-make errors should concern any organization. Here are some common spreadsheet errors and how to solve them.

Spreadsheet risk

In spreadsheets, it is almost too easy to make small errors that go unnoticed – a misplaced value might seem trivial and can be very hard to detect. However, many errors can compound and turn into much bigger problems further down the line. A wrong sign, a missing significant digit, the wrong formula, and a bad assumption could all slip into your work and there are many error points in using a spreadsheet that can expose you and your organization to significant risk.

Fortunately, you can ask questions of your process and technology tools to ensure that your organization is doing everything possible to prevent damaging errors.

Are you using the right data?

Often, you don’t need all the data in your modelling exercise as this can over-complicate the analysis and increase the likelihood of errors. Avoid copying and pasting of data as this can be a high-risk activity. If you’re off by just a single cell on your copy or paste and your model is no longer correct. Use systems that are available to establish a dynamic and secure access point to the data source that can be updated on-demand.

Are you asking the right question?

Ask the simple question first. Don’t get lost in the weeds with overly complex calculations leading to huge, multifaceted, error-prone (and often slow) models. Remember, it is easier to add further calculations to a simple model than try to simplify an overly complex model. And don’t forget to always check your assumptions. Are they accurate? Get others involved in the process to sanity check your assumptions to make sure the answer makes sense. Get others to check your answer, challenge your questions, then check it again!

Are you using the right structure?

We live in a multi-dimensional world with most organizations offering multiple product lines to multiple regions. If you start asking the “what-if” questions, scenario analysis becomes vitally important and this can only work properly in a well-designed and well-structured multi-dimensional model. Think about structure first, then layer in the logic and data.

Are you using the right logic?

It is very important to make sure the business logic of your model (the formulas) are well documented

and easy to understand. Explaining the calculations of the model in plain English will go a long way if you (or someone else) need to revisit the model in the future. Future proof your models by spending the time to clearly document the business logic for all to see and use.

Are you using the right tools?

This may seem simple, but it is important to ask if you are using the right tool for the job. Are you doing something very simple that lacks dimensionality? A spreadsheet is probably fine for this purpose, which it can act like an advanced calculator. However, if you need the model to be dynamic, easily change as your business conditions change, be secure and auditable both now and in the future, you may want to consider a different piece of technology.

Whether it’s visualization tools such as Tableau or Qlikview, or a full enterprise-level business modelling platform such as Quantrix, using the right tools for the right job will decrease the risk that you land on any unfavorable “top 10 spreadsheet error” lists in future. Modern applications use professional features such as natural language formula writing, built-in audit trail and a visual dependency inspector to help you mitigate the risk of spreadsheet error and put your focus where it should be: making sound databased decisions to help drive your business forward. Therefore, you need consider whether your existing spreadsheets are appropriate for the complex tasks at hand. As we showed in the introduction, even the biggest companies can get it wrong. Make sure you’re not one of them.


This article was contributed by Quantrix, the world’s leading business modelling platform.