The latest release, Microsoft Dynamics 365 version 10.0.36, brings a significant enhancement to the advanced bank reconciliation process. This update introduces a more user-friendly approach to defining bank reconciliation matching rules, which can substantially streamline and optimize the process for clients. In the past, users and consultants were confined to a single "one-to-one" matching rule, which often led to the creation of numerous complex rules or extensive manual matching.
However, by enabling the "Advanced bank reconciliation improvement: enable group conditions in reconciliation matching rules" feature in Feature Management, users now gain access to three additional matching types. Let's get introduced to our new matching rules:
"Many to One": One D365 transaction matched to multiple bank statement transactions.
"One to Many": Many D365 transactions matched to one bank statement transaction.
"Many to Many": Many D365 transactions matched to multiple bank statement transactions.
We will cover two of them in today's blog, "One to Many" and "Many to One".
Scenario 1: "One to Many"
This feature can be used for a number of reasons, including but not limited to the following;
Multiple incoming payments are posted individually in D365 but get recorded as
one deposit in the bank statement.
Check deposits are recorded individually in D365 but are recorded as one deposit in the bank statement.
Transactions coming from a 3rd party system are being imported/integrated into D365 in detail while the bank statement only has one lump sum.
In this case, we need to identify those transactions and make note of the transaction code, transaction type, and any other identifying information. In this example, we noticed that we made 3 ACH payments to AAA on 10/11 and they totaled $72,557.38. When we go to apply our matching rule sets, the system doesn't match these transactions. This is a common occurrence so let's set up a new matching rule to recognize these transactions to further decrease the need for manual intervention throughout the reconciliation process.
Bank statement (BAI2) transactions:
D365 transactions:
When configuring the "One to Many", we need to find "grouping" values in the D365 transactions that are not "Bank transaction type" or "Date". We notice that "Related party name" and "Related party type" are the same for the transactions we want to group together. "Related party type" will always be "Vendor" on a vendor payment journal transaction, so we decided to use "Related party name" as our grouping field. Determining the correct grouping field is crucial for this process to work as intended.
Navigate to Cash and bank management > Setup > Reconciliation matching rules > New
These are the fields that the matching rule will look at and group transactions by. If all three of those values are the same, it will consider it one summed amount and look for a line on the bank statement to match it too.
We want to match the date, amount, and transaction type.
Here we tell the matching rule which bank statement lines to look at while matching. We know from experience that this happens when our bank transaction code is equal to 466.
We can get very complex with the matching criteria, for these examples, we will keep it simple.
Now we navigate to our bank statement worksheet reconciliation! Cash and bank management > Bank statement reconciliation > Bank reconciliation > Worksheet
Select "Run matching rules" in the action pane and run DSB O2M.
Voila! We matched one transaction in our bank statement to many in D365!
Scenario 2: "Many to One"
Multiple incoming payments are broken out in a bank statement but get recorded as
one lump sum in D365.
Check deposits are recorded individually in the bank statement but are recorded as one lump sum in D365.
Transactions coming from a 3rd party system are being imported/integrated into D365 in bulk and not in detail while the bank statement breaks them out individually.
In this example, we made four separate payments to a vendor in our AP payment system that fed into D365 as one single payment transaction. When the client runs their matching rules, it does not recognize that the singular D365 payment is the summation of our four outgoing ACH payments.
Bank statement (BAI2) transactions:
D365 transactions:
When creating matching rules for "Many to One" we need to identify related values. In this example, we are going to match our bank transaction type, amount, and date.
Because we are looking at the bank statement (BAI2) transactions list for groups of transactions to match, we use "Booking date" instead of the D365 transaction nomenclature.
Adding additional criteria to ensure we are only searching a subset of our bank transactions.
Once we create our new matching rule, we can navigate back to our bank reconciliation worksheet. Select "Run matching rules" in the action pane.
Success! Very nice!
Not every transaction mapping will be this simple. There will be some that will require a keen eye to dissect each grouping and BAI2 file.
In conclusion, Microsoft Dynamics 365 version 10.0.36 introduces user-friendly bank reconciliation matching rules that enhance the advanced bank reconciliation process. Users were previously limited to a one-to-one matching rule, leading to complex rules or manual matching. With the new "Advanced bank reconciliation improvement" feature, three additional matching types are now available, including "One to Many" and "Many to One." "One to Many" helps reconcile transactions where multiple D365 entries correspond to a single bank statement transaction, while "Many to One" handles scenarios where bank statement items need to be matched to a single D365 transaction. These innovations streamline the reconciliation process in Microsoft Dynamics 365, offering greater flexibility and efficiency.