Debtors or Creditors Reconciliation Out of Balance

Article ID: 2024724         Last updated: 5 June 2014

 

Note!  This support note is equally applicable to the Debtors and Creditors reconciliations. The main body of the support note will use 'debtors' as an example, however the exact same principals and reports can also be applied creditors.

Introduction

It is important you check from time to time that your debtors and creditors totals are in balance with the value on your Balance Sheet. What this means is that the total value your customers/suppliers owe you (shown on the debtors or creditors report) is equal to the balance of your Trade Debtors and Trade Creditors accounts on your Balance Sheet.  The Debtors and Creditors Reconciliation reports compare these values for you.

Summary

If your debtors/creditors reconciliation reports show an out of balance value:

  • Go to the Accounts section of the Index to Reports and run the Debtors or Creditors Reconciliation Exception report to see any transactions that may have caused the imbalance.
  • If the report displays 'Nothing to Print' and yet you know there is an out of balance, go to the Sales or Purchases section of the Index to Reports and run the Debtors or Creditors Reconciliation Summary. Run the report several times as of earlier and earlier dates until you are able to identify the date the out of balance occurred.
  • Use the Find Transactions or Transaction Journal function to examine the Trade Debtors or Trade Creditors account for that day and identify the transaction that has caused the difference.

 

What are the Debtors Reconciliation Summary and Debtors Reconciliation Detail reports?

The Debtors Reconciliation reports provide a ‘snapshot’ of your Debtors as at a specific date.

Below is an example of a Debtors Reconciliation Summary taken from the Widgets Ltd example file:

2024724A

The Total (£60,706.46), is the summed total balance of open invoices on the individual Customer Cards.

The Debtors Account balance (£60,706.46), is the balance of the Trade Debtors control account on your balance sheet.

Any variance between the Total and the Debtors Account is displayed as the Out of Balance Amount, which in this case is zero.

Common causes of Out of Balance amounts

When you record sales invoices or a customer payments through the Sales command centre, the value of open invoices should balance to the equivalent total on the balance sheet (the Trade Debtors value). This is because AccountEdge automatically updates both the Customer Card balance and the Trade Debtors control account balance when invoices and payments are recorded; in this manner, both are kept synchronised.

However, the following scenarios will cause the Trade Debtors control account and the Customer Card values to appear as being out of balance:

  1. Transactions such as General Journals (from the Accounts command centre), Receive Money entries (from the Banking command centre), or Sales Invoices (from the Sales command centre) have been allocated directly to the Trade Debtors account. Such transactions will affect the Trade Debtors nominal account balance but will NOT affect the invoice/customer balance, producing an imbalance between the two.
  2. A 'false' out of balance is being reported. This happens if a payment applied to an invoice is dated prior to the invoice itself. This will also happen if a credit note is 'applied' on a date prior to the date of an invoice or the credit note itself.
  3. If there was not previously an out of balance amount, then a change may have been made to the opening balance figure of the Trade Debtors account. Similarly, if the Trade Debtors has always been out of balance, then the opening balances of the Trade Debtors and Historical Sales (as of the company file's Conversion Date) may not have been entered correctly or completely.
  4. The linked account for tracking Debtors has been changed.
  5. A partially deleted Finance Charge transaction remains in the company file.

 

We will now look at each of the above scenarios in more detail:

Reason 1: A Transaction Has Been Directly Allocated to the Trade Debtors Control Account

Allocating any kind of non-Invoice, or non-Receive Payment transaction (any non-Sales Register transaction) directly to the Trade Debtors/Creditors control account will alter the nominal account balance but not alter the balance of the invoices / customer cards, therefore causing an out of balance.

Note!  For details about how to find transactions that have been allocated directly to the Trade Debtors account, see 'Finding the cause of your Out of Balance', below.

Below is an example of a Receive Money transaction entered through the Banking command centre, allocated directly to the Trade Debtors account.

2024724B

 

The Banking Receive Money transaction window is not designed for applying customer payments against invoices will not affect the balance of any invoice or update the customer card, even if the customer card is selected. Recording this type transaction will update and reduce the balance of the Trade Debtors account but not the Customer Card, thus causing an out of balance.

Solution:  If you have entered a Receive Money transaction in this manner, you can go to the Edit menu and choose Delete Deposit Transaction and then re-enter the payment through the Sales Command Centre> Receive Payments window.

 

Similarly, a Nominal Journal (general journal) transaction allocated directly to the Trade Debtors / Creditors account can also cause an out of balance amount. An example of such a transaction is shown below:

2024724C

 

Solution:  Try to understand why the journal was recorded. Was it intentionally intended to adjust something? Who posted it and why? Was the journal posted in order to correct a previous out of balance, or was it a genuine adjustment recorded for a particular reason? Is this journal the one causing the current out of balance? 

 

Note!  If your transactions cannot be edited or deleted, click Setup> Preferences> Security and un-tick the Transactions can't be changed; they must be reversed option. You may need to re-reconcile your bank account if the deleted transaction had been previously reconciled.

 

Reason 2: A 'False' Out of Balance Is Being Reported

Varying combinations of the dates of invoices, credit notes, applying of payments, and the settlement of credit notes against invoices can produce ‘false’ out of balances which do not necessarily mean there is a problem with your transactions. These are sometimes referred to as 'timing issues'. False Out of Balances can occur when:

  • The payment applied to an invoice is dated prior to the invoice itself.
  • If the settlement of a credit note is dated prior to either:
  • - The invoice it has been applied to
  • - The date of the credit note itself
  • A payment was applied to an order and that order was changed to an invoice, with the date of the payment being different to the invoice date.


In all of the above cases, your Debtors Reconciliation reports will balance if you run the reports as of a date that incorporates all the payments / settlements / invoice dates. You can determine if you have a real Out of Balance by dating the Debtors Reconciliation report as 31/12/9999. If there is no out of balance amount as at 31/12/9999, then everything balances when taking into account every sale, credit note, settlement and payment in your file.

The out of balance amount as of 31/12/9999 can be viewed as the 'true' overall out of balance amount in your file.

The example below illustrates five steps that shows the effect of settling a credit note at a different date than it was recorded:

  1. A credit note is entered dated 01/10/2014.
  2. An invoice is entered dated 30/10/2014.
  3. The settlement of the credit note against the invoice is dated 05/10/2014.
  4. A Debtors Reconciliation report is run as of 18/10/2014 showing an out of balance amount.
  5. If the report is re-run for any date on or after 30/10/2014, it will balance because the application of the credit note is included in the date range.

 

Reason 3: The Opening Balance of The Debtors Control Account Has Been Changed

Go to Setup> Balances> Account Opening Balances a screen similar to below will appear:

2024724D

The amounts shown here relate to the Conversion Month, that is, the first day of the month in which you began using this particular company data file to track your balances and transactions. If the opening balance of your Trade Debtors account is has been modified, your debtors will be out of balance. Restoring a backup may help determine the original opening balance figure.

Similarly, if the Trade Debtors amount in this window did not agree to the same value as value of Historical Sales entered as of your Conversion Date, then an imbalance would always have been present.

 

Reason 4: The 'Asset Account For Tracking Debtors' Has Been Changed

Your current linked debtors account can be found by going to Setup> Linked Accounts> Sales Ledger Accounts (or Purchase Ledger Accounts). The linked debtors account is the account entered in the Asset Account for Tracking Debtors or Creditors field.

If the selection of this account is changed at a time when there are open (unpaid) invoices and additional invoices are subsequently entered after the change, the balance of your Customer Cards will be spread across two Trade Debtors/Creditors control accounts. However, the Debtors/Creditors Reconciliation reports will only report the balance of the account which is linked at the time the report is generated.

The only way to check for a changed linked account is to restore the earliest known backup of your company data file. Compare the linked accounts in the restored data file with your current working file. The linked accounts in both files should be the same.

 

Reason 5: A Partially Deleted Finance Charge Transaction Exists in the File

When a Finance Charge is entered, AccountEdge needs to create two transactions in order to correctly record the charges. If the finance charge subsequently needs to be deleted and only one of the two transactions is removed, an out of balance will occur.

 


Finding the cause of your out of balance

 

Step 1 - Run the Debtors Reconciliation Exception report

The Debtors (or Creditors) Reconciliation Exception report is the primary tool for finding the cause of out of balances.

Go to Index to Reports and click on the Accounts tab at the top of the window. Go down the list to the Exceptions report group and select the Debtors (or Creditors) Reconciliation report.

The report displays the Total Amount Outstanding from the Customer Cards, the balance of the Linked Debtors Control Account (from the balance sheet), any Out of Balance Amount between the two, and any transactions that may have caused an imbalance. An example of the report can be found below:

2024724E

As you can see, the report has identified a possible offending transaction. From the report, click the Date of the transaction to open and view the transaction in detail.  See Reason 1 above for more details about how this transaction may have caused the out of balance.

If you receive a 'Nothing to Print' message, take the following steps:

 

Step 2 – Check For a 'False' Out of Balance... Find the 'true' Out of Balance

As previously noted in Reason 2 above, customise and run the Debtors Reconciliation to a date of 31/12/9999. If the out of balance is false, the report will show a zero Out of Balance amount.

The out of balance amount as of 31/12/9999 can be viewed as the 'true' overall out of balance amount in your file.

 

Step 3 - Is the out of balance from a previous financial year?

Go to Setup> Company Information and note your Current Financial Year and Last Month in Financial Year.

In this example, we will assume the Current Year is 2014, with a Last Month of September. This would mean that our current year started on 1 October 2013.

Lets find out what the out of balance was on the first day of the current year. So, in our example, we ould run the Debtors Reconciliation report as of 1 October 2013.

Is the Out of Balance Amount zero? If not, then the cause of the out of balance has arrived from a previous financial year. To find the cause of the imbalance you will need to restore last year’s backup (or earlier) and then search for the cause of the out of balance in the restored file. For more information on this, please refer to the final section below.

 

Step 4 – Narrow down date range in which the out of balance originated

If the cause of the Out of Balance is in the current year, finding the reason for it is simply a matter of finding the day the out of balance occurred. This can be achieved by continually customising the Debtors Reconciliation Summary report as of a sequence of dates that allows you to systematically hone in on the problematic day. A popular method is to run the report 'backwards' from the current date, quarter by quarter, month by month, week by week, and eventually day by day until the exact date is known.

Once the date is known, each transaction posted to the debtors control account for that day can be examined and checked by using the Account tab in the Find Transactions window, or the Nominal tab of the Transaction Journal window. Do any of the transactions match the scenarios talked about in the earlier sections of this support note?

 

What if the out of balance is present at the start of the Current Financial Year ?

If the out of balance is present on the first day of the current financial year, then the cause of the imbalance was already present from an earlier financial year. If the transaction in question was from a previous financial year, it may not be practical to identify and resolve the problem from that year. An alternative may be to enter an adjusting general journal.

If you wish to record a journal to correct your imbalance, go to the Accounts menu and select Record Journal Entry.

Date the journal at the first day of the current financial year. Every journal must have a debit and a credit and therefore will affect two accounts. Obviously, in this case, one account will be the Trade Debtors account. The question is, which account should the other side of the transaction be posted to?

There are several options:

  • You can restore the back up from the year in question and determine which account was used in the original problematic transaction, if you can find it, and use that account again.
  • You can post the transaction to an 8-xxxx ‘Other Income’ or 9-xxxx ‘Other Expense’ account. This way they are grouped differently on your profit and loss.
  • You could create a new ‘Debtors Adjustment’ account and allocate the transaction to it. This can be a good idea if you don't know where to allocate the amount to since it will keep the amount visible for you or your accountant in order to re-allocate later, if need be.


IMPORTANT NOTES

You should consult with your accountant or accounting professional prior to performing any such adjustment. The transaction may affect a VAT return from a previous period. It will also affect other reports including your profit and loss.

If you are unable to consult with your accounting professional at the time, the use of a 'Debtors Adjustment' account may be the best option. This way, the amount can be journaled out of the suspense account at a later time.

In all cases, it is important to enter a meaningful explanation in the memo field of the correcting journal so that the purpose of the transaction can be clearly understood in the future.

 

 

Disclaimer: This information is of a generic nature. For specific advice regarding your particular circumstances please seek assistance from your accountant, HMRC, or your IT administrator as appropriate.