Why Bank Reconciliation?
Bank Reconciliation makes sure the bank balance in your Balance Sheet is the same a the actual balance at the bank. This is a crucial part of ensuring the overall accuracy and integrity of your accounts and a requirement for auditing.
Verifying that your balance sheet bank balance matches the balance at the bank also means any unpaid invoices are either due for payment or entered in error which is why the Aged Debtors & Creditors list is also a essential requirement for auditing.
Your accounts are audited to the end of your financial year, so for auditing you need to show the bank reconciles at your year-end date, and your list of aged creditors and debtors at the year-end date (which you can do using the ‘Retrospective Aged’ reports). Its quite common that the end-of-year balance sheet does not match the bank account on the end-of-year date and reconciliation is the process of working out why.
What about Bank Matching
Bank Matching is a big help in getting your bank to reconcile as it reduces keying error and the chance of missing transaction. But it does not guarantee the bank balance in your Balance Sheet is the same a the actual balance at the bank. At a more fundamental level, Bank Matching matches bank transactions to Journal Items, whereas to reconcile your Balance Sheet you need to match to Ledger Items.
Why your Bank does not reconcile
At the end of the day, there are only three reasons why the bank balance in your Balance Sheet does not match the actual balance at the bank.
- You have Bank Transactions in Sage that were not actually paid or received
- You have made Payments or Receipts but not recorded them in Sage
- You have recorded Payments or Receipts incorrectly in Sage (wrong date, wrong amount)
Its actually pretty unusual for the Balance Sheet to match the Bank Statement at the end of the year, so the challenge is to track down why it does not match. But before you get into the details of the transactions there are a couple of checks you should run first.
- Run re-build balances
Before doing end of year processing, I would always recommend running ‘Rebuild Balances‘ even though it means your users need to keep out of the system for a while. - Check the Tag Balance matches the ‘Ledger Account Statement’ balance for each bank account
- Refresh the Balance Sheet report
- Check the bank balance in your Balance Sheet is the same as the ‘Ledger Account Statement’ balance at the end-of-year date. If not, there is something wrong with your Balance Sheet configuration. (See Sep Issue of Sage Financials knowhow)
Tools in Sage Financials
Bank Matching
The Bank Matching will reliably show you bank payments or receipts that have not been recorded in Sage.
- Remember to set the date range large enough to catch all the bank transactions
- Be warned that you can bypass Bank Matching by using the ‘Ignore’ function
- With earlier versions of Bank Matching you could get into a mess with the bank transaction being ‘Reconciled’ but the related Journal Item unposted or deleted
Reconciliation problems not managed by Bank Matching
There are several scenarios that cause problems with Bank Reconciliation – here are some:
- Payment to suppliers processed in Sage, but money not actually paid
This can be alleviated by posting to a clearing bank first, but does involve additional processing. - Posting errors in Bank Matching journals
If a Bank Match journal fails to post, the record appears as ‘reconciled’ in Bank Matching, but is not actually posted. - Unposting and Deleting journals
In early versions of Bank Matching, if you unposted and/or deleted a matched journal, the record still appeared as reconciled - Manual Adjustments using Ledger Entries
These will never appear in Bank Matching - Old ‘Bank Takings’ journals from the old Bank Matching can be altered or unposted
- Using the ‘Ignore Transaction’ in Bank Matching
- Foreign currency transactions not handled in the correct way.
Unreconciled Journals
Some of these bank reconciliation problems are due to ‘reconciled’ journals that are not posted, or simply ‘unreconciled’ journals. So it would be really useful to to have a report to show a journal’s reconciliation status. With a bit of configuration it is possible to create a report that shows Journal Items that have not been reconciled and with careful choice of filters this report can show what you need:
- You will need to create a Custom Report Type to include ‘Journals with Journal Items with/without Feed Item’
- Use the essential filters (see Sage Financials knowhow article on Salesforce Reports) and also filtering on: JournalType UID to select Payment & Receipt journals only, Feed Item ‘Reconciliation Status’ and Journal Status (Submitted or Unsubmitted).
This report is likely to display more data than you want, but at least its a starting point. The idea is to show posted journal items that are NOT reconciled in Bank matching, and unposted journal items that ARE reconciled in Bank Matching.
Reconciled Ledger Items
Some of these errors are much more difficult to track down, and when the obvious mistakes have been ruled out, what you really want is a list of ALL the Ledger Items that impact the Bank Account, with their Bank Matching reconciled status. Unfortunately this is not available in Sage Financials. The reason being Bank Matching works on Journal Items, not Ledger Items. There is a loose connection between the two, but not enough to create a report.
The good news is Alpha Index have released a new plugin that enables you to get this report with a high degree of accuracy. And right now, you can download it as a free trial to see if it works for you.
Feature Pack Bank Reconciliation
Newly release, the Feature Pack Reconcile plugin can process your data to enable you to display the reconciliation status of Ledger Items. It also includes the kind of reports I’ve been talking about and is available as a free trail.
Click here to download the free trial.
Did you find this useful? Please register to receive regular updates from Sage Financials knowhow.