Thursday, 18 October 2007

How To Reconcile

Have you seen this situation?

  1. Two accounts don't reconcile (e.g. intercompany accounts between two subsidiaries or the subledger and the GL control account)
  2. A spreadsheet is created
  3. Several adjustments are attempted
  4. The reconciler spends days on the problem but does not finish
  5. Result: A frustration wrapped in a disappointment inside an annoyance!
A company now has a software package called RecWizard to help you with reconciliations, but here is an approach that only requires a spreadsheet. The key is having a methodology. Rather than diving in with random adjustments, start eliminating sources of mistakes, so that you can narrow down the places to look. For example, let's say that the Accounts Receivable Aged Trial Balance (ATB) doesn't agree with the GL balance at October 31.

  1. Limit Time - check the balances at the end of previous months to figure out which month contains the error.
  2. Limit Sources - Accounts receivable consists of invoices, cash receipts and adjustments. Check the subtotals for the sources for the month so you can narrow down to one source. Check that the difference(s) you found in the sources totals to the amount of the discrepancy.
  3. Match ALL the transactions - Download all the detail transactions for the identified source and month into a spreadsheet and use a common field (e.g. your system's transaction number) to link between the GL transactions and the AR transactions. The total of your unmatched transactions should equal the total difference identified in step 2.
  4. Eliminate all the transactions that match and investigate the remaining transactions.
  5. Document what you have done so that others can follow the adjustments you make.
  6. Change procedures so that whatever caused the problem does not recurr.
Hint: whenever you are comparing two columns of numbers, take the time to get them sorted in the same order so that you can see the differences easily.

Reconciliations are time wasters and frustrating no matter how good your methodology. The best cure is always prevention.