The bank matching process allows you to import a bank statement and either match it with existing transactions in your accounts, as a check that they exist, as well as create new transactions for items which have cleared your bank account but which have not yet been processed through Brightpearl, for example, money received from customers or paid out to suppliers using BACS.
Setting Up
Brightpearl accepts CSV files, here's how to get it setup correctly:
Formatting an Excel file
- Export a CSV or Excel file from your online banking.
- Save this statement to your computer and open it.
-
Reformat so that the first column is the date in format YYYY-MM-DD, the second column needs to be the statement reference and the third column the amount (with a positive value as a bank deposit and a negative value is a bank withdrawal). In Excel, you format the date as follows:
- select the column header (to highlight all fields in the column)
- from the Format menu, choose More number formats then from the bottom select Custom
- Type yyyy-mm-dd in the Type box
- Click OK - Save the statement as a CSV, with no header row (column headings).
Duplicate entries
Brightpearl will check the database for possible duplicates in the file you are importing. Any entries that have already been uploaded will be ignored. Duplicates are identified as lines with the same reference, amount and date. If in fact there are two genuine entries on the same day for the same amount with the same reference, you will need to edit your CSV import so that the references are not the same, otherwise Brightpearl will only import one item.
Importing a Statement
Follow these steps to import your statement:
- Log on to your online banking and export a statement. Ensure it follows the format described above.
- Log in to Brightpearl and go to Accounting > Import Transactions and select the bank account.
- Click the Import transactions link.
- Click Browse and select your saved statement file.
- Click Upload.
- If the import is successful, the statement lines are displayed on the left of the screen.
Matching Statement Lines to Transactions
Select the first statement item you want to match. Brightpearl will search the database for any currently unmatched transactions of the same amount. One or more matches might be found and a relevance score is used to indicate how well each matches.
How does relevance scoring work?
Relevance scoring works by matching various criteria including contact name or code, invoice or order number, date etc. The closest match will be displayed at the top of your list with a score out of 10.
Which transactions are searched for matches?
If you are importing a statement for the primary bank account (which is usually code 1200), Brightpearl will check for existing, unmatched transactions on that account, as well as checking against all outstanding supplier and customer invoices.
Matching
A matching payment:
If a payment (in or out) has cleared your bank account and a matching transaction exists in Brightpearl, a Match button will be given. The accounting entries have already been created so no further action is needed except to match them together so you can see in the journal the item has cleared your account.
A matching invoice:
If a payment (in or out) has cleared your bank account and a matching invoice (sales or purchase) exists, an Allocate Receipt or Payment button will be given. The accounting entries for the payment have not yet been created (the invoice is currently unpaid), so by clicking the Allocate Receipt button you can generate the payment in Brightpearl against the invoice and match the items in one go. This makes receipting lots of payments directly into your account really quick!
No matches:
If no match is found it could be because the transaction amount is different or there is no relating transaction in Brightpearl or it is to match multiple items.
If the item hasn't yet been processed through Brightpearl, select the Bank Transaction tab and process the payment or receipt. However, if the amount is different you will have to exit the Bank Matching process and enter the payment against the contact and invoice so that you can create the necessary adjustment. You can then return to the Bank Matching process to simply match the statement line with the payment you just posted (see matching payment above).
Paying Multiple Invoices:
If you've got a number of payment cheques on a single paying in slip (i.e. a single bank deposit on your statement) from one or more customers, you will need to match the single deposit showing on your statement to multiple invoices in Brightpearl. Select the Invoice allocation tab and search for and match each relevant transaction. You can continue searching and matching until they are all selected. When there is zero left to allocate, click the Match selected button. This will create a sales receipt for all invoices, and mark all the resulting sales receipts as matched against the deposit.
You can use this process to match a single bank payment against multiple supplier invoices or purchase payments, too.
Un-matching
If you match an entry by mistake, you can un-match it in the same screen. Search for the statement line using the filters on the left-hand side to view Matched items for a particular date range. Once you find the line, click the Unmatch link next to it. The items are immediately unmatched from each other. This allows you to amend or cancel these transactions; unless they have been reconciled on a VAT return.
If you have multiple lines to un-match you may find it easier to go to the Bank Activity report to un-match.
- Go to Accounting > List bank accounts.
- Select Activity Report for the relevant bank account.
- Use the Filters to help you find the transactions.
- In the Matched column you will see a green tick indicating the item is matched. Click on the green tick to un-match the item.
Once un-matched the journal will be open to amendments and the statement line will be available for matching on the import screen.
Deleting Statement Lines
You can delete an imported entry from the matching screen by clicking the cross icon on the relevant statement entry, it is permanently removed and you will no longer able to match that item. You can, however, re-import the line to generate it again.
What if I have transactions on my bank account that do not match a statement entry? (Voiding)
If you have a transaction in Brightpearl that hasn't cleared your account you will need to rectify it to keep them in balance. If the transaction has not been reconciled against the bank account or VAT return, then you can zero the transaction and change the bank account nominal code to the suspense account to move it out of the way.
If the transaction can not be zeroed, and it should not be on the activity for that bank account, then we suggest that you create an equal and opposite transaction on the same date by creating a journal. These two transactions can then be matched as "void", to do this go to Accounting > Activity report and select the relevant bank account. Click the red cross in the Matched column for each item. A void transaction is marked with a blue exclamation icon.
