Bank reconciliations

  • 18 minutes

In large organizations, bank reconciliation can be a time-consuming and tedious process when done manually.

The advanced bank reconciliation feature lets you import bank statements. The imported bank statement can then be automatically reconciled from within bank transactions. The steps in the advanced bank reconciliation flow are:

  1. Set up a bank statement import.

    • Import bank statements through the data entity framework.
    • Three typical bank statement formats are supported in Finance: ISO20022, BAI2, and MT940.
    • The functionality can be extended to any format.

  2. Set up a number sequence to use for advanced bank reconciliation and define the bank reconciliation matching rules. A reconciliation matching rule is a set of criteria that is used to filter bank statement lines and Finance bank transaction lines during the reconciliation process. Depending on your business practice, you can set up more than one matching rule to automate and optimize your reconciliation process.

  3. Reconcile bank statements with Dynamics 365 Finance bank transactions.

    • Perform automatic matching and creation of reconciliation journals.
    • View bank statements and Finance bank transactions side by side.
    • Automatically post Finance bank transactions if they appear on a bank statement but don't appear in Finance.
    • Generate a reconciliation statement.

Note

You can disable the advanced bank reconciliation functionality and reduce complexity of bank reconciliation for users by using simple bank reconciliation. If there are already reconciled transactions for an account or posted bank statements for an account you will not be able to revert to simple bank reconciliation.

Import an electronic bank statement

You can import your bank statements by using the Import statement action on the Bank statements page. On the bank statement, the bank account is identified through a combination of values that are set on the bank account details. These values include the bank name, bank account number, routing number, Society for Worldwide Interbank Financial Telecommunication (SWIFT) code, and the International Bank Account Number (IBAN).

You can upload a bank statement that contains information for a single account or multiple accounts. If multiple accounts exist, the accounts can be in different legal entities.

  • To import a single bank statement file for a single account, set the Import statement for multiple bank accounts in all legal entities option to No, and then select the bank account that is associated with the statement. Select Browse to select the associated bank statement file and then select Upload.

  • To import a single bank statement file for multiple accounts, set the Import statement for multiple bank accounts in all legal entities option to Yes. Select Browse to select the associated bank statement file and then select Upload.

If any statements in the electronic file can't be associated with a bank account by using the identifying fields, they won't be imported. However, other statements in the file can still be imported. The user then receives a message stating that the import of bank statements was unsuccessful for specific bank accounts. The user who is importing the bank statement file must have access to a legal entity to import statements for that legal entity's bank accounts.

You can use a zip file to upload multiple statement files in a single process. To import multiple bank statement files for multiple accounts, combine all the bank statement files into one zip file. In the Import bank statements dialog box, set the Import statement for multiple bank accounts in all legal entities option to Yes. Select Browse to select the zip file that contains the bank statement files and then select Upload. The import process will recognize the zip file and upload each statement that is included in it, regardless of the legal entity of the bank account.

A Reconcile after import option is available. When you set this option to Yes, the system automatically validates the bank statement, creates a new bank reconciliation and worksheet, and runs the default matching rule set when the bank statement is uploaded. This functionality automates the process up to the point where transactions must be manually matched.

Cash and bank management parameters

Several parameters that are specific to the advanced bank reconciliation process are available on the Cash and bank management parameters page. The Show statement line amount in debit/credit option changes the view of amounts on the Bank statement page. If this option is selected, the bank statement transaction amounts will be shown in separate debit and credit columns. If the option is not specified, the bank statement transaction amounts will be shown in a single amount column with the appropriate sign.

The validation options that are set on the parameters page override the selections that are set on matching rules.

For example, you cannot manually or automatically match documents beyond the date difference that is set on the parameters page.

Also, suppose that the Validate transaction type mapping option is selected. In that case, the transaction types must be mapped between the Finance bank transaction and bank statement transaction for the transactions to be manually or automatically matched.

You must also configure number sequences on the Cash and bank management parameters page. On the Number sequences tab, set up number sequence codes for the Download ID, Statement ID, Reconcile ID, and Bank reconciliation references.

Matching rules and matching rule sets

You can set up reconciliation matching rules and reconciliation matching rule sets to help with the bank reconciliation process. A reconciliation matching rule is a set of criteria that is used to filter bank statement lines and Finance bank transaction lines during the reconciliation process. Use the Reconciliation matching rules page to set up the reconciliation matching rules. You can set up more than one matching rule and then create a reconciliation matching rule set on the Reconciliation matching rule sets page.

Bank account reconciliation options

You must first enable Advanced bank reconciliation for the bank account. Several options are available on the Bank account page when the Advanced bank reconciliation functionality is enabled:

  • Use bank statements as confirmation of electronic payment - Integrates the bank reconciliation functionality with electronic payment statuses. When this functionality is enabled, a bank document will automatically be created for the electronic payment when the status is set to Sent. In addition, the status of an electronic payment will be updated from Sent to Received after the payment is matched, reconciled, and posted.

  • Bank account name in statements - The name used for the bank account on your electronic bank statements. Use this name when you are determining what transactions to import for a bank account from a statement that might contain information for multiple bank accounts.

  • Reconcile after import - Automatically validates the bank statement, creates a new bank reconciliation and worksheet, and runs the Default matching rule set. This functionality automates the process up to the point of the transactions that must be manually matched. The setting on the bank account will default when you are importing.

Transaction codes

Transaction codes can be used as part of the bank reconciliation matching rules. Transaction codes will help to match the same types of transactions between Finance and your bank statement. To do this type of matching, you must first define transaction types that are used for bank transactions from Finance, and then map those types to statement transaction codes that are used by your bank. Transaction types for Finance are defined on the Bank transaction type page. This page is also where you can define the main account to be used for postings that are associated with each transaction type.

When your Finance bank transaction codes are defined, you can then map those to the transaction codes that are used in your electronic bank statements. This mapping process is done by using the Transaction code mapping page. Transaction code mapping is completed for each bank account.

Trial balance report with transactional detail

The Trial balance report with transactional detail report in General ledger > Inquiries and reports adds transactional detail for ledger accounts to the summarized amounts on the Trial balance. All accounts should have equal debit and credit balances. You can use this report to identify errors for account balances that do not equal.

The report data can be filtered by:

  • Posted transactions
  • Pending transactions (These transactions include all transactions that aren't posted.)
  • All transactions

Screenshot of the Trial balance with transactional detail page with report criteria.

Note

Electronic reporting must be set up for this report to run. If it isn't, the Format mapping field will be blank.

The Trial balance report with transactional detail report also includes information from General journal entries, opening balances, debits, credits, and the resulting balances for a given date range.

For transactions, the report includes the transaction date, voucher number, transaction description, debits or credits, and a running balance for the year to date based on the current fiscal year. Also, if the transaction has financial dimensions, the report will display them. Otherwise they will be grouped at the top of the page. The report output is a Microsoft Excel document. You may need to disable pop-up blocking for the report to be displayed.

Screenshot of an example of a Trial balance with transactional detail report.

For more information about the information displayed on the reports, see Trial balance with transactional detail report.

Validate the bank statement

Bank statements must be validated before they can be reconciled. To validate a statement, on the Bank statements page, select Validate. This step is automatically completed if you set the Reconcile after import option to Yes at the time of import.

Bank statement validation verifies the following details:

  • The bank statement matches the selected bank account.
  • The bank statement currency matches the bank account currency.
  • The opening balance of the statement equals the closing balance of the previous statement for the bank account.
  • No gaps exist in the statement dates for the bank account.
  • Dates on the statement lines are between the from date and to date of the bank statement.
  • The opening balance and summarized line amounts equal the ending balance.

The Bank reconciliation statements can overlap dates. You can set the end date of one statement to overlap the start of the following bank statement. For example, suppose you set the range for statement #1 for midnight July 1st to midnight July 2nd. In that case, you can select the range for statement #2 from midnight to July 2nd to midnight July 3rd.

When the validation is complete, the status of the bank statement is updated to Validated.

Reconcile the bank statement

After you've imported an electronic bank statement and validated the statement on the Bank statements page, you can reconcile the bank statement by using the Bank reconciliation and Bank reconciliation worksheet pages.

On the Bank reconciliation page, select New to create a new reconciliation, and then select the bank account of the imported statement. A bank account can have only one open bank reconciliation. The cut-off date determines the bank statement transactions and Operations bank transactions that are included on the reconciliation worksheet. By default, the current system date is used as the cut-off date, but you can change the date for the reconciliation. The remaining header information is automatically taken from the statement. This step is automatically completed if you set the Reconcile after import option to Yes at the time of import.

On the Bank reconciliation page, select Worksheet to open the Bank reconciliation worksheet page. If you set the Reconcile after import option to Yes, the default matching rule set is automatically run for the reconciliation. To manually run matching rules, select Run matching rules to select the matching rule sets or matching rules to run against the bank transactions. If you have many transactions to process, you can complete this step as a batch process.

The Bank reconciliation worksheet page has four grids that contain transactions. The two upper grids show transactions from the bank statement and Operations that haven't yet been matched. The two lower grids show matched transactions. The Bank statement transaction details tab shows details for the unmatched bank statement transaction that is selected in the upper grid.

The three ways to match or reconcile bank statement transactions are:

  • Match the transactions with an Operations bank transaction.
  • Match the transactions with a reversal bank statement transaction.
  • Select the transactions as New so that they can be posted later as a bank transaction in Finance.

To manually match transactions, select the transactions in the Bank statement transactions grid, select the corresponding transactions in the Operations bank transactions grid, and then select Match. The selected transactions are moved from the upper grids for unmatched transactions to the lower grids for matched transactions.

To unmatch the matched transaction select either Unmatch to unmatch a single transaction, or select Unmatch all to unmatch all matched transactions at one time. All associated transactions are moved back to the upper grids for unmatched transactions, and the matched and unmatched total amounts are updated.

You can have one-to-one, many-to-one, and many-to-many transaction matches. Matches must follow the rules for allowed date differences and transaction type mapping. These rules are set on the Cash and bank management parameters page.

Screenshot of the Bank reconciliation worksheet page.

Penny differences might occur in your reconciliation. You can match a single bank statement transaction and a single Operations bank transaction that have penny differences if the penny differences are within the tolerance amount that is defined by the Allowed penny difference field on the bank account. The amount is shown in the Correction amount field on the matched Operations bank transaction. When the bank reconciliation is shown as reconciled, corrections are automatically posted by using the main account that is defined on the associated bank transaction type. Corrections to a bank transaction will create two bank transaction lines on the Bank to ledger reconciliation report. One line is the original transaction, and the other is the correction. The two lines from the bank transaction will match the two lines from the general ledger transaction.

Corrections aren't supported for the Check and Deposit document types.

Bank statement transaction reversals are matched by using the reconciliation worksheet. Two statement lines can be matched if the amounts are opposite and if one of the transactions is selected as a reversal. You can also set up a matching rule for the Clear reversal statement lines action.

Reversed Operations bank transactions must be reconciled by using the Operations bank transactions page. You can reconcile two Operations bank transactions together if the documents have the same bank account, document type, and payment reference, and if they have opposite amounts. You can also reconcile a single canceled check to prevent those transactions from appearing on the reconciliation worksheet.

Suppose that new bank-initiated transactions (such as interest, fees, and charges) occur, and they aren't yet in Finance. In that case, you can add them to a journal that is associated with the selected bank statement reconciliation. Select a bank statement transaction in the Bank statement transactions grid for unmatched transactions, and then select Mark as new. The transaction status is set to New, and the transaction is moved to the Bank statement transactions grid for matched transactions. You will post transactions that are selected as New later from the Bank statement page.

When bank statements are posted without new transactions you can reverse them by selecting the Reverse Statement option at the top of the Bank statement page, then you can view the Posted status field to ensure the posting has been reversed.

Screenshot of the Bank statement page, showing the Posted field.

After you have completed your reconciliation process, select the Bank reconciliation worksheet as reconciled. This process will automatically post correction amounts by using the accounts that are set on the Bank transaction type page. The bank reconciliation for a statement can be selected as reconciled at any time, even if some bank statement lines have not yet been matched. The unmatched transactions will automatically move to the next reconciliation worksheet as unmatched bank statement transactions to be reconciled. When a bank statement reconciliation has been selected as reconciled, it cannot be undone; the reconciliation will no longer be editable.

In addition to reversing a statement, you can also reverse a reconciliation. To reverse a bank statement that has already been reconciled, select the bank account, then select Reverse reconciliation.