Microsoft Dynamics NAV 2009R2: How to Create a Bank Reconciliation
A bank reconciliation is used to reconcile the bank balance recorded in Microsoft Dynamics NAV with the balance on the bank statement. This process includes clearing ledger entries that match bank statement transactions, monitoring differences due to outstanding transactions, and recording adjustments when company and bank records differ. For example, if the balance on a bank account does not match the bank statement, there may be outstanding checks, deposits, or transactions reported on the bank statement that have not been posted to the bank account ledger. You can also use bank reconciliation to identify and reconcile bank account conflicts due to errors in company or bank records.
Creating a Bank Reconciliation:
- Go to Departments>Financial Management>Cash Management>List>Bank Account Reconciliations
- Select “New” in tool bar
- General Tab – This FastTab displays general bank reconciliation information. This information includes bank account numbers, general ledger (G/L) balance amounts, and checks and deposits for each statement.
- Select or type in the Bank Account No. code to specify the bank account the reconciliation applies to.
- Statement No. – Create a unique Number (Example: Year Month – 2014 Jan)
- Statement Date – This field is entered to indicate the as of date of the statement. All G/L balances will be calculated based upon this date.
- G/L Balance ($) – This field is automatically populated by the system and contains the G/L balance local currency (USD) for the assigned account number, based on the G/L Bank Account No. field for the Bank Account assigned to the statement, as of the Statement Date.
- G/L Balance – This field contains the related bank transaction information stored in the system.
- + Positive Adjustments – This field contains the total of the positive adjustments from the Adjustments tab
- Subtotal – This field contains the sum of the G/L Balance and the Positive Adjustments.
- – Negative Adjustments – This field contains the total of the negative adjustments from the Adjustments tab.
- Difference – This is the Ending G/L Balance minus the Ending Balance. The Bank Rec. Worksheet cannot be posted until this amount is zero.
- Cleared With/Cheks. Per Stmnt – This field includes the total of the cleared withdrawals/checks per the statement being reconciled.
- Cleared Inc./Dpsts.Per Stmnt – This field includes the total of the cleared increases/deposits per the statement being reconciled.
- Balance on Statement – This amount is entered by the operator from the balance found on the bank statement.
- + Outstanding Deposits – This field contains the total of the increases and deposits from the Increases/Deposits tab.
- Subtotal – This field contains the sum of the Balance on Statement field, plus the Outstanding Deposits.
- – Outstanding Checks – This field contains the total of the outstanding withdrawals and checks from the Withdrawals/Checks tab.
- Ending Balance – This field is a calculated sum of the Balance on Statement, plus the Outstanding Deposits, minus the Outstanding Checks.
Figure 1 – New Bank Reconciliation Worksheet.
- Bank Rec. Process Lines - Action >Function>Suggest Lines - Bank Rec. Process Lines Batch Job - Use this process to perform one of three possible functions, Suggest Lines, Mark Lines, or Record Adjustments, depending on the menu option chosen. You can define the scope of the process by choosing one of the three record types to process options: Checks, Deposits, or Both. You can also choose to replace any existing lines with new lines by checking the Replace Existing Lines box, or update the existing lines by leaving the box unchecked.
- Select the following filters - Options - Click on this option to only process check type records.
- Record Type to process
- Record Type to process
- Bank Rec. Line – Enter filters as needed.
- Checks Tab - This FastTab displays information about Check Transactions and Ledger Entries. You should see a list of check that the above suggest lines populated. The Cleared field indicates whether or not this line has been cleared, as indicated on the bank statement. Select all items that has cleared on the bank statement.
Figure 2 – Bank Reconciliation Check Lines Subform.
- Deposits/Transfers Tab - This FastTab displays information about deposit or transfer deposit lines. You should see a list of check that the above suggest lines populated. The Cleared field indicates whether or not this line has been cleared, as indicated on the bank statement. Select all items that has cleared on the bank statement.
Figure 3 – Bank Reconciliation Deposits Lines Subform.
- Adjustments Tab - This FastTab displays information from the Bank Rec. Check Lines Subform window for adjustment deposit lines. You can enter adjustment manually for Interest, Bank Charges etc. Complete the following fields:
- Posting Date – should be the same as the Bank Reconciliation.
- Document Type - Select the type of document that the entry on the journal line is. (Blank): ordinary G/L entry, Payment: payment from a customer or to a vendor, Invoice: invoice Credit Memo: credit memo. Finance Charge Memo: finance charge memo in connection with a purchase or sale, Reminder: reminder in connection with a purchase or sale or Refund: payment to a customer or from a vendor
- Document number – will default from number series.
- Account Type - Select the type of account that the entry on the journal line will be posted to (G/L accounts, bank accounts, customer, vendor or fixed asset accounts.)
- Account No. - Select the number of the account that the entry on the journal line will be posted to.
- Description – Enter Description to describe your entry.
- Amount – Enter the amount.
- Bal. Account Type – Enter or Select the balance account type of account that the entry on the journal line will be posted to (G/L accounts, bank accounts, customer, vendor or fixed asset accounts.).
- Bal. Account No. - Select the number of the account that the entry on the journal line will be posted to.
- Department Code – Enter Dimension if needed.
- Project Code – Enter Dimension if needed.
Note: One of the Account Type will need to reflect the Bank Account and Bank Account number of the bank you are reconciling.
Figure 4 – Bank Reconciliation Adjustment Lines Subform.
Bank Rec. Test Report - Select Action>Posting>Test Report. To review a preliminary draft of the bank reconciliation statement. This step in the bank reconciliation process allows you to test the bank reconciliation statement entries for accuracy prior to posting the bank reconciliation statement.
- Options – select items you would like to print.
- Print details
- Print checks
- Print deposits
- Print adjustments
- Print outstanding checks
- Print outstanding deposits
- Bank Rec. Header – system will default Banks Account No. and Statement No., enter any filters or Limit totals as needed.
Figure 5 – Bank Reconciliation Test Report.
Post – Select “Post” in the top tool bar to post Bank Reconciliation Statement when ready.
Note – The Difference field in the General Tab cannot be posted until this amount is zero
Figure 6 – Difference field in the General Tab.