Once you have run [Get Transactions] to pull in bank transactions using Sage Bank Feeds or a CSV import, you can reconcile the bank transactions.
Getting started with Bank Reconciliation
Go to ‘Banks- BANK ACCOUNTS’) identify the correct bank account and choose [Matching] from the right-hand drop down menu:
This displays the bank matching screen:
You can filter the bank transactions from Date or text from Description, Reference or Additional Information.
How Bank Reconciliation works
Bank Reconciliation work by matching to Journal Line Items. You can either match to an existing Journal or create a new Journal to match to.
123 Step Processing
Sales Invoices are typically processed in two steps:
- Raise invoice & send to customer
- Record Receipt using Bank Reconciliation
You may be sent a remittance advice, but you are only sure you have been paid when the money hits you bank.
Purchase Invoice are typically processed in three steps:
- Add the invoice
- Pay the supplier
- Reconcile the payment using Bank Reconciliation
Other transaction may be a single step, for example Bank Interest payments.
Reconcile a Customer Receipt
Follow the TWO STEP process: you may be sent a remittance advice, but you are only sure you have been paid when the money hits you bank. So create a [New Journal] to reconcile a customer receipt.
Its a good idea to have two tabs open (you can right-mouse-click on the ‘Sales’ tab or right-mouse-click on the browser tab and choose ‘Duplicate’) one with the Bank Reconciliation and one with your Sales Invoices open with the ‘Unpaid and Overdue’ List View selected:
From the Bank Reconciliation click on the [New Journal] button:
- Set ‘Journal Type’ to ‘Customer Receipt’
- Set ‘Receipt Type’ to ‘Bank Receipt’
Now you need to choose the invoices to match to:
To filter by Customer: add the Customer Dimension in the Journal Header
Or use the filters to help find the invoice
Choose the invoice (or invoices) and click [Save]. This will create a journal which you can see by going to ‘Sales – CUSTOMER RECEIPT’ subtab.
Overpayment
If your customer has overpaid you, reconcile the correct amount to an invoice and the balance in displayed:
Then click on the to add a new Journal Item and add this as a ‘Payment on Account’
- Leave the Sales Invoice Number blank for ‘Payment on Account’
- The ‘Remaining Amount’ is Zero so you can now [Save] the Journal
Underpayment
If a customer underpays you for an invoice you can part-pay the invoice. In this example only £109.20 was paid:
Reconcile a Supplier Payment
Follow the THREE STEP process: you will pay the supplier and then reconcile the bank transaction to the existing payment, so click on [Match].
- Set ‘Journal Type’ to ‘Supplier Payments’
This lists the Journal LINE ITEMS. If you paid multiple invoices in one go, you will see one line item for each invoice paid.
Single invoice payments can be matched by choosing ‘Filter by amount’ set to ‘By transaction amount’
For multiple invoice payments the best way to match is to find the Supplier Payment (‘Purchases – SUPPLIER PAYMENT’) and then use the ‘Name’ (eg ‘PPJ-000000106’) to filter the line items to match using the ‘Filter by other words’ field.
NB: The Supplier Payments list cannot be filtered by supplier name, or by reconciled or not. The best filter is the Reference so it is important to get the Reference right when choosing [Pay] Invoice. I suggest including the supplier name.
Reconcile a Bulk Supplier Payment
Currently there is a bug in Bank Rec that prevents you using [Match] when you pay more than one invoice at a time. The workaround is to use [New Journal] instead of matching. With this process the payment is posted to ‘1215 Bank Clearing Account’. You then create a journal in Bank Rec which posts to the bank.
Supplier Payment
- Choose ‘Bulk BACS’ as the ‘Payment Type’ which makes the journal post to ‘1215 Bank Clearing Account’ AND creates a ‘Payment Number’ Dimension Tag.
- Remember the ‘Reference’ is visible in the ‘Payment Number Report’, so make it something that will help you identify the payment.
Bank Rec
- You need to match the Bank Rec to ‘Payment Numbers Report’ You can get a list of unreconciled Payment Number by navigating to ‘Purchases – REPORTS’ sub tab. I find it helps to keep this report open in another tab.
- In the Bank Rec click on [New Journal] and choose the ‘Bank Rec Bulk Payment’ Journal Type, and fill in the Date. You will then be presented with a list of ‘Payment Numbers’ to match:
Tick the Payment Numbers to add them as Journal Items until you have exactly matched the ‘Remaining Amount’
Once you click [Save] the bank transaction is reconciled, and you can view the Journal created under the ‘Purchases- BANK REC BULK PAYMENT’ subtab.
Reconcile a Supplier Payment without an Invoice: 1 step processing
For small cash or debit card purchases where you don’t really want to create a supplier and invoice, you can simply generate the whole transaction in Bank Rec:
- Set the ‘Journal Type’ to ‘Bank Rec Purchase’
- Enter the Date and Payment Type
- Check the ‘Tax Treatment ‘ is correct
- Enter ‘Various Suppliers’ as the supplier (this is a dummy Supplier Account)
- Click on the to add your own Journal Item
- Check the Tax Code
- Choose the Ledger Account this purchase should be posted to.
(You can edit the ‘Gross’ amount add additional lines with different Tax Codes and Ledger Account to reach the total for the bank transaction)
Once you click [Save] the bank transaction is reconciled, and you can view the Journal created under the ‘Purchases- BANK REC PURCHASE’ subtab.
The Journal is created with a Purchase Invoice Number, and VAT calculated. If you need to amend the VAT you can un-post, edit and re-post the ‘Bank Rec Purchase’ Journal.
Reconciling simple Bank Transaction without VAT: 1 step processing
Many bank transactions do not involve invoices, and have no VAT implications. EG: Bank Charges, Interest Payment, VAT Payment, Dividend Payment. For these transactions we have created two general Bank Rec Journals: ‘Bank Rec Money IN’ and ‘Bank Rec Money OUT’
Following the ONE STEP process the entire transaction is handled in Bank Rec so click on [New Journal] to create the Journal in the Bank Rec screen.
- Set the ‘Journal Type’ to ‘Bank Rec Money OUT’
- Enter the Date
- Click on the to add your own Journal Item
- Choose the Ledger Account the journal should post to
- Optionally fill in the ‘Loan’ Dimension Tag (Loan repayment only)
Once you click [Save] the bank transaction is reconciled, and you can view the Journal created under the ‘Bank – BANK REC MONEY OUT’ subtab.
Reconcile a Supplier Payment on Account
A typical example of this is with electricity which is paid in regular monthly instalments, but you are invoiced for the actual electricity used. The Invoice does not match the Payment, but should balance out over the long term.
Purchase Invoices are created in the normal way when they are received.
The monthly instalments are handled in Bank Rec:
- Click on [New Journal]
- Choose the Journal Type ‘Supplier Payments on Account’
- Fill in the appropriate header details, including ‘Date’ and ‘Supplier’:
- This will display unpaid invoices for the supplier by DO NOT choose from the list of invoice, instead click the to add your own Journal Item:
- Now click Save to complete the reconciliation
Allocating Payment on Account
If you go to ‘Purchases – SUPPLIER PAYMENT ON ACCOUNT’ subtab you will see the journal you have created. This can be allocated to the Purchase Invoices by choosing [Allocate] from the drop down menu:
Repayment of Loans
To reconcile repayment of loans:
- Choose [New Journal]
- Choose the Journal Type ‘Bank Rec Money OUT’ and enter the correct date.
- Add the Journal Item for the amount of the replayment with ‘Ledger Account’ (‘2300 Loans’ or ‘2310 Hire Purchase’)
- Make sure you fill in the ‘Loan’ Dimension Tag as well as the
For more details on processing Loans using the Loan Dimension see Loan Processing