I would be surprised if you did not know what reconciliation is, however I would also be surprised if you knew how important it was to your compliance efforts. I’m sure you view reconciliation as something that is “good to do”. However, I would urge you to be a little more thorough and diligent about reconciliation, when compliance matters are at hand.

Reconciliation is more than just a way to make sure that your data loaded properly. You will see reconciliation come up as a key way to control a number of risks. Here are just a few of the risks that can be controlled with reconciliation:

• The risk that an experienced processor accidentally enters the wrong data
• The risk that an inexperienced processor purposely enters the wrong data
• The risk that a system is calculating the data wrong
• The risk that a data load failure went undetected
• The risk that journal entries were missed
• The risk that fraud or embezzlement is being committed

So, to tackle reconciliation from a compliance data system standpoint, I’ve compiled a few tips to help you build a good reconciliation subsystem.

Tip # 1: Don’t Confuse System Reconciliation with Business Reconciliation

As a matter of good design, you will need to make sure your data is loading properly. This is what we have ( or should have ) been doing for years. To do this, you will probably have a point of reference from the source system that you will compare to once all your transformations are done. I would expect you to do this, and you should, however this is not the kind of reconciliation we’re talking about.

Reconciliation from a control standpoint, involves reconciling two or more different business systems, to make sure that the business processes are under control. For instance, you might have two different financial systems – one contains balances and the other contains transactions. Your reconciliation system will make sure that the sum of transactions for a period equates to the difference between the closing balance and the opening balance.

You want as many reconciliation points on the same metric as possible. You must have at least two, however don’t stop there. If you can get three or four or more, that will make your system much more valuable and reliable.

Tip # 2: Reconcile To an Authoritative Document or Report

 I’ve built a number of financial data warehouses, for the Finance department, and I’m alarmed at how many times the recon reports don’t really tie to anything! This tends to happen when Finance segregates you out of the solution. They will probably take your recon data, and upload it to their spreadsheets so they can do the “real” Finance work. Then they will come to you when they get stuck, and you will have no context of what is going on.

This is why it is so important to partner with Finance, instead of subcontracting for Finance. Engage yourself in the business problem at hand. When you do that, you will find that there are a set of authoritative reports within your company. The most authoritative financial report in your company is your company’s 10K. This is the annual report your company files with the Securities and Exchange Commission ( SEC ).  Quarterly, they file a 10Q with the SEC, which is another good authoritative document. In these reports are stated all the “official” financial numbers for the company.

The ideal situation would be to have a feed from the system that submits your company’s 10K and 10Q, and load that data directly into your database. If that’s not possible, try to find a feed that is as reliable and accurate as possible. If you cannot find a feed, you may need to suggest the construction of a transactional system that will store this data, or upload it somehow directly to your database ( not recommended ).

However it gets there, make sure you have an image of the authoritative set of financial data housed in your system. This will be the gold standard, by which all the rest of the numbers in your system are compared.

Tip # 3: Highlight Reconciliation Exceptions in Your System

A lot of times, I see reconciliation reports that just produce summary data, or actual data. In more sophisticated systems, I will see actual data sitting next to the gold standard data described above, or expected data. In both cases, however you are leaving it up to the user to compare actual data to expected data ( whether or not the expected data is actually on the report ), and make a human-based determination on the match. It’s quite odd to me that this is so common. Computers obviously do a much better job at comparisons than humans, so let the computer do the comparison.

I always turn control situations like this into Booleans. Store the numbers, compare the numbers, and store the result of the comparison. Then compare all the comparisons, and summarize the comparison result. Each number to number comparison will result in a TRUE or FALSE. Summarizing the comparison result means looking at all the TRUE/FALSE data, and if there are any FALSEs, the summary is FALSE.

You want to make exceptions ( FALSEs ) easy to find, so I would suggest creating a separate table that contains only exceptions. Your exception table should give clues on how to troubleshoot what’s going on.

Tip # 4: Consider a Supplemental System for Auxiliary Support

Not all data can be downstreamed into your system. I would consider a small transactional system or supplemental data system where users can upload supportive data, so your reconciliation system is holistic. Here are three things you will want to consider for your supplemental data system:

1. Authoritative financial data. As mentioned above, if you do not have a direct feed from a place that contains authoritative financial data, you may want to store it here.
   
2. User entered reconciliation notes. This can be to explain away exceptions, or just document other findings as your users are going through the reconciliation process. This should be as structured as it needs to be. If you have one big NOTES field, and users are using a common format for entering common data in this field, you should redesign the structure.
   
3. Reconciling entries. In the end, the combination of your actual data and your reconciling entries must equal your expected data, without exception. If you can get your system in this state every reconciling period ( i.e. every month ), your system will be ironclad.

When building a compliance data system, reconciliation will certainly play a role, if not a huge role. It’s not just “a nice thing to have” anymore. Getting your reconciliation right will enhance your ability to accurately communicate business process control to your end users. Using authoritative sources, highlighting exceptions, and using supplemental data to support your system are key tips to building it in the right way.