In this post, I will share how to prevent one of the most common Bank Reconciliation problems I have had to address. I am regularly called in to assist customers by rescuing them from reconciliation issues in their Microsoft Dynamics 365 Business Central company and this is what I have found.
In some cases, I am brought in after more than a year of live transactions and the same amount of time battling with the bank reconciliation. A bank reconciliation isn’t a simple task, but your system should not be making it more difficult. System setup, configuration and proper transaction entry are all paramount to successful reconciliations. If these are not done correctly from the start, you may just be in for reconciliation hell.
Prevention is worth a pound of cure
A common and very simple cause of bank rec issues is the bank ledger and the cash G/L account not balancing in the legacy system. Even if you transfer everything from your legacy system using the proper go live processes in BC, what doesn’t balance in your old system will not balance when migrated.
Make certain what you are bringing into BC balances in your legacy system first. Surprisingly this is more common than you think, especially with companies coming over from QuickBooks. One way to do this is to follow the approach below when bringing over your beginning balance entries. Though not impossible I guess, it does make it more difficult to have an out-of-balance situation when you follow this approach.
What is Direct Posting and when it is “bad”
Let’s start with setup and configuration and the difference one simple setting can have on an entire process.
To understand the setting, we must first understand what happens and doesn’t happen when we direct post to a G/L account. Direct posting is selecting a G/L account directly on a transaction or a journal line rather than selecting the entity associated with the subledger. For example, when posting a customer payment, we should not be selecting the accounts receivable G/L Account in the cash receipt journal, we need to select the customer. Don’t select the cash G/L account, select a bank account. By doing this, we get a customer ledger entry, a bank ledger entry and G/L entries. By selecting a G/L account, the result is only G/L Entries, nothing posts to a subledger.
To ensure posting to sub-ledgers, don’t select G/L accounts. To prohibit the selection of specific accounts and posting only to the G/L account without any subledger entries, disable “Direct Posting” on the Account Card. A best practice recommendation is any account assigned to a posting group or posting setup in Business Central will have Direct Posting disabled. Not having the cash accounts set properly can be the root cause of bank reconciliation issues early in your BC journey or at any time in the future.
Now that we have that straight, let’s continue with preventing the most common cause for what I see when resolving bank reconciliation issues found early in a BC implementation – how the beginning balances are entered in BC.
Month End Net Change Entries
When entering net change journals that include cash accounts during your go live process, select Bank Account instead of G/L account in those journal entries. By using the Bank Account instead of G/L account, we are:
- Preserving “no direct posting” to G/L accounts assigned to bank accounts,
- Getting a bank ledger entry you will use for reconciliation
- And G/L balance that matches the bank account balance.
Every Month Prior
For every month’s entry except for the last month, replace the cash G/L account with Bank Account and enter the net change amounts. You will have one line in these journal entries representing the change in cash for the entire month.
“Final” Bank Balance Entry
For the last month’s net change entry (or when entering a single beginning balance entry prior to going live,) you will enter multiple lines. These lines will represent all the individual outstanding transaction amounts included in your legacy bank balance. One entry for every outstanding check and one for every outstanding deposit, and one that is the balance for the bank from the legacy system plus outstanding checks, less outstanding deposits. Each entry should include the check or transaction no. in External Document No. field of the entry. I like to then use “Net Change less outstanding” for the External Document No. of the last entry or Net Change +/- the outstanding. You can also use Document Date for the original transaction date.
Simple Illustration
Month’s net change in cash GL Account (legacy system): $100,000
Outstanding transactions:
Check 1 10,000
Check 2 5,000
Check 3 100
Deposit 4,000
Total Outstanding $11,100
Entries to Bank Account in Net Change Journal
| Date | Account Type | Document No | Ext. Document No | Debit | Credit |
| 10/31/24 | Bank Account | 100 | CK12565 | 10,000 | |
| 10/31/24 | Bank Account | 100 | CK12955 | 5,000 | |
| 10/31/24 | Bank Account | 100 | CK12956 | 100 | |
| 10/31/24 | Bank Account | 100 | 241029 | 4,000 | |
| 10/31/24 | Bank Account | 100 | October Net less outstanding | 111,100 |
*This table may appear unbalanced but only represents the cash related records in a larger month-end balanced entry.
This works to preserve the no direct posting rule to the G/L account and post beginning and open transactions into both the bank and G/L ledgers. It is important that you date all the transactions with the same Posting Date (last of the month.) Use Document Date to represent the original transaction date for each transaction entry to aid in reconciliation later.
To complete the circle, an initial beginning balance bank reconciliation is needed. This bank rec will clear all of the month-end “cash” related entries. I like to use the day before going live as the posting date for this reconciliation. The outstanding check and deposit entries remaining will clear as needed in future bank reconciliations.
The right entries, the right results
When beginning balance entries are entered properly using a bank account, not GL account, and the bank and GL accounts are confirmed to balance prior (legacy) to and after entry (BC), you should not encounter the number 1 and 2 causes for bank reconciliation hell.
I hope this post helps get you to The Righter WayTM of using Business Central.
Additional information can be found for bank reconciliation in Business Central at https://learn.microsoft.com/dynamics365/business-central/bank-manage-bank-accounts?WT.mc_id=MVP_348604
