Calculating the real impact of poor data in finance is the key to avoiding errors in future reporting.
Even though many of us know that we risk making mistakes by leaving big decisions until the very last minute – we still do it. Many of you will recognize this from your personal life, but it certainly holds for many accounting decisions. Whether it is due to time pressure, overconfidence, too many tasks, insufficient hands, or other things, we often see that last-minute journal entries go wrong – even when experienced finance professionals are involved. But why is that?
In this blog, we investigate the answer to this question – and the answer also holds the key to avoiding errors in future reporting.
Why is it always last-minute entries that go wrong?
Reporting from consolidated entities is often subject to multiple local controls. Your control framework – which is usually worked intensely on with the help of skilled auditors and consultants – requires all journal entries to be documented, approved, tested, and audited. Also, local reporting often forms the basis for local KPIs, bonus schemes, and balanced scorecards. In itself, this requires due diligence in reporting (not saying, of course, that you cannot find errors and omissions in the history of accounting, even down to accounting performed in local sub- or sub-sub-entities). However, the final accounting adjustments, made on a group level, are often handled by a select group of employees.
And this group of employees is often crucial in finalizing the financial statements for the year. It probably has the company CFO in it, as an example, who may be the only person in the company knowing whether the company:
- wants to restructure a division,
- close down a factory,
- cease an operation,
- Or other
This group of people is the one to assess a potential impairment. As a result, the final adjustments may not be ready until the very last minute. Furthermore, they may be hand-carried through the books by the same people who prepare the adjustments. But if this is the case, there should be processes that ensure a fast and accurate approach to avoid the risk of errors. The 1-10-100 rule illustrates the importance of this.
Applying the 1-10-100 rule to your excel spreadsheets
Excel spreadsheets are great for many things but not for audit trails! Often, we mishandle these last-minute adjustments in the spreadsheets.
Below you might recognize some of the three classic examples of easy mistakes that most of us have (unintendedly) come across at some point in our careers as finance professionals:
1) Formulas are overwritten by a value. The CFO assessed the value of the obligation, and it is quicker to book it right there and then in the consolidation spreadsheet. You will worry about that entry in the accounting system some other day.
2) Cells, rows, or lines are added or deleted, which further down the sheet results in incorrect totals and subtotals.
3) A rule to transform a source value to a result is not documented. The entered currency rate between the local entity reporting currency and the consolidation currency is just a number in the spreadsheet cells.
With these (typical) errors in mind, let us consider why the 1-10-100 rule is relevant.
In 1992, George Laboviiz and Yu Sang Chang developed the concept of the 1-10-100 rule. The rule suggests that correcting errors is more costly than preventing them. In fact, moving from the prevention of an error to detection increases the cost of a mistake by a factor of 10. And, if you move on to correction – or failure – that factor is 100! Relevant for everyone in finance today who spends more time correcting errors than preventing them. Even more so due to the digital tools, we have available to ensure data quality. In truth, you know it, and we know it; it all comes downs to data quality when delivering reporting.
The three phases of the 1-10-100 rule
Let’s illustrate the rule in detail. The rule has three phases, each explaining the cost of maintaining data quality.
In phase 1, the “prevention” phase, the cost of errors is $1.
Following this rule, it will cost you $1 to verify accurate data at the capture point. It is the least expensive and, by far, the most effective way of ensuring you capture clean and precise data. At this point, your data originates, and haven’t influenced other data yet, e.g., your subsidiary’s wrong balance sheet hasn’t affected the group’s result. Therefore, it makes sense that controlling your data before it enters the report will always be the preferable and less expensive choice.
In phase 2, the “correction” phase, $1 has increased exponentially to $10.
Now your $1 has increased exponentially to $10. Why? The $10 represents the increased cost of incorrect data on your business the longer you leave it. More specifically, if one subsidiary has delivered a wrong number, this data will feed into the balance, which then will be part of the group’s results – suddenly, you risk providing an incorrect result. And it will take much longer to find and correct the mistake, ergo the high increase.
In phase 3, the “failure” phase, the $10 has increased tenfold to $100.
If you do nothing and do not catch the error, the cost increases to $100. Furthermore, you risk delivering data filled with unintended mistakes. If you do not correct poor data in time, it will affect your overall result. And honestly, it happens way too often in finance reports – whether you recognize it or your colleague can. But the time and money you use to correct and prevent it from happening again have suddenly increased drastically too.
Poor data leads to poor decisions
While we can all agree that it is a financial (and time-consuming) problem to correct poor data, the biggest issue with poor data is that it leads to poor decisions. So, do your company (and yourself) a favour and find a method that supports accurate data processing. A method that won’t let you easily commit those manual errors we are often prone to make, especially when we are in a hurry and lack a formalized and structured data processing method.
Studies suggest that almost 9 out of 10 spreadsheets contain errors. And by errors, we mean unintended actions with negative consequences or failing to achieve the desired outcome. Of course, we are all convinced that the errors do not apply to our own Excel spreadsheets – but with 9 out of 10 spreadsheets containing errors, the risk is high!
Nobody is perfect, and there is always a risk when processes involve humans.
So, the takeaway is to learn to identify those processes where errors can occur and create new processes around them, which will help you avoid those errors next time you are in a hurry to complete, e.g., your consolidation.
Right now, start by taking a good hard look at your Excel solution and evaluate if it is the tool you should use for complex reporting like your consolidation.
Furthermore, your auditors will also investigate your Excel sheets in a few weeks or months. They will verify that you can track all the consolidation amounts back to their source, and their focus will also be on those last-minute journal entries.
Preventing errors with a formalized structure
We, of course, recommend using consolidation software to avoid unintendedly making mistakes in your last-minute journal entries. The advantages of an actual system vs. a spreadsheet solution are many. But the particular advantage for the purpose of documenting those important journal entries is the formalized structure a consolidation software gives you.
Here are just a few examples:
- A system requires you to enter a journal entry in a standardized manner.
- A system will provide you – and your auditors – with an audit trail.
- A system makes sure you cannot post a one-legged entry.
- A system (often) requires you to ask experienced colleagues to perform the entry because of segregated duties between employees.
- A system calculates all entries and does not skip cells or columns.
Even for SMEs, we believe that the advantages of consolidation software outweigh the costs. If you have just a few consolidated entities in your group, chances are that the consolidation process is not embedded in your daily routines – every consolidation is a new experience, and you have to start all over again. Here, a consolidation software solution will give you a head start.
So, if you have ever made a wrong call due to a last-minute decision, don’t take this habit into your accounting manners. Get started on a formalized consolidation process and stop making errors in the last-minute journal entries.
Get a quick preview of how your consolidation can look with a formalized structure in Konsolidator.