How to Set Up Bank Account Beginning Balances in Dynamics NAV

Primary Goal:

Establish the Bank Account’s sub ledger detail in a manner which prepares for processing future bank reconciliations. To do this, you’ll need to account for the following items:

  • Ending Bank Balance
  • Outstanding checks
  • Outstanding deposits/other transactions

Success Criteria: At the end of the process we need the following:

  • The Bank Account’s Balance field value should match exactly with the Bank Account’s G/L account balance (as found on the Chart of Accounts and associated via the Bank Account’s Bank Acc. Posting Group field).
  • We should have appropriate Open Bank Account Ledger Entries to use on future Bank Reconciliations.

Bank Account – Beginning Balances:

Presumption: In my demo scenario, I presume the GL account associated to the Bank Account has already been set up with its proper GL account balance, via the process used to populate all other GL opening balances. Note in the screenshot below that the GL account, “New Bank Account,” has a balance of $14,400, but the Balance value on the associated Bank Account Card shows a zero. This is what we’re working to fix.

NAV bank account 1

HIGH LEVEL NOTES:

o   In order to properly establish the Bank Account’s sub-ledger, General Journal entries will be set up and posted in a way so they won’t change any GL Account Balances. To do this we will post transactions that will debt and credit the same GL account but will create Bank Account Ledger Entries which can be used for reconciliations.

  • In our example scenario, we’ll use the following detail for establishing the Bank Account’s sub-ledger detail as of 1/31/18:

NAV bank account 2

Setup:

o   On your bank account’s GL Account Cards, make Direct Posting = Yes.

  • IMPORTANT NOTE: At the end of this process, you’ll need to make this field value = No again or you might have issues processing bank reconciliations in the future.
  • In order for this method to work, your Bank Account’s “Bank Acc. Posting Group” needs to be set up properly, associating the Bank Account to its proper GL Account. Here’s what mine looks like in this example:

NAV bank account 3

General Journal Entries:

Create General Journal entries, like shown in the following screen shot, to establish your Bank Account sub ledger detail.

  • Note that the highlighted columns are the minimum values you’ll need to set up — all of them are needed.
  • You’ll want one line for each transaction you want to see as an open transaction in the Bank Account Reconciliation.
  • Especially note that the “Bal. Account No.” field value is set up with the same GL Account No. assigned to the Bank Acc. Posting Group used by the Bank Account, as shown in the screenshot above. By setting up the journal in this manner, NAV will debit and credit the same G/L account, causing a net zero effect to the GL Account’s balance, while still leaving open Bank Account Ledger Entries.
  • Of course, if you have many lines you need to import, you can use the RapidStart/Configuration Package tools or copy/paste values from Excel to speed up the process of setting up these lines.

NAV bank account 4

  • IMPORTANT NOTE: After these entries are posted, don’t forget to go back to you G/L Account and make the Direct Posting = No again. This will help ensure your Bank Account and G/L Account balances stay synchronized.

Results:

After posting the above entries, the Bank Account and the G/L Account balances now match, as shown below, and there are Open Bank Account Ledger Entries that we’ll use for processing our Bank Reconciliations.

NAV bank account 5

Bank Reconciliations:

NOTE: In the North American version of Dynamics NAV, there are two bank reconciliation options available: (1) the Bank Recon. with Auto Match and, (2) for lack of a better name, what I call the North American version. The General Ledger Setup page’s Bank Recon. with Auto Match field determines which version you’ll get. When this field is checked, you get the Bank Recon. with Auto Match version and when it’s not checked you get the North American version. My scenario below uses the North American version, which I prefer. If you use the Bank Recon. with Auto Match version, the idea is the same. You need to clear the right transaction.

In order to be prepared for your first real life bank reconciliation (for 2/28/18), we need to post a bank reconciliation for 1/31/18. For the first bank reconciliation, like shown below, we only need to clear the initial entry which represented the establishment of the Bank Balance as of 1/31/18 (and NOT any of the outstanding checks, deposits or other items). You’ll need to populate the following fields: Statement Date (1/31/18), Cleared Inc./Dpsts. Per Stmnt (12,500), and Balance on Statement (12,500).

In order to get the lines populated with transactions that can be marked as cleared, you’ll need to run the Suggest Lines function, as shown below. After the lines are visible, you can mark the lines that have been recorded by the bank as cleared.

NAV bank account 6

Note that the value in the Cleared Inc./Dpsts. Per Stmnt field needs to match the line amount in the Deposits/Transfers section that represents the initial bank balance. If filled out correctly, the Bank Reconciliation will post without any errors. If there are errors, fix the issues and post it. If you’ve meet the initial success criteria, your process worked and you’re done.

NAV bank account 7

Processing the First Real Bank Reconciliation:

Effectively, we’ve already finished what we wanted to accomplish in this blog, but to be thorough, I’ll take you through processing your first real bank reconciliation.

In the following scenario, we’re reconciling as of 2/28/18 and presuming the only activity for the month was that one check and that the deposit cleared the bank. The following outlines the bank account details used in my scenario:

NAV bank account 8

For the above scenarios, the 2/28/18 Bank Reconciliation would look like the following. Note that the header field values need to be populated with proper values in order for the bank reconciliation to post. I posted it and I’ve successfully posted my first real bank reconciliation.

NAV bank account 9

Success Criteria – SUCCESSFUL:

  • The Bank Account’s Balance field does match exactly with the Bank Account’s G/L account balance.
  • We have appropriate Open Bank Account Ledger Entries to use on future Bank Reconciliations.

NAV bank account 10