Article ID: 2024724 Last updated: 28 July 2022
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.
From time to time, it is important you check 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.
If your debtors/creditors reconciliation reports show an out of balance value:
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 sample company:
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.
When you record sales invoices or 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:
We will now look at each of the above scenarios in more detail:
Allocating a sale or purchase directly ind 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.
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:
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.
Allocating a sale or purchase invoice directly to the Trade Debtors or Creditors account, rather than to an appropriate income or expense account will not affect the Trade Debtors/Creditors nominal account balance but WILL affect the invoice/customer balance, producing an imbalance between the two.
The trade debtors/creditors account will not be affected because the invoice will both debit and credit the control account, leaving no change in the balance of the account.
Solution: Edit the invoice and change the allocation account from Trade Debtors/Creditors to an appropriate account (usually an income or expense account.
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:
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:
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.
Go to Setup> Balances> Account Opening Balances a screen similar to below will appear:
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.
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.
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:
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:
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.
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 2022, with a Last Month of September. This would mean that our current year started on 1 October 2021.
Let's find out what the out of balance was on the first day of the current year. So, in our example, we could run the Debtors Reconciliation report as of 1 October 2021.
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.
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?
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 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.
Note: The information in this support article 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.