Transcript Bank Reconciliation Training

Bank Reconciliation
Introducing the BK
module in IFAS
DL Session Agenda






Introduction to BK
BK Process Overview
Key Features of BK
Preparing Your System
Using Bank Reconciliation
Function Overviews
Introducing Bank Reconciliation


The Bank Reconciliation module is designed
to provide a method for organizations to
reconcile monthly bank statements to their
own record of transactions.
This reconciliation involves comparing the
organization’s record of debits and credits to
the bank’s record of posted checks and
deposits.
Introducing Bank Reconciliation


Bank Reconciliation (BK) is a Windowsbased application that interacts with your
existing IFAS subsystems for Accounts
Payable (AP), Cash Receipts (CR) and
Payroll (PY).
Organizations using the BK system use their
existing AP, CR and PY systems for basic
data entry and Click, Drag, and Drill (CDD)
for reporting.
Introducing Bank Reconciliation


The BK module is then used to update check
and deposit information, track changes, and
match transactions reported by the bank to
those recorded in IFAS.
Additionally, BK provides an effective method
of tracking and processing year-end 1099
information.
Introducing Bank Reconciliation


Reporting from BK is handled exclusively by
CDD.
Standard CDD reports are delivered as part
of the BK system, making reporting on the
reconciliation process fast and easy.
BK Overview

There are five main menu options within the
BK module.
–
–
–
–
–
BK99 – 1099 Process
BKCS – Client Specific
BKDS – Distribution of Cash in Bank
BKUP – Transaction Update
BKUT - Utilities
BK Overview

BK99 – 1099 Processing
–
We will not be discussing this area in today’s
training session. It is usually handled in a
separate DL session.
BK Overview

BKCS – Client Specific
–
–
–
This selection offers some client specific options.
The interface for the Bank Tape written for each
specific client site will be located here.
We will not be discussing this topic in today’s
session.
BK Overview

BKDS – Distribution of Cash in Bank
–
–
This mask distributes deposits marked “In Transit”
to “In Bank” status.
It takes deposits that have been reconciled and
have a status of “In Transit” and creates a set of
these items changing the deposit status to “In
Bank”.
BK Overview

BKUP – Transaction Update consists of the
following functions:
–
–
–
–
–
BKUPBA – Update Bank Statement Info
BKUPCA – Review Date Maintenance
BKUPCD – Update Check/Deposit Info
BKUPMM – Mass Maintenance
BKUPRC – Bank Reconciliation
BK Overview

BKUPBA – Update Bank Statement Info
–
–
The bank statement screen displays information
about a particular bank statement that has been
uploaded to or manually entered into the BK
system.
The top portion of the screen contains general
information about the statement; the bottom
portion of the screen shows the detail of each line
item on the statement.
BK Overview

BKUPCA – Review Date Maintenance
–
The Review Date Maintenance screen allows
selected Check records to be updated with a
review date
BK Overview

BKUPMM – Mass Maintenance
–
–
The Mass Maintenance screen allows you to
query records held in the database.
Selected records may then be updated with a
chosen event date and notes relative to the
record update.
BK Overview

BKUPRC – Bank Reconciliation
–
–
–
The Bank Reconciliation screen allows you to
select and reconcile a bank statement.
Any bank items that do not reconcile
automatically will be displayed on a grid by
default, enabling you to selectively match them
with IFAS items.
You may also view automatically reconciled items
on this screen.
BK Overview

BKUT – Utilities consist of the following:
–
–
–
–
BKUTLB – Load Bank Reconciliation File
BKUTPU – Selectively Purge Transactions
BKUTSD – Stale Date Checks
BKUTUB – Undo In Bank Posting (BK Only)
BK Overview

BKUTLB – Load Bank Reconciliation File
–
–
–
This utility will convert a bank’s electronic
statement into IFAS BK reconciliation records.
The client is responsible for transferring the bank
file into the admin/data directory of the IFAS
server.
The bank’s file is described by a series of
common codes that must be defined prior to
running this process.
BK Overview

BKUTPU – Selectively Purge Transactions
–
Allows the user to purge transactions by entering
selection criteria that is used to identify the
transactions to be purged.
BK Overview

BKUTSD – Stale Date Checks
–
Allows the user to stale date checks based on
criteria that is defined in the common code CKID.
BK Overview

BKUTUB – Undo In Bank Posting (BK Only)
–
Will undo the distribution of cash in bank
performed using the BKDS menu mask.
Key Features of BK





Integrated with AP, CR and PY systems.
Supports an unlimited number of bank
accounts.
Import electronic bank statements for
reconciliation purposes.
Record separate dates for all events in the life
of a check – issue, cancellation, reversal,
appraisal, etc.
Supports Consultant 1099-MISC forms.
Key Features of BK

Standard Reports available include:
–
–
–
–
–
–
–
–
–
–
–
Vendor Check Summary
Bank Statement Listing
Unclaimed Funds by Account
Consolidated Check Register
Deposit Listing – Detail
Deposit Listing – Summary
Bank Reconciliation Document History Report
Consolidated Exception Report
Outstanding Checks
Payee Report – Short Format
Payee Report – Verbose Format
Key Features of BK

BK stores more than checks, so each entry is called
a “document”.

There are currently 6 supported document types:
–
–
–
–
–
–
CHK - check
DEP - deposit
EFT - electronic funds transfer (payment)
FEE - bank fee
INT - interest
ADJ - adjustment
Key Features of BK

The document number is the reference for the entry.

This could be a check number, deposit slip number,
or any other unique reference.

The document number must be unique within the
document type for a given check stock ID.

For example, you can have only one check (CHK)
with number 00123456 for check ID “AP”.
Preparing Your System

Generic Bank Reconciliation Common Codes
–
–
–
–
These codes tell the system how to handle the
bank statements being imported into IFAS using
the function BKUTLB.
Only works with fixed length ASCII files.
There can only be one account per bank file.
If the file has header and/or trailer records, the
client must delete all blank records prior to the
header record and after the trailer record.
Preparing Your System

Codes are set up using NUUPCD
– The codes that must be set up are:





BKRC/xxCHK – Defines the check number position within the
bank file.
BKRC/xxCKDATE – Defines the check date position within
the bank file.
BKRC/xxCKAMT – Defines the check amount position within
the bank file.
BKRC/xxGEN – Defines the general characteristics of the
bank file.
BKRC/xxCHKNUM – Defines the check number position
within the bank file. Will be right-shifted and zero filled to
convert.
* Each common code has the check stock as the first two characters of the
code value. This is represented by the ‘xx’ in the settings above. Example:
BKRC/APCHK
Preparing Your System

Additional Common Codes:
–
–
–
BKRC/xxDEP – Converts bank recon file deposit code(s)
into BKR-TYPE of ‘DEP’. Optional if bank file and/or client
does not support deposits.
BKRC/xxADJ - Converts bank recon file adjustment
code(s) into BKR-TYPE of ‘ADJ’. Optional if bank file and/or
client does not support adjustments.
BKRC/xxTRANNO – Defines unique transaction reference
position within the bank file for non-checks. If this unique
reference is not provided in the bank file, define seed code
in Medium Description so a unique reference can be
derived. (Required only if non-checks are to be reconciled.
Often uses same position as check number.)
Using Bank Reconciliation

The basic steps involved in the bank
reconciliation process are:
–
–
–
–
Data Entry
Updating and tracking changes
Reporting
Reconciling bank records to database records
Using Bank Reconciliation

Data entry for BK is performed through three
IFAS subsystems:
–
–
–
Accounts Payable (AP)
Cash Receipts (CR)
Payroll (PY)
Using Bank Reconciliation

When a check is printed in AP or PY, an entry
is placed in the BK database.
–
–
–
Check information is updated from AP or PY
whenever a check is reprinted or reversed.
BK keeps track of all checks written and
references Amounts for individual checks.
In addition to a Check Number and Invoice
information, a Check Type code is attached to the
record to identify how the check was generated.
Using Bank Reconciliation

Deposit information enters BK through the
posting of IFAS Cash Receipt batches.
–
–
Information is stored at the deposit slip level for
reconciliation purposes.
Cash receipt information tied to each deposit slip
will also be stored in a detail table in the BK
module.
Using Bank Reconciliation

Updating and tracking changes
–
BK also provides a method for directly updating
records after they have been entered into the
database.

–
This function must be carefully controlled.
Three functions are primarily used for updating
deposit and check information in the database:



BKUPCD – Check and Deposit Information
BKUPMM – Mass Maintenance
BKUPCA – Review Date Maintenance
Function Overview – BKUPCD
Function Overview – BKUPCD

To browse records stored in the database and
make necessary changes or updates, select
BKUPCD from the main menu to access the
IFAS Check and Deposit Information screen.
–
–
This screen displays information relative to a check
or deposit slip entry and acts as a “check register”.
View invoices related to a check, and cash receipts
related to a deposit on the lower portion of the
screen.
Function Overview – BKUPCD


In addition to viewing data generated from within IFAS,
use this screen to Add a new record, or update a
record after it has already entered the database.
The ability to add or update a record is available
primarily for three purposes:
–
–
–
Enter check and deposit information during system
installation.
Enter information from small outside sources that have no
means of providing electronic data.
Make minor adjustments such as noting 1099 information or
updating Event Dates or Check Status.
Function Overview – BKUPCD

The Main Window provides basic information
about the document such as:










Bank ID
Document Type
Document Number
Payee ID
Issue Date
Subsystem
Check Type
Amount
Status
Job Number
Function Overview – BKUPCD

Details Tab – Displays the details of the
document.
–
–
If a check written to a vendor references several
invoices the check will appear as one record in
the main section but details of each invoice will
appear on the Details tab.
When a deposit is made, it may be made up of
multiple cash receipts. The main tab will display
the deposit information but details of each cash
receipt will appear on the Details tab.
Function Overview – BKUPCD

Notes Tab – Notes can be attached to each
record by clicking on the Notes tab.
–
–
–
Notes can be added either on this screen or on
the Mass Maintenance screen.
Notes added on the Mass Maintenance screen
will be displayed here.
To save text, press <SHIFT> <ENTER>.
Function Overview – BKUPCD

Associations Tab – Used to record optional
information about a BK document where there
is no standard field available.
–
This tab offers a field for each of the three most
common types of values:



–
Character
Numeric
Date
Each association record is identified by its code
value.
Function Overview – BKUPCD

Address Tab – Used to store the name and
address associated with a BK document.
–
When IFAS creates a check entry the name field
is populated with the name of the payee and the
address on the check.
Function Overview – BKUPCD

Status Dates Tab – The Status Dates are
event dates in the life of the document.
–
These dates can be directly updated on this
screen or by using the stale dating utility or the
mass maintenance option.
Function Overview – BKUPCD

Recon Tab – This tab displays reconciliation
and tracking information
–
–
Once a bank statement has been reconciled to a
document in the BK system, this page will display
information about the reconciliation process as it
pertains to this particular record.
This information is generated by the reconciliation
process and is attached at the time it is created.
Function Overview – BKUPCD

Audit Tab – The first three fields on this tab
work in conjunction with the Deposit Status
field.


–
When a deposit status is flagged as Cash in Transit, a
process may be run (BKDS) to change the status to
Cash in Bank.
These fields are updated only when this process is
used.
The last six fields on this tab are to track
information regarding checks that have been
reissued and are updated directly on this screen.
Function Overview - BKUPMM
Function Overview - BKUPMM



Used to update the event dates and
optionally attach notes to the updated
records.
Allows the user to query records in the
database using a record filter.
The chosen records can then be updated
with the chosen event date and any notes
the user wishes to add to the records.
Function Overview - BKUPMM

The record filter at the top of the screen
allows you to select the records you wish to
update by entering selections into the
following fields:





–
Check Number or Deposit Slip Number Range
Bank Code
Document Type
Check Status
Amount >= and <=
Entering information into any one or more fields
will identify the selections to be returned.
Function Overview - BKUPMM


The other two fields on the screen allow you
to select the event dates that need to be
updated and specify what new event date is
to be attached to the selected records.
After entering the desired information, click
Select and the records that match the criteria
selected will be presented in the display
window in the middle of the screen.
Function Overview - BKUPMM



Verify that the displayed records are correct.
To attach notes to the records, enter
information into the Notes section below the
record display.
Click Save to actually update the selected
records with the new information.
Function Overview - BKUPCA
Function Overview - BKUPCA





Allows multiple records to be selected and
updated with a review date.
This screen is used to set the date the
checks were reviewed and approved for
payment.
Select the Bank ID from the drop-down list.
Select the appropriate review date.
Checks that have not been reviewed will be
displayed in the grid.
Function Overview - BKUPCA

Click Select All to select all the records or
select them individually by clicking in the
selected column of the record.
–

To remove a selection click again in the selected
column.
Once the displayed records reflect the
checks approved for payment, click Save to
update the records with the review date.
Function Overview - BKUPBA
Function Overview - BKUPBA




This screen is used to view bank statements
and make minor adjustments.
It displays information about a particular
bank statement that has been uploaded to
(or manually entered into) the BK system.
The top portion of the screen contains
general information about the statement.
The bottom portion shows the detail of each
line on the statement.
Function Overview - BKUPBA


Data on this screen is generated from
loading and reconciling bank statements.
When no bank tape is available, or when
changes need to be made in order to
reconcile, data can be keyed directly on this
screen.
Reconciliation Process

1) Load your bank statement into IFAS

2) Auto match the “easy” ones

3) Manual match the “close” ones

4) Handle exceptions
Reconciliation Process

Load Statement
–
–
–
Bank statements can be loaded manually
or through an interface.
Manual method means typing cleared
check & deposit information into BKUPBA
Interface method means obtaining file from
your bank and providing it to the interface.
Reconciliation Process


Bank statements are identified by a check
stock ID and the statement date.
You are not limited to monthly reconciliation you can follow this process as often as your
bank can provide the information.
Reconciliation Process

Auto Match
–
–
When you start the reconciliation process, the
system will automatically link the bank entry with
the IFAS entry when there is an exact match
between type, number, and amount.
For example, if both the statement and IFAS show
check (CHK) number 00123456 for $100.00, then
the IFAS record will be marked as “matched”.
Reconciliation Process


The reconciliation process is started with
menu mask BKUPRC.
The first step is identifying which open
statement you wish to work with.
Reconciliation Process


After choosing the
check stock ID, the
window shows you a
list of open
statements.
Click on a statement
and then click OK.
Reconciliation Process
Reconciliation Process

The following sample bank statement has 5
entries:
–
–
–
–
–

Check 00123456 (matches IFAS exactly)
Check 00123460 (mismatched amount)
Check 00123477 (exact match)
Check 00123499 (not in IFAS)
Deposit BS0001 (mismatched number)
There is another check 00123488 that is in
IFAS, but has not been cashed yet. (still
outstanding)
After selecting the statement, the screen loads all bank records and only
IFAS records where the type and number are present in the bank statement.
Reconciliation Process

Three IFAS records were loaded because only three of
the five had matching types & numbers.
–
The sample bank statement has 5 entries:






Check 00123456 (matches IFAS exactly)
Check 00123460 (mismatched amount)
Check 00123477 (exact match)
Check 00123499 (not in IFAS)
Deposit BS0001 (mismatched number)
The screen does not load all outstanding
checks/deposits because that could be 1000’s of
records!
From these loaded IFAS records, those that
exactly match by type, number, and amount will
be “auto matched”. Matched records are
indicated by a check mark.
Reconciliation Process


When you “save” the reconciliation, all
matched entries will be marked as “cleared”
and will no longer appear on the outstanding
report.
Hopefully most of the entries on the bank
statement are auto matched, so there is less
manual work to do!
Reconciliation Process

Manual Matching
–
The deposit in our example was not auto matched
because the bank had a different reference
number than IFAS.


–
IFAS Bank Slip: BS0001
Statement Ref: 3574251
The easy way to find the deposit in IFAS is by
amount.

Essentially we are telling the screen: “Find all
outstanding IFAS deposits for $5000”
Result
•Click on the bank’s deposit to select it
•Change the filter to “Amount”
•Enter the dollar amount in the search field
•Click “Search”
Clicking the “Match” button manually links the
two entries together.
Reconciliation Process

Exceptions
–
There are only two bank entries left to deal with:


check 00123460 = mismatched dollar amount
check 00123499 = not in IFAS
•First, we need to locate the corresponding IFAS entry
for the $101 check on the bank statement.
•Select the check in the bank statement, and choose a
filter of “Type and Number”. This will show all IFAS
records that have the same type and number as the
record you selected above. (note: there should never
be more than one!)
•Before manually matching the entries, enter
the amended amount on the bank statement.
•Then when you match them, the system will
record the $100 override amount and use that
amount in the totals.
Reconciliation Process

To correct check 00123499 that is on the
statement but not in IFAS:
–
–
First, need to determine if it SHOULD be in IFAS
(possible fraud). If not, do not match the entry work with bank to clear up issue.
Or… maybe someone forgot to record a hand
written check? If so, save your work here, get the
check recorded, then return and finish reconciling.
Reconciliation Process


At any point in the reconciliation process you
can save your work and continue later - just
click the “Save” button before closing the
screen.
When you are done with a statement, click
the “Approve” button instead - this saves
your work and marks the statement as
“done”.
BKUPBA
Matched bank records are linked with the IFAS record
BKUPCD
The IFAS record is linked to the statement...
BKUPCD
…and the status is set to “CX” and the cancel date
is filled in.