Bank Reconciliation Out of Balance

This Support Note is suitable for:

• ABSS Premier
• ABSS Accounting
• ABSS AccountEdge

Introduction

The ABSS bank reconciliation function makes it quick and easy to reconcile your cheque book. Because it's an integrated feature of your ABSS programs, you will rarely experience problems reconciling your accounts. However, any problems that are encountered can usually be resolved with a minimum of inconvenience.

Solution Summary
Check your previous reconciliation is still valid by using the Last Reconciled Date as the Bank Statement Date and previous statement balance as the new Statement Balance in the Reconcile Accounts window.

Run the Bank Register report sorted by ID and displaying the reconciled date field to identify deleted or re-entered transactions.

What happen when I reconcile my account in ABSS?

Each transaction in ABSS has several hidden fields and one of these is the Reconciles Date field. In the Reconcile Accounts window, when a transaction is selected as cleared and the Reconcile button pressed, ABSS inserts the date from Bank Statement Date field into the transaction's Reconciled Date field.

ABSS uses the date in the Reconciled Date field to indicate several things within the system, including:

• Preventing reconciled transactions from appearing in the Reconcile Accounts window.
• Purging (deleting) data when starting a new financial year.
• The Banking — Reconciliation and Bank Register reports that can be customized to include the Reconciled Date.

Can a reconciliation be reversed or deleted?

Because a reconciliation is a simple recognition of transactions and their Reconciled Dates, strictly speaking a reconciliation can't be reversed or deleted as such. For more information refer to support note 'Delete' Reconciliation.

If you have a back up your data file from immediately prior to performing your reconciliation, you may restore that back up to bring you back to that prior point. It is good practice to take a back up prior to performing a reconciliation.

Alternatively, deleting and re-entering all the transactions involved in that reconciliation will cause them to reappear in the Reconcile Accounts window.

Why doesn't my chequebook reconcile with my bank statement?

There are a number of reasons that can cause this, and by following a simple set of steps you will be able to quickly identify the problem.

Step 1: Check that the last reconciled bank statement still reconciles

This step will determine if the out of balance is due to a mistake in this current reconciliation, or if a previously reconciled transaction has been deleted. If a previously reconciled transaction has been deleted, it will affect the previous reconciliations as well as the current one.

To check if the last bank statement still reconciles, open the Reconcile Accounts window and enter the date of the current bank statement in the Bank Statement Date field. With no transactions selected is the Calculated Statement Balance the same as the opening balance of the current bank statement? You can de select all transactions by clicking the check/uncheck all toggle button. 

Please note: The opening balance of the current bank statement should be the same as the closing balance of the bank statement to which the account was last reconciled. With no transactions selected as cleared, ABSS will not reflect any of the activity on this current bank statement. Therefore, the Calculated Statement Balance should be the same as the opening balance of the current bank statement.

The window below shows that with no transactions selected as cleared, the Calculated Statement Balance equals the opening balance of the current bank statement (New Statement Balance - bank statement not shown).

Yes, the Calculated Statement Balance is the same as the current bank statement's opening balance.

This indicates the last reconciled bank statement still reconciles. Therefore, the out of balance will be related to this current reconciliation and will be due to:

1. A transaction not listed on the bank statement has been selected as cleared in ABSS. Or, a transaction that is listed on the bank statement hasn't been selected as cleared in ABSS
2. A difference in the value of a cleared transaction in ABSS and the bank statement. For example: A transaction has been entered (and selected as Cleared) in ABSS as $10.26, but on the bank statement the same transaction is shown as $10.62.

This can be confirmed by comparing the Total Cleared amounts at the bottom of the Reconcile Accounts window with the corresponding totals on the bank statement.

To overcome this, with no transactions selected (click the 1=1 button). Select the presented transactions as cleared, and at the same time continually monitor and compare the running balance on the bank statement with the Calculated Statement Balance. They should remain the same when marking off presented transactions as cleared. While doing this, it is important that you work from the bank statement; work sequentially from the first transaction to the last. Click the Date button to have the transactions displayed in date order.

Step 2: Finding the out of balance if the last reconciled bank statement no longer reconciles

This usually indicates a reconciled transaction(s) has been deleted.

Compare the window below with the window on the previous page. The Calculated Statement Balance no longer equals the current bank statement opening balance of $524,983.15.

Briefly note:

• The current bank statement opening balance has been entered in the New Statement Balance field ($524,983.15).
• The Out of Balance amount of $1242.30.
• Cheque #58 now appears in the list waiting to be reconciled.

ABSS Calculated Statement Balance represents the total value of ALL deposit and withdrawal transactions that have a date (prior to the Bank Statement Date) in their Reconciled Date Field. If a previously reconciled transaction has been deleted, then the Calculated Statement Balance will no longer equal the amount it was last reconciled to.

In the above example, one, or a combination of reconciled transactions totalling $1242.30 have been deleted.

The quickest way to find a missing reconciled transaction is to use the Bank Register report. To use this report:

1. Go to Reports, select Index to Reports, then click the Banking tab and highlight the Bank Register report.
2. Click Customise and click the Accounts field drop down arrow and select the account being reconciled.
3. Click the Report Fields tab and mark the Reconciled Date field in the Available Fields by clicking to the left of it.
4. Click the Finishing tab then click the Sort by drop down list and select the ID# option.
5. Click the Advanced Filters tab and enter a date range spanning the beginning of the Current Financial Year to the current date. The Current Financial Year can be determined by going to Setup then Company Information.
   • For example: If the Current Financial Year is 2005, enter 1/7/04 in the From date field.

6. Click Display.

The report will list the transactions by their ID#, allowing you to scroll down and look for missing transaction numbers. For Example: Cheque number 56 is listed after cheque number 54, therefore it could be assumed cheque number 55 is missing.

Check any missing transactions with your bank statements to see if they have been presented to the bank. If they have been presented, then it can be assumed that the transactions were entered in ABSS, reconciled and subsequently deleted.

Also, while scrolling down through the report, take note of any transactions that don't have a Reconciled Date, but were most likely reconciled. For example, a cheque dated 13/4/2005 appears on the report but it doesn't have a Reconciled Date. The age of this cheque suggests it should have been reconciled. Again, check your bank statements to determine if the transaction has been presented to the bank. If it has, then it indicates the transaction was entered into ABSS, reconciled, deleted and has since been re­entered.

The window below shows an excerpt from the Bank Register report which has been customised to include the Reconciled Date. Note the following:

1. Cheque # 55 is missing, which indicates it may have been deleted.
2. Cheque # 58 is listed without a Reconciled Date. As cheques 54, 56 and 57 have been reconciled,it is possible that this cheque was previously reconciled, deleted and has since been re-entered. 

Both these transactions should be checked with your bank statements. Also, if the Reconciliation report is printed after reconciling your accounts, customise the Banking - Reconciliation Report for the Reconciled Date (as per the above Bank Register report), and compare it with the Reconciliation Report that was printed when the account was originally reconciled.

The window on the next page shows an excerpt of the original Reconciliation Report, printed at the time the bank account was reconciled. 

Compare the original report with the one below, which has just been printed/displayed (note the report Date of Bank Statement dates are the same). It can be seen that cheque numbers 55 and 58 appear as reconciled on the original report, but on the report below printed from the index to reports at a later date, they are both missing.

Note: When printing from the index to reports, if one bank account is selected in the customise screen, you have the option of selecting previous reconciliation dates for the Bank Reconciliation report.

It can be concluded that:

• Cheque # 58 was reconciled, deleted and has since been re-entered (it appears on the Bank Register report).
• Cheque # 55 was reconciled and has since been deleted, but it hasn't been re-entered (it doesn't appear on the Bank Register report).

Step 3: Re-enter and reconcile the deleted transactions.

Once the missing reconciled transaction has been identified, it will need to be re-entered in to ABSS and reconciled (reconcile the transactions at the date they were originally reconciled). After re-entering and reconciling the transaction, Check the Calculated Statement Balance again.

The below window shows that cheque number 55 has been re-entered. Notice that with cheque numbers 55 and 58 both selected as cleared:

• The Calculated Statement Balance now equals the opening balance of the current bank statement.
• The Total Cleared amount is $1242.30 - compare this with the Out of Balance example on Step 2.
• The Bank Statement date is the same as the date the transactions were originally reconciled.

After reconciling these transactions, you will find that the Reconciliation Report will now be as it was when the transactions were originally reconciled; providing an accurate audit trail.

What else will cause my cheque account not to balance with the bank statement?

Listed here are other potential causes for an out of balance:

The previous bank statement was 'forced' to reconcile. This basically means the accounts was reconciled to the wrong amount; the Calculated Statement Balance of the last reconciliation was not the same as the bank statement's closing balance. The user then proceeded to enter the displayed Calculated Statement Balance amount in the New Statement Balance field and then press the Reconcile button.

This can be checked by using the Banking - Reconciliation Report. Display the report filtered for the date of the last reconciliation (this date can be obtained from the Reconcile Accounts window). Check the Expected Balance on Statement to see if it is the same as the amount displayed at the ABSS Balance On (date) (in the report), which should be the same if no previously reconciled transactions have been deleted. However, if both these amounts are different to the closing balance on the last reconciled bank statement, then the reconciliation was forced. 

Any correction action will depend on why the last reconciliation was incorrectly reconciled. An option may be to enter an adjusting general journal.

You would date the journal as ata the first day of the current financial year. For example, 1/7/2004. Every journal must have a debit and a credit and therefore will affect two accounts. Obviously, one account will be the Bank account. The question becomes what account should the other side of the transaction be posted to.

There are several options: 

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

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 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 down the track.

• Once entered, open the Reconcile Accounts window, select the Bank account and enter the same date as displayed in the Last Reconciled Date field in the Bank Statement Date field. Select the adjustment transaction as Cleared and then check that the Calculated Statement Balance is the same as the opening balance of the current bank statement. If it is, click on Reconcile to reconcile the transaction.
• With the Reconcile Accounts window open, check the Last Reconciled Date. Is this date the same as the closing date of the bank statement that was last reconciled. It maybe possible that someone has entered the Reconcile Accounts window and accidentally reconciled a transaction.
• The bank account's Opening Balance may have been changed. To check the account's Opening Balance, go to Setup select Balances and click Account Opening Balances.

If you suspect it has been changed, restore an early backup of your data file and compare the account's Opening Balance in the restored data file with the same Opening Balance in your current working data file.

• Ensure your bank statements are being reconciled sequentially.
• Check that the bank statement calculation are correct.