Advance Bank Reconciliation in Dynamics 365 F&O | Complete Guide

This blog will cover the complete Advance bank reconciliation setup and process in Microsoft Dynamics 365 F&O.

1. How to import a Bank Statement
2. Define the Reconciliation matching rules

• A reconciliation matching rule is a set of criteria that are used to filter bank statement lines and Microsoft Dynamics 365 Finance bank transaction lines during the reconciliation process. Depending on your business practice, you can set up more than one matching rule to automate and optimize your reconciliation process.

3. Reconcile bank statements with Dynamics 365 bank transactions.

• Perform automatic matching and creation of reconciliation journals.
• View bank statements and Dynamics 365 bank transactions side by side.
• Automatically post Dynamics 365 bank transactions if they appear on a bank statement but don't appear in Dynamics 365.
• Generate a reconciliation statement.

Pre-requisite step: To setup Advance bank reconciliation we don’t need to create the data project for bank statement format using data management. Now, its completely based on electronic reporting framework.

Step 1: Create a Bank Account & activate Advanced Bank Reconciliation

Navigation: Cash & Bank Management > Bank Accounts > Bank Accounts

1. Provide the desired Bank account code which you like to save in Dynamics 365.
2. Mention the Main account or create it in the General Ledger module.
3. Mention the Bank account number (i.e. 0123456789). It is important that the bank account number should be the same in Dynamics 365 and the bank statement (MT940) which we will be uploading. If it's not the same the system will throw an error.
4. Mention the name of the Bank account (i.e. Demo Bank Account)
5. Open the Reconciliation tab.

Activate the checkmark "Advanced bank reconciliation".

The system will pop up the message as highlighted above. Advanced bank reconciliation can now be turned off by activating a feature from feature management if there are no uploaded statements in unreconciled state.

Step 2: Import a Bank Statement (MT940)

Before you import a bank statement, make sure that the bank account number in the MT940 bank statement and Dynamics 365 are the same otherwise you will get an error. In the example mentioned above, the bank account number is "0123456789" in Dynamics 365.

Tag 25 is the account identification field that should match with the bank account number mentioned in Dynamics 365.

Upload a Bank Statement and Validate

Navigation: Cash & Bank Management > Bank Statement Reconciliation > Bank Statements

Click on "Import Statement".

1. Select the Bank account from the drop-down menu.
2. Select the statement format defined earlier. (Part 1)
3. Click on Browse and select the file which you would like to upload.
4. Click on Upload.
5. Once successfully uploaded, click on Ok.

Click on the Statement ID to open the uploaded bank statement.

The bank statement has been uploaded, you can see multiple details in the above-highlighted screenshot which will be discussed in detail in the next step. If you would like to see transactions separately in the debit/credit column, activate the check "Show statement line amount in debit/credit" in Cash and bank management parameters.

Before validating it make sure to check that all transactions are reflecting in the statement. Once done, click on the Validate button. You can also use the Reopen button to open the bank statement after validating it.

After the validation step, the Reopen button pops up.

Step 3: Define Reconciliation Matching Rules, Transaction Code Mapping and Bank Transaction Types

Defining matching rules is a bit tricky but if you completely understand the bank statement format provided by the bank, this will be an easy task.

The screenshot attached above demonstrates the transactions in the bank statement which we have uploaded in Step 2. The two highlighted transactions occur when a bank error causes two bank statement lines to be listed in the imported bank statement, and the lines must be reconciled.

We will be matching the following columns:

1. Transaction Date
2. Amount
3. Bank statement transaction code
4. Reference number

You can use other information as well e.g. Document number, description, etc. It totally depends on the information your bank is providing.

Create Bank Transaction Types & attach them to payment methods

First, create the bank transaction types.

Navigation: Cash and bank management > Setup > Bank transaction types

You can use the numbers of your choice in the bank transaction type. If there are multiple transaction types of the same nature, you can also club them in bank transaction groups (Cash and bank management > Setup > Bank transaction groups).

In the screenshot attached above, I've created all the transaction types used in the uploaded bank statement except bank reversal (N208) because we have to remove it from the worksheet using the matching rule "Clear reversal statement lines". Also, I've attached the main account with Bank charges because these will be marked as new transactions through a matching rule and shall be posted later.

After defining the bank transaction types, attach them to the relevant Payment methods in the Account Payable/Receivable module.

Navigation:

Account Payable > Payment Setup > Methods of payment

Accounts Receivable > Payments Setup > Methods of payment

For example

Transaction Code Mapping

Now, we have to map the Bank statement transaction codes with bank transaction types which we have defined in Dynamics 365.

Navigation: Cash and bank management > Setup > Advanced bank reconciliation setup > Transaction code mapping

1. Click on New.
2. Mention the Bank account and the name will be auto-populated.
3. Mention the Bank statement transaction code in the statement transaction code column.
4. Select the relevant bank transaction type defined earlier against each statement transaction code.

The purpose of transaction code mapping is that in the reconciliation worksheet Bank statement lines will be mapped according to the bank transaction types which we have defined in Dynamics 365 which helps in defining matching rules.

Define Reconciliation Matching Rules and Sets

Navigation: Cash and bank management > Setup > Advanced bank reconciliation setup > Reconciliation matching rules

There are three types of action that can be defined in a reconciliation matching rule:

• Match with bank document 

It is used to create criteria to specify how the bank documents and bank statement lines are matched when the matching rule is run from the Bank reconciliation worksheet.

The above matching rule will match the Dynamics 365 transactions with the Bank statement transactions. Considering the bank statement uploaded in Step 2, this rule will be applicable on the following bank statement transaction codes (N175, N475 & N508).

In the basic criteria, checkmark the following:

• Match amount
• Match date
• Match transaction type

We mapped the bank transaction types with the bank statement transaction codes earlier. This will be more clear once we move on to the bank reconciliation worksheet (Step 4).

In the additional criteria:

Select field (Dynamics 365) "Payment reference" = Bank Statement field "Reference no"

We need to mention the exact transaction reference no. in the payment reference (Dynamics 365) field while making/receiving payments in the Accounts Payable/Receivable module. When we generate checks in the Accounts payable module, the check number is auto-populated in the payment reference field.

Leave the "Step (2): Select the statement lines to run matching rules against" empty. This is used if you want to run the matching against a specific type of transaction in a bank statement.

• Mark new transactions

It is used to create criteria to specify how new transactions should be marked on the Bank reconciliation worksheet when the matching rule is run.

The bank statement transaction code N699 is the code for Bank charges in the bank statement which we have uploaded. Any transactions with the N699 code will be marked as a new transaction which will be later posted in the relevant main account defined in the bank transaction type (i.e. 01 - Bank Charges).

• Clear reversal statement lines

It is used to create criteria to specify how reversal statement lines should be removed from the Bank reconciliation worksheet form when the matching rule is run. This option is used when the bank makes a mistake and there are two bank statement lines listed in the imported bank statement and the lines must be reconciled.

The bank statement transaction code N208 is the code for Bank reversal in the bank statement which we have uploaded.

In "Step 1: Find reversal statement lines"

Mention Bank transaction code = N208

In "Step 2: Find original statement lines"

This step will search the transaction in the bank statement which has been reversed later.

Checkmark the following:

• Opposite amount
• Match date

In the Additional criteria, we have defined that reference no. field is the same in the bank statement transactions.

Make sure to activate all the matching rules.

Reconciliation Matching Rule Sets (Optional)

Now, we have to add all the matching rules in a set. However, you can run a matching rule individually.

Navigation: Cash and bank management > Setup > Advanced bank reconciliation setup > Reconciliation matching rule sets

1. Click on New.
2. Mention the name of the matching rule set.
3. Add the reconciliation matching rules defined earlier and make sure they are active.

Step 4: Reconcile a Bank Statement

Navigation: Cash and bank management > Bank statement reconciliation > Bank reconciliation

1. Select the bank account from the drop-down menu and the system will auto-pick the rest of the details from the bank statement uploaded earlier. The system allows reconciling only one bank statement of a bank account at a time.
2. Click on Worksheet.

The bank reconciliation worksheet will appear as above. The Bank transaction type column will be updated due to Transaction code mapping (Step 3) due to which the "Match transaction type" checkmark will work defined in the matching rule "Match with Bank document (Step 3).

Review the excel (Exported) below to develop an understanding of how matching rules defined earlier will work.

Now, click on Run matching rules and select the matching rule set.

The transactions will be auto-matched once the matching rule is run.

Select any matched transaction to check against which transaction it has been matched (right tab). Now, post the bank charges which have been marked as new transactions.

Navigation: Cash and bank management > Bank statement reconciliation > Bank statements

Select the Matched statement and then click on Post. This will post the transactions marked as new. Once done, you can click on the Accounting tab to check accounting and distributions.

Now, go back to the reconciliation tab and mark the bank reconciliation worksheet as reconciled. After marking it as reconciled, you can print the reconciliation statement.

This sums up the Advanced Bank Reconciliation in Dynamics 365.

Implementing Advanced bank reconciliation seems to be a tough job. There are gazillion scenarios in the process, you need to completely understand the format provided by the client's bank and the way a client is entering data into the system. Following are a few common challenges:

• The banking system of the country in which the client operates does not provide any of the three formats (e.g. ISO20022, MT940, and BAI2). However, there's always a room to make manual reconciliation a bit automatic through customization.
• Banks provide bank statements in different patterns which makes it more complex to design it in the system.
• The transaction breaks up (e.g. there's one transaction of 50,000 in the bank statement and in Dynamics due to some reason the client is entering five transactions of 10,000). There are different ways to cater to such situations but it increases the complexity.
• End-users are not trained enough. In order for it to work properly, Advanced bank reconciliation requires high-level accuracy in data entry.

In the end, nothing is impossible. The sky is the limit!

Hope you learned from this article. If you have read this far, please leave a comment/reaction and feel free to message me for bank statement sample formats.