Auto Bank Reconciliation in Tally

Auto Bank Reconciliation in Tally

As the entry in Bank Book corresponds to entries in Bank Pass Book, they can be digitally matched if the Bank Pass Book is also available in digital Form.

In Auto Bank Reconciliation, Bank Pass Book data in digital form (e-Bank Pass Book) are imported into Tally ERP 9 to perform  matching through the common reference (Cheque Number etc)

Here we discuss about the tasks related to Auto Bank Reconciliation (i.e digital matching of records) between Bank Ledger Account as recorded in Tally ERP with imported Record of Bank Pass Book.

Auto Bank Reconciliation may be helpful in case of Companies having large number of Bank Transactions, having Net Banking Access. It helps reduce manual matching considerably. However, you have to manually match the entries left out in Auto Bank Reconciliation process.

You have to first download the digital copy of Bank Statement from Bank Site, through Net Banking Account.

Auto Bank Reconciliation Configuration in Tally

At Gateway of Tally, click F12:Configure>Banking Configuration to get Banking Configuration screen. Now set as follows:

  • Unreconciled Transaction : At Show all Unreconciled Transaction in BRS Till Date, set Yes to show the Opening Unreconciled Entries. You may also see it from Configuration Option of Bank Reconciliation.
  • Bank Date Removal on altered Voucher : At Remove Bank Date on Altering Reconciled Voucher (Bank Ledger / Amount), set Yes to remove the manual tags, on alteration of any voucher. A message ‘Altering Reconciled Voucher will remove the Bank Date as it may not match the Bank Balance on Reconciliation’ appears to make the user aware of the consequences of activating this option.
  • Location of Bank Statement files : Here enter the location where the downloaded Bank Statement  will be stored. By default, the location of Configuration File as specified in Data Configuration is shown, which you may change.

Type Yes at the prompt Continue Yes / No to save the configuration settings

Configuration Set Up for Banking in Tally

Auto Bank Reconciliation Activation in Tally

At Bank Ledger Account, set Yes at the field Set / Alter Banking Configuration, to get Banking Configuration screen. At Banking Configuration screen, set Yes at Activate Auto Reconciliation to activate Auto Bank Reconciliation feature for the selected Bank Account (you must have selected a Bank Account from the list , otherwise, the field Activate Auto Reconciliation would be skipped.

At GoT>Banking>Bank Reconciliation screen, select the Bank for which you need to perform Auto Bank Reconciliation to get the Bank Reconciliation Screen. Now you will find Button (B:Bank Statement) to import digital Bank Pass Book from the website of Bank.

Downloading Bank Statement in Tally

At Bank Reconciliation screen, Click B:Bank Statement button to get Select File screen to import Bank Statement 

  • Directory : At the field Directory, the Bank File Location specified at Banking Configuration screen  is displayed.
  • File Type : Select the File Type (e.g Excel, CSV etc) from the list to show the files of the selected Type residingin the Directory specified at Directory field, as follows:
  • Selected File Type : Select the File Type (CSV, Delimited, Excel) to list the files of the specified File Type, residing in the specified Directory.   
  • All Files : Select All to list all files residing in the residing in the specified Directory. 
  •  Supported Files : Select Supported  to list file types supported by the Bank.

Digital Bank Statement File Type selection

Bank Statement Files : Now a list of File names of the selected File Type would appear. Click I: With View button (or press Alt+I keys) to view the Bank statement to be imported.

Digital Bank Statement Download in Tally

Display of Imported Statement in Tally

When importing the e-bank statement, the statement which is already imported will also appear To hide the imported statement, click S: Hide Imported toggle button to import only the statement which is not yet imported gets displayed (click S: Show Imported to show all the statements)

e-Bank Statement Display in Tally

Import of Bank Statement in Tally

Click F12:Configure and set Yes at the Option Show Imported Statement , to re-import the viewed imported Bank Statement. The list of Bank Statement files are displayed in Alphabetical order showing the  Import Status.

  • File Import :To Import the file, click I:Import button (or press Alt+I)
  • Abandon Import : To abandon the Import, press <Esc> key or click Q:Quit (the file will not be re-imported)

If the file has been Edited or changed in any way, a message ‘Not a valid Format’ appears

During Import, a progress bar appears and on completion of Import process, the Success message appears, showing :

  • Total entries in the Imported Bank Statement
  • Number of Reconciled Entries
  • Number of extra Entries in Bank Statement

Message on Import of e-Bank Statement

If the file is already Imported, on attempting to Import again by mistake, an error message Bank Statement already imported Continue Yes / No will be displayed.

During Import, the entries with same cheque number & Amount are matched and the Bank Date as per imported Bank Statement is posted and shown in the Bank Voucher. Now the Bank Reconciliation Statement appears with Bank Date filled in (in Bank Date column) for Matched (reconciled entries).

Bank Reconciliation Statement showing Auto Matched entries

The status Reconciled Successfully would displayed and the Date of corresponding record in Imported Bank Statement would be posted in Bank Date column.

Bank Reconciliation Entries in Tally : The entries found in imported Bank Statement, for which no corresponding entries are found in Bank Ledger Accounts, are shown under the head ‘Amount Not reflected in Company Books’ .

Bank Reconciliation Summary in Tally : At the end of the Bank Reconciliation Statement, the Balance as Per Company Books, Entries not reflected in Company Books (Debit & Credit figures shown separately) and the Balance as per Imported Bank Statement is shown.

Unreconciled Entries Manual Matching in Tally

During Import, the entries with same cheque number & Amount are matched and the Bank Date as per imported Bank Statement is posted in the Voucher, and shown in the Bank Voucher. The Bank Reconciliation Screen shows following unreconciled entries. The Bank Reconciliation at this stage is in unfinished state, showing unreconciled entries as follows, that are to be further matched (reconciled)  manually:

  • Unreconciled entries in Company Bank Book : These are unreconciled entries in the Bank Reconciliation screen, in which the Bank Date  is not posted (the field in Bank Date column for the entry is blank) as no matching entries are found in imported Bank Statement. It includes :
  • Entries which have not been cleared at Bank. Cheques issued  but not yet presented to Bank and Cheques deposited not yet collected by Bank. These will be automatically cleared in subsequent Reconciliation process.
  • Unlinked entries : Corresponding entries may exist in Imported Bank statement but did not get reconciled as the common reference number  could not be found between the Company Bank Book and Imported Bank  Statement. These are to be traced by further scrutinising both types of Unreconciled Entries 
  • Unreconciled entries in Imported Bank Statement : These are unreconciled entries appearing under the head Amount Not reflected in Company Books. These are entries appearing in Imported Bank Statement but no corresponding entry  found in Company Bank Book. It includes :
  • Entries not yet entered in Company Bank Book :  Entries appearing in Imported Bank Statement for which the corresponding entries has not been entered in Company Bank Book. These include Bank Charges , Interest etc. debited, amount paid by Bank under standing instruction, Interest Credited, Amount directly credited to bank account etc

For such entries, corresponding Vouchers are to be entered and these will get reconciled in subsequent Reconciliation Statement.

However the entries which has not been yet cleared by Bank (Cheques Drawn on Bank / Cheques deposited with Bank), will remain in the Bank Reconciliation at the end of the current period, which will get auto reconciled at subsequent next Reconciliation processes, when these instruments are cleared.

Unlinked Entries Manual Matching in Tally

 To reconcile an Unlinked entry in Company Books with Unreconciled Entry in Imported Bank Statement (not reflected in Company Books),

  • Click R:Reconcile Unlinked  button (or press Alt+R). The corresponding entry to an unreconciled amount would be highlighted. Select the correct entry and press <Enter>.
  • Click R:Reconcile All Unlinked  to reconcile all unlinked transactions to reconcile transactions from the imported statement and company’s book from a single screen without having to manually match the transactions.

The Unreconciled Transactions screen displays unmatched transactions of Company Bank Book and Bank Statement.

Press Enter to Accept the transaction displayed to link and to reconcile. Click S:Stop to stop the reconciliation process,

If there are no matching transactions of similar amount, no transaction would be shown an a message No unreconciled transaction exists with same amount will be displayed.

Missing Bank Entries Voucher creation in Tally

The entries under Amount Not reflected in Company Books are not entered in Company Books. Click C:Create Voucher button (or press Alt+C) to create a new Voucher to get Voucher Creation screen. Create the new Voucher. Now the entry will no ore appear in the Reconciliation screen.

Difference in Balance in Bank Reconciliation Statement

Sometimes, the Bank Balance as reported in Bank Reconciliation may not match with Imported Bank Statement. To see the difference, at Bank Reconciliation Configuration screen, set Yes at the option Show Bank Balance Difference to show the amount of difference between Bank Balance as per Bank (computed Bank Balance as per Bank Book and the unreconciled entries) and the Bank Balance as per Imported Bank Statement is shown separately at the Reconciliation Summary, at the end of Reconciliation Statement. Now you have to scrutinise the Imported Bank statement and locate the difference.

Bank Reconciliation screen after matching in Tally

Click here to see PDF