Producing a Bank Reconciliation Report

The bank reconciliation report in the reporting suite shows the unreconciled items 'as at' a particular date.

Select the criteria using the drop-down menus at the top of the report and click View Report.

'Include Register?' Yes/No - Include banking documents posted to the register period.

Explanation of each heading

  • Open Items in Bank - Unreconciled items showing in the bank statement as at the date specified
  • Open Items in Books - Unreconciled documents posted to the bank nominal (cash book).
  • Actual Balance at bank - Statement balance
  • Total for open items in bank - Total of unreconciled statement items
  • Total for open items in books - Total of unreconciled cash book items
  • Calculated balance in books - Total statement balance plus/minus value of open bank rec items above
  • Actual balance in books - Balance of Bank nominal code

Bank Reconciliation does not agree:

If you see this message at the bottom of the report showing a difference, the most common reason will be down to the dates on the items that have been reconciled.

For example:

Here the report is run with an 'as at' date of 31/05/2017 but is showing a difference of £50.

If we check the transactions in more detail we can identify the issue.

If you check in Masters > Bank Accounts > Statements.

Enter the sheet number of the bank statement you have reconciled (usually the most recent sheet).

Reconciled transactions will show in blue, unreconciled ones will show in black. Here, the £50 for John Taylor Design is showing as reconciled.

From the 'Sheet' menu at the top of the screen select 'Show Reconciliation'

Two boxes will appear showing the reconciled items. The statement entry is shown on the left, and the cashbook entry on the right.

Here we can see that, although the item is reconciled, the cash book document it relates to is dated 02/06/2017. Because this date falls outside the 'as at' date selected on the report, it is excluded from the total therefore shows as a difference on the report.

To amend the date on the document, it will need to be unreconciled and amended. Further details on how to do this can be found in the following guides:Unreconciling Transactions andAmending a Posted Document Using a Change Document

Troubleshooting: Item shows a one-sided reconciliation

If an item shows as reconciled, but if you find when checking the reconciliation that it doesn't balance, this will be down to an error in the system that has caused the allocation to register incorrectly. If you unreconcile the transaction this should reinstate both entries on to the bank rec, where they can be matched up again.