Knowledgebase:
Sage 200 - Reconciling Debtors/Creditors
Posted by Kathryn Smith, Last modified by Kathryn Smith on 10/11/21 14:57

Debtors/Creditors Reconciliation
(this article refers to Sales Ledger but can also be applied to Purchase Ledger)

Reconciling the Sales Ledger to the Nominal Ledger

To help you make sure that your financial statements are correct, we recommend that you reconcile the sales ledger with the nominal ledger regularly. This is done by making sure that the total balance on your aged debt reports is the same as the balance of your debtors control nominal accounts on the trial balance.

There may be occasions when the sales ledger balance does not match the balance in the nominal ledger.

To help you to investigate this you can:

  • Use the Debtors Reconciliation Enquiry.

This identifies any discrepancies between the balance of the debtors control nominal accounts for a selected period, and the sales ledger transactions posted to debtors control nominal accounts in that selected period.

  • Use the Closed Period Balances.

Once you have closed an accounting period for the sales ledger, you can view the period end balances for the closed period in the Accounting System Manager module. The View Closed Period Balances window shows the balance for the sales ledger and the debtors control accounts in the nominal ledger for the same period. You can compare the balances to identify any discrepancies and the periods in which they occurred.

Even if no discrepancies are shown in the Debtors Reconciliation Enquiry or View Closed Balances window, the sales ledger and the nominal ledger may not match.

Before you begin

You must have:

  • Produced a trial balance report.
  • Produced aged debtors reports to the end of same period as the trial balance.

What do you want to do?

Investigate any differences between the sales ledger and nominal ledger

  • View the Debtors Reconciliation Enquiry
  • View the Closed Period Balances

How to investigate differences between the sales ledger and nominal ledger

Differences can arise between the sales ledger and nominal ledger for the following reasons:

Debtors control accounts not included in the debtors reconciliation enquiry

The Debtors Reconciliation Enquiry only shows the balance of the nominal accounts you have specified as debtors control accounts in the Reconciliation Enquiry Settings. The Sales Ledger includes all transactions.

If you have not included all the debtors control accounts, the sales ledger and nominal ledger figures will not match.

Transactions not yet posted to the nominal ledger

The debtors control account only contains current sales ledger transactions that have been posted to the nominal ledger. If you have sales ledger transactions in your waiting postings or deferred posting files, the sales ledger and nominal ledger figures will not match.

Transactions posted to the suspense account

Sales ledger transactions are posted to the debtors control account automatically. Several debtors control accounts may be used depending on:

  • How the default debtors control nominal account is set up.
  • Whether you have chosen to use cost centre breakdown when posting to your balance sheet accounts.
  • Whether you have chosen to use cost centres and departments for your sales ledger accounts.

If you have not created the required debtors control accounts, the sales ledger transactions will post to the suspense account. Therefore that sales ledger and nominal ledger will not match.

Transactions posted to different periods

Once you have closed a period, any transactions dated in the closed period, are posted to the next open period. This can create differences between the sales and nominal ledger as the transactions are reported in different periods.

For example, you enter an invoice on February 4th that is dated January 29th. The nominal ledger period for January is closed. The transaction is posted to the nominal ledger in February but will appear in the sales ledger balance for January.

Currency adjustments

Currency adjustment transactions are posted according to their allocation date and not the transaction dates. For example, if you enter a receipt dated in February, but the allocation to the invoice is dated in March, any currency adjustment transactions will be posted to March. This will create a difference between the sales and nominal ledgers.

Transactions that are posted to one ledger and not the other

Opening balances transactions do not update the Nominal Ledger. However, opening balances are included on the aged debt reports.

If you have entered an opening balance on your customer accounts, you will expect to have a matching opening balance journal in the nominal ledger to account for this.

Previous imbalance

The aged debt reports show all outstanding transactions regardless of period.

If your aged debt reports have not matched the debtors control accounts in the past, this balance difference is brought forward into later periods.

To investigate differences between the sales ledger and nominal ledger

  1. Check the Reconciliation Settings in the Nominal Ledger.

Make sure you have selected all the debtors control nominal accounts in the Reconciliation Enquiry settings.

  1. Check which debtors control nominal accounts are included in the View Closed Period Balances window.

The Nominal Control Total shown on the View Closed Period Balances depend on the default nominal account settings for the Debtors Control account.

 How the default nominal account settings affect the Nominal Control Total

Default Nominal Account settings

Nominal Control Total

Nominal Account

Cost Centre

Department

Use Specified

 

44100

 

 

No

This is the total of all 44100 accounts regardless of cost centre or department.

44100

 

 

Yes

This is the balance of only the 44100 account with no cost centre or dept.

44100

ADM

ADM

Yes

This is the balance of the 44100 ADM ADM account only.

 

  1. Open all periods for the year.

This makes sure that any waiting postings are posted to the correct period.

  1. Update the waiting postings in the nominal ledger.

This makes sure all transactions entered in the sales ledger are also posted to the nominal ledger.

  1. Check the suspense account for any transactions that should be posted to the debtors control nominal account.

Journal out any sales ledger transactions. Make sure they are dated in the same period as the original transactions posted to the suspense account. This makes sure that the Closed Period Balances are updated correctly.

  1. Use the View Closed Period Balances to find out when the sales ledger and nominal ledger last matched.

If the ledgers matched in the past, start with period directly after the balanced period and work forwards.

If the ledgers have never matched, start with the most recent period and work backwards.

  1. Open the Debtors Reconciliation Enquiry and select the period you require.
  2. Produce a retrospective aged debt report for the end of the period you require.
  3. Check the Debtors Reconciliation Enquiry and the aged debt report for:
    • Any opening balance transactions entered in the sales ledger within that period.
    • Any journals posted directly to the debtors control account in the nominal ledger within that period.
    • Any sales ledger transactions where the transaction date is different to the accounting period.
    • Any nominal ledger transactions where the transactions date is different to the accounting period.
    • Any currency adjustment transactions where the transaction date is different to the posting date.

These transactions have a reference of Revalue in the nominal ledger.

There may be many transactions, with any of the listed situations, that can account for the difference between the sales ledger and nominal ledger.

Debtors reconciliation enquiry

About the debtors reconciliation enquiry

Use the Debtors Reconciliation Enquiry to reconcile your sales ledger accounts with the nominal ledger. The reconciliation enquiry shows any discrepancies between the debtor's control nominal account and the sales ledger.

It shows the following for the selected period:

  • The opening balance of your debtors control nominal accounts.

These are the debtors control nominal accounts you specified on the Reconciliation Enquiry Settings in the Nominal Ledger.

  • The totals of any transactions posted to the debtors control nominal accounts from the sales ledger.
  • The totals of any currency gains or losses posted to the nominal ledger.
  • The totals of any journals posted directly to the debtors control accounts.
  • The closing balance of your debtors control nominal accounts.
  • Any discrepancy in the opening balance and closing balance of the debtors control nominal accounts, that did not arise from the sales ledger.

If there is a discrepancy, this usually means that the aged debt report in the Sales Ledger will not reconcile with the debtors control nominal accounts in the Nominal Ledger. However, having no discrepancy does not mean that aged debt reports and the debtors control nominal accounts will match.

See How to investigate differences between the sales ledger and nominal ledger for more information on how to investigate any differences.

The enquiry only works with open item sales ledger accounts.

Before you begin

You must

  • Select the debtor and creditor control nominal accounts you want to see in the report in the Reconciliation Enquiry Settings.

To run the debtors reconciliation enquiry

Open: Sales Enquiries > Debtors Reconciliation Enquiry.

  1. Select an accounting period and click Display to view the reconciliation information for the period.

The following information is displayed:

 

Debtors Opening Balance

This shows the closing balances for the debtor's control nominal accounts, from the period immediately before the period you have selected.

Note: This balance could change if the relevant sales ledger period is not closed. Further sales ledger transactions may have been posted the next time you use the Debtors Reconciliation Enquiry.

Invoices, Credit Notes, Receipts and Payments

These show the total of the original base currency value of sales ledger transactions that are posted to the nominal ledger for the selected accounting period.

Note: This is based on the period the transactions are posted to the nominal ledger, not the transaction date. For example, you enter an invoice on February 4th that is dated January 29th, and the nominal ledger period for January is closed. The transaction is posted to the nominal ledger in February but will appear in the sales ledger balance for January.

Opening Balances

This shows the total net value of opening balances dated in the selected period.

Opening balances do not update the nominal ledger and so do not carry an accounting period. However, opening balances are included on the aged debt reports.

The incorrect use of opening balances can cause discrepancies, so these details are shown to help you identify possible problems with this.

Currency Adjustments

This shows the total value currency adjustment gains and losses that are posted to the nominal ledger for the selected period.

These are:

          ·       Any currency gains or losses posted to the debtors control account(s) when outstanding invoices are revalued in the selected period.

This occurs when you revalue your customer accounts.

          ·       Any currency gains or losses from the allocation of receipts or payments, posted in the selected period.

This occurs when the exchange rate on the allocated receipt or payment is different to the exchange rate on the original invoice or credit note.

Net Change

This shows any difference between the total sales ledger transactions posted to the debtors control accounts and the balance on the debtors control accounts in the nominal ledger.

Debtors Journals

This shows the total value of any journals that have been posted directly to the debtors control nominal accounts from the nominal ledger, for the selected period. These are not present in the sales ledger.

Note: We recommend that journals are not posted directly to the Debtor Control Accounts.

Previous Year Adjustments

This shows the total value of any Previous Year Journals Entries that have been posted directly to the debtors control nominal account(s) in the selected period.

Note: We recommend that journals are not posted directly to the Debtor Control Accounts.

Debtors Closing Balance

This shows the balance on the debtor's control nominal account at the end of the selected period.

Discrepancy

This is the difference between the opening and closing balances on the debtors control nominal accounts(s) that did not arise from the sales ledger.

The Discrepancy is calculated in the following way:

(Debtors Opening Balance + Net Change from Sales Ledger) - Debtors Closing Balance.

 

  1. To display related transactions in the drilldown Details section of the window, select the arrow to the left of the description.
  2. When you have finished viewing the details, click Close.

Produce credit control reports

About credit control reports

Use these reports to identify your outstanding customer debt.

Aged debtors reports

You can use the Aged Debtors reports to:

  • Analyse the trends in your customer payments.
  • Identify which customers payments are overdue.

You can use the reports to identify which customers have not been paying you during the agreed payment terms and therefore which customers you need to target to reclaim outstanding monies.

  • Reconcile the sales ledger with the nominal ledger.

This reports show you:

  • All the outstanding balances on each account.
  • The age of the transactions.

These are aged using the transaction date, the due date or the accounting period, depending on your Sales ledger settings.

Retrospective aged debtors report

The aged debtors reports show the outstanding balances for your customers, based on the data in the sales ledger on the day that the reports are run.

You can also produce the aged debtors reports for selected date in the past. To do this, select 'Restropective' on the report criteria. The aged debt report that is produced is for the Base data for ageing date entered. Invoices paid after this date are shown as not paid. Receipts allocated after this date are shown in the report as if they have not been allocated.

How transactions are shown on the aged debt reports

The following table shows when invoices entered and paid on different dates are shown on the aged debtors reports:

 

Invoice details

Amount outstanding

Amount

Date Entered

Invoice Date

Amount and date allocated

Report Run 30/01

Ageing date 30/01

Report run 10/02

Ageing date 30/01

Report run 10/02

Retrospective ageing date 30/01

60

04/01

04/01

All

29/01

0

(fully paid)

0

(fully paid)

0

(fully paid)

04/01

04/01

All

04/02

60

(not yet allocated

0

(fully paid)

60

(allocation date after retrospective date)

04/01

04/01

£30

04/02

60

30

60

(allocation date after retrospective date)

80

05/02

29/01

0

0

(did not exist)

80

80

05/02

29/01

All

29/01

0

(did not exist)

 

0

(fully paid)

0

(fully paid)

05/02

29/01

All

04/02

0

(did not exist)

0

(fully paid)

80

(allocation date after retrospective date)

20

29/01

04/02

0

20

20

 

0

(transaction after retrospective date)

29/01

04/02

All

29/01

0

(fully paid)

0

(fully paid)

0

(transaction after retrospective date)

29/01

04/02

All

04/02

20

 

0

(fully paid)

0

(transaction after retrospective date)

30

29/01

29/01

All

Allocated: 04/02

Allocation date: 29/01

30

(not yet allocated)

0

(fully paid)

0

(fully paid)

29/01

29/01

All

Allocated: 04/02

Allocation date: 03/02

30

(not yet allocated)

0

(fully paid)

30

(allocation date after retrospective date)

 

Due Date reports

Use the Due Date report to review outstanding balances on each account, aged using the due date held for each transaction.

To produce credit control reports

Open: Reports > Credit Control.

  1. Select the report that you want to produce.
  2. Enter the report criteria that you want to use to produce the report.
  3. Click OK.

Transaction allocation dates

When allocation transactions, the allocation date should be the later of the 2 (e.g the date of the payment being allocated to the invoice).

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 period 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:

Transaction Type

Date

SI - Sales Invoice

02/07/2007 (P7)

SR - Sales Receipt

28/06/2007 (P6)

If you produce the Aged Debtors report and the Trial Balance report up to the 30th June 2007 (Period 6):

  • 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.

Alternatively both the Invoice and Receipt could be dated 28th June 2007 but if the allocation date is 2nd July (Period 7), this will also cause a discrepancy as the invoice will be classed as outstanding when the Aged report is ran to 30th June (Period 6).

(0 vote(s))
Helpful
Not helpful

Comments (0)