Standard Microsoft Dynamics 365 for Finance and Operations enables users to import bank statements, like the MT940 and CAMT053 files. But the system does not help the user to settle these with outstanding invoices. As a result, after importing a bank statement, settlement is still a time consuming manual activity. With the reconciliation function of Banking the user only has to focus on the exceptions as the system will try to settle the payments automatically.

The primary process consists of these steps:

- Download a Bank Statement file from the bank. This can be a CAMT053, BAI2 or MT940 file

- In the Bank Management Workspace, import the Bank Statement using GER. This can be done manually, but you can also set up a batch routine that reads an Azure folder periodically

- Transfer the Bank Statement to a (set of) Banking Journal(s), which will create the journal lines and has the option to immediately assign lines to companies, customers, vendors or ledger accounts

- If desired, import a payment specification for a journal line

- Run the reconciliation function, which will execute the 7 main scenarios

- Manually add / correct settlements

- Post the journal

Process

Download files from the bank

Almost every bank is able to provide their statements in an electronic format. Within the European Union, CAMT053 is becoming the norm, but MT940 is also still around. BAI2 is usually the format in the United Kingdom and (former) Commenwealth countries. More information on statement formats can be found here.

Import files, using GER

The Bank Management workspace is the best place to import the statements. Using Generic Electronic Reporting the various statement formats are mapped and the required data is brought into the Bank Statements. This can be done manually, importing a single file, but can also be scheduled. More information on importing Bank Statements can be found here.

Review imported statements

After import, the imported data is visible in the Bank Statements. During implementation, consultants and users will use this as an intermediary step to validate the data. In a production setting, this step is usually skipped.

Transfer to Banking Journal

After reviewing / validating the imported Bank Statements, they are transfered to banking Journals. Large statements can be split into multiple journals, to facilitate efficient processing. Usually, users will specify that they want the data to be interpreted in this step and assign companies, customers, vendors and ledger accounts immediately.

Reconciliation

Often already triggered as part of the Transfer, the reconciliation tries to interpret the data and searches for custmers, vendors and ledger accounts. And within those found accounts, it searches for open invoices that can be settled with the imported lines. Details of reconciliation can be found here.

Payment Specification

In some cases, customers are paying multiple invoices and they send a detailed specification by email. Such a specification can be imported into a journal line. Here you can find more information on External Payment specifications.

Posting of Bank Journal

When all lines have been taken care of, the journal can be posted. This will create the accounting transactions and update the Customer and Vendor transactions.

Setup

Import methods

If Electronic reporting is used to import the statement, please find more details here. Although it is not the preferred way, it is still possible to import a statement directly into a general journal. In that case, use this setup:

General ledger > Ledger setup > Bank statement import methods for general journal

To enable the system to import Bank statements, the import method must be specified in the Bank statement import methods for general journal.

Create an import method and select e.g. “NL – SWIFT MT940” or “CAMT.053 Bank to Customer Statement” in the field ‘Import format’.

Fill the company’s own bank account number.

If Bank statements will be imported for multiple bank account numbers, create a separate import method per bank account.

The bridging account is the account for vendor payments or customer direct debits, that have been generated and posted, but have not yet cleared the bank. It is connected to the chosen bank account number.

Journal names 

General ledger > Journal setup > Journal names

Create a new Journal name:

• Type Daily
• Offset account proposal, account type = Bank
• Fixed offset account = true
• Attach accounts receivable to bank account
  • None
  • Interactive: User will get a pop-up and decides if a bank account is attached to the selected customer. When a user changes the customer on a journal line, and the bank account and new customer are not already connected, the system will ask if the bank account needs to be transfered to the new customer. As of release 1009.33.641 the bank account record will include the country code. As of release 1011.35.650, a bank account will not be removed from an existing customer, if a bank is attached to a new customer. This is to prevent data inconsistency.
  • Automatic: No question will be asked; the imported bank account is attached to selected customer automatically. On changing a customer on the journal line, the system will not transfer the bank account to the selected customer, but it will add the bank account to the new customer too. So, if you switch customers on a journal line the bank account will be added to multiple customers. Removal from the old customer wil have to be done manually. As of release 1009.33.641 the bank account record will include the country code.
• All lines must be green: Journal can only be posted when all lines are marked green. This means that all lines with a warning need to be approved manually.
• Cumulate bridging/Bridging account:
  When you are processing a bank account, you are usually most interested in the payments you receive from your customers. The payments to your vendors, you will most likely have triggered, using a payment journal. The posting of a payment journal is typically Vendor @ Bridging Account. When these payments are processed in the bank, the posting will be Bank @ Bridging Account. If you want each payment to come into your general journal as a separate line, set cumulate bridging to ‘No’. If you would like to put all these bridged vendor payments together on a single line, select cumulative bridging to ‘Yes’.

•  Note
  

  Field Cumulate Bridging and Bridging Account have been removed from the form. The functionality is not in use anywhere, and in a future release the functionality will be removed completely.
     

• Validate offset bank account:
  To help users prevent importing Bank Statements (MT940 or CAMT53) into the wrong journal, the field ‘Validate Bank Account’ can be activated on a journal. If activated, Microsoft Dynamics 365 for Finance and Operations will first compare the IBAN in the Bank Statement to the IBAN associated with the Offset Bank Account in the journal. If they are different, Microsoft Dynamics 365 for Finance and Operations will compare the Bank accounts. If those are also different, the user receives either an error or a warning, depending on the setting on the journal name. In the case of an MT940 format, the bank account is taken from line :25.
• Sort by Statement:
  • If set to 'Yes', Microsoft Dynamics 365 for Finance and Operations will use the Internal Statement Line Number to present the rows.
  • If set to 'No', the standard way of sorting is used
• Split Line on Settle: determines the results of answering 'Yes' to the question that pops up, if there is a difference between the journalline amount and the sum of the related settled transactions. 
  • Yes: The difference between the value of the journalline and the settled transactions is put on a new line.
  • No: The amount of the journal line is adjusted to the amount of the settlements.
• Partial Payments
  •  Settle: If an invoice is found, but the paied amount is less than the open amount on the invoice, the payment will still be applie to the invoice
  • Mark green: If partially settled, the line will be marked as green
  • Limit by amount: A partially settled line will only be marked green if the difference between the paid amount and the open amount is less than or equal to the Accounts Receivable / Accounts Payable parameter 'Maximum penny difference'

Bank reconciliation setup

In the form Bank reconciliation setup (General ledger > Ledger setup > Bank reconciliation setup) you can specify the overall working of the functionality.

The parameters are the same as the parameters found in Cash and bank management parameters.

On tab Bank Reconciliation Settings it is specified how the information in the payment description will be interpreted by the system. It is possible to define settings for all customers (Relation type = All), or to define customer / vendor specific settings (Relation Type = Account Number).

Tab Tracing allows the user to configure the Tracing settings. Tracing can be used to understand why the system find a customer/vendor and/or an invoice. As it consumes more resources, use it when needed. It does not have an effect on the way accounts and invoices are found.

Internal Bank Statement Line number 

To keep the original order of the imported lines, an internal line number is assigned.

Payment terminals

General ledger > Ledger setup > Payment terminals

Cash and bank management parameters

Cash and bank management > Setup > Cash and bank management parameters > tab: Bank reconciliation

The parameters are visible in both the Cash and Bank parameters, and the Bank Reconciliation setup. Over time, all parameters will be in the same place: ...