340 likes | 493 Views
Electronic Check Reconciliation. Wendy O'Rourke – Hampshire College Jeff Butera – Hampshire College Session 10630 July 28, 2005 3:00PM - Room 102. NERDug 2005 – July 28-29 – Hampshire College. Outline. High Level Overview of Process Low Level Look at Details
E N D
Electronic Check Reconciliation Wendy O'Rourke – Hampshire College Jeff Butera – Hampshire College Session 10630 July 28, 2005 3:00PM - Room 102 NERDug 2005 – July 28-29 – Hampshire College
Outline • High Level Overview of Process • Low Level Look at Details • One-Time Setups to Implement • Walk Through (end to end) NERDug 2005 – July 28-29 – Hampshire College
High Level Overview • Get data from bank monthly • Copy onto Datatel server • Import data into Colleague • Run batch reconcile • Run reports • All done! NERDug 2005 – July 28-29 – Hampshire College
Lower Level Details • Check data from bank varies: • timing • format • media Contact your bank for details. NERDug 2005 – July 28-29 – Hampshire College
Lower Level Details • Copy data to server: • Many methods • FTP • Network drive Whatever works best for you. NERDug 2005 – July 28-29 – Hampshire College
Lower Level Details • Run batch reconcile (the fun part) • CF - LBRT (AP checks) • HR – BKRI (PR checks) Watch how much faster it is! NERDug 2005 – July 28-29 – Hampshire College
Lower Level Details • Run reports • Datatel has some canned reports • We had others we added It's all what you're looking for. NERDug 2005 – July 28-29 – Hampshire College
One-Time Setups • For AP checks: • CF subroutine S.LOAD.AP.CHK • Will need to customize (bank-specific) • Subroutine reads/parses bank data NERDug 2005 – July 28-29 – Hampshire College
One-Time Setups • 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 NERDug 2005 – July 28-29 – Hampshire College
One-Time Setups • For HR checks: • Create an ELF spec • Datatel provides INTER.BANK.RECON • ELF requires check issue/paid dates (fake it) NERDug 2005 – July 28-29 – Hampshire College
One-Time Setups • Enter a place for bank files on ELFT: Description: 1: RECON FILE File Type..: VD Variable Disk Directory....: /datatel/live/collive/H08.DATA/_CHECKS_ • GEN your ELF spec • On CF – BKRR enter: Custom ELF Specification: H08.RECON.MAP NERDug 2005 – July 28-29 – Hampshire College
Walk Through July 25 2005 ELECTRONIC CHECK RECONCILIATION REPORT 08:22 Check ID Amount Reconciled Error Message ---------- -------- ---------- -------------------------- 01*0030551 100.00 No This check does not exist 01*0076208 500.00 Yes 01*0076209 2,000.00 Yes 01*0076210 183.49 Yes 01*0076211 650.00 Yes 01*0076212 23.55 Yes 01*0076214 15,555.43 Yes
Walk Through RECONCILED 030105 thru 033105 Curr Current CHK.DATE CHECKS. CHK.MISC.NAME..... CHK.AMOUNT... Stat Stat Date 09/10/03 0076073 Payee name here 94.89 R 03/22/05 02/22/05 0076208 Payee name here 500.00 R 03/31/05 02/17/05 0076209 Payee name here 2,000.00 R 03/31/05 02/18/05 0076210 Payee name here 183.49 R 03/31/05 02/22/05 0076211 Payee name here 650.00 R 03/31/05 02/24/05 0076212 Payee name here 23.55 R 03/31/05 02/25/05 0076214 Payee name here 15,555.43 R 03/31/05 ============= TOTAL 1,157,811.78
Walk Through OUTSTANDING 033105 Curr Current CHK.DATE CHECKS. CHK.MISC.NAME....CHK.AMOUNT... Stat Stat Date 02/24/05 0076213 Payee name here 57.05 O 02/24/05 03/28/05 0076221 Payee name here 2,002.00 O 03/28/05 01/07/04 0418847 Payee name here 219.22 O 01/07/04 01/28/04 0419373 Payee name here 50.00 O 01/28/04 01/30/04 0419488 Payee name here 65.00 O 01/30/04 02/18/04 0420054 Payee name here 5,600.00 O 02/18/04 ============= TOTAL 925,834.49
Walk Through July 25 2005 Electronic Bank Recon Import Report EP Employee Payroll Check Issued Paid Paid Number 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 033105EP 019281 03/02/05 03/02/05 545.80 033105EP 019295 03/02/05 03/02/05 823.12 033105EP 019298 03/08/05 03/08/05 917.50 033105EP ===================== Total Amount and Count 144,749.33 183
Walk Through Bank Code Check Date Check No. Amount Status Status Date EP 06/27/03 015088 776.75 O 06/25/03 EP 07/25/03 015346 310.78 R 09/30/20 EP 07/25/03 015385 30.00 O 07/23/03 EP 08/08/03 015491 249.14 R 09/30/20 EP 08/08/03 015518 258.58 O 08/06/03 EP 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/05 EP 02/04/05 019230 819.57 R 03/02/05 EP 02/18/05 019325 475.75 R 03/15/05 EP 02/18/05 019334 513.91 R 03/17/05 EP 02/18/05 019336 136.67 R 03/22/05 ========= TOTAL 10,112.38