Statement Centric Bank Reconciliation

Overview

Upload the bank statement and then match it to existing transactions

Use examples of bank lines matched to journals to create templates > so the system suggests journals you have not yet created - and will create them for you

After reconciling a journal may be adjusted in value - in this case the reconciliation flag remains however the values are highlighted in yellow

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

RHS is the detail from the system

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.

2. When re-importing day(s) for a bank rec, the system checks for grouped bank lines that include lines within the date range.

   1. If found, check if the groups include lines outside the date range.

      1. If found then the re-import cannot proceed.

      2. The system will Show a text box listing the group(s) and bank line(s) outside the date range.

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.

A rounding journal will be added only if a 1c difference exists in order to match a bank line and the batch value will show that it now matches the bank line.

Tyro Batches

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

1. All non AMEX cards - normally the same day or next day
2. AMEX cards - could be a number of days later

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

Unreconciling reconciled lines will split them again.

When creating a journal from a bank line (not via a journal template), on saving:

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

Right click to

• ignore and create a different journal
• open the proposed journal to edit then save (eg to create multi-line journal)

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

Manually finding lines to reconcile

1. Right click on a line with bank details only
2. will search the grid for our transaction matching amount and show actions to match the bank line with our journal or batch.

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

On lines with bank details but no matching journal, Right Click for options

1. If Bank Credit has an amount:
   1. Start a debtor payment
   2. Start a debtor prompt payment
   3. Start a bank transfer

Note also > a local currency bank account can have a debtor payment from a foreign currency

1. If Bank Debit has an amount:
   1. Start a creditor payment
   2. Start a creditor prompt payment
   3. Start a debtor refund
   4. Start a bank transfer

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

Right click on any matched line to use the journal that was used as a match to create a new Journal Template

The full Bank Description will be copied to the Rule Text. This can be edited and the Partial match check box ticked as required.

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.

Unreconciled lines that have a cleared date

A cleared date is put on the journal line when the line is reconciled in the bank statement.  It cannot be removed by a user.  This feature is an audit check and if you see this button please contact support.

When the Action menu option ‘List bank statement(s)’ is chosen then a check is done to see if there are any journal lines with

1. the selected bank GL account
2. and within the selected date range
3. that have a cleared date
4. but are not in any bank statement.

If any are found then a red button labelled ‘Cleared bank lines not in bank statement’ will show under the ‘Journal unreconciled;’ label.

1. Clicking this button will open the ‘G/L Health’ form at the ‘Cleared bank lines’ tab with the bank rec date range and bank account selected and the list of relevant journals showing.

G/L Health form (Admin menu, Audit, View G/L health checks), new ‘Cleared bank lines’ tab:

1. If no date range and no bank account are selected then all journal bank lines with a cleared date but not in a bank statement will be shown.

   1. Journals with a journal date before the date of the first bank statement for the selected bank account will not be shown.

   2. Right click with one or more lines selected to remove the cleared date from the selected journal line(s).

   3. Right click on a line to open the journal.

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