Lesson 39-Auto Bank Reconciliation

Auto Bank Reconciliation in Tally.ERP9 Release 3.61 onwards

For the benefit of business entities and users, Tally.ERP 9 has provided the facility of selecting the formats which include configurations and output file type for printing the cheques. Currently the formats of many banks with supporting file types - Excel, CSV and MT940 are made available. Depending on the formats pre-filled in the ledger master of the bank selected in payment/contra voucher and the output file types supported for it, the cheques can be printed. In Tally.ERP 9, Bank Reconciliation capability is enhanced to view, import the e-Bank Statements and to Auto Reconcile the transactions. For Auto Bank Reconciliation (importing Bank Statement), valid Tally.NET Subscription and Internet Connectivity are required for performing auto reconciliation.

To view, import and auto reconcile the e-banking transactions:

Go to Gateway of Tally > Banking > Bank Reconciliation > select the Bank > Bank Reconciliation Statement appears

For banks enabled with the feature of auto reconciliation in ledger master, the buttons:

·         B: Bank Statement and R: Reconcile Unlinked appear on the right side button bar.

·         D: Delete Unlinked button appears on the button bar above Info Panel.

Note:  The facility to Import and View Bank Statements will get enabled only for banks enabled with auto reconciliation feature in bank ledger creation / alteration mode.

Bank Statement

Bank Statement button with the facility to view and import is provided to meet the customers requirement. There could be cases where the user just want to view it and not import the statement directly or wants to view it before it is imported, mainly to confirm that it’s the same file that has to be imported.

Reconcile Unlinked

Once the transactions are imported and reconciled, if there are any entries appearing under the section Amount not reflected in Company Books the Reconcile Unlinked button needs to be used to reconcile it.

Delete Unlinked

Delete Unlinked button is provided to delete the unlinked vouchers.

View Bank Statement

Tally.ERP 9 makes it possible for the user to view the Excel, MT940 or .CSV files in a readable format, with an option of choosing to import or not to.

Click on B: Bank Statement button or press Alt+B key.

On pressing the button, the Bank Statement File screen displays the Directory which was specified as the Location of Bank Statements in F12: Configuration > Banking Configuration. Select the required file format in File Type field based on the format of bank statement being imported.

In File Name filed, a list of Excel files will get displayed. To view the Bank Statement which would be imported, click the button I: With View or press Alt+I.

Press F12: Configure. The option Show Imported Bank Statement will be displayed and set to Yes by default. Using this option, the bank statement which is already imported can be selected again for import. The files will be listed in alphabetical order with details of file type and import status (showing whether it is already imported or not).

The Bank Statement appears as shown:

·         Users can either import the statement using Import button or Quit the Bank Statement screen by pressing Escape key or click on Ctrl+Q: Quit button (where the user is not intending to import the file).

Import Bank Statement

In the Bank Statement screen which is previewed press I: Import button. Users must ensure that the bank statements being imported haven’t been edited or altered in any way. If any changes are made, the error message - Not a valid format will be displayed while importing the statement.

During import, the Import progress bar gets displayed followed by the Success message with count of Total Entries in Bank Statement, transactions Reconciled and Additional Bank Entries.

The Bank Reconciliation Statement with Imported Bank Statement details appears as shown:

The Reconciliation found at the bottom of the screen shows:

o        Balance as per Company Books

o        Amounts not reflected in Bank

o        Amount not reflected in Company Books

o        Balance as per Bank

The Balance as per Company Books reflects the balance as on the last date (in the example, it is 30th April).

The Amounts not reflected in Bank are the debit and credit sums of all those vouchers, whose Bank Date is either BLANK, or LATER than 30-Apr (i.e. these vouchers have not yet been reflected in the bank statement).

The Amount not reflected in Company Books are entries which are present in the bank statement but are not recorded in company's books.

The Balance as per Bank is the net effect of the Book Balance offset by the amounts not reflected in the Bank – which must be equal to the balance in the bank statement.

Note: Some discrepancies may persist due to entries made in the bank statement, which may not have been entered in the books of accounts.

Auto Reconciliation of e-Banking Transactions

After Importing the file, the bank dates will get filled in Bank Dates field automatically in Bank Reconciliation screen.

However, there may be transactions which have been executed at the bank’s end (example, bank charges, interest credit etc.) but not yet reflected in the books of accounts. Such transactions will appear under the Amount Not Reflected in Company Books in BRS statement with the required details.

User can either Reconcile Unlinked transactions to existing transactions or create a new voucher (to create vouchers click on C: Create Vouchers button or press Alt+C key) using this information or delete them.

Note: It is suggested to save the Bank Reconciliation before reconciling the Unlinked Transactions to avoid mistakes.

1. Reconcile Unlinked

In this example, there is a withdrawal of 2,500 on 23-4-2012 which is not reconciled. To reconcile the transaction select the transaction displayed under Amount Not Reflected in Company Books and click on R: Reconcile Unlinked button or press Alt+R.

Application will display the transaction(s) matching the Transaction amount for selection. Select the appropriate transaction using spacebar and press Enter. The Status will be displayed as Reconciled Successfully.

2. Create a New Voucher

If there are any transaction which is not reflected in books of accounts, click on C: Create Voucher button or press Alt+C key to navigate to Voucher Creation screen.

On recording the transaction against the Amount Not Reflected in Company Books, the details displayed will be removed from the Amount Not Reflected in Company Books section indicating that the corresponding entry is found in the books of accounts.

3. Error on re-importing the Excel/csv file

While selecting the bank statement for importing, the statement which is already imported will also appear. To hide the imported statement, click S: Hide Imported button. On using this button only the statement which is not yet imported gets displayed. The Hide Imported is a toggle button which can also be used to Show Imported statements.

In cases where the user has already imported an Excel/csv file and tries to re-import it by mistake, an error message will be displayed indicating that Bank Details are reflected in your books of accounts.

4. View Difference in Bank Balance

To view difference in Bank Balance click on F12: Configure and the option Show Difference in Bank Balance to Yes.

The difference in Bank Balance (difference in Balance as per Bank and Balance as per Imported Bank Statement) will be shown in Bank Reconciliation Statement. In the Bank Reconciliation screen, the Balance as per Imported Bank Statement refers to the balance displayed on importing the bank statement for reconciliation.

You can see the Balance as per Books & Balance as per Bank is same.