Bank Reconciliations Plugin

 Introduction to the Bank Import Plugin

Using this Bank Import Plug-in, you may import a valid bank statement file (downloaded from your online banking) saved on your system into a Bank Account (Cash-book).

This Bank Import Plugin will save you time, since you do not need to physically enter the transactions manually in the Payments Journal and Receipts Journal.

This plug-in uses Bank references to automatically allocate or link payments and deposits, receipts, other debits. credits, etc. to the correct accounts. 

It further supports the bank accounts of your debtors (customers / clients) to use the automatic receipts for debit orders, etc. 

It further supports the bank accounts of your creditors (suppliers) to use the automatic payments to manage the payments to suppliers, etc.

You may download bank statements for more than one bank account; credit card statements; etc.; provided that: You are registered for online banking at your bank or financial institution. Have a created separate Bank, Credit Card accounts, etc. which is linked to its own Payments and Receipt Journals).

Once you have imported your bank statement (downloaded and saved on your system), the transactions on your bank statement will be imported (by a few mouse clicks) into the Payments and Receipts batch of your bank account as follows:

1. Debit transactions (payments, bank charges, interest payed, debit orders, electronic payments, etc.).
2. Credit transactions (receipts, interest received, electronic payments (receipts), debit orders, etc.).

Once this is done; you need to do the following simple process:

1. Select the Account code and Tax account; if applicable to a payments transaction.
2. Select the Account code and Tax account; if applicable to a receipts transaction.

You may also use the Context menu to locate select and search for a specific transaction, debtor (customer), creditor (supplier), or a general ledger account.

If any debtor (customer), creditor (supplier), or a general ledger account is set as an open item account, you may link specific debit transactions to specific credit transactions (and vice versa); e.g.:

1. Payment transactions – link to specific credit transactions (e.g. Purchase documents, Purchase Journal, etc.).
2. Receipt transactions – link to specific debit transactions (e.g. Invoices, Sales Journal, etc.).

This will display the Outstanding amount for each transaction, etc.

You may also select and link a part of a payment / receipt and / or one payment / receipt to more than one outstanding transactions.

You may also link an account to a specific debtor (customer), creditor (supplier) or a general ledger account to allow the correct automatic linking (allocation (selection of accounts)) during the next bank statement import.

Purchase and Register the Plugin

Commercial - One time fee - or combined in a bundle of multi plug-ins.

Order trial of full version of then bankimport plug in

The Plugin is included in the TurboCASH / osFinancials installation file. Once the Plugin is purchased, n e-mail with the registration (activation) codes and instructions will be forwarded to you.

This Plug-in needs to be separately purchased and registered. Unregistered versions only allows a specific number of transactions to be imported and processed.

Activating the Bank Import Plugin

To enable this feature, you first need to deselect (remove the tick) on the Default Bank Reconciliation Method setting; if necessary.

This will change the Bank Account Recon Options screen and combine the Payments Journal and Receipts Journal Entry screens into one screen.

To Activate the Bank Import Plugin:

1. Click on the Setup → Setup → Data Parameters menu. 
2. Deselect (remove the tick) on the “Default Bank Reconciliation Method”.
3. Click on the OK button. This will save your setting and close (exit) the Data Parameters screen.

Managing Bank Accounts and Account Bank References

When Bank Statements are imported, it will automatically import all transactions into the Batch Entry screen (combined Payments and Receipts Journal). You need to allocate the transactions to the correct accounts, as the bank statement does not know which accounts in TurboCASH is matched to items on the bank statement.

TurboCASH allows you to manage these references to specific accounts. When importing a bank statement, and the account links are correct, it will automatically allocate the bank statement transactions to the correct accounts.

When importing statements and these are not linked, e.g. new receipts, payments, etc., these will then be set to Empty in the Accounts column of the Bank Accounts Batch Entry screen.

To automate the process for allocating (selecting accounts) to an imported bank statement, please take not of the following settings:

1. General Ledger Accounts

   • Bank References – This needs to be correctly entered on the Bank Links tab. If this is correctly entered (edited), it will match the references as imported from bank statements.

   • Tax (VAT / GST / Sales Tax) - If you are registered as a VAT / GST / Sales Tax Vendor, and VAT is applicable to the specific account, you need to link that account to the correct Input or Output Tax (VAT / GST / Sales Tax) account.
2. Debtor Accounts
   • Bank References - This needs to be correctly entered on the Bank Accounts tab. If this is correctly entered (edited), it will match the references as imported from bank statements.
   • Bank Accounts – The Banking details for the Debtor (customer / client) account needs to be correctly maintained on the Accounting Information tab of the Debtor account. This may be used to manage debit orders, refund debtors, etc. via the automated payments system. BIC (Bank Identifier Code) and IBAN (International Bank Account Number) accounts are also supported.
3. Creditor Accounts
   • Bank References - This needs to be correctly entered on the Bank Accounts tab. If this is correctly entered (edited), it will match the references as imported from bank statements.
   • Bank Accounts – The Banking details of the creditor (supplier) account needs to be correctly maintained on the Accounting Information tab. This may be used to pay creditors (suppliers) via the automated payments system. BIC (Bank Identifier Code) and IBAN (International Bank Account Number) accounts are also supported.

Ledger Account Settings

For the BankOnline plugin, the Bank Links and the Link Tax are important.

To set the Accounts for BankOnline:

1. Click on the Action → Accounts menu.
2. Select an Account. 
3. Link Tax – If you are registered as a Tax (VAT / GST / Sales Tax) vendor, and Tax (VAT / GST / Sales Tax) is applicable to the specific account, you need to link that account to the correct Input or Output Tax (VAT / GST / Sales Tax) account. If you are not registered as a Tax (VAT / GST / Sales Tax) vendor, this field should be set to T-Empty.
4. Bank Links – Enter the reference as per Bank Statement (if available).
5. Click on the Save (tick) icon.
6. Click on the Save icon or Save button.
7. Repeat the process as necessary.

Debtor / Creditor Account Settings

You may need to enter / edit the Bank Details of your Debtor / Creditor Accounts. These accounts may be used to manage references and for the following automatic payments in the F9:Process options for bank accounts:

• Automatic Payments (Payments Debtors / Creditors)
• Automatic Receipts (Receive Debtors / Creditors)

To Enter the Bank Details of a Debtor Account:

1. Click on Action → Debtor menu.

2. Select a Debtor account and click on the Edit button.

3. Click on the Accounting Information tab. 

4. Enter the following where applicable:

   1. Bank Account Type – Only Proof / Abitrair is available for the dutch market. When workin in the Bank Account Batch Entry screen, and selecting the Update Bank Accounts (on the context menu) this is field will automatically be updated to “AutoAdded”.

   2. Bank Account No. - Enter the standard Bank Account Number of the debtor (customer / client).

   3. Bank Name – Enter the name of the standard Bank Account of the debtor (customer / client).

   4. BIC - (Bank Identifier Code)

   5. IBAN - (International Bank Account Number) See - http://en.wikipedia.org/wiki/International_Bank_Account_Number

5. Click on the Bank Accounts tab. This tab is used to manage the Account Bank References for debtor (customers / clients).

6. Repeat the process for creditors (suppliers) on the Action → Creditor menu.

Managing Bank Statements

Download and Save your Bank Statement

Before you can use this Bank Import Plugin, you need to download your Bank Statement from your financial institution and save it in a valid file format.

When downloading a bank statement; from your bank or financial institution; you may need to: Sequence to list your transactions, e.g. Date, Amount or Description. Select the correct start and end dates, for your requirements.

You may download bank statements for more than one bank account; credit card statements; etc.; provided that: You are registered for online banking at your bank or financial institution. Have a created separate Bank, Credit Card accounts, etc. which is linked to its own Payments and Receipt Journals).

If you have created and use the Petty Cash account as a Bank Account, you may still enter the transactions manually, if required.

List of Supported File Formats

In addition to a various file formats for the Belgium and the Netherlands environment, the following file formats are currently supported for the international banking environments:

• INT-Simple comma separated column Date dd/mm/yyyy, description, Amount . is thousandseperator.
• INT 053 CAMT file format')
• INT-MT940 (algemeen formaat)
• INT-OFC / OFX standaard
• INT-OFC / OFX standaard 2
• INT-OFX bankexport
• INT-PayPal
• INT-QIF format

Launch the Cashbook and Settings

This is a combined Batch Entry screen for the Payments Journal and Receipts Journal.

 To Launch the Bank Batch Entry screen for the BankOnline Plugin:

1. Click on the Action → Reconcile Bank menu.

   Alternatively you may: Click on the Batch Entry icon. (You may also press the F2 key). The Batch Type screen will be displayed. Select the Payments Jnl (or “Receipts Jnl”) and click on the Open button. The “Batch Entry – [Payments Jnl]” (or “Batch Entry – [Receipts Jnl]”) screen will be displayed.

2. Select the correct Bank Account; e.g. “Current Bank Account”; if not already selected.

3. Enter the closing Bank Balance.

   The Last total will be displayed as the closing balance for the previous period of the selected bank account.

4. Click on the Open button. The “Batch Entry – [Payments Jnl]” screen is displayed: 

5. Enter the Bank Statement No or Name in Change Alias field.

6. Click on the F10: Setup icon and check the following settings on the Standard tab: 

   1. No Tax – If you are registered as a VAT / GST / Sales Tax vendor, you need to uncheck (remove the tick) this field.

      If you are Not registered as a VAT / GST / Sales Tax vendor, you may tick this field. This will hide the Tax column and the Tax lookup.

   2. Click on the Advanced tab. Check that the settings are the same as the screen below: 

7. Click OK to save the settings.

Import a Bank Statement

Once your settings are done, you may proceed to import your bank statements.

 To Import a Bank Statement:

1. Click on the Action → Reconciliation menu. 
2. Select the correct Bank Account; e.g. “Current Bank Account”; if not already selected.
3. Enter the closing Bank Balance.

   The Last total will be displayed as the closing balance for the previous period of the selected bank account.

4. Click on the Open button. The “Batch Entry – [Payments Jnl]” screen is displayed: 

   The Difference of -5758.00 displayed on the “Batch Entry – [Payments Jnl]” screen, will change to 0.00; once the net value Credit (Deposits / Receipts) minus the Debit (Payments) amounts (displayed as a positive amount) is imported. The difference should be 0.00 which means that all transactions on the imported bank statement is correctly imported.

5. Enter the Bank Statement No or Name in Change Alias field.
6. Click on the Import Bank Statement icon. The “Import Bank Statement” screen is displayed: 
7. Select the File Type. For this example; the “INT-OFC / OFC standaard 2” (“Microsoft Money” file format) is selected.
8. Select the “Show Details” option.

   If the “Show Details” is not selected (not ticked), the Check screen will not be launched.

9. Click on the Import button. The “Open” screen will be displayed.
10. Locate the valid bank statement file and click on the Open button. The selected file will be imported and displayed on the “Check” screen as follows: 
11. The references in the “Reknr” column, will if they match your Bank Links tab for General Ledger Accounts (Action → Accounts menu) and the Bank Account No. tab for debtor (customer / client) accounts (Action → Debtor menu) and for creditor (supplier) accounts (Action → Creditormenu), be allocated to the correct accounts on the Batch Entry screen for this bank account.

    Should you import the same Bank Statement, the bank statement transaction details in the rows will be displayed in a yellow background colour.

    You may enter a Statement Reference (if applicable). Should you enter a reference; and you click on the Apply button; this reference will be displayed in the Reference column of the selected bank statement transaction.

12. Click on the Close button. The “Import Bank Statement” screen is displayed:

The details displayed on the “Import Bank Statement” screen is as follows: Transaction No. – This will be the transaction number generated and would consist basically of the date yyyy/mm/dd format; followed a time stamp and a reference number. This may differ from your operating system's regional and date settings. Total – This is the net value Credit (Deposits / Receipts) minus the Debit (Payments) amounts. If the Credit (Deposits / Receipts) is more than the Debit (Payments) this will display a positive amount. Should the Debit (Payments be more than the Credit (Deposits / Receipts) this amount should be displayed as a negative (“ -”) amount. From – The start date as selected while downloading (exporting) the bank statement from your bank or financial institution. Until – The end date until when transactions would be included in the downloaded (exported bank statement). Posted – The default value is “0”. This means that the transactions have not yet been posted (updated to the ledger). Once you have allocated (selected) Accounts and / or Tax accounts (where applicable) a value of “1” as posted (processed) will be displayed.

The colour of the font for this line will change from black to blue if transactions have been processed and / or posted. If the bank reconciliation is done, the font colour will change to green.

13. Click on the Close button. The transactions as per your bank statement is imported into the “Batch Entry – [Payments Jnl]” screen. 

Merging / Splitting the Bank Statement Transactions

Before you close the Bank Import Statement – Check screen, you may Delete a line, merge or split transactions.

Merging Transactions

If there are a few transactions on the Bank Import Statement – Check screen, you may merge (consolidate) it into one (1) entry.

To do this on the Bank Import Statement - Check screen:

1. Press the Ctrl key and while holding your mouse down, click on the transactions to merge (consolidate).
2. Click in the Merge button. You may also right-click and select the Merge option on the context menu.

To do this on the Batch Entry screen:

1. Select the transaction to be merged and over type the amount with the total of the transactions to be merged.
2. Select the transaction to be deleted and click on the ^D:Delete icon. Repeat as necessary.

Splitting transactions

If there any transactions on the Bank Import Statement – Check screen, you may split the transaction into two (2) or more entries.

You may need to do this, if a payment or receipt needs to be allocated to different accounts.

To do this on the Bank Import Statement - Check screen:

1. Click on the transaction to split.
2. Click in the Split button. You may also right-click and select the Split option on the context menu. The Split amount screen will be displayed.
3. Enter the amount for the amount to split. It will insert another transaction with the same details as the selected transaction with the remaining amount.
4. Click on the OK button. Repeat the process, if necessary.

If this is not done at this stage (on the Bank Import Statement – Check screen), you may add it later on the Batch Entry screen for the Bank Account.

To do this on the Batch Entry screen:

1. Select the transaction and click on the ^N:Insert icon. In the blank row, enter the transaction details and the new amount.
2. On the selected transaction to split, over type it with required amount. Repeat as necessary.

Process Bank Statement Transactions

Once your bank statement is imported; you may proceed to check and allocate the transactions to the correct accounts, where necessary.

The recommended process is as follows:

1. If the bank references for all accounts are set correctly, and these references match the bank statement, these will be automatically be allocated to the correct accounts and Tax (VAT / GST / Sales Tax) accounts.
2. Please refer to the next sections for more information.
3. Once all the transactions are correct, you need to:
   • Click on the F8: List icon to print a list of the transactions in the batch.
   • Click on the F6: Post icon to Update the transactions to the ledger.
4. Exit or close the Batch Entry screen.

Notes

The ^D: Delete statement icon is similar to the Erase Batch option on the Process options for this batch (F9:Process). It will only clear erase all the transactions in this batch. The Bank Statement will still be on the list of Bank Statements on the Import Bank Statement screen.

The lines (rows) with a cream background colour, indicates that the account is an open item account, which is already linked to a transaction.

The Tax column with a red background colour, indicates that Tax (VAT / GST / Sales Tax) should not be selected for debtor (customer / client) accounts and / or for creditor (supplier) accounts, as the Tax are already processed in the sales or purchase transactions.

Open Item Accounts

When allocating (selecting) a bank statement transaction to an account, which is set as an Open Item Account (e.g. D000001 - Mr. Mike Davies), the following open item selection screen is displayed: 

On this screen; you may select a specific invoice or other transaction to link the transaction. As in this example, this deposit need to be linked to INV004.

In addition to this, you may also use the following context menu options:

1. Select from account open item list – This will display a list of the transactions on the Open Item Selection screen for the selected account. On this screen, you may select a transaction from a list to link the transaction in the Batch Entry screen.

2. Open Item – Launch the Open Item screen, if an account is selected.

3. Payment difference - This will launch the Receive Payments screen with the default PAYDIV in the reference field. On this screen you may process discounts or adjustments, if necessary.

Bank Account Batch Entry Context menu

When you right-click on a row (line) in the batch, the following context menu will be displayed:

This will allow you to select more advanced functions to assist you with the allocation process.

If there are any transactions, which are displayed as “Empty” in the Account column, you need to allocate them to the correct account using the Account lookup. In addition to the Account Lookup (select an account from the list), you may use the context menu (right-click on a selected line) to allocate or link the transactions.

1. ^D:Delete – This will remove a selected transaction or transactions from the Batch Entry screen. This is the same as the ^D:Delete icon on the Batch Entry screen.

2. Update Bank Account – This will update the references as in the “Reknr” column (Bank Import Bank Statement Check screen) to the following:

• Bank Links tab of Ledger Accounts (Action → Accounts menu)
• Bank Account No. tab of the debtor (customer / client) accounts (Action → Debtor menu).
• Bank Account No. tab of the creditor (supplier) accounts (Action → Creditor menu).

3. Lookup Document – Find locate a specific document.

4. Lookup Document with Amount - Find locate a specific document by amount.

5. Lookup Debtor - Find locate a specific debtor (customer / client) account.

6. Lookup Creditor - Find locate a specific creditor (supplier) account.

7. Show refs for account- View, add, edit, delete to manage references for accounts.

8. Link bank account from import to accounts

9. Link all records with current account bank accounts

10. Create Invoice / Credit Note or Create Purchases / Supplier Returns

11. Select from account open item list - This will display a list of the transactions on the Open Item Selection screen for the selected account. On this screen, you may select a transaction from a list to link the transaction in the Batch Entry screen.

12. Open Item – Launch the Open Item screen, if a selected account

13. Payment difference – This will launch the Receive Payments screen with the default PAYDIV in the reference field. On this screen you may process discounts or adjustments, if necessary.

Creating Invoices / Credit Notes from the Context Menu

If a Debtor account is selected, and there are no documents (i.e. Invoices or Credit Notes), you may launch the context menu (right-click) to select the Create Invoices / Credit Notes context menu option:

1. Create Invoices – If the amount is a credit in the Amount Entry column and in the Receipts column, the Invoices document entry screen (with the next invoice number) for the selected debtor (customer / client account) will be launched. You may then edit the details, add stock items and / or comments to the Invoice.

2. Create Credit Note - If the amount is a debit in the Amount Entry column and in the Payments column, the Credit Note document entry screen (with the next credit note number) for the selected debtor (customer / client account) will be launched. You may then edit the details, add stock items and / or comments to the Credit Note.

Creating Purchases / Supplier Returns from the Context Menu

If a Creditor account is selected, and there are no documents (i.e. Purchases or Supplier Returns), you may launch the context menu (right-click) to select the Create Purchases or Supplier Return context menu option:

1. Create Purchases – If the amount is a debit in the Amount Entry column and in the Payments column, the Purchases document entry screen (with the next purchase document number) for the selected creditor (supplier account) will be launched. You may then edit the details, add stock items and / or comments to the Purchase document.

2. Create Supplier Returns - If the amount is a credit in the Amount Entry column and in the Receipts column, the Supplier Returns document entry screen (with the next supplier return document number) for the selected creditor (supplier account) will be launched. You may then edit the details, add stock items and / or comments to the Supplier Return document.

Lookup Document

This option allows you to quickly find the / filter or search for specific documents.

Lookup Document with Amount

The “Lookup Document with Amount” will launch the similar Document Entry screen as the Lookup Document option. It will list Credit Notes as the default Documents (Document Type) instead of Invoices.

Lookup Debtor

This option allows you to quickly find the debtor (customer / client) account. You may use the Filter column find and locate a specific debtor (customer / client) account.

In addition to this, you may also select the debtor (customer / client) account; and click on the Edit button of the “Debtor Accounts” screen. On the Documents tab, all posted documents for that debtor (customer / client) will be listed. If you go to the Transactions tab, you may also locate other transactions (e.g. manual invoices entered in the Sales Journal, Charge Amounts, Interest Charged on overdue accounts, Fixed Amounts to all debtors, etc.).

Lookup Creditor

This option allows you to quickly find the creditor (supplier) account. You may use the Filter column find and locate a specific Creditor (Supplier) account.

In addition to this, you may also select the creditor (supplier) account; and click on the Edit button of the “Creditor Accounts” screen. On the Documents tab, all posted purchase documents (i.e. Purchases and Supplier Returns) for that creditor (supplier) will be listed. If you go to the Transactions tab, you may also locate other transactions (e.g. manual purchase documents entered in the Purchase Journal, Charge Amounts, etc.).

Account Bank References

The bank account references are managed from the following options on the context menu:

1. Update Bank Account – This will update the references as in the “Reknr” column (Bank Import Bank Statement Check screen) to the following:

• Bank Links tab of Ledger Accounts (Action → Accounts menu)
• Bank Account No. tab of the debtor (customer / client) accounts (Action → Debtor menu).
• Bank Account No. tab of the creditor (supplier) accounts (Action → Creditor menu).

2. Show refs for account – displays the detail and / or a list of all bank account references.

3. Link bank account from import to accounts – to be advised.

4. Link all records with current account bank accounts - to be advised.

 

Show refs for account

If an account has references, you may right-click on a selected row and select the Show refs for account option. The Details tab of the Account Bank References screen will be displayed for the selected account.

In the account selected in this example, the Standard Bank Account No. and the IBAN (International Bank Account Number) (See - http://en.wikipedia.org/wiki/International_Bank_Account_Number) and BIC (Bank Identifier Code) account numbers as entered in these fields on Accounting Information tab of the Action → Debtor menu is displayed.

You may add / edit or delete the references on this screen. This will update the Reference records on the tabs of the selected account.

• Bank Links tab of Ledger Accounts (Action → Accounts menu).

• Bank Account No. tab of the debtor (customer / client) accounts (Action → Debtor menu).

• Bank Account No. tab of the creditor (supplier) accounts (Action → Creditor menu).

If you click on the List tab you may select an account to view / add / edit / delete references, if necessary. 

Bank Reconciliation

Reconcile your Cashbook with Bank Statements

To do a Bank Reconciliation:

1. Click on the Action → Reconciliation menu. 

   If all bank statement transactions are imported and processed correctly; the Bank Balance and the Last total should be the same amount.

2. Select the correct Bank Account; e.g. “Current Bank Account”; if not already selected.

3. Click on the Reconcile button. The Reconcile Bank Account screen displaying the name of your current account will be displayed listing all your transactions in this Cashbook for the selected period i.e. 01 to 31 March 2013. These entries represent your unreconciled bank statement items.

   Before beginning your bank reconciliation you may set up the following functions: Show Statement – The BankOnline Plugin is not implemented at this stage for TurboCASH. You may use the Import Bank Statement option (if the Default Bank Reconciliation Method field is not selected on the Setup → Setup → Data Parameters menu option. Search – Click on the Search icon and a window will appear at the bottom of the Reconcile Bank Account screen. This allows you to locate an item by Date, Reference or Amount. Reconcile – Click on the Reconcile icon to reconcile all items, or specific items by reference or date. Unreconcile – Click on the Unreconcile icon to unreconcile all items, or specific items by reference or date. Process – Click on the arrow of the Process icon to select one of the following: Apply Reconciled process all items selected (ticked) in the Reconciled column. Transfer Unreconciled to Batches - This will transfer all items not selected (not ticked) in the Reconciled column) to the Payments and / or Receipt batches. Report – Generate the Bank Reconciliation Report.

4. Since all these items should be reconciled as they are in your cash-book as well as in your imported bank statement, these items may be ticked as reconciled. To do this, select the item, and click (select or place a tick in the Reconciled column).

5. Once you have “ticked” all entries - click on the arrow on the Process button and select the “Apply Reconciled” option. All reconciled items will disappear from the list and you will be left with a list of unreconciled items.

   You may click on the Reconcile icon to reconcile all items, or specific items by reference or date.

6. Click on the Report icon. The “Bank Account Recon Options” screen is displayed: 

   All the data of the Bank Account, Date, Bank balance, etc. should still be the same as per your your previous selection criteria.

7. Click on the Open button. The Cashbook reconciliation report is displayed: 

   TurboCASH will subtract any unreconciled payments and add any unreconciled receipts from the bank balance according to the bank statement, thus giving a reconciled balance.

8. After studying and printing the report, you may close the report.

9. Click on the Close button of the Reconcile Bank Account Options screens; or press the Esc key to exit (close) these screens.

Automatic Payments

The use of "automatic payments" means that you can select in a batch payments of the creditors. 

In the bank journal click F9: Process journals, and the Process options for this batch screen is launched.

 Choose "Automatic Payments" to select payments, and you will arrive in the next screen.

On this screen, you may select the accounts to pay. It is important that these accounts have the correct bank numbers. The Clieop file, if selected will not process and accounts without bank account numbers.

If you click on the Print button, the Automatic Payments report will be printed.

 

If you click on the Update button, these transactions will be imported into the batch entry screen.

 

You then need to list and post the batch.