ANSWER ID:9096

It is important you often check that your receivables are in balance. This means that the sum total of the amounts your customers owe you as shown on customer cards is equal to the balance of your Receivables or Trade Debtors account. The Receivables Reconciliation Exception report compares these figures for you.

[Australia only]

Watch a receivables reconciliation in action:

[New Zealand only]

Watch a receivables reconciliation in action:

Solution summary

1. Run the Receivables Reconciliation [Summary] report found in the Sales tab of the Index to Reports. This will show if your receivables reconcile, in other words there is no Out of Balance amount shown at the bottom of the report.
2. If you find your receivables do not reconcile, run the Receivables Reconciliation Exceptions report found in the Accounts tab of the Index to Reports. If it reports 'Nothing to Print' you know that your receivables were reconciled (balanced) as at the date of the report. Run the report again for an earlier date until you identify the day the out of balance occurred. This will enable you to identify the specific transaction that has caused the out of balance.

What is the Receivables Reconciliation [Summary] report?

The Receivables Reconciliation [Summary] report provides a snapshot of your receivables as at a specific date. This report is found in the Sales tab of the Index to Reports. Shown below is an excerpt of a Receivables Reconciliation [Summary] report taken from Clearwater (the sample company file).

Please note:

• The Total ($108,810.42), is the balance of the customer cards and is sometimes referred to as the receivables subsidiary ledger.
• The Receivables Account balance ($108,710.42) is the balance of the linked receivables ledger account, which is sometimes known as the Trade Debtors control account.
• Any variance between the Total and the Receivables Account is displayed as the Out of Balance Amount ($100.00).

What is the Receivables Reconciliation Exception report?

When an out of balance exists in your receivables, the Receivables Reconciliation Exception report provides details of the out of balance, as well as possible causes of the exception(s). This report is found in the Accounts tab of the Index to Reports. Note this report is not available in BusinessBasics. Shown below is an excerpt of a Receivables Reconciliation Exception report taken from Clearwater (the sample company file). Note the $100 Out of Balance amount and the possible cause of the exception.

What are the common causes of out of balance amounts?

When you record an invoice or a customer payment through the Sales command centre, your ledger stays in balance. This is because the software automatically updates both the customer card and the Trade Debtors or Receivables account. This way they are both kept synchronised.

The following transactions, scenarios and user actions will cause an out of balance. Each of these is explained in greater detail below.

• Transactions such as General Journals, Receive Money entries made in the Banking command centre, or Sales transactions posted directly to the Trade Debtors account
• A false out of balance. 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
• A change to the opening balance figure for the linked Trade Debtors account. This would be done by going to the Setup menu, choosing Balances then Account Opening Balances and changing the amount that appears next to Trade Debtors or Receivables account
• The linked account for receivables has been changed
• A partially deleted Finance Charge transaction

A transaction directly allocated to the linked receivables account

Allocating a transaction directly to the Receivables account won't update the balance of the customer card. This will cause an out of balance.

Below is an example of a Banking Receive Money transaction allocated directly to the Trade Debtors account.

Shown here is the Receivables Reconciliation [Summary] report after the above transaction has been recorded.

Because the Receive Money transaction has updated the Trade Debtors account but not the customer card, the out of balance occurs. The Banking Receive Money transaction is not designed for this and will not update the card even if the customer card is selected.

As described earlier in this support note, the Receivables Reconciliation Exception report is designed to identify incorrect transactions such as the above example. A sample of this report containing details of the Out of Balance amount is shown below:

As you can see, the Receivables Reconciliation Exception report has identified the offending transaction. If you put your mouse pointer over the line representing the transaction, it will turn into a magnifying glass. Click the line to display the transaction itself. You can now go to the Edit menu and choose Delete Deposit Transaction and then re-enter the payment through the Sales command centre by clicking Receive Payments (in BusinessBasics go to the Command Centres menu and choose Sales then choose Receive Payments). You may need to re-reconcile your bank account if the deleted transaction had been previously reconciled.

Similarly, a sales transaction posted directly to the Trade Debtors account, which in the example below is 1-1700, will also cause an out of balance amount. An example of such a transaction is shown below.

To fix this, change the allocation account to an income account. If this transaction cannot be changed, you may delete or reverse it.

A false out of balance

A false out of balance will occur if:

• The payment applied to an invoice is dated prior to the invoice itself.

• If the application of a credit note is dated prior to either the:

  • invoice it has been applied to, or
  • 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.

Eventually, once you run the Receivables Reconciliation [Summary] report to a date that incorporates both the payment and invoice dates, you will then be back in balance.

You can determine this by dating the Receivables Reconciliation [Summary] report for a far-off future date such as 31/12/9998. This is because the Receivables Reconciliation [Summary] report will balance once it is dated for the same, or a later date, than the credit note. Below is an example of this:

• The invoice is dated 30/05/2013
• The credit note is dated 01/05/2013
• The application of the credit note is date 05/05/2013

If you run the Receivables Reconciliation [Summary] report as at 18/05/2013, the report will show an out of balance amount. If you run the report for any date after the 30/05/2013 the report will show that the receivables are in balance because the application of the credit note is included in the date range.

Changing the opening balance of the linked receivables account

If you go to the Setup menu and choose Balances then Account Opening Balances you will see the following window.

The amounts shown here relate to the conversion month or time at which you began using this particular company file to track your balances and transactions. If the opening balance of your Trade Debtors account is modified, your receivables will be out of balance. Restoring a backup may help determine the original opening balance figure.

Changing the linked account for accounts receivable

The linked receivables account is accessed by going to the Setup menu and choosing Linked Accounts then Sales Accounts. The linked receivables account is the account entered in the Asset Account for Tracking Receivables field.

If:

• there are open invoices when the account is changed and
• additional invoices are entered after the linked account is changed,

the balance of your customer cards will be spread across two ledger accounts, but the Receivables Reconciliation [Summary] report will only report the balance of the account which is the one that 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 file. Compare the linked accounts in the restored file with your current working file. The linked accounts in both files should be the same.

A partially deleted Finance Charge transaction

When a Finance Charge is recorded, the software creates two transactions. If only one of these is deleted, an out of balance will occur. For further information and instructions on correcting this please refer to our support notes Finding and deleting or reversing a finance charge in AccountRight 2011 and lateror Finding and deleting or reversing a Finance Charge (AccountRight v19 and AccountEdge).

What steps are needed to find the cause of the out of balance?

The Receivables Reconciliation [Summary] report is the primary tool for both reporting and finding out of balances. Complete the following steps to find the cause of the out of balance.

1. Check for a false out of balance.

2. Is the out of balance from a previous financial year?

3. Eliminate or narrow down date ranges where the transaction causing the out of balance took place.

4. Find the offending transaction.

5. Correct the error.

Task  1 - Check for a false out of balance

As previously noted, customise the Receivables Reconciliation [Summary] report for a far-off future date. If the out of balance is false, the receivables will show a zero Out of Balance Amount.

Follow the remaining steps if the out of balance remains.

Task 2 - Is the out of balance from a previous financial year?

Assume:

• the date is now 30/6/13 (end of the 12/13 financial year); and
• the company file's current financial year is 2013 (identified by going to the Setup menu and choosing Company Information).

Customise the Receivables Reconciliation [Summary] report for 1/7/12 (or the beginning of your current financial year). Is the Out of Balance Amount zero?

If not, then the cause of the out of balance will be in the previous financial year(s). You will need to restore last year's backup and then search for the cause of the out of balance in the restored file.

For more information on this, please refer to Correct the error below.

Task 3 - Eliminate or narrow down date ranges where the transaction causing the out of balance took place

Finding the out of balance is simply a matter of finding the day the out of balance occurs. This is achieved by continually customising the Receivables Reconciliation [Summary] report for a sequence of dates that allows you to systematically hone in on the particular day.

Once the day is known, each transaction posted to the linked receivables account for that day can be checked. For example:

1. Re-customise the report for 1/1/XX (the middle of the financial year). If the Out of Balance Amount changes, then the offending transaction must be dated between 1/1/XX and 30/6/XX.
2. Re-customise the report for the 1/4/XX (halfway between 1/1/XX and 30/6/XX). If the Out of Balance Amount doesn't change, then the offending transaction must be dated between 1/1/XX and 1/4/XX.
3. Re-customise the report for the 15/2/XX (halfway between 1/1/XX and 1/4/XX). Continue re-dating the Receivables Reconciliation [Summary] report until the date the out of balance occurs is identified.

Task 4 - Find the offending transaction

Once the date that the out of balance occurs is identified, check the transactions for that day to find the offending transaction.

In these examples we will find some of the transactions discussed above.

1. Go to the Accounts command centre and click Transaction Journal, then click the All tab.
2. Enter the date identified in the previous step in both the Dated From and To fields.
3. In the sample shown below, a sale has been directly allocated to the linked account for tracking receivables. Normally a sale transaction would be posted to an income account, but in this case both the debit and credit are posted to the Trade Debtors account.
    
4. To fix this refer to the instructions in A transaction directly allocated to the linked receivables account above.
5. Repeat the above steps for further out of balance amounts.

Task 5 - Correct the error

Before you take steps to correct the error, you need to determine what was the purpose of the original transaction. For example:

• An invoice being allocated back to the linked receivables account may have been a simple mistake and can be corrected by changing the allocation account to an income account.
• The Receive Money transaction allocated directly to the Trade Debtors should be deleted and replaced using Receive Payments.

If the transaction in question was from a previous financial year, it may not be practical to restore that year's backup and re-enter the data entered since then. An alternative may be to enter an adjusting general journal.

You would date the journal as at the first day of the current financial year, e.g. 1/7/2013. Every journal must have a debit and a credit and will affect two accounts. Obviously, one account will be the Trade Debtors account. The question is - what account should the other side of the transaction be posted to?

There are several options:

• You can restore the backup from the year in question and determine what account was used in the original transaction and use that account again.
• You can post the transaction to a 8-XXXX 'Other Income' or 9-XXXX 'Other Expense' account. This way they are grouped differently on your profit and loss. (Not available for BusinessBasics or FirstEdge).
• You can create a 'Suspense account' and post the transaction to it.

Note: You must consult with your accountant or accounting professional prior to performing any such adjustment. The transaction may affect a BAS 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 suspense account may be the best option. This way, the amount can be journaled out of the suspense account at a later time. In any case, it is important to enter a meaningful explanation in the memo field of the journal so that the purpose of the transaction can be understood in the future.