electronic check reconciliation

Electronic Check Reconciliation

NERDug 2005 – July 28-29 – Hampshire College

Wendy O'Rourke – Hampshire CollegeJeff Butera – Hampshire College

Session 10630July 28, 2005

3:00PM - Room 102

Outline

NERDug 2005 – July 28-29 – Hampshire College

●High Level Overview of Process●Low Level Look at Details●One-Time Setups to Implement●Walk Through (end to end)

High Level Overview

NERDug 2005 – July 28-29 – Hampshire College

●Get data from bank monthly●Copy onto Datatel server●Import data into Colleague●Run batch reconcile●Run reports●All done!

Lower Level Details

NERDug 2005 – July 28-29 – Hampshire College

●Check data from bank varies:●timing●format●media

Contact your bank for details.

Lower Level Details

NERDug 2005 – July 28-29 – Hampshire College

●Copy data to server:●Many methods●FTP●Network drive

Whatever works best for you.

Lower Level Details

NERDug 2005 – July 28-29 – Hampshire College

●Run batch reconcile (the fun part)●CF - LBRT (AP checks)●HR – BKRI (PR checks)

Watch how much faster it is!

Lower Level Details

NERDug 2005 – July 28-29 – Hampshire College

●Run reports●Datatel has some canned reports●We had others we added

It's all what you're looking for.

One-Time Setups

NERDug 2005 – July 28-29 – Hampshire College

●For AP checks:●CF subroutine S.LOAD.AP.CHK●Will need to customize (bank-specific)●Subroutine reads/parses bank data

One-Time Setups

NERDug 2005 – July 28-29 – Hampshire College

●AP Bank Setup●CF – AP – APC – BKCD●Setup any bank(s) and/or account(s)●Populate subroutine name:Reconciliation Tape Block Size.: 1 Reconciliation Tape Record Size: 1 Reconciliation Tape Subroutine.: H08.S.LOAD.AP.CHK

One-Time Setups

NERDug 2005 – July 28-29 – Hampshire College

●For HR checks:●Create an ELF spec●Datatel provides INTER.BANK.RECON●ELF requires check issue/paid dates (fake it)

One-Time Setups

NERDug 2005 – July 28-29 – Hampshire College

●GEN your ELF spec●On CF – BKRR enter:

Custom ELF Specification: H08.RECON.MAP

Description: 1: RECON FILE File Type..: VD Variable Disk Directory....: /datatel/live/collive/H08.DATA/_CHECKS_

●Enter a place for bank files on ELFT:

Walk Through

Walk Through

Walk Through

Walk Through

Walk Through

Walk Through

Walk Through

Walk Through

Walk Through

July 25 2005 ELECTRONIC CHECK RECONCILIATION REPORT08:22 Check ID Amount Reconciled Error Message---------- -------- ---------- --------------------------01*0030551 100.00 No This check does not exist01*0076208 500.00 Yes01*0076209 2,000.00 Yes01*0076210 183.49 Yes01*0076211 650.00 Yes01*0076212 23.55 Yes01*0076214 15,555.43 Yes

Walk Through

RECONCILED 030105 thru 033105 Curr CurrentCHK.DATE CHECKS. CHK.MISC.NAME..... CHK.AMOUNT... Stat Stat Date 09/10/03 0076073 Payee name here 94.89 R 03/22/0502/22/05 0076208 Payee name here 500.00 R 03/31/0502/17/05 0076209 Payee name here 2,000.00 R 03/31/0502/18/05 0076210 Payee name here 183.49 R 03/31/0502/22/05 0076211 Payee name here 650.00 R 03/31/0502/24/05 0076212 Payee name here 23.55 R 03/31/0502/25/05 0076214 Payee name here 15,555.43 R 03/31/05 =============TOTAL 1,157,811.78

Walk Through

OUTSTANDING 033105 Curr CurrentCHK.DATE CHECKS. CHK.MISC.NAME....CHK.AMOUNT... Stat Stat Date 02/24/05 0076213 Payee name here 57.05 O 02/24/0503/28/05 0076221 Payee name here 2,002.00 O 03/28/0501/07/04 0418847 Payee name here 219.22 O 01/07/0401/28/04 0419373 Payee name here 50.00 O 01/28/0401/30/04 0419488 Payee name here 65.00 O 01/30/0402/18/04 0420054 Payee name here 5,600.00 O 02/18/04 =============TOTAL 925,834.49

Walk Through

Walk Through

Walk Through

Walk Through

Walk Through

Walk Through

Walk Through

July 25 2005 Electronic Bank Recon Import Report EP Employee Payroll Check Issued Paid PaidNumber Date Date Payee ID and Name Amount ELF Batch------ -------- -------- ----------------- ---------- ---------019199 03/01/05 03/01/05 4,388.57 033105EP 019230 03/02/05 03/02/05 819.57 033105EP019281 03/02/05 03/02/05 545.80 033105EP019295 03/02/05 03/02/05 823.12 033105EP019298 03/08/05 03/08/05 917.50 033105EP =====================Total Amount and Count 144,749.33 183

Walk Through

Walk Through

Walk Through

Walk Through

Bank Code Check Date Check No. Amount Status Status Date EP 06/27/03 015088 776.75 O 06/25/03EP 07/25/03 015346 310.78 R 09/30/20EP 07/25/03 015385 30.00 O 07/23/03EP 08/08/03 015491 249.14 R 09/30/20EP 08/08/03 015518 258.58 O 08/06/03EP 01/21/05 019115 125.93 O 01/19/05 ===========TOTAL 173,532.39

Walk Through

Bank Code Check Date Check No. Amount Status Status Date EP 01/28/05 019199 4,388.57 R 03/01/05EP 02/04/05 019230 819.57 R 03/02/05EP 02/18/05 019325 475.75 R 03/15/05EP 02/18/05 019334 513.91 R 03/17/05EP 02/18/05 019336 136.67 R 03/22/05 =========TOTAL 10,112.38