I like it when thing lines up as they’re supposed to.  Maybe it’s a slight degree of obsessive-compulsive disorder on my part, but my personal well-being is dependent upon keeping certain aspects of my life in a state of tranquility.

When it comes to my operations, and especially my finances, I don’t want to come across any unexpected surprises.  At the end of the month, I need to know that my bank statements all sync up with my bank transactions.  I don’t think that’s too much to ask???  In Microsoft Dynamics NAV, performing a bank reconciliation is quick and easy to do and helps ensure that all is in order, and here’s how:

First you need to set up your worksheet.

The bank rec functionality is accessed through Cash Management > Bank Account Reconciliations.  Select an existing worksheet from the list or click New to start a new one.

  1. Choose the Bank Account number.  The Statement Number should fill in automatically from the bank account number.
  2. Enter the Statement Date. This is the date up to which the bank is being reconciled.
  3. The Cleared With/Chks Per Stmnt and Cleared Inc/Dpsts Per Stmnt fields are for your information only and can be entered from the Total Debits and Total Credits typically on the last page of a printed bank statement.  These fields are optional and are not used by the program.
  4. The Balance on Statement field is mandatory and is the amount to which the reconciliation must balance.

An example of a Bank Rec Worksheet with these steps completed is shown below:

[lightbox_image size=”full-banner” image_path=”/wp-content/uploads/Worksheet11.png” lightbox_content=”/wp-content/uploads/Worksheet11.png” group=”Bank” description=””]

The next step is to bring in all entries that have been posted in NAV up to and including the Statement Date entered above.

To retrieve bank transactions, from the Bank Rec Worksheet click on the Actions button > Functions > Suggest Lines.  On the Options tab, select Both in the Record type to process field.  Only select the Replace existing lines option if starting the bank rec over.

[lightbox_image size=”full-banner” image_path=”/wp-content/uploads/Worksheet21.png” lightbox_content=”/wp-content/uploads/Worksheet21.png” group=”Bank” description=””]

Click OK.

To review the open cheques for this bank account, click on the Cheques FastTab.  To review non-cheque transactions such as deposits, transfers, journal entries, etc., click on the Deposits/Transfers FastTab.

The bank rec is now set up.  You can start reconciling. Yay!!

Processing the Bank Reconciliation

There are two ways to clear the outstanding transactions: manually or program-assisted.

Manually clearing transactions involves:

  1. Matching the NAV transaction to the bank statement transactions and putting a checkmark in the Cleared field.
  2. Transactions that are not marked cleared when the bank rec is posted, are considered outstanding and will appear on the next bank rec worksheet.

[lightbox_image size=”full-banner” image_path=”/wp-content/uploads/Processing11.png” lightbox_content=”/wp-content/uploads/Processing11.png” group=”Bank” description=””]

The program-assisted clearing of transactions involves using NAV functionality to clear the transactions en masse.  You can either set filters to select particular transactions or select all transactions.  If all transactions are marked as cleared, then you manually uncheck the outstanding transactions.  This method is quicker when there are relatively few outstanding transactions.

  1. Click on the Actions button > Functions > Mark Lines to pull up the request form for this functionality.  This opens the Bank Rec Process Lines request form.  The Options FastTab allows you to select the Record type to process.
  2. Put a checkmark in the Mark lines as cleared field.  Filters are set on the Bank Rec Line FastTab.  Click OK to mark the transactions as cleared.  This marks all cheques and deposits as cleared.
  3. The next step is to manually go and uncheck the transactions that did not actually clear the bank.

When all the cleared transactions are marked, return to the General FastTab of the bank rec worksheet to review the amounts.  The General FastTab maintains running totals of the reconciliation.  In my example, I have a Difference of $165.22.

Upon review of the bank statement, I see that there is $165.22 of unrecorded bank charges.  NAV’s bank rec functionality allows you to record this type of transaction from directly within the bank rec worksheet.

Recording Adjustments

To record miscellaneous bank transactions and adjustments, go to the Adjustments FastTab.  This FastTab lets you enter in adjustments for transactions that appear on the bank statement but have not been recorded in NAV.  You can record many different types of transactions but the most common use is for recording bank charges.

Note: If after starting your bank rec, you do enter and post additional bank transactions (outside of your bank rec), you will need to Suggest Lines again to bring in the new transactions.  Do not choose the option Replace Existing Lines unless you intend to start your bank rec process all over again.

Posting Date – This transaction date should be on or before the bank rec Statement Date.
Account Type – Select Bank Account.
Account No. – This must be the bank account number for the bank being reconciled.
Amount – Enter the amount of the adjustment.  A negative amount indicates an amount coming out of the bank and a positive amount indicates an amount going into the bank.
Bal. Account Type – Select the balancing account type here.  It is usually a G/L Account.
Bal. Account No. – The Account where the adjustment is being posted.

[lightbox_image size=”full-banner” image_path=”/wp-content/uploads/Processing21.png” lightbox_content=”/wp-content/uploads/Processing21.png” group=”Bank” description=””]

These adjustments can also be posted with dimension information, by showing the dimension columns and entering this information.

Return to the General FastTab to review the reconciliation totals.  When the bank has been reconciled successfully the Difference will display zero.  The bank rec can now be posted.  Awesome!

 Posting the Bank Reconciliation

Click on the Actions button > Posting > Test Report to review the Bank Rec Test Report.  The report details are set on the Options FastTab; these options are retained when using Post and Print in the future.  The most commonly used options are shown below.

[lightbox_image size=”full-banner” image_path=”/wp-content/uploads/Processing31.png” lightbox_content=”/wp-content/uploads/Processing31.png” group=”Bank” description=””]

You can either Post or Post and Print the completed bank rec by clicking on these action buttons.
A progress window is displayed while the system works.  The posting process clears the marked transactions and posts the adjustments.  It also closes the open bank rec worksheet and posts a bank statement.

And with that, there is balance in the universe.