THE 1-10-100 RULE: THE REAL IMPACT OF POOR DATA IN FINANCE AND THE KEY TO AVOIDING ERRORS IN FUTURE REPORTING
Have you ever wondered why you leave the big decisions until the last minute, and then make a mistake?
A lot of you will recognize this from your personal life, but it certainly holds true for a lot of accounting decisions. And often, it means that it is these last-minute journal entries that go wrong, even when experienced finance professionals are involved.
Why is that?
In this blog, we summarize the answer to this question – and the answer also holds the key to avoiding errors in future reporting.
WHY IS IT ALWAYS THE LAST-MINUTE JOURNAL ENTRIES THAT GO WRONG?
Reporting from consolidated entities is often subject to multiple, local controls. Your control framework – why is often worked intensely on, with the help of auditors and consultants – requires all journal entries to be documented, approved, tested, and audited. Also, local reporting often forms the basis for local KPI’s, bonus schemes, and balanced scorecards. In itself something which 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 very final accounting adjustments, made on a group level, are often handled by a very select group of employees.
And this group of employees is often pretty important in finalizing the financial statements for the year! It probably has the company CFO in it, as an example. They may be the only people in the company knowing that the company wants to restructure a division, close down a factory, cease an operation, or they are ones to assess a potential impairment etc. Therefore, 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.
APPLYING THE 1-10-100 RULE TO YOUR EXCEL SPREADSHEETS
Excel spreadsheets are great for many things but not for audit trials! Often, these last-minute adjustments are incorrectly handled in the spreadsheets.
Here are three classic examples of easy mistakes the most of us have come across (unintendedly) at some point in our career as a finance professional:
1) Formulas are overwritten by a value. The CFO assessed the value of the obligation, and it is quicker to just book it right there and then, in the consolidation spreadsheet. You will worry about the entry in the accounting system some other day.
2) Cells, rows, or lines are added or deleted, with totals and subtotals then being incorrect.
3) A rule to transform a source value to a result is not documented. The currency rate between the local entity reporting currency and the consolidation currency is just entered as a number in the spreadsheet cells.
So, with these (typical) errors in mind let us look at the 1-10-100 rule.
The 1-10-100 rule (developed by George Labovitz and Yu Sang Chang in 1992) suggests that it is much less costly to prevent a defect than to correct one.
In fact, moving from prevention of an error to detection increases the cost by a factor of 10. And, if you move on to correction – or failure – that factor is 100!
Let us illustrate the rule in its details.
There are three phases in the rule – each of which explains the cost of maintaining data quality.
In phase 1, the “prevention” phase, you have $1.
This represents the amount it costs to verify accurate data at the point of capture. This is the least expensive and by far the most effective way of ensuring you capture clean and accurate data.
In phase 2, the “correction” phase, $1 has increased exponentially to $10.
The $10 represents the increased cost that incorrect data has on your business the longer you leave it.
In phase 3, the “failure” phase, you will see the $10 has increased tenfold to $100.
This $100 represents the amount your company will pay for doing nothing about their poor data.
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 favor and find a method that supports accurate data processing and that won’t let you easily commit those manual errors we are often prone to make 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 that has negative consequences or fail to achieve the desired outcome. Of course, we are all convinced that the errors do not apply to our Excel spreadsheets… but with 9 or if 10 spreadsheets containing errors, the risk is high!
Nobody is perfect and whenever processes involve humans there is a risk error will happen.
So, the takeaway here is to be able to identify those processes where errors can occur and create processes around them that help you avoid those errors next time you are in a hurry to complete your consolidation. Right now, you can start by taking a good hard look at your Excel solution and evaluate if it is the tool you should be using for your consolidation.
By the way, your auditors will also focus on your Excel sheets in a few weeks or months from now. 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
To make sure you avoid unintendedly making mistakes in your last-minute journal entries, we recommend using consolidation software. The advantages of an actual system vs. a spreadsheet solution are many, but particularly for the purpose of documenting those important journal entries the advantage is of course the formalized structure, a consolidation software gives you.
Just a few examples:
- A system requires you to enter a journal entry in a certain 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 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 as a result of a last-minute decision, then 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