Overview

After suggesting rows to pay your vendors and moving them into your banking software, all these payments have to be reconciled with the bank statement after a couple of days. This document is a quick reference on how to work with the bank reconciliations. With the help of Bank Reconciliation Journal or Payment Reconciliation Journal, a mentioned step can be done. In bank statements appear also other types of transactions, e.g. bank charges and receipts. These ones can be also processed through this functionality and transferred into the Journal to create G/L entries. Into Journals Bank statements can be imported automatically by synchronization or manually - imported from a file. They can of course also be typed on the keyboard, which is the slowest way.

Neccessary settings

Most of the settings have to be done on each bank account card separately, such as selecting the Bank Statement Import Format and Payment Reconciliation Journal set up. Bank Statement Import format set up is common to Bank Reconciliation and Payment Reconciliation Journal. Text to Account Mapping and Payment Application Rules are used by Payment Reconciliation Journal only.

Bank statement import format

Before you start importing, please select the Bank Statement Import Format on the Bank Account Card. Choose an import file format which matches the file format of your bank statement.

Open

This format version allows NAV to determine, how to read your bank statement import files. Provided that you've chosen the file format, but import is still not working, try to choose another one. In case your Bank Statement Import Format register is empty, please contact your consultant.

Text to account mapping

In Bank Account Card please choose Text-to-Account Mapping Code. This code can be created for each bank account separately or can be common (you can create Text-to-Account Mapping rule e.g. for EUR and USD bank account). For creating Text-to-Account Mapping codes type the name into Search bar or use advanced lookup in bank account card for the field Text-to-Account Mapping Code.

Open

In the Text-to-Account Mapping window, which can be opened from the Payment Reconciliation Journal window or from the Text-to-Account Mapping codes window, you can set up mappings between text on payments and specific debit, credit, and balancing accounts so that such payments are posted automatically to the specified accounts when you post the payment reconciliation journal.

Payments posted based on text-to-account mapping are not applied to open entries, but are merely posted to the specified accounts in addition to creating bank account ledger entries. Accordingly, text-to-account mapping is suited for recurring cash receipts or expenses, such as frequent purchases of car fuel or bank fees and interest, that regularly occur on the bank statement and do not need a related business document.

By clicking “New”, you can add new line with mapping.

• Mapping Text = word or phrase repeating in each transaction (e.g. Shell – the name of the fuel station)

• Bal. Source Type – Please choose G/L Account for G/L Account mapping (Debit and Credit Acc. No field has to be filled. When chose Customer or Vendor, Bal. Source No. has to be filled. 

• Bal. Source No. – No. of vendor or customer.

• Debit Acc. No. – No. of Account for debit transactions,

• Credit Acc. No. – No. of Account for credit transactions.

Payment application rules

In Payment Application Rules page (use the Search box to find it) there are default rules used for applying the payments in Payment Reconciliation Journal. If these default values don’t suit your needs, feel free to add your own rules. In every time you can restore Default Rules easily by clicking the button, see picture in text.

You can set up another bank payment application rule for each bank account separately. Bank Payment Application Rule Codes are available only by lookup on the bank account card.

Import process

There are two ways how to reconcile incoming and outgoing payments by importing bank statements into the NAV.  You can proceed it through Bank Account Reconciliations or Payment Reconciliation Journal. This process has been changed and simplified a little bit in comparison with previous versions of NAV.

In Business Central, both Journals reconcile bank statement transactions with bank account entries, but only the Payment Reconciliation Journal can also create new customer/vendor/bank account or G/L entries directly. If there is some entry of the bank statement in Bank Account Reconciliation Journal, which is not posted in NAV yet, it has to be first Transferred to General Journal and posted.

Bank account reconciliation journal

After you have important settings done, you can go directly to the Bank Account Reconciliations list. (Simply write the Bank Account Reconciliations into the search field.)

Click on the  button and create new empty Bank Account Reconciliation. Fill in Bank Account No. field, Statement date and Statement Ending Balance.  Then click on the Import Bank Statement and simply choose an import file from your disk storage. New Bank Statement lines will be imported.

Lines can be also typed manually or suggested by the NAV. For the suggestion of lines, use the “Suggest Lines..” button. Than in pop up you have to choose Statement Period and decide if Check should be included or not, see the picture on the right side.

After the import/insert, in the left column lines from the bank statement will appear. You have two options for how to apply the lines into the ledger entries. It can be done manually or automatically. We will talk about this in the following chapters.

Bank reconciliation journal can be posted, after all lines are matched and the Total Difference is 0.  If an item appears as a bank fee in the bank statement, keep in mind that you must first create a general ledger entry for that item.

Automatic lines matching

To automatic matching the Bank Account Ledger Entries and Bank Statement Lines, simply click on Match automatically (you can find the button on the ribbon, in section Matching).

After that window with transaction tolerance setting will pop up. Please fill the tolerance = the maximum possible difference between the posting date of the Bank Account Ledger Entry and the Posting date in the bank statement line.

Manual lines matching

Of course, you can do some adjustments. For this purpose, there are two options in the “Matching” section of the ribbon. Match Manually and Remove Match.

If you need match lines manually, simply chose one line from the left side of the window, one line from the right side of the window and click on the “Match Manually”. If you need to “disconnect” lines, use the “Remove Match” button in the same way.

Payment reconcilliation journal

For faster and simpler reconciliation of payments, you can use the Payment Reconciliation Journal. You can post the payments and reconcile the bank account in one step here. Bank transactions can be imported it the same way like in the Bank account reconciliation journal.

For each bank account has to be created new journal, see the picture.

After choosing the bank account, Payment Reconciliation Journal will open. By using Import Bank Transactions create lines of the journal.

Try to Apply lines Automatically, for details see the Automatic lines matching chapter.

As you can see above, imported payment -10.100 was applied to London Candoxy Storage Campus with Low match confidence and Difference -100. To check if everything is correct, use „Apply Manually..“ button and new Payment Application window will open. By using Related-Party Open Entries button only one line with open payment shows.

I agree with this way of application, so I will close the window to solve the Difference -100,-. I've decided to apply the difference to the customer's account as a new payment. Now I can use Transfer Difference to Account button, see the picture below. New window will pop up, here I can choose type and number of the account (e.g. customer, vendor, G/L account, etc.).

The Transaction amount will be reduced and divided into two lines. Now the journal can be posted. To reconcile bank account in one step, use Post Payments and Reconcile Bank Account Button.

Automatic lines matching

Automatic line matching needs some simple settings on the Bank Account card, see the chapter Payment application rules and Text to account mapping. Than you can use Apply Automatically or Apply Automatically with Setup buttons.

If you choose Apply Automatically with Setup, you can change predefined application parameters from the bank account card.

Manual lines matching

Manual lines matching works almost in the same way like in the Bank account reconciliation journal.

If you decide to use Apply Manually button, new window Payment Application will pop up.

If selected payment was applied before, you will see applied amount in bold. You can filter rows with ribbon buttons: Applied Entries, All Open Bank Transactions, All Open Payments, All Open Entries and Rellated-Party Open Entries – this button is filtering all rows with the same Account No. You can populate the Applied Amount field as you need to.