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.

Bank statement upload process in summary

1. Set the Bank import format on the bank account configuration screen
2. Browse for 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.

• Set the bank import format
• Importing a Bank Statement
• Processing an uploaded statement
• Review Import Item Dialogue
• Refresh the search for an Item
• Outstanding Documents
• Setting up and Managing Rules
• Undo an Item before Finalisation
• Completion of an Import

Set the bank import format

The import format of the bank statement(the format that the relevant bank outputs its file) needs to be set. This is done via the Bank Account configuration.

Bank - Relevant Bank Account - Configure 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 symbol click Configure Account

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

On the Account Setup Tab select the relevant Bank Import Format from the Bank Import Format drop-down.

Click SUBMIT to store the changes to the setup

Importing a Bank Statement

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

Bank - Relevant Bank Account - Process Import

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

or

Accounts - Bank/Credit Accounts - from the action box symbol click Statement Import

The Bank Import screen is presented.

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

Processing an uploaded statement

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 :-

• 60 days
• 90 days
• 90+ days

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.

Automated processing of rules

When the system can determine that the application of a rule for a particular transaction is Unambiguous the item is automatically processed as reconciled and does not appear in the list.

By automatically applying the rules there will be fewer key strokes required by the user when completing the import i.e. you won't have to click OK to confirm the application of each rule. You can review what has been applied by clicking 'Show All' at the bottom of the page and, if you see something that you want to be recorded different then you can UNDO the application. All auto-applied rules are shown in a salmon colour.

It remains the case that nothing is recorded on to the system until you FINALISE the import.

Where there is any possible conflict or a user decision that has to be made then the rule won't be auto-applied. Possible cases are as follows:

• the rule is to apply a payment against a supplier bill
• the rule is to apply a receipt against a customer invoice
• a system transaction is identified that may be a manual entry for the intended rule i.e. to avoid possible duplication more than one rule applies to the imported transaction

Where a user decision is required the user will be required to click on the action box symbol to make their selection.

For any other transaction where there is no rule in place the the standard procedure of adding as a new transaction or matching to a manually entered transaction still applies.

This will speed up processing for the situation where there are many rules in place.

Processing statement items

Where items are automatically processed as above, they are not listed in the screen.

When an item has been processed it disappears from the screen. Note to show all items that have been dealt with toggle 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 blue ADD 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. If the user is certain that is the case click the green OK button to confirm. However as it is more likely to be a different item but with the same value, inspect the date and click ADD as an item to reconcile.

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 counterparty 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 counterparty 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.