Bank Statement Upload and Reconciliation

The bank statement upload and reconciliation process provides for electronic bank statements being uploaded into the system. Items are reconciled to existing transactions or if they do not already exist then they are posted and reconciled. Users are expected to confirm the matching that the system rules suggest or amend as necessary. Electronic statement files may be browsed for or, for certain bank service providers, directly retrieved. The system DOES NOT store any security information with respect to accessing the bank account, so for direct import the user will need to be present with appropriate user name, password and pin sentry machine. This ensures that security is not compromised and banking terms and conditions are not breached.

Bank statement upload process in summary

  1. Initiate a bank account for electronic statement upload
  2. Browse or directly import an appropriate bank statement electronic file
  3. Process and/or confirm each item uploaded
  4. Confirm the items to be reconciled by clicking the Finalise Import button that will appear when all items have been dealt with

This guide addresses the following topics.

Initiating a bank account for electronic statement upload

A bank account needs to have a banking provider activated to allow statement uploads to that account. This is done via the Bank Account Maintenance Screen accessed via the Accounts menu.

Accounts - Relevant Bank Account

Hover the cursor over the relevant bank account and from the sub-menu click the Configure Account command.

or

Accounts - Bank/Credit Accounts - from the Action Box click Configure

The account setup screen is presented displaying the set up as originally entered when the bank account was created.

On the Bank Account Details Tab select the relevant Bank Account Provider from the Bank Import Format drop-down. Depending on the selection an additional field will appear that relates to the account naming associated with the bank account when online statement download is taken from the bank web site. Where direct import is available a 'Enable Direct Download' box appears. Ticking that box allows for direct downloads. Correct names will need to be entered, particularly if direct retrieval of statements (rather then secondary access after statements have been manually downloaded from the banking provider) is used. See Bank Statement Import section below for more information.

Click the Enable Customer Reimbursable Expense Allocation box for the case where a user regularly wishes to record a Customer allocation to a expense item. When a expense item is analysed/reconciled in the import window, a drop-down is offered to allow the selection of a Customer. If this box is un-ticked; the default position; then the drop-down is not offered.

Click SUBMIT to store the changes to the setup and two new items are now displayed in the Action Box - Statement Import and Import Rules

Importing a Bank Statement

The retrieval and upload of a bank statement can be initiated from one of two points via the Accounts menu.

Accounts - Relevant Bank Account

Hover the cursor over the relevant bank account and from the sub-menu click Bank Import.

or

Accounts - Bank/Credit Accounts - from the Action Box click Statement Import

The Bank Import screen is presented.

Upload File

Upload File requires that a previously downloaded bank statement file is available on the current machine. If a file has not been already down-loaded then quick access to the appropriate banking provider website for a download is available from the blue WEBSITE link.

Choose SELECT FILE to browse for the appropriate bank statement file and click SUBMIT. The statement is then loaded into the Statement Items screen. For more details on the Statement Items screen see below

Direct Import from the Bank

For many banking providers direct download is possible. A user will need to be present with appropriate user name, password and if required by the banking provider a pin sentry machine and/or a card. In the Bank Import screen click the 'Online Banking'

Additional fields will appear displaying To and From Dates for the statement period (these may be amended) as well as mandatory entry of relevant Username or ID, Password and Card Number with Security Code from a Pin sentry machine if required by the banking provider. Click SUBMIT to request the electronic statement download. The statement items screen will be presented with those items from the bank account within the date range.

Processing an uploaded statement

Whichever import approach is taken the statement details are presented in the Statement Item list.

The statement item list consists of two columns. The left hand column displays all the items that are on the bank statement. The right hand column shows possible matches that the system has found and offers a description of what that may mean. Where nothing is shown on the right hand column adjacent to an item, the system has no suggestions.

Match Window(days)

Initially when a statement is imported the system uses the default window of 30 days from the date associated with the imported transaction (the date given by the bank) to look for a match. In most cases this window will be sufficient and is the most efficient in processing terms.

However there may well be transactions (often cheques)that are presented to the bank outside of this 30 day window in which case the system will not present a match. A user can therefore chose to extend globally the match window by either :-

Where an organisation has a significant number of transactions extending the global window can potentially extend processing time.

Note where more than one matching transaction may exist click on the blue … symbol to pick from a list and if required extend the match window days to suit for this transaction.

Processing statement items

When an item has been processed it disappears from the screen. Note to show all items that have been dealt with click the SHOW ALL button at the base of the page.

For those items where there is nothing in the right hand column the user will create the transaction (as the system does not already have it). The process of creating the transaction will also reconcile the item. Click the green + symbol and the Add Transaction screen is presented, see below.

Where an item is matched to a transaction that has been already posted and reconciled, the right hand column displays the shield and tick icon for reconciled. Click the green OK button to confirm.

Where an item is matched to a transaction that has already been posted but not yet reconciled, the right hand column displays the green OK button. Click it to reconcile and confirm.

Where a processing rule exists the right hand column identifies the rule and describes the impact. To accept the processing of the rule click the green OK button. Where more than one matching transaction may exist click on the blue … symbol to pick from a list, if none apply then green OK button.

Note where the imported value could be allocated to outstanding documents such as invoices or bills. a list of documents is presented when the green OK button is clicked. If the user would prefer to not allocated to documents then click ADD, in the left hand column to enter a specific transaction.

Add Transaction

Select the Document Type if one is not being displayed. Many import file formats will contain the document type.

Select a 3rd Party from the drop-down lists If necessary a new customer or supplier can be created at this point. Simply click ADD and complete the dialogue making sure you correctly create a customer or supplier as needed.

For a purchase (payment) transaction a Reverse Charge VAT tick the box will be visible. Tick this if the transaction is subject to reverse charge. See the VAT user guides and consult with your professional advisor is you are unsure as the meaning.

Select an Account; if learned account codes are associated with the 3rd Party then the Accounts box will be pre-populated with the default. The user can amend the selection. A user can also create a new account by clicking Create Account.

If additional Line items are required to analyse the statement item over a number of accounts click the green + symbol to open up a new line. The total of the item amount may be allocated as absolute amounts or as a percentage by toggling the £|% symbols.

If appropriate the VAT Rate displays the default rate associated with the account. The user may amend as required; and the finally displayed rate will be used in any necessary computations.

Enter any analysis is needed, Branches or Employee for P11D Purposes in the fields shown.

To complete the processing of the item click SUBMIT. The details of the transaction will be posted and when all items in statement batch have been processed and the Finalise Import button clicked the item will be marked as reconciled.

Setting up and managing rules

When a statement item is being processed the opportunity exists for setting up the processing selections as a learned rule that will be applied whenever the rule search criteria identify similar items in future statement uploads.

When the item details have been entered, tick the 'Save as a rule' box. The rules dialogue is then displayed on the screen.

Enter a unique name for the rule in the 'Rule Name' field. The name need only be unique to this bank account. Enter or amend the search contents in the "Memo Contains' box and complete the processing of the item by clicking SUBMIT. The rule is memorised and the details of the transaction posted and when all items in statement batch have been processed and the Finalise Import button clicked the item will be marked as reconciled.

The 'Save as a rule' box is also visible on the Outstanding Documents dialogue.

Existing rules can be viewed, edited and if required deleted by hovering the cursor over the relevant bank account and from the sub-menu click the Rules. All rules in existence for this particular bank account are then listed. EDIT a particular rule to view the details and make changes if necessary. DELETE to remove it from application for all future imports.

In the edit rule dialogue make any changes required and when finished click SUBMIT to save.

The revised rule will be applied to any non-processed items remaining in the current process. Note it is possible to have multiple rules applying in a given situation depending on the various rules search criteria. As noted above a user will need to select which rule should be applied.

Access to Rules management is also available outside of an import processing activity. Navigate to the Accounts menu.

Accounts - Relevant Bank Account

Hover the cursor over the relevant bank account and from the sub-menu click 'Rules' to view all existing rules. Or "Add Rule" to add an additional rule.

Undo an Item before Finalisation

An item that has been processed may be reversed, provided it is prior to a finalisation, by toggling to the SHOW ALL view. All processed items are then visible, with an UNDO button displayed in the left hand column.

Click UNDO and the item is re-set to a non processed condition. If a transaction had been created previously it is deleted. Note both the creation and deletion are recorded in the Transaction Audit Trail.

Completion of an Import

Complete the processing of all items.

When all processing has been competed click the FINALISE button, this will mark all items as reconciled remove any unprocessed items from the list and confirm the batch details. A report of the process is available from the Reconciliation History menu.

Reconciliation History

A history of all reconciliation processes is available.

Accounts - Relevant Bank Account

Hover the cursor over the relevant bank account and from the sub-menu click the 'Reconciliation History' command.

A list of reconciliation batches is displayed. Any batch may be printed or deleted. Deleting a batch un-sets the reconciliation flag on each item that was in that batch. The delete action cannot be reversed except by re-reconciling the items.