How do I investigate a debtors or creditors reconciliation variance?

Question

How do I investigate a debtors or creditors reconciliation variance?

Answer

The Debtors Reconciliation and Creditors Reconciliation reports calculate the difference between the Trade Debtors and Trade Creditors control accounts in the general ledger, and the sum of the transactions in the sub-ledgers.  Any variance between the general ledger and the underlying transactions is the result of an error and needs to be corrected.

The reconciliation reports should be run weekly when you first start using Jiwa so that problems can be identified quickly, and changes to processes or more training can be implemented.  After several months you may want run these reports on a monthly basis. (Refer here for other month end reports: What reports should I check at the end of each month?)

Follow these steps to resolve your variance:

1. Determine the date of the variance

Both the Debtors Reconciliation report and Creditors Reconciliation report can be run as at a particular date.  Keep running the report moving gradually backwards in time to determine exactly when the variance occurred.

TIP: If you have future dated transactions in your database, this will affect the reconciliation report.  Try running the report as at a couple of months in the future and see if the variance resolves itself.

Using the transaction filters on the General Ledger Maintenance form you can examine the transactions posted to the control account around the date of the variance to look for irregular transactions.

General Ledger > Maintenance > Transactions

The best report to use is the General Ledger Listing with Source report.  ‘Source’ refers to the source of the transaction, i.e. which form was used to enter the transaction.

This report can be used to determine all the transactions that have posted to the control account, sorted by source.

TIP: If all your Classifications are set up correctly, the most common source of variances is Local Journals.  Check transactions with a source of ‘Local’.

This report can also be used to determine the inverse – that is, all the likely source transactions and which accounts they have posted to.  For example, a creditors reconciliation variance may be the result of a creditors adjustment that posted to the wrong account.

TIP: Do you have any creditors with a Period Type setting of ‘Custom’?  In Jiwa versions 07.00.180 and prior there are some reports that don’t read creditors transactions arising from creditors with this setting.  The Creditors Reconciliation report is one of them.  Change the setting on your creditors to Weekly or Monthly and the variance may resolve itself.

The best solution to resolving the variance depends on the nature of the error and the time period it spans.  It most cases a simple general journal may be all that is required.

If you have followed all the steps above and you still can’t determine the cause of your variance or how to fix it, please contact your Jiwa Solution Provider.