Reconciling the reconciliation

17th September 2011

Re-con-cil-iation, the word alone sends a shudder down my spine. Tying numbers together across months of work, huge volumes of data and complex processing is no simple task, however it’s such a robust method of QA that you ignore it at your peril.

Working with data or numbers in any capacity requires some level of reconciliation. Banks love them, and almost just as much love following them up with lots of adjustments. I have worked on projects with only a few team members and projects with a hundred and although the approaches and methodology used in each kind may be different, they have all involved some level of reconciliation. With my roles typically being very data intensive, I have seen more than my fair share.

Remember though, reconciliation is but a means to an end. Usually, how do you ensure that what you’ve done is what you were meant to do? Whether it’s to give yourself comfort or to prove to someone else that what you’ve implemented is correct, reconciliation proves something. Therefore it’s important to consider what it is you want to be able to prove before you start. Understanding what this is and building it into your approach, log files and outputs will help anybody trying to understand your work immensely and will save hours of pointless totalizing that nobody will ultimately use.

Another important part of reconciliation technique is understanding materiality. Some projects will require that numbers match exactly while others will be content if they are only a few digits out, a few hundred or sometimes a few million. This will all depend on context and is something that needs to be a part of the reconciliation design. Without it you will spend hours working on a reconciliation gap that nobody will care about.

The final main point I’d like to cover is Rubbish In Rubbish Out. If the process you’re executing receives something it is not designed for, it will likely produce erroneous results. Where possible reach a data supplier or interface agreement to ensure that both parties are aware of what will and will not be provided with any data set.

If you’re trying to flesh out a reconciliation approach for your project or task, try the following reconciliation techniques.

Follow the entries through, identifying specific line entries or subsets of data may help identify where the error is introduced.
Focus on the big numbers first, if you have a gap with value start investigating by looking at the big numbers, the more of them you clear the closer you’ll be to meeting your materiality agreement.
Look for outliers, what are the biggest or smallest values, can you identify patterns in entries or perhaps things that shouldn’t be there.
Check for duplication, sometimes entries may be posted multiple times especially in systems that involve archive processes or operate at Mon-Fri/9-5 processing schedules,
Try isolating the reconciliation error to a particular process step and try to understand what in that process may be introducing the error.

Reconciliation is rarely much fun but, the earlier you start, the easier it’ll be.

Back to the blog