The Automated Bank Reconciliation Process

The Bank Import lets you reduce manual processes when reconciling system bank transactions with actual transactions in your bank accounts.

The process is a s follows:

1.  Upload an electronic bank statement. This can be from online banking or created from a scanned statement using AutoRec. Alternatively, you can set up Bank Feeds.‍ ‍ ‍  
2. Perform a bank reconciliation using automatic matching rules. If the system cannot find a match, it will suggest possible options. If none of these are correct, you can create and post a matching transaction and set rules for future matching. In future imports, the system will remember the matching rules. 

Bank Reconciliation Schematic Overview

See:

Bank Feeds (6B.1) - AIQ Academy

Importing bank statements manually (6B.2) - AIQ Academy

Transaction Matching and Quick Post (6B.3) - AIQ Academy

Advanced Posting and Matching (6B.4) - AIQ Academy

Bank Reconciliation (Manual) (6A.6) - AIQ Academy

Step One: Create a Bank Transaction File

For more details on this process, see Generating Bank Statement Files (Online Banking)‍ or Generating Bank Statement Files (AutoRec). Alternatively, see Managing Bank Feeds to further automate this process.‍ 

1. Log into your On-Line Banking Facility.
2. Choose the relevant Bank Account.
3. Click Export Data. 
4. Select the relevant Date range and click Export. 
5. Click Save. Save the file to a convenient location on your local computer/file server with an appropriate name. The example shown is from Lloyds Bank U.K. Here, the output file is in CSV format which can be opened in Excel.

Importing Bank Files using the Document Manager

If you don’t have access to the online banking or require a specific BI Code or individual to conduct the Bank File extraction, proceed as follows:

1. Have them extract the file and download it to a secure location that is accessible to you.
2. Go to Bank > Bank List and under Docs, click on the relevant document icon and upload the file.
3. The file is now available to import as described in the next step.

Step Two: Import the Bank Transaction File into AIQ

1. In AIQ, in the white banner go to Bank > Import Bank Transactions.
2.  Under Select Bank, choose the appropriate Bank Account.
3. Click Import.
4. Select your previously saved copy of the exported bank file, select the correct date format, and click Import.

The imported transactions will appear on screen. Note the following:

• Clear Imported Transactions: Click this to clear all imported (but not yet reconciled) transactions from the listing. You may wish to use this if you have transactions on your bank statement that you cannot identify or match with existing transactions in the system. Note that all transactions must be cleared from this screen before you can reconcile your bank account. This lets you reimport or use another Import file or Bank Account.

Transactions are displayed as follows:

• Matched (Green): This displays posted system transactions that match those on the imported bank statement file. They have all passed the matching decision algorithm (see Transaction Matching, below).
• Multiple Matches (Yellow): This displays multiple potential matches found in the System that match a single transaction on the Bank Import file. Here several system transactions have passed the matching algorithm. Examine these to select the correct match.
• No Match Found (Red): This displays transactions on the Bank Statement Import file that have no matches in the system. They will mainly be items such as Bank Transfers, and Direct Debits that are currently unrecorded in the System’s Bank Account.
  • Export:  You can also export the contents of the No Match Found category to Excel for printing or further review and analysis.
• Suggested Auto Post (Amber): This displays transactions in the import file that look like previously created and remembered examples. These remembered postings (with updated date, amount, and description from the imported transaction) appear here for you to examine. 
• Imported Bank Statement: The bottom half of the screen shows all the transactions that you are importing together with their color-coded status. View the contents of each category on its own by clicking any of the fields. 

Viewing Imported Bank Statement Files

When you import a Bank File, it automatically goes to the Document Manager.

1. Go to Bank > Bank Accounts. 
   
2. Find the Bank Account and click the document icon.

How the algorithm works

The matching algorithm is complex and works in a specific order. It will create a match if an unreconciled system transaction has a corresponding import file transaction with the following:

1. For unreconciled Lodgements, the exact same amount, with Dates within 14 days of each other.
2. For both unreconciled Lodgements and Bulk Payments, the exact same Reference No. somewhere in its Description field.
3. The exact same Amount and Date.
4. The exact same Amount, with Dates within 14 days of each other, and a partial match on Description or Reference.
5. The exact same Amount, with Dates within 14 days of each other.
6. A matching Reference No. (for example, Cheque) and Amount.

Adjusting the algorithm

You can adjust these algorithm defaults. For example, the exactly matching amount as used above can become an approximately matching amount. 

To modify the algorithm’s parameters:

1. Go to Bank > Import Bank transactions.
2. Click on the Settings icon.
3. Adjust the parameters and click Save to apply these modified parameters to all your Bank Accounts.
4. Click on the Refresh icon to carry out a re-match of any outstanding transactions. You can use this feature at any time throughout your work.

Matched Transactions

Click Match All against the Green band of Matched Transactions. These are transactions which are already posted in the accounts system that match up against those on the imported bank statement file. They have all passed the matching decision algorithm.

Alternatively, you can use the list of Matched Transactions and select each individually by clicking Match.

Multiple Matches 

Click on Multiple Matches to view the potential matches. Several system transactions have passed the matching algorithm. Examine them and select the correct match. Click Type to view the Transaction Details screen. When you find the correct transaction, click Match next to the transaction. 

If you need further assistance in identifying matches, go to the Bank List. Click on View Transactions for your account and find a match.

No Match Found Transactions

The Red Band represents those transaction on the Bank Statement Import file for which no matches can be found in the system. They will mainly comprise Bank Transfers, Direct Debits, etc. that have yet to be recorded in the system's Bank Account.

1. Click on Export to create an Excel record of unmatched transactions.
2. Examine the unmatched transactions against the data in either the:
   • Bank Transaction: Go to Bank List > View Transactions for your account.
   • Bank Reconciliation: Go to Bank > Reconcile Banks. In the Matched column, turn on the Show All filter. Against the appropriate corresponding transaction in the system, check Matched.

Unmatching Transactions

If you want to unmatch any of the items use the Bank reconciliation screen:

1. Go to Bank List > Reconcile Now >Current Reconciliation. If there is an existing Bank Reconciliation open for this bank it will be loaded automatically. If there is no bank reconciliation open, a new one will be created.
2. Make sure that the Show All parameter is on to see both Matched and Un-Matched Transactions. Deselect the match by unchecking the box.

If the system does not recognise items on the Bank Statement and presents them in the No Match Found bucket, you can post them directly from the Import Bank Statement screen as follows:

1. Under Import Bank Statement, click Create new Posting against the relevant transaction.
2. Select the type of transactions you need to create:
   • Purchase Payment / Sales Receipt
   • Bank Payment / Bank Receipt
   • Bank Transfer
   • Customer Refund / Supplier Refund
3.  Complete the required posting details. 
4. Click Post. This creates, posts, and marks the relevant transaction as matched in both the Supplier’s Account and/or the Bank Account(s).

Purchase Payment Advanced Posting

If you select a Purchase Payment, clicking Create Advanced Posting will bring up these additional options:

• Create split payment across GL Accounts: Click to display Sundry Bank Payments to allocate your Bank Payment (Debit Card Purchase) across several General Ledger Expense Postings. See Creating Sundry Bank Payments for more details.
• Create payment with advanced options: Click to display full Purchase Payments and Allocations facilities and create a payment. If you have entered the Supplier Code above, then the complete Suppliers details will autofill in Payments and Allocations. See Using Purchase Payments and Allocations for more details.

Sales Receipt Advanced Posting

If you select a Bank Receipt, clicking Create New Posting displays a set of possible transactions you can create. The required data depends on transaction type.

 Create Advanced Posting for Bank Receipt includes:

• Create split lodgement across GL Accounts: This displays the Sundry Bank Receipts program. See Creating Sundry Bank Receipts for more details.
• Create receipt with advanced options: This displays the Sales Receipts and Allocations program. See Using Sales Receipts and Allocations for more details.
• Create split lodgement across Customer accounts: This displays the Customer Receipts Bulk Entry program. Click Auto Invoice to automatically create an Invoice for each entry. This is of special relevance if your primary source of Sales Transactions is from the Bank Import file. See Using Sales Receipts - Quick Entry.

When you create a transaction (see previous section), you can tag it. If a future imported file contains a similar transaction, it will appear in the Suggested Auto Post band (orange) with updated date, amount, and description from the imported transaction. These remembered postings then must be processed.

Step One: Create Recognition Rule

The example here shows Purchase Payment made to a Supplier based on a specific Description. The Description from the Bank Import File automatically fills the Recognise based on Description field. Edit if needed. If the Description changes on future imported transactions, the system will still recognise it based on its Partial Match capability.

You can also tell the system to recognise a transaction based on a partial match by using a wildcard character in the Recognise based on Description field. For example, if you are billed each month by British Telecom, you could recognize this transaction in the future by memorising the description as “BT*.” This means any reference starting with “BT….”  will register as a possible match.

Click Memorise & Post. Alternatively, by just clicking Post, the system will automatically recognise any future transactions that have the same description.

1. In Import Bank Statements, click Clear Imported Transactions.
2. Re-Import the same Bank File again. 

Guidelines

• If your first import of the Bank File resulted in automatically matched and processed transactions, then these will no longer appear on the import bars. 
• If you create new Bank Import Files with overlapping dates with a previous import, then these duplicate imported transactions will show up as No Match Found as the corresponding system transactions were previously reconciled. Therefore, the duplicates will fail the Matching criteria.
• The purpose of the Bank Import facility is to assist in the Bank Reconciliation process, not to replace it. Therefore, it is not necessary to find a match for all transactions on a Bank Import file. Match as many as you can and leave the remaining unresolved transactions. When you have finished, click Clear Imported Transactions.