Background
The bank reconciliation feature is used to check the bank ledger entries and the balance on the accounts against the statement from the bank.
You perform bank reconciliation to make sure that the balance in NAV (your books) and the actual balance as per Bank Statement are always matched.
Bank reconciliation is also a practical way to discover and resolve missing payments and bookkeeping errors.
Possible Discrepancies
Note:
For a Customer using NAV, Company's Book = Dynamics NAV (COA or General Ledger area).
Setup at a Glance
Data Exchange Definition.
Bank Export/Import Setup.
Fields to be updated on Bank Account Card.
Setup in Detail
1) Data Exchange Definition
Step 1: Go to the Data Exchange Definition page following the below path:
Departments/Administration/Application Setup/General/ Data Exchange Definition page
Here, we need to specify a data exchange setup that enables us to import a specific bank statement file.
Step 2: Click on 'New'.
Step 3: The ‘Data Exchange Definition' page will open. Enter the following details In the General Tab.
Code= TEST BANK IMPORT (Any relevant Code of your choice)
Name= TEST BANK IMPORT (Any relevant Name of your choice)
File Type= Variable Text
Type= Bank Statement Import
Reading/Writing Xml Port= 1220
Ext Data Handling Code unit= 1240
File Encoding = WINDOWS
Column Separator = Comma
And the following details in the Posting Line Definitions Tab:
Line Type: Detail.
Code = Test (Any relevant Code of your choice)
Name = TEST BANK IMPORT (Any relevant Name of your choice)
Column Count= 7 (The Number of columns in the Bank Statment to be imported. For my e.g., it's 7.)
Add the following details in the Posting Column Definitions Tab for all columns from the .CSV bank statement file, which we need to map in Dynamics NAV.
Enter Name of Column, Data Type of Column, Data Format, Data Formatting Culture.
Step 4: Click on the Field Mapping button in the Line Definition FatsTab. Now the Field Mapping window will open. Click on NEW.
Enter the following details to map the Posting Exchange with the Bank Reconciliation Line Table, through which we will import the Bank Statement.
General FastTab:
Table= 274
Name = Test CSV for Bank Recon (Any relevant Name of your choice)
Mapping Code unit = 1248
Field Mapping FastTab:
In Column No. click on the drop-down, then select the first line. It will point to the Column Definition from the Data Exchange Definition page.
Then, go to the Field ID column, click the dropdown. Field lookup will open. Select the field, then click on 'OK'.
Follow the same Procedure for all Lines.
Now the Field Mapping FastTabwill look something like this:
2) Bank Import/Export Setup
Step 6: Now go to Bank Export-Import Setup.
Path: Departments/Administration/Application Setup/General/Task/Bank Export-Import Setup. Click on New.
Enter the following details:
Code = TEST BANK IMPORT
Name = Test for Bank Recon
Direction = Import
Processing Code Unit Id= 1270
Processing Code Unit Name= Exp. Launcher Gen. Jnl.
In the Exchange Definition Code, Click the drop-down & Select = TEST BANK IMPORT
Now, you can exit this window.
3) Bank Accounts
Step 7: Go to the Bank Account Card.
Path: - Departments/Financial management/Cash Management/Bank Accounts
Select the Bank and click on Edit. For my example, I have selected "WWB-EUR" bank account.
Transaction
4) Create Bank Reconciliation
Step 1: On the Navigation pane, click Departments and then click Financial Management.
Step 7: Click Cash Management and then click Bank Account Reconciliations.
Step 3: In the Home FastTab, click New.
Header
Step 4: In the Bank Account No. field, select the bank account code.
Step 5: The Statement No. and the Balance Last Statement fields are filled in automatically based on the information on the bank account card.
Step 6: In the Statement Date field, enter the date of the statement.
Step 7: In the Statement Ending Balance field, enter the balance from the bank’s statement.
If you have a file that has the current bank statement, proceed to fill the lines automatically.
Lines
Step 7: On the Lines, Fast Tab, users perform the actual reconciliation by entering and adjusting the posted transactions and the transactions on the bank statement until they agree with one another.
The lines include the following fields:
Transaction Date: Specifies the posting date of the bank account or check ledger entry on the reconciliation line when the Suggest Lines function is used.
Type: This shows the type of ledger entry or a difference to be reconciled on this line.
The options are as follows:
Bank Account Ledger Entry: Used to reconcile an entry from a bank account ledger.
Check Ledger Entry: Used to reconcile an entry from a check ledger.
Difference: Used to record an amount in the bank reconciliation that is not posted in Microsoft Dynamics NAV to balance the reconciliation.
Description: This shows a description of the transaction on the reconciliation line.
Statement Amount: This shows the transaction amount that appears on the bank's statement that shows on the reconciliation line.
Applied Amount: This shows the transaction amount on the reconciliation line that is applied to the bank account or the check ledger entry.
Difference: This shows the difference between the amount in the Statement Amount field and the Applied Amount field for the reconciliation line.
Value Date: You can use this field to enter the official date when funds are available and when interest is calculated on the amount on the reconciliation line.
Document No.: Shows the document number that is specified on the original bank account ledger entry.
Cheque No.: Shows the Cheque number (specified as the document number) used on the original check ledger entry.
Applied Entries: Specifies whether the transaction on the bank's statement on this reconciliation line is applied to one or more bank account or check ledger entries.
Import Bank Statement
Step 8: On the Actions tab, in the Functions group, choose Import Bank Statement.
Step 9: Locate the file, and then choose Open to import the bank transactions into the lines of the Bank Acc. Reconciliation window.
The format of the file is specified in the Bank Statement Import Format field in the Bank Account Card window.
The below format is used for Importing the Bank Statement in our example. Click here to download the sample file.
Match Bank Statement Lines with Bank Account Ledger Entries
To automatically match bank statement lines with bank account ledger entries:
Step 10: On the Home tab, in the Matching group, choose Match Automatically. The Match Bank Entries window opens.
Step 11: In the Transaction Date Tolerance (Days) field, specify the span of days before and after the bank account ledger entry posting date within which the function will search for matching transaction dates in the bank statement.
Step 12: Choose the OK button to start the Match Automatically function.
All bank statement lines and bank account ledger entries that can be matched change to green font, and the applied check box in the right pane is selected.
To remove a match, select the bank statement line, and then, on the Home tab, in the Matching group, choose to Remove Match.
Just for Information:
To manually match bank statement entries with bank account ledger entries:
In the left pane, select a bank statement line.
In the right pane, select one or more banks account ledger entries that can be matched with the selected bank statement line. To choose multiple lines, press and hold the Ctrl key.
On the Home tab, in the Matching group, choose Match Manually. The selected bank statement line and the selected bank account ledger entries change to green font, and the applied check box in the right pane is selected.
Repeat steps 3 through 5 for all bank statement lines that are not matched.
To remove a match, select the bank statement line, and then, on the Home tab, in the Matching group, choose Remove Match.
5) Post an open transaction that is reflected in the Bank Statement
Step 1: Let’s add an additional entry on the left pane: Bank Statement Lines.
Step 2: In the bank account reconciliation, select the line with the entry to be posted.
Step 3: On the Actions tab, in the Functions group, choose Transfer to General Journal. In the Gen. Journal Template field, select the journal template. In the Gen. Journal Batch field, select the journal batch.
Step 3: Open the General Journal. The entry appears on the top line. Notice that the Bank account number has been inserted. Enter the General Ledger account as the balancing account, and then post the journal.
Step 4: Again, click on Match Automatically to match all the lines.
6) Post Bank Account Reconciliation
Step 1: Open the relevant bank account reconciliation.
Step 2: Update the Statement Ending Balance.
Step 3: On the reconciliation lines, verify that all the fields in the Difference column should be 0.00, and the Total Difference field at the bottom should be 0.00.
Step 4: On the Actions tab, in the Posting group, choose Post.
After the bank reconciliation, has been posted, a bank account statement is created. To view the bank account statement, open the relevant bank account card, and from the Navigate tab, in the Bank Acc. group, choose Statements.
Step 4: Click on Edit to view the Bank Statement Entries.
