Wiki

Case Status
Register Log In

Wiki

 
Bank Transaction Import & Reco…

Bank Transaction Import & Reconcile Process

The Bank Transaction Reconciliation is a six step process as shown below.

This reconciliation process can be performed as frequently as required. This could be daily, weekly or monthly.

1. Enter Expense and Income Entries in Insight

On a daily basis, enter details of the financial transactions that are unlikely to identifiable from the Bank Statements. For example, Cash purchases  or invoices generated from Insight to your customers.

2. Import Receipt-Bank.com Receipts 

If you have a Receipt Bank subscription, you can scan in your receipts and invoices and export the data as CSV in the default format and Import into Insight.

The CSV file should be in the following format:-

If the type (column 2) is Receipt, the transactions will be imported with the Invoice and Payment dates set to the Date Column (column 3). The payment method will be set to "Other". The payment method and Payment reference will be updated during the reconciling process when selecting a bank transaction to reconcile against.

The Date Column format can be in either dd-MMM-yy (eg. 15-Jul-19) or dd-MMM-yyyy (eg. 15-Jul-2019)

If the type is Invoice it will imported with only the Invoice Date set to the date column and the Insight entry will be marked for attention. The payment details will be updated during the reconciling process when selecting a bank transaction to reconcile against

3. Import Payroll data

Each time you process a payroll run in SAGE (or other payroll software), you can export that data into a CSV and import into Insight using the Import Payroll Data option. This will create the following transaction in Insight

- Wages transaction for each employee

- Employee NIC

- Employer NIC

- Employee Pension Contributions

- Employer Pension Contributions

- Student Loan and other PAYE Deductions 

When importing, it can optionally upload the Payslip created in SAGE (or other payroll software) to the Insight Employee Portal.

4. Download and import the Bank Statement in a CSV format.

The bank formats currently supported by Insight are:-

  • Natwest Business Banking
  • Natwest Corporate Banking
  • Barclays
  • Lloyds 
  • HSBC Business Banking

Follow the on screen Import Wizard instructions to select the file and view the bank statement entries 

This will only import the bank transactions into Insight and allow for reconciliation against entries already in Insight and allow you to create new entries  in Insight using the bank statement data.

When importing data from the Bank Statement, the supplier/client name is determined from the narrative lines on each bank statement entry. You can setup mappings to automatically link certain descriptions to certain suppliers/clients. See Bank Statement Import Patterns Mapping for more details.

5 & 6. Reconcile Debits & Credits

Once the bank statement entries have been imported, you can reconcile all the non-reconciled debit and credit entries using the "Bank Transactions Reconciliation - Debits" and "Bank Transactions Reconciliation - Credits" screens.

Both screens are designed in the same way, with ALL non-reconciled Insight Entries on the left hand side and ALL non-reconciled Bank Statement entries on the right hand side.

Reconciling existing Insight entries

If an entry has already been created in Insight, use the Tick Boxes to select the matching Insight and Bank Statement Entries and then click the "Reconcile ticked entries button". This will mark these these entries as Reconciled , but will NOT create any new records in Insight.

If the Insight transaction(s) being reconciled against the bank transaction(s) don't have the payment date this will be updated as part of this reconciliation process.

The Insight transactions being reconciled will have their Payment Method and Payment date updated to those of the selected bank transactions.

Creating new Insight entries using the statement transaction information

If an entry has NOT been created in Insight (eg. Direct Debit), use the Tick Boxes on the Statement Entries section to select the Bank Statement entries and then click the "Create New Entry". This will create a new Insight Expense or Income entry using the information from the Bank statement. 

 

Banks Transactions shown on left hand side

By default, only Debits (for Reconcile Debits screen) or Credits (for Reconcile Credits screen) Bank statement entries are displayed . You can select to view all non-reconciled Bank Transactions by changing this toggle switch.

For example, this could be useful if you have a single  Expense entry in Insight for £100, but there's a debit entry for £120 and credit entry for £20 relating to the same Insight entry and you want to mark both the credit and debit entry as reconciled.

Once you've reconciled the Credits or Debits and you've created new Insight entries, you'll be presented with a summary screen showing the NEW entries being creating in Insight. You can double-click an entry to view/update .

Click Complete Batch to finalise the process.

 

Notes

Auto-Reconciliation

1. Cash Payments 

Cash Payments made are auto reconciled when the weekly summary sheet is marked as reconciled

 

2. Cheques

Cheques are auto-reconciled  based on Cheque number, value and date

3. Auto-Reconcile Screen

From the Bank Transaction Reconciliation screen, you can reduce the number of transactions that you have to manually reconcile by using the Auto-Reconcile feature . This is available for both Expenses/Debits and Income/Credits.

The screen shows all the transactions a page at a time that have been identified as suitable for auto-reconciliation. The rules are to determine is they are suitable for auto-reconciliation are:-

a. Same Value 

AND

b. Dates within 5 days of each other

AND

c. Same Organisation when identified on both Insight and Bank Transaction entries. 

OR

d. If Organisation not identified on Bank Transaction

When the dates or the Organisation names are different, those cells are highlighted for you to double-check

You can select the transaction to reconcile by ticking the checkbox on the left hand side. You can select all entries on the page using the checkbox in the header.

Once the transactions have been selected, click the "Reconcile & Next Page" to process this page and move on to the next page.