bank reconciliation introducing the bk module in ifas

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.


Bank Reconciliation (BK) is a Windows-based 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.


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.


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.


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


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


Data entry for BK is performed through three IFAS subsystems:– Accounts Payable (AP)– Cash Receipts (CR)– Payroll (PY)


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.


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.


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


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.


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.


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


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.


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


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.


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.


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.


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.


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


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.


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.


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.


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


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.


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


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.


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


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.


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.


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


The reconciliation process is started with menu mask BKUPRC.

The first step is identifying which open statement you wish to work with.


After choosing the check stock ID, the window shows you a list of open statements.

Click on a statement and then click OK.


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.


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.


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!


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.


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.


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.


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

Matched bank records are linked with the IFAS record

BKUPBA

BKUPCD

The IFAS record is linked to the statement...

BKUPCD

…and the status is set to “CX” and the cancel date is filled in.

bank reconciliation introducing the bk module in ifas

Documents

bank status

bank items

bank tape

bank reconciliationintroducing

bank reconciliationreporting

particular bank statement

bk system

bk overviewthere