The New Way to Do Bank Reconciliation in Dynamics 365 Business Central

The new bank reconciliation behavior in Dynamics 365 Business Central, released in 2023, changed how accounting teams reconcile their bank account statements.

However, there can be some challenges when dealing with uncleared transactions, certain error messages, or handling the details of bank integration. I’ve helped several companies resolve issues like these.

In this article, I’ll walk you through the basics of how to do a bank reconciliation in the new way and then show you how to address some of the most common challenges.

Table of Contents

How to Do Bank Reconciliation in Dynamics 365 Business Central

Step 1. Using global search, type in and select Bank Account Reconciliations. This window will list any unposted Bank Account Reconciliations. Next, select “New”.

Screenshot inside "Bank Account Reconsiliations".

Step 2. Select the relevant Bank Account and Statement Date and enter the Statement Ending Balance.

Screenshot in Bank Acc. Reconciliation windodw showing both the Bank Statement Lines and the Bank Account Ledger Entries listed.

Step 3. The window’s Bank Account Ledger Entries side will list uncleared bank transactions in Business Central.

In our example, italicized entries listed have the Applied column selected. This indicates that an unposted bank account reconciliation is in progress.

Step 4. To populate the Bank Statement Lines, they can be entered manually, line by line. Alternatively, there are a couple of other options to ease the process.

Firstly, Import Bank Statement is an option described in more detail later in this article.

A second option to populate the Bank Statement Lines is to select Suggest Lines. This will create a line for each unapplied entry in the current bank reconciliation. These lines will then automatically match to the unapplied Bank Account Ledger Entries.

Screenshot in Bank Acc. Reconciliation window showing the Suggest Lines option.

What happens if there are Bank Statement Lines that I do not want to clear right away?

In this situation, you can select the relevant Bank Statement Line, click on the three dots, and choose Delete.

Screenshot under Bank Statement Lines showing the options when you click on the three dots being "New Line" "Delete Line" and "Select More".

Note: These Bank Statement Lines do not affect the G/L in Business Central when manually created, imported, suggested, or deleted.

Step 5. Next, the Bank Statement Lines need to be matched to the Bank Account Ledger Entries. The options to accomplish this are to Match Automatically or Match Manually.

When using the Match Manually option, select the relevant line(s) on the Bank Statement Lines and Bank Account Ledger Entries sides. Select Match Manually under the Matching tab.

Note: Matching can be done 1:1 or 1:Many, but not Many:Many.

The Match Automatically function uses the text on the bank statement to match with the Bank Account Ledger Entries. For example, the description on the Bank Statement Lines could be the cheque number, which would then match with the Document No. on the Bank Ledger Entries side. You can select the Match Details action to verify how the matches are made.   

You can use a mix of matching manually and matching automatically.

Note that, in some cases, using the Match Automatically function may not be the best option. For example, if your bank statement import does not include the document number as the description, the automatic matching may not be accurate, and you will need to unmatch it and proceed with the manual matching process.   

Step 6. Once matching is complete, select the Home tab, Post, and Test Report. Select Preview. This report will indicate any warnings before posting.

Step 7. Post the bank account reconciliation.

How to Undo a Bank Reconciliation

Unlike the previous version of bank reconciliation in Dynamics Business Central, this new version allows a user to undo a bank reconciliation. To do this, open the related Bank Account, select the Related tab, and choose Statements. Highlight the relevant statement ID and select Undo. This will cancel and copy the statement to a new, unposted Bank Account Reconciliation.

Bank Integration with Dynamics 365 Business Central

A bank integration with Business Central will let you import statements from the bank by connecting directly with the bank to pull in the statement or uploading a CSV file.

In terms of connecting to the bank when importing the bank statement, the steps are:

Step 1. Select Import Bank Statement within the Bank Account Reconciliation.

Screenshot in Bank Acc. Reconciliation showing "Import Bank Statement" option.

Step 2. If this is not already set up in Business Central, you may need to create a Bank Statement Import Format. Business Central has a wizard tool that allows for easier setup of the bank statement format.

Screenshot showing the following message The bank account must have a value in Bank Statement Import Format. Do you want to open the bank account card?"

Step 3. Open the bank account card and select Set up Bank Statement File Import Format.

Screenshot of a bank account card showing the following statement: Set up Bank Statement File Import Format.

Step 4. Click Next, select Upload a bank statement file, and upload a CSV bank file.

Screenshot of the Bank Statement File Setup window.

Step 5. Follow through the steps to map the fields and format desired for import.

Screenshot of the Bank Statement File Setup Window with a file preview. Screenshot inside th Bank Statement File Setup window showing the Date, Amount, and Description Column No. Fields.

Step 6. Name the format code, assign it to the relevant bank account, and select Finish.

Screenshot showing the completed Bank Statement File Setup.

Step 7. Once the Bank Statement File Setup is complete, you can use the Import Bank Statement option in the Bank Account Reconciliation process.

Screenshot of the Bnak Statement File Setup Window stating "Choose Next to upload your bank statement file. If you upload a file, we'll detect it's format and apply it for you. You can also skip this step and define the format for yourself."

For specific help to address your situation with Bank Statement File Setup, please get in touch with an experienced Dynamics 365 Business Central partner.

Troubleshooting

Date Format Error

A common error with bank statement file setup is related to the date format. You may receive the following error message:

  • “The file that you are trying to import is different from the specified Bank Statement Import. The value in line 1, column 1 has an incorrect format or type. Expected format: Date, according to the Date Format and Data Formatting Culture of the Data Exch. Column Def.”

To resolve, recreate the bank file format and update the date format. For example, mm-dd-yyyy. 

Dealing with the “Direct Posting” Warning

When printing the test report prior to posting a Bank Account Reconciliation, you may see the following warning: “Bank reconciliation might not be possible because there are direct posting entries.”

This means that the system has noted that some transactions have been directly posted to the bank account G/L. This is just a warning and may not actually affect your bank reconciliation. However, this could cause a situation where transactions are listed on the bank statement lines section but not in the bank account ledger entries section.

In this case, you may need to follow the process in this article related to Fixing Uncleared entries.

Then, you can get back to your normal processes. However, you may want to speak to your system administrator or Dynamics Partner if you run into additional issues.

Fixing Uncleared Transactions

What happens if there are Bank Statement Lines but no related Bank Account Ledger Entries to match with?

A common occurrence relates to situations where direct posting is taking place, but there is no related Bank Ledger Entry where it is expected to be.

The solution is to create a General Journal entry to clear the Bank Statement Lines.

Step 1. In the General Journal, select the account type as Bank Account, leaving the document type field blank. Enter the relevant amount that needs to be cleared.

Screenshot inside General Journals showing the Account Type selected as Bank Account and the Document Type field as blank.

Step 2. In the Bal. Account Type field, select G/L Account and choose the account number that relates to the bank that we are performing the bank reconciliation for.

Screenshot inside of General Journals showing theBal. Account Type field.

When reviewing the entries before posting, you will notice two G/L entries, each offsetting the other in the bank G/L account. You will also see a Bank Ledger Entry, which will appear in the bank reconciliation.

After posting your General Journal and navigating back to Bank Account Reconciliation, notice the related clearing entry on the bank ledger entries side, which can then be matched to the relevant Bank Statement lines.

For specific help to address your particular situation with uncleared transactions, please reach out to an experienced Dynamics 365 Business Central partner.

Resolving the “Statement Ending Balance is not equal to Total Balance” Error

You may also encounter this error message: “Statement Ending Balance is not equal to Total Balance.”  In this case, you’ll need to ensure that these two fields match and that the Total Difference field is $0 before posting the bank account reconciliation.

Screenshot of a Bank Account Statament.

In this case, there is either a missing Bank Statement Lines entry or the Balance Last Statement or Statement Ending Balance fields are incorrect. Follow these steps:

Step 1. Confirm Balance Last Statement or Statement Ending Balance fields are correct.

Step 2. Confirm if there is a missing entry on the Bank Statement Lines

In the example provided, the resolution shown is that the Balance Last Statement amount was incorrect. Once updated, the error is resolved.

Screenshot inside Bank Acc. Reconciliation showing the Bank Account Ledger Entries and Bank Statement Lines sections.

In another example below, there is an unapplied entry on the Bank Statement Lines, resulting in an amount showing in the Total Difference column. This column must be $0 to allow the bank account reconciliation to be posted. To resolve this, see the Fixing Uncleared Entries section in this article.

Screenshot inside Bank Acc. Reconciliation showing the Bank Statement Lines and the Bank Account Ledger Entries sections.

For support and advice tailored to your specific situation, please feel free to contact us. We’d love to help you.

Excel Layouts for Reporting in Dynamics 365 Business Central

In this webinar, learn how to create, manage multiple layouts, and use custom Excel layouts for reporting in Dynamics 365 Business Central.

October 8
9:00 am – 9:30 am PST

Register here

Webinar - Excel Layouts for Reporting

Oct. 8
9:00 am – 9:30 am PST

Register