My aged debtors / creditors report doesn't match the nominal account

Differences between the balance of these reports can be caused by a number of issues. Check the following:

Are you running the reports for the same date?

  • Check the that you're running the reports using the same dates.
  • If you're checking balances retrospectively,
    • Make sure you've ticked Retrospective on the Aged Debtors or Creditors report.
    • Make sure the Transaction date criteria is set to To and the date is the same as the Aged Debtors or Creditors report.
    • Try running the reports for all dates rather then just retrospectively.

Check for any deferred (future) transactions

Deferred transactions are those that have been posted to a Future accounting period. These are included on the balance of your customer and supplier accounts but not the nominal accounts.

Tip: If you have a large number of deferred transactions- check you've opened your accounting periods.

To do this:

Open: Nominal > Nominal Deferred Transaction List.

  1. Refresh the list to make sure all transactions are included.
  2. Filter the list to only show transactions posted to your Debtors or Creditors Control Account.
  3. To find the total of these transactions:
    • Click Clear and then Swap.
    • Select Scrollable View. This just makes sure that all transactions are included, in case they run over more one page.

  4. The total debits and credits are shown at the bottom of the list. Work out the total balance.

  5. Add the total balance of the deferred transactions to the balance of your nominal account.

In the example shown:

  • The Debtors Control Account has a balance of 808.06.
  • The value of deferred transactions posted to the account is 240 - 180 = 60 (Debit minus Credit)
  • Add the deferred transactions to the posted account balance and the balance of Debtors Control Account is 868.06.

Check for journals posted directly to the Debtors or Creditors Control accounts

Transactions posted directly to the Debtors or Creditors Control Account, using the journal entry screens, are not reported on the Aged Debtors or Creditors report as they're not linked to a customer or supplier account. The value of these journals will need to be added to the Aged Debtors or Creditors total.

To do this:

Open: Nominal > Nominal Reports > Account Analysis > Manual Journal Listing.

  1. Select the following criteria:

    Account number or

    Account Name

    Select Is and enter the code or name of your Debtors / Control Control Account.
    Posting date

    All

  2. The total Debits and Credits are shown at the bottom of the report. Work out the total balance.

  3. Add this balance to the total of your Aged Debtors or Aged Creditors report.

In the example shown:

  • The total of the Aged Debtors report is 738.06.
  • The balance of the Debtors Control Nominal Account is 868.06.
  • A single manual journal has been entered for 180.00.
  • Add this to the Aged Debtors total 738.06 + 180 = 918.06.
  • The difference is now £50. (918.06 - 868.06 = 50)

Check for any opening balances?

When you enter an opening balance on a customer or supplier account, it's not posted to your nominal accounts. This is because the value of the Debtors or Creditors Control nominal account is usually included in your opening balance journal.

If you haven't entered an opening balance journal, then your Aged Debtors or Creditors report won't match the balance of the Debtors or Creditors Control nominal account. The same applies if you have entered an opening balance journal but haven't entered opening balances for the customer and supplier accounts.

Add the value of any opening balances to the balance of the Debtors or Creditors Control account.

To do this:

Check the Customer or Supplier Transaction workspace for any opening balance invoices or credit notes.

  1. Enter Opening Balance to the quick filter for the Transaction Type column. This will filter the transactions to only show opening balances.
  2. Total up the opening balance transactions. Don't forget to take the credit notes away from the invoices.

  3. Add this balance to the total of your Debtors or Creditors Control account.

In the example shown:

  • The balance of the Debtors Control Nominal Account is 868.06.
  • After adding the value of any journals, the balance of the Aged Debtors report is now £918.06 (738.06 + 180 = 918.06).
  • Two opening balances have been entered; an invoice for £100 and a credit note for £50.
  • The total of the opening balance transactions is 100 - 50 =50.
  • Add the total to the Debtors Control account 868.06 +50 = 918.06. This now matches the Aged Debtors report.

Check for transactions posted to the suspense account

Transactions are automatically posted to the Debtors or Creditors Control accounts . To do this, Sage 200cloud uses the account specified in the Default Nominal Accounts. If you entered transactions before specifying these accounts, they will have been posted to the suspense account.

To check this:

Nominal List > Reports > Transaction Listing.

  1. On the report criteria, set the following

    Account number or

    Account Name

    Select Is and enter the code or name of your Suspense Account
    Source

    Sales or Purchases

  2. If there any transactions on the report, they value of each one will need to be posted to the Debtors or Creditors control account. To do this, you'll need to move the value from the suspense account using a journal. Make sure the journal to do this is entered for the same posting date. This is to make sure that the value is reported in the correct period. Otherwise your Aged Debtors or Creditors report may not match again in the future.

Check your allocation dates

When running a retrospective Aged Debtors or Creditors report, the date that receipts or payments were allocated to invoices can also create differences.

This occurs when the date a receipt or payment is allocated is before the date of the receipt or payment , and the Aged Debtors or Creditors report is run for a date between the allocation date and the receipt or payment date. This is because:

  •  Aged Debtors or Aged Creditors  report:

    As the invoice has been allocated in the period the report is being run for, the invoice doesn't have outstanding balance so is not included on the balance of the report.

  • Debtors or Creditors Control Account:

    As the invoice has been posted in the period the report is being run for, the invoice is included in the balance of the account. The receipt or payment is not included as it's transaction date is in the following period, hence creating the difference.

How to find the differences:

  • Keep running the reports for future dates until they agree.
  • Use the Day Book report to the date the credit notes and receipts or payments were posted.

    Open: Customers . Reports > Account Analysis > Day Book

    • Enter the following criteria

      A/C Ref All
      Posting date Enter the same date range.
      Trans Type

      Select Is and choose Receipts or Payments and Credit Notes.

      User number All

Check for currency adjustments

If you use foreign currency, any currency adjustments create differences in the same way as the allocation date. This is because currency adjustments are posted according to their allocation date and not their transaction date.

The difference occurs when the date a receipt is allocated is before the date of the receipt or payment, and the Aged Debtors or Creditors report is run for a date between the allocation date and the receipt date. This is because:

  • Aged Debtors or Aged Creditors report:

    As the receipt / payment and the invoice have been allocated to each other in the period the report has been run for, neither the invoice or receipt or payment have an outstanding balance.

  • Debtors or Creditors Control Account:

    The invoice and the currency adjustment are included. This is because the currency adjustment's transaction date is the same as the allocation date and in the period the report is being run for. The receipt or payment is not included as it's transaction date is in the following period, therefore creating the difference.

To find your currency adjustments:

Open: Nominal > Reports > Account Analysis > Transaction Listing.

  1. Enter the following criteria.

    Account number or Account Number Select Is and enter the code or name of your Exchange Differences account  
    Transaction Date Select To and enter the date of the end of the period.  
  2. Make a note of the adjustment transactions.

Check your nominal account balance is correct

Use the Verify Data option to make sure there are no errors in your data and your nominal account balances are correct.

This checks to make sure that:

  • The balance on each nominal account agrees with the total of the transactions posted to each account.
  • Previous years figures balance for all accounts.

If any errors are found they are corrected and shown in the Error Log. Any imbalances in your nominal accounts are posted to the suspense account.

Before you start

Before your run the verify data option, you must:

  • Make sure you've backed up your data.
  • Make sure all other users are logged off. A list of all logged users is displayed when you start.
To verify your nominal data

Open: Period End Routines > Verify Data > Financials

  1. Choose Nominal from the Module drop-down list.

  2. Click OK.

  3. Any errors and fixes are reported on the Error Log.

Check for transactions entered in a closed period

If you have entered customer or supplier transactions late with a date inside a closed accounting period, Sage 200cloud records the transaction for the customer or supplier account on that date, but the nominal posting will be in the next open period.

For example, you may have closed January (period 1) and are now working in February (period 2). You receive and enter a supplier invoice dated for January, but as this period is closed the nominal posting would appear in February.

As the aged reports are based on the transaction date, and the trial balance report is based on the period, this would cause the transaction to show in one but not the other.

You can check these transactions using the Transaction Enquiry:

Open: Nominal > Nominal List.

  1. Select the Creditors or Debtors Control account and click Transaction Enquiry.
  2. Sort the transactions by date, by clicking the Trans Date column.
  3. Check for any transactions which have a date that does not match the period number (PN column).

    In the example above, you might see a transaction with a date of 29/01/2017 (January - period 1) but with a period number of 2 (February).

Reach out

Want to find out more or get in touch?

Contact our Sage 200cloud support team