Skip to content

Data Reconciliation

Reconciliation is a component of an integrated, data solution that will lead to the blame game if it is not attended to. How many times do we hear of systems that the business treats as a mysterious black hole delivering questionable results? These are the systems that are candidates for being thrown out when the next CFO\CTO\CIO comes along.
Now it could well be that these systems are accurate and delivering the intended business value, but the project team have an uphill job of proving it, if they haven’t fulfilled their obligation of proof. To quote a recent client, “it’s not right until it’s reconciled”. You might say that SIT, UAT and all the other testing phase you wish to go through should be enough to “persuade” a business sponsor to sign off on your implementation. The issue with this approach is that it only tells us that the system was working during the controlled testing phase, it does not tell us that the system is continuing to work. So, what are some of the tangible benefits? They include:

  • Increased business confidence by being able to explain differences if they exist
  • Reduced Revenue Leakage by ensuring you invoice for all the work done
  • Increased efficiency by not having to spend valuable time digging into discrepancies
  • Improved staff engagement, as they are doing interesting work not simply crunching reconciliation issues.
  • Easier Fraud Detection as we know where the differences are originating from.
  • Reduced SIT, UAT and BAU effort as there is already a solution to assist in testing.


Reconciliation solutions come in many shapes and sizes but typically share the following functions:

  • Independent, arm’s length approach. The reconciliation solution should not be closely coupled to any systems.
  • Low level transactional data should be used where possible as this enables diagnosis of the issue, not just identification.
  • Master Data reconciliation is integral to ensuring that systems are in sync. For example, do the lists of vendors agree between ERPs?
  • Report do not resolve. It is not the responsibility of the reconciliation solution to resolve data issues. Always return to the source and make your corrections there, this will ensure separation of tasks and avoid confusion of where the “real” data resides.
  • Reconciliation issues are ranked based on their significance, this can be based on risk, financial, or any number of other factors. This helps users prioritize their work which is of particular importance when new systems are implemented. It is very easy for users to be overwhelmed with exceptions and they need to be able to choose which ones matter most.
  • Configurable tolerances which ensure that the reconciliation system can evolve overtime as the data quality and business processes improve.
  • Analytics and reporting of issue trends need to be available to help raise visibility of issues and help drive changes in behavior or improvements in system integration.
  • Flexible scheduling to deliver nightly, month end or even near real-time reconciliations
  • Tracking changes made to a record to achieve reconciliation, thereby ensuring an audit trail is provided. These changes need to be made in the source but should be tracked with the reconciliation system. For example, last time purchase order was loaded it had a total value of $1,200 and was out by $7, today it is correct with a balance of $1,207. There should be two version of this record in the reconciliation solution showing the before and after changes.
  • Reconciliations are tagged as having triggered a particular version of a rule (i.e. say a tolerance of +/- 5%). If we revise the rule in the future, it is important that we know what it was when the original exception was reported.
  • Workflow functionality needs to be implemented in order to driver accountability and timely resolution of reconciliation issues. There is little point in reporting issues if you are not going to motivate users to resolve them.
  • In areas where fraud is a real risk it is vital that access to the reconciliation rules is secure. Failure to do so negates the purpose of reconciling.

So, what are some of the issue in building or implementing a reconciliation solution? The following are a few:

  • Deletions in the source. How many times do we find that users/dba(s) find a way to delete information which throws the integration out of sync? See my post ‘What do you mean they physically delete the data!!!’
  • Embedding business rules in the reconciliation rules which will require maintenance if the business change the rules within the source system(s).
  • How to effectively communicate issues to the Business Users, BAU, ICT, Projects, etc. to avoid the blame game?
  • Managing different hierarchies in the various systems, sometimes you do need to compare Apples and Oranges
  • Different accounting periods in the various systems. What do we mean by all transactions in March 2019?

After having delivered several of these types of projects, here are some learning’s:

  • Well defined Usage Contracts or Interface Contracts are vital. They are the first line of defense for the system integrator.
  • Deliver the reconciliation service at the same time as the underlying system(s). It will assist in SIT, UAT and general business user confidence.
  • Sponsorship from Execs

In conclusion, reconciliation is a Business and an IT Application Control obligation that can smooth the way to timely implementations and reduced support costs 

Image by congerdesign from Pixabay

Leave a Reply

Your email address will not be published. Required fields are marked *

Close Bitnami banner