Statement Centric Bank Reconciliation

Overview

Upload the bank statement and then match it to existing transactions

...

A single line in the bank can be matched to multiple transactions in the system by putting all the related journals into a batch.  This happens automatically for credit card related batches.

FX Bank Accounts and related transactions

...

Bank Reconciliation Example

Upload of a bank statement

Example of xls sheet

Reconciliation view

LHS is the detail from the bank

...

1. Displays all journals:
   1. that have been allocated to a bank line within the date range OR
      1. are not in a batch
      2. that are before the from date and have not been reconciled
      3. That have NOT been reversed (Journals reconciled in the bank cannot be reversed until reconciliation removed)
      4. have not been cleared in an old style bank rec
2. Display all batches:
   1. that have been allocated to a bank line within the date range OR
      1. that are before the from date and have not been reconciled
      2. have not been allocated to a bank line outside the date range
      3. have journals that have not been cleared in an old style bank rec
3. Displays all Bank statement lines that are
   1. within the date range
   2. before the from date and have not been reconciled.

Process Flow

Statement-centric bank Account reconciliation upload

...

1. Importing a statement file from the bank:
   1. The file must be an xlsx or csv format file.
   2. It must have column headings in row 1, containing (Column headings are not case sensitive) :
      1. narrative OR description
      2. date
      3. debit
      4. credit
      5. balance (optional)
   3. The balance on the first data row + debit - credit must match the balance on the last row of the previous import for the host/bank account, unless it is the first import.
   4. the date on the first import line must be after the last bank line Bank Date
   5. Drag the statement file over the Statement-centric bank rec form and drop it.
   6. Reply Yes to upload the file.
   7. Action menu
      1. 'Import bank statement'.
      2. 'List bank statement(s)'.

Overwriting a statement import

1. Action menu option, 'Re-import bank statement days':
   1. Must have uploaded a spreadsheet or .csv file of bank statement lines.
   2. The balance on the first import line, if any, plus the Bank Out-DR or minus the Bank In-CR of the line must match the balance on the last bank line dated before the Bank Date of the line, if any.
   3. The balance on the last import line, if any, must match the balance on the first bank line dated after the Bank Date of the line, if any, plus the Bank Out-DR or minus the Bank In-CR of the line.
   4. All existing bank lines between and including the date of the first import line and the date of the last import line will be deleted before re-importing the uploaded bank statement lines.

Batches of Journals

Journal batches are common - example credit card batches where an aggregator deposits the result into the bank at the end of the day from a POS.  In this case all the journals as they are created are linked to the batch.

Reconciling the batch will reconcile all the journals in that batch.

Tyro Batches

Tyro payment settlements are made in two amounts for a single day

...

If the Tyro machine takes some offline transactions then the amount expected will be different to the amount paid.  The matching process will find dates that align with amounts that do not so the missing journals can be added to the batch before it is matched off.

Adding a journal to the POS Tyro Batch

User will require to either swipe their thumb or use initials and pin 

Can create a credit card refund linked to a closed batch.  Options available American Express, EFTPOS, Mastercard, Visa.  American Express will reduce the Amex total for the batch,  others will reduce the tyro total.

Retail Takings Bank Deposits

Some organisations bank cash daily for the previous day and the full amount each time.  In this case the expected bank deposit journal will have been created as the POS is closed in the following day ready to be reconciled.  Right click to open and change it if required.

Reconciling statement lines

1. If a line has been reconciled the Reconcile box will be ticked and the matching journal or batch details will be shown
   1. Lines in the bank statement may reconcile with individual journals or with the total of a journal batch
2. The journal or batch can be opened from a right click on the line
3. Likely matches are listed beside the bank statement line details

Rules Engine

The rules engine proposes the most likely match or template to the user that can be actioned, rejected or ignored.

Right click options offered to the user

1. Right click on a line with bank details only
   1. will show matching amounts with different dates in the grid as possible matches - select to action (match and reconcile)
   2. Will provide option to create a new journal from scratch
2. Right click on a line with our details only
   1. will show matching amounts with different dates in the grid as possible matches - select to action (match and reconcile)
   2. Will provide option to create a new journal from scratch

...

1. If the bank line has been reconciled or re-imported then a message will display and the journal form will not close.
2. If the amount of the journal going to the bank does not match the bank line amount then a message will display and the journal form will not close.
3. Otherwise:
   1. The new journal will be reconciled with the bank line.
   2. All journal lines to the bank will be given a cleared date of today.
   3. The journal form will close.

How are matches made by the rules engine

Example of a match with a batch

Example Tyro match of closest batch

Ticking the Reconcile Box

1. Ticking the Reconcile Box will:
   1. Link the journal or batch to the bank statement line.
   2. Write today's date as the cleared date for all journal lines to the bank account in the journal or batch, unless the line already has a cleared date.
2. Un-ticking the Reconcile Box will:
   1. Unlink the journal or batch from the bank statement line.
   2. Remove the cleared date for all journal lines to the bank account in the journal or batch, unless the date is on or before the last original bank rec reconciled date for the host/bank account.
3. The Action menu includes an option to 'Reconcile all lines with matching amounts'

Mass tick using Action Button > Reconcile all lines with matching amounts

Accepting proposed Journal templates

Ticking the reconcile box will create the journal

...

Mass create using Action Button >Create and reconcile Journals from Rules

Manually finding lines to reconcile

...

1. Right click on a line with our details only
   1. will search the grid for lines with bank details only and a matching amount and show actions to match the bank line with our journal or batch.
2. Choosing to match will merge the lines and tick Reconcile.
3. Unreconciling these lines will split them again

Adding new transactions to match the bank

...

1. Each option will open the appropriate form and pre-populate it with Bank Date, the selected Bank Account and the Bank Debit or Credit.
2. When the journal is saved the Bank Statement list will be refreshed.

Creating New Journal Templates

...

The result when the grid refreshes

Grouping Bank Statement Lines

In some situations bank statement lines are desired to be grouped either for reconciliation reasons - or to hide the detail (eg salary payments).

1. Bank Reconciliation (Statement-centric):
   1. Any bank lines that are in a group will not show in the bank rec.
   2. The group will show on a line with:
      1. 'Group' in the Bank Balance column.
      2. The group Date in the Bank Date column.
      3. The group Description in the Bank Description column.
      4. The sum of the debit amounts of the bank lines in the group in the Bank Out-DR column.
      5. The sum of the credit amounts of the bank lines in the group in the Bank In-CR column.
   3. The group line will have all of the same functionality of a bank line:
      1. Match to an existing journal or batch.
      2. Be reconciled to an existing journal or batch.
      3. Create a journal according to the DR or CR amount.
      4. Create a new Journal Template (bank rec rule).
      5. Show a draft journal according to a bank rec rule.
2. If the user has secure feature Secure Features#CanPerformBankReconciliation with a qualifier greater than 0:
   1. On a bank line that has not been reconciled and is not a group, new right click option 'Add to new group':
      1. A new group will be created, with the Date and Description from the bank line.
      2. The bank line will be added to the group.
      3. A popup window will show the group and list the bank lines in the group.
   2. On a bank line that has not been reconciled and is not a group, new right click option 'Add to Date Description':
      1. One option for each unreconciled group shown in the bank rec.
      2. Multiple groups will be listed in order of the number of days difference between the bank line date and the group date.
      3. The bank line will be added to the group and contribute to its amount.
   3. On a group line a new right click option 'Show bank transaction group':
      1. A popup window will show the group and list the bank lines in the group.
   4. In the group popup window:
      1. The Date and/or Description for the group can be changed, and click Save in the popup.
      2. If the group has not been reconciled then a new right click option 'Remove from group'.
3. Right click option 'Delete all bank lines from here on' is not available if any of the lines that would be deleted are in a group. Remove from the group first.

Changing from the non-statement centric (old method) Bank Reconciliation process

1. If there is a LOCKED old method bank reconciliation for a bank account (ie one that is active)
   1. then no journal or batch can be reconciled for the same host/bank account.
   2. Close or delete the LOCKED old method bank reconciliation

...