Financial Reconciliation

At MCA Namibia, two groups of staff kept track of payment-related data in their own systems. For Contract Managers, each payment corresponded to a Contract deliverable, so they were responsible for managing the correct amounts, and original, currently expected, and actual dates for each payment. For Finance Staff, each payment needed to be coded against the correct Chart of Accounts code, and they tracked their performance by how fast they could affect each payment.

A few key details for each payment were common between the two groups’s sets of data–currency, amount, actual payment date, amount in USD, and Chart of Accounts code.  Since each group’s systems were not capable of replacing the other’s (and it was too expensive to get a new system that could), Patrick was tasked to build a system that could ensure the data common to both groups was equivalent at a payment-for-payment level.

Patrick designed and developed a set of tools to respond to this need:

• A command-line tool that automated the reconciliation of payments where all the important details matched between the systems, and updated the Actual USD Amount and Actual Paid Date in one system based on the others when reasonable. This automation made no changes if any of the important details did not match.
• A way for a manual review of each issue found by the automated program. This program resulted in a list of issues for individual payments that could be easily reviewed by people who would determine the correct action to resolve each issue. Most issues found were payment amounts being off by a cent (rounding errors), or use of an incorrect Chart of Accounts code, but occasionally, larger problems were found, for example a decimal point in the wrong spot, or a currency mismatch.
• A payment lookup tool that could be used to determine the status of a specific payment in each system.
• A search engine built specifically for financial data. We’ll feature this separately in our portfolio because it’s pretty awesome in itself.
• A dashboard to track the progress of the reconciliation process to aid management (so they could decide if additional people should help the process or not)

The results of these systems, and a good deal of manual labor, was that out of US$304.5 million in payment data, we were able to match up 99.998% of that. Not perfect, but not too shabby.