Electronic Check Reconciliation

1
Electronic Check Reconciliation
Wendy O'Rourke Hampshire College Jeff Butera
Hampshire College Session 10630 July 28,
2005 300PM - Room 102
NERDug 2005 July 28-29 Hampshire College
2
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
3
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
4
Lower Level Details

• Check data from bank varies
• timing
• format
• media
• Contact your bank for details.

NERDug 2005 July 28-29 Hampshire College
5
Lower Level Details

• Copy data to server
• Many methods
• FTP
• Network drive
• Whatever works best for you.

NERDug 2005 July 28-29 Hampshire College
6
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
7
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
8
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
9
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
10
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
11
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
12
Walk Through
13
Walk Through
14
Walk Through
15
Walk Through
16
Walk Through
17
Walk Through
18
Walk Through
19
Walk Through
20
Walk Through
July 25 2005 ELECTRONIC CHECK
RECONCILIATION REPORT 0822

Check ID Amount Reconciled Error
Message ---------- -------- ----------
-------------------------- 010030551 100.00
No This check does not exist 010076208
500.00 Yes 010076209 2,000.00 Yes 010076210
183.49 Yes 010076211 650.00 Yes 010076212
23.55 Yes 010076214 15,555.43 Yes
21
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
22
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
23
Walk Through
24
Walk Through
25
Walk Through
26
Walk Through
27
Walk Through
28
Walk Through
29
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
30
Walk Through
31
Walk Through
32
Walk Through
33
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
34
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