The advanced bank reconciliation process in Microsoft Dynamics 365 Finance and Operations is an easy feature that allows us to import electronic banks statements and make an automatic reconciliation with bank transactions.
To access this feature, we need to setup a service, an advanced integration framework inbound port, and link it to a bank format resource. Dynamics 365 Finance and Operations 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).
In this article I am going to explain how you can setup an advanced bank reconciliation in Microsoft Dynamics 365 Finance and Operations 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, it is necessary to setup some basic parameters first:
Follow this path: Cash and bank management > Setup > Bank transaction types:
Select the main account for the type. Then we can create the Bank transaction type.
Click on Cash and bank management > Setup > Bank reconciliation > Transaction code mapping.
Now, the statement transaction code that we received is mapping accordingly to the bank transaction type we setup on Dynamics 365 Finance and Operations.
Reconciliation matching rules
Matching rules are a set of criteria used to filter bank statement lines and bank document lines during the reconciliation process. We can setup more than one matching rule and create a reconciliation matching rule set that we can then tie to our 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 > Methods of payment.
There we will be able to assign a bank transaction type to each kind of payment method.
After that we can import the file. To import a BAI File, go to Cash and bank management > Bank statement reconciliation > Bank account
From the top menu we click on Reconcile > Bank statements > Import statement, and select the parameters.
Once in the system, we need to validate it, post it, create a new reconciliation journal and match all the lines within.
1. Advanced Bank Reconciliation process
After this configuration, we can start the reconciliation process.
First, run a trial balance at the day we want to reconcile the bank statement, just for comparative purposes to check the balance showing as the closing balance in the bank. Run it in General ledger > Inquiries and reports > Trial balance.
Now, to reconcile the bank statement, go to Cash and bank management > Bank accounts > Bank accounts and select the bank we want to reconcile with.
Then, we click on Bank statements, and select the one we just imported. It should appear as open in the status field.
Next, we click on the bank statement. There we can see the lines of transactions and edit or delete them.
After that validate and post the bank statement. The status is going to change from Open to Validated.
Next, create a new journal entry. go to the Bank accounts sheet and click on Reconcile > Bank reconciliation > New, and that’s going to pull it in with a draft status.
The next step is the matching part. Since we are not making a manual reconciliation process, we do not have to select all the transactions to manually match them. In this order, we are going to use our matching rules. Click on Worksheet > Run matching rules and select the parameters.
The reconciled transactions will now appear at the bottom part under the Matched transactions description.
Finally, click on Mark as reconciled to declare the entire journal as a reconciled journal.
I hope this article was useful in setting up the advanced bank reconciliation process within Microsoft Dynamics 365 Finance and Operations. If you require specialized advice for bank reconciliation or more details about all Microsoft Dynamics 365 solutions, please contact us.