Reconcile the Aged Debtors and Creditors
The Debtors and Creditors Control Accounts are two of the most important accounts on the nominal ledger, as they represent money owed to you by your customers and money owed by you to your suppliers. Therefore, most companies reconcile these accounts on a monthly or yearly basis.
How the Debtors and Creditors Control Accounts are updated
Debtors Control Account
| Transaction type | Effect on control account |
|---|---|
| SI | The total value including VAT is automatically debited to this account, increasing the balance. |
| SR or SA | The total value is automatically credited to this account, decreasing the balance. |
| SC | The total value, including VAT, is automatically credited to this account, decreasing the balance. |
| SP | The total value, including VAT, is automatically debited to this account, increasing the balance. |
Therefore, the balance on the Debtors Control Account, is the total amount that your customers currently owe you and should always agree with the total balance outstanding on your Customers ledger.
Creditors Control Account
| PI | The total value including VAT, is automatically credited to this account, increasing the balance. |
| PP or PA | The total value is automatically debited to this account, decreasing the balance. |
| PC | The total value including VAT, is automatically debited to this account, decreasing the balance. |
| PR | The total value including VAT is automatically credited to this account, increasing the balance. |
Therefore, the balance on the Creditors Control Account, is the total amount that you currently owe your suppliers and should always agree with the total balance outstanding on your Suppliers ledger.
To reconcile the Debtors Control Account on a monthly basis
Note: Before running any of the customers reports mentioned in this article, you should ensure that there’s no searches applied.
You can follow this process at the end of any period dictated by your business, for example, you may only need to do this at the end of a year. If you want to reconcile the Debtors Control Account on a yearly basis, you should run this procedure but check the annual trial balance figures and run the day book reports for the financial year, rather than a month.
Note: In this example, all transactions for the month of May have been posted, you’re reconciling the Debtors Control Account for May, and the previously reconciled month was April.
- Check the following balances:
- Last month’s Debtors Control Account balance – To find or check this figure, you should print out the trial balance report for the previously reconciled month, for example, print the trial balance report for April.
Nominal ledger > Trial balance.
Sage Accounts 2014 (v20) and below – Modules > Financials > Trial Balance.
- The monthly movement on the Debtors Control Account – To find or check this figure you should print the Trial Balance report for the month you’re reconciling, then deduct the Debtors Control Account balance for the previous month, from the Debtors Control Account balance on this Trial Balance report. For example, deduct the April Debtors Control Account balance from the May Debtors Control Account balance.
- The totals on each of the customer day books reports for the month you’re reconciling. To do this, run each of the following day book reports, using the date range for the month you’re reconciling:
| Report | Report location |
|---|---|
| Day Books: Customer Invoices (Summary) | Customers > Reports > Day books. |
| Day Books: Customer Credits (Summary) | Customers > Reports > Day books. |
| Day Books: Customer Discounts (Summary) | Customers > Reports > Day books. |
| Day Books: Customer Receipts (Summary) | Bank accounts > Reports > Customer receipts and payments.
Sage Accounts 2014 (v20) and below – Bank > Reports > Customer receipts and payments. |
| Customer Refunds | Customers > Reports > Customer activity. |
To check the monthly movement on the Debtors Control Account, use the following formula:
| Day Books: Customer Invoices – Total Gross Amount | ||
| Minus | Day Books: Customer Credits – Total Gross Amount | |
| Minus | Day Books: Customer Receipts – Total Gross Amount | |
| Minus | Day Books: Customer Discounts – Total Gross Amount | |
| Plus | Customer Refunds – Total Gross Amount | |
This balance should equal the balance of the Debtors Control Account at the end of the month you’re reconciling. For example, the balance on the Debtors Control Account from the May Trial Balance report.
To reconcile the Debtors Control Account with the Customers ledger use this formula:
| The previous months balance on the Debtors Control Account from the Trial Balance report | |
| Plus | Day Books: Customer Invoices – Total Gross Amount |
| Minus | Day Books: Customer Credits – Total Gross Amount |
| Minus | Day Books: Customer Receipts – Total Gross Amount |
| Minus | Day Books: Customer Discounts – Total Gross Amount |
| Plus | Customer Refunds – Total Gross Amount |
This balance should equal the balance of the Creditors Control Account at the end of the month you’re reconciling. For example, the balance on the Creditors Control Account from the May Trial Balance report.
To check that the Debtors Control Account balance matches the Aged Debtors balance at the month end
Note: To check that the creditors Control Account balance matches the Aged creditors balance at the year end, you should run this procedure using the annual Trial Balance report figures and running the Aged creditors Analysis report to the end of the financial year, rather than to the end of the month.
- Check the Creditors Control Account balance at the month end, by running the Trial Balance report for the required month.Nominal ledger > Trial balance.Sage Accounts 2014 (v20) and below – Modules > Financials > Trial Balance. or Sage Accounts 2014 (v20) and below – Modules > Financials > Trial Balance.
- Print or preview the Aged Creditors Analysis (Detailed) report from, Suppliers > Reports > Aged creditors.When running this report, you should ensure that you use a transaction date range from 01/01/1980 to the end of the month being reconciled and select the Exc Later Payments check box.Note: When running this report, you should ensure that there’s not a searches applied to the suppliers module.
Make a note of the balance from the Grand Totals line of the report. - Compare the Creditors Control Account balance to the balance from the Aged Creditors Analysis (Detailed) report. They should be the same.If there’s a difference between the two balances, check the Creditors Control Account activity for any transactions that aren’t supplier transactions, for example, journals. Any journals posted to the Creditors Control Account don’t affect the suppliers ledger and therefore cause a discrepancy between the two balances. If these journals are incorrect you should reverse them and then repost them correctly.
To reconcile the Creditors Control Account on a monthly basis
You can follow this process at the end of any period dictated by your business, for example, you may only need to do this at the end of a year. If you want to reconcile the Creditors Control Account on a yearly basis, you should run this procedure but check the annual trial balance figures and run the day book reports for the financial year, rather than a month.
Note: In this example, all transactions for the month of May have been posted, you’re reconciling the Creditors Control Account for May, and the previously reconciled month was April.
- Check the following balances:
- Last month’s Creditors Control Account balance – To find or check this figure, you should print out the Trial Balance report for the previously reconciled month, for example, print the trial balance report for April.
- The monthly movement on the Creditors Control Account – To find or check this figure you should print the Trial Balance report for the month you’re reconciling, then deduct the Creditors Control Account balance for the previous month, from the Creditors Control Account balance on this Trial Balance report. For example, deduct the April Creditors Control Account balance from the May Creditors Control Account balance.
- The totals on each of the supplier day books reports for the month you’re reconciling. To do this, run each of the following day book reports, using the date range for the month you’re reconciling:
Report Report location Day Books: Supplier Invoices (Summary) Suppliers > Reports > Day books. Day Books: Supplier Credits (Summary) Suppliers > Reports > Day books. Day Books: Supplier Discounts (Summary) Suppliers > Reports > Day books. Day Books: Supplier Payments (Summary) Bank accounts > Reports > Supplier payments and receipts. Sage Accounts 2014 (v20) and below – Bank > Reports > Supplier payments and receipts.
Supplier Refunds Suppliers > Reports > Supplier activity.
- To check the monthly movement on the Creditors Control Account, use the following formula:
Day Books: Supplier Invoices – Total Gross Amount Minus Day Books: Supplier Credits – Total Gross Amount Minus Day Books: Supplier Payments – Total Gross Amount Minus Day Books: Supplier Discounts – Total Gross Amount Plus Supplier Refunds – Total Gross Amount This balance should then equal the monthly movement on the Creditors Control Account.
- To reconcile the Creditors Control Account with the Suppliers ledger use this formula:
The previous months balance on the Creditors Control Account from the Trial Balance report Plus Day Books: Supplier Invoices – Total Gross Amount Minus Day Books: Supplier Credits – Total Gross Amount Minus Day Books: Supplier Payments – Total Gross Amount Minus Day Books: Supplier Discounts – Total Gross Amount Plus Supplier Refunds – Total Gross Amount This balance should equal the balance of the Creditors Control Account at the end of the month you’re reconciling. For example, the balance on the Creditors Control Account from the May Trial Balance report.
To check that the creditors Control Account balance matches the Aged creditors balance at the month end
- Check the Creditors Control Account balance at the month end, by running the Trial Balance report for the required month.
Nominal ledger > Trial balance.Sage Accounts 2014 (v20) and below – Modules > Financials > Trial Balance. - Print or preview the Aged Creditors Analysis (Detailed) report from, Suppliers > Reports > Aged creditors.When running this report, you should ensure that you use a transaction date range from 01/01/1980 to the end of the month being reconciled and select the Exc Later Payments check box.Note: When running this report, you should ensure that there’s not a searches applied to the suppliers module.
Make a note of the balance from the Grand Totals line of the report. - Compare the Creditors Control Account balance to the balance from the Aged Creditors Analysis (Detailed) report. They should be the same.If there’s a difference between the two balances, check the Creditors Control Account activity for any transactions that aren’t supplier transactions, for example, journals. Any journals posted to the Creditors Control Account don’t affect the suppliers ledger and therefore cause a discrepancy between the two balances. If these journals are incorrect you should reverse them and then repost them correctly.
