Reasons why the Debtors or Creditors Control Accounts do not agree with the aged debtors or creditors reports, and how to take corrective action
As part of your weekly or monthly routines, you may produce and reconcile your Aged Debtors report with the balance on the Debtors Control Account. This enables you to keep track of your customers’ payment performance and chase any outstanding debts. You are also able to check that the Debtors Control Account balance agrees with the balances on the Customers ledger.
Note: This article explains why the balance on your Debtors Control Account may disagree with your Aged Debtors Analysis report. To use this article to check your creditors balance, substitute debtor with creditor and customer with supplier.
To produce an accurate Aged Debtors report, you must ensure that there are no searches applied to the Customers list, you have selected the Exc Later Payments check box and if you are using the detailed reports that you use a Transaction Date Range from 01/01/1980 to the end of the period being reconciled.
If the two balances do not agree it could be due to one of the following:
- A genuine discrepancy in the accounts, for example a journal entry posted directly to the control account
- Transaction allocation problems
- Data corruption
Here we explain how to establish the reason why the discrepancy has occurred and how to correct this difference and contains the following sections:
- To run the Check Data option – Before proceeding with this article, you should run the Check Data option and check for warnings about the Debtors Control Account balance.
- To check for and correct journals posted to the Debtors Control Account – If you post journal entries directly to the Debtors Control Account, they do not update the Customers ledger and therefore do not affect the Aged Debtors report. This section explains how to find out if you have posted any journals directly to the Debtors Control Account and how to correct them.
- Transaction allocation dates – If the Aged Debtors report does not agree with the Debtors Control Account for the same period and no warnings appear when you run the Check Data option, it may be that the transaction allocation dates are causing the discrepancy.
To run the Check Data option
Note: If you have a multi-user licence all other users must log out of the company before you can access this option.
- Open the File menu, choose Maintenance then click Check Data.
As the data files are checked, the progress appears on the Checking Data Files window.
- If there are no problems, the File Maintenance window appears informing you of this, to continue, click OK.
If you have data warnings about your Debtors Control Account balance disagreeing, you should make a note of the values by which they disagree.
If you have errors on your data, you must correct them before proceeding. Please ring Synergy Network straight away.
- To close the File Maintenance Problems Report, click Close.
The File Maintenance window appears.
- To close the File Maintenance window, click Close.
You have now checked the validity of your data files. If you have a warning about your Debtors Control Account balance disagreeing, please refer to the following section.
If your Debtors Control Account balance disagrees with the balance on the Customer ledger, but there is no data warning about this, please proceed to the section What to do if correcting journal entries have been posted to the Debtors Control Account with incorrect dates later in this article.
To check for and correct journals posted to the Debtors Control Account
- Open the Company module, from the Links pane select Nominal Ledger then click Reports.
The Nominal Reports window appears.
- Open the Day Book Reports folder, then select the Day Books: Nominal Ledger report.
From the drop-down list, choose Printer then click Generate Report.
The Criteria window appears.
- In the Nominal Code From and To boxes, enter your Debtors Control Account nominal code then click OK.
Tip: If you are using the Sage default Chart of Accounts, the Debtors Control Account is 1100, and the Creditors Control Account is 2100.
The Print window appears.
Note: If there are no journals posted to the selected account, an information window appears informing you that there is no information for the report. To confirm this and return to the Nominal Ledger Reports window, click OK. You should now proceed to the following section To check for and correct duplicate opening balances.
- Complete the Print window as required then click OK.
The Day Book: Nominal Ledger report for your Debtors Control Account prints.
You have successfully printed the Nominal Ledger day book report and should now check all of the journal entry transactions that appear on this report.
- If the journal entries on this report are correct and you know the reason why you have posted them directly to your Debtors Control Account, then you should make a note of the transactions and the reason why you have posted them. This information should be passed to your accountant or your auditors at the year end.
- If any journal entries appear which should not have been posted directly to the Debtors Control Account, you should make a note of them. You will need to reverse these entries by posting a reversing journal entry transaction. For further information about posting journals, please press F1 to open the Help system.
You have now checked for and corrected any journals posted to the Debtors Control Account. If this has corrected the discrepancy between the Debtors Control Account and the Aged Debtors, you can continue using Sage Accounts. If this has not, please proceed to next section
Transaction allocation dates
When you run the Check Data option, if there are no warnings then the discrepancy is probably due to one of the following:
- An invoice with a date after the specified report date has been paid by a transaction with a date that falls before the specified report date. For further information about this, please refer to the following section Invoice allocation dates.
- The Clear Audit Trail option has been used. This option may have removed invoices that should have been outstanding at a specific date, therefore causing a discrepancy until the report is run to a date after the date of the payment. For further information about this, please refer to the section Transactions have been removed by the Clear Audit Trail option.
Invoice allocation dates
If you post an invoice which is dated after the receipt or payment that pays it, and the invoice date does not fall within the date range specified for the Aged Debtors report, it will cause discrepancies when you try to reconcile. This is because both the invoice and the payment do not appear on the Aged Debtors report, but the effect of the receipt or payment is included in the Trial Balance report, for example:
|SI – Sales invoice||02/07/2007|
|SR – Sales receipt||28/06/2007|
If you produce the Aged Debtors report and the Trial Balance report up to the 30th June 2007:
- The sales invoice does not show on either the Aged Debtors report or the Trial Balance report.
- The sales receipt does not show on the Aged Debtors report because it is not outstanding, but is included on the Trial Balance report because it falls within the date range used for the Trial Balance report.
- This causes a discrepancy between the two reports.
A set of reports are available to check for transactions with allocation date problems. For a copy of these reports please contact Synergy Network.
When you have located these transactions you need to either reverse the sales receipt or amend the sales invoice date. For further information about this, please press F1 to open the Help system.
When you have corrected the allocation dates and transaction dates, you will be able to reconcile the Aged Debtors report and the Debtors Control Accounts.
If you still have not discovered the discrepancy between the Aged Debtors report and the Debtors Control Account, please contact Synergy Network.