Miami, United States 701 Brickell Ave. Miami, FL 33131
+1 (800) 501-3631

How to set up Advanced Bank Reconciliation Process

Companies must match the balances in their accounting records, such as check register, general ledger account or balance sheet, to the corresponding information on a bank statement. This process is known as Bank Reconciliation.

Generally, a company prepares bank reconciliations on a monthly basis. The objective of this process is to discover the differences between records and register changes to the accounting records as necessary.

The 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 set up a service and an advanced integration framework inbound port and link it to a bank format resource.  The system comes installed with three standard formats: MT940 from SWIFT, the Society for Worldwide Interbank Financial Transactions; BAI2; and ISO20022, also called SEPA, the Single Euro Payments Area.

In this article I am going to explain how you can set up 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. It has the following records:

    • Record Type: 01 – File Header – indicating the beginning of the file.
    • Record Type: 02 – Group Header – identifying the group of accounts – a file may contain multiple groups and would be followed by a 03 record.
    • Record Type: 03 – Account Identifier & Summary Status – indicating the account number, activity summary and account status information.
    • Record Type: 16 – Transaction Detail –Transaction detail information such as generic reference information is indicated in this record type (optional record).
    • Record Type: 49 – Account Trailer – indicating account control totals.
    • Record Type: 98 – Group Trailer – indicating group control totals.
    • Record Type: 99 – File Trailer – end of file indicator.

advanced bank reconciliation 1

 

 

 

 

 

 

 

 

 

With the purpose of starting the matching process it is necessary to set up some basic parameters. First, we need to set up Transaction types which are used to link transactions. We can also print a summary of the transaction types on the bank statement report. We can reconcile a bank statement by the transaction type

To set them up, we have to follow this path: Cash and bank management > Setup > Bank transaction types:

advanced bank reconciliation 2

Then we can create the Bank transaction type. After, we select the main account for the type.

  • advanced bank reconciliation 3

After these steps we can initiate the advanced bank reconciliation setup process.

1.Transaction codes

We have to click on Cash and bank management > Setup > Bank reconciliation > Transaction code mapping.

advanced bank reconciliation 4

Here we are saying that the statement transaction code that we receive is mapping to what the bank transaction type we have set up on the system.

advanced bank reconciliation 5

2.Reconciliation matching rules

Matching rules are a set of criteria that are used to filter bank statement lines and bank document lines during the reconciliation process. We can set up 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, we have to click on Cash and bank management > Setup > Advanced Bank reconciliation setup > Reconciliation matching rules

advanced bank reconciliation 6

advanced bank reconciliation 7

3.Method of payment

Now, we must configure the method of payment that is going to be used in the transaction. The path is Accounts payable > Setup > Payment > Methods of payment.

advanced bank reconciliation 8

There we will be able to assign a bank transaction type to each kind of payment method.

advanced bank reconciliation 9

After that we can import the file. To import a BAI File, go to Cash and bank management >  Bank statement reconciliation > Bank account

advanced bank reconciliation 10

From the top menu we click on Reconcile > Bank statements > Import statement, and select the parameters.

advanced bank reconciliation 11

Once in the system, we need to validate it, post it, create a new reconciliation journal and match all the lines within.

Advanced Bank Reconciliation process

After this configuration, we can start the reconciliation process. First, we need to 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.

advanced bank reconciliation 13

 

advanced bank reconciliation 16

Now, to reconcile the bank statement, we have to go to Cash and bank management > Bank accounts > Bank accounts and select the bank we want to reconcile with:

advanced bank reconciliation 18

advanced bank reconciliation 19

Then, we click on Bank statements, and select the one we just imported. It should appear as open in the status field.

advanced bank reconciliation 20

Next, we click on the bank statement. There we can see the lines of transactions and edit or delete them.

After that we have to validate and after that post the bank statement.

After the posting process, the status is going to change from Open to Validated.

Next, we have to create a new journal entry.  We have to go at 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.

advanced bank reconciliation 23

After that all the transactions to reconcile will appear automatically checked. Now click on Match to complete the process. The reconciled transactions will now appear at the bottom part under the Matched transactions description.

advanced bank reconciliation 24

Finally hit on Mark as reconciled to declare the entire journal as a reconciled journal.

advanced bank reconciliation 25

I hope this article was useful in settting 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 fill out the form bellow and one of our experts will contact you.

Andrea Gomez

Andrea Gomez

Microsoft Dynamics 365 Finance and Operations Junior Consultant at Pangea Group
Andrea is an industrial engineer. She has one year of experience working as Microsoft Dynamics 365 Finance and Operations Junior consultant, focused on implementation of the ERP systems and processes consultancy.
Andrea Gomez

Latest posts by Andrea Gomez (see all)

Related Posts

Contact Us