The advanced bank reconciliation process in Microsoft Dynamics 365 Finance is an easy feature that allows us to import electronic bank statements and make an automatic reconciliation with bank transactions.
Dynamics 365 Finance comes with three standard formats: MT940 from SWIFT, the Society for Worldwide Interbank Financial Transactions (BAI2), and ISO20022 (or SEPA, the Single Euro Payments Area).
This blog will explain how you can set up an advanced bank reconciliation in Microsoft Dynamics 365 Finance using the BAI2 format. From my experience, the BAI2 format is widely used in the US, and it is globally supported by most of the major global banks.
To start the matching process is necessary to set up some basic parameters first:
Create the bank transaction types required and assign them to the main account, which is suggested automatically at the offset account in the transactions with that transaction type.
Cash and bank management > Setup > Bank transaction types:
The transaction codes are an optional setup used as a matching character into the Matching rules. It has to be done per bank, matching the Finance codes and the statements transaction codes used in the bank.
Cash and bank management > Setup > Advanced bank reconciliation setup> Transaction code mapping.
Matching rules are criteria used to filter bank statement lines and bank document lines during the reconciliation process. More than one matching rule can be set up, and then a matching rule set can be tied to the bank account.
To set them up, click on Cash and bank management > Setup > Advanced Bank reconciliation setup > Reconciliation matching rules.
Now, configure the method of payment that is going to be used in the transaction.
The path is Accounts payable > Setup > Payment setup > Methods of payment.
In there, the method of payment can be linked to the Bank transaction type.
After that, the BAI2 file can be imported. To import a BAI File, go to Cash and bank management > Bank statement reconciliation > Bank accounts.
From the top menu, click on Reconcile > Bank statements > Import statement, and select the parameters.
Once in the system, it should be validated and posted, creating a new reconciliation journal.
Cash and bank management > Bank statement reconciliation > Bank accounts > Reconcile > Bank Reconciliation.
After this configuration, the reconciliation process can start.
First, run a trial balance on the day the bank statement will be reconciled, just for comparative purposes to check the balance showing as the bank’s closing balance. Run it in General ledger > Inquiries and reports > Trial balance.
To reconcile the bank statement, go to Cash and bank management > Bank accounts > Bank accounts and select the bank to be reconciled.
Then, click on Reconcile > Bank reconciliation, and select the one just imported. It should appear as open in the status field.
Click on Worksheet at the header options; this will display the form showing the bank statement transactions and the system bank transaction pending to be reconciled.
The next step is the matching part. Since a manual reconciliation process is not being done, all the transactions do not have to be manually selected to match them manually. The Matching rules are going to determine the records to match. Click on Worksheet > Run matching rules and select the parameters. A Matching rule or a Matching rule set can be selected.
The reconciled transactions will now appear at the bottom part under the Matched transactions description.
If any transactions are still open, the remaining reconciliation can be done manually.
Finally, click on Mark as reconciled to declare the entire journal as a reconciled journal.
