Direct Loan Reconciliation Datatel

1
Direct Loan ReconciliationDatatel

• Gisella Baker Hawkeye Community College

2
RECONCILIATION

• Hawkeye
• Compare 3 ways
• Financial aid to GL
• Financial aid to COD
• GL to G5
• During the year compare net disbursements
• System report of net disbursements with DSLF
  loan detail report
• Use Merge/Compare in Word to find differences

3
RECONCILIATION

• THE KEY TO EASY RECONCILIATION
• Finding rejected originations and disbursements
• Set schedule that reports are run to look for
  these
• Correcting rejects and follow up that it is fixed
  in COD
• Watch month end processing

4
Reconciliation

• Reports to run throughout the month
• DCRR DL COD Reject Report
• Run after each batch of originations is received
  by from COD

5
DCRR
6
DCMR COD Missing Response Report
7
CODC COD Comparison Report
8
COD SET UP
9
School Account Statement (SAS)

• Generated by COD on 1st weekend of the month
• Contains data through the end of the previous
  month
• Separate SAS for each open award year
• Four Primary SAS Components
• Cash Summary
• Disbursement Summary by Loan Type
• Cash Detail
• Loan Detail
• Loan Level or Disbursement Activity Level

10
Datatel

• Using FMGT generate a report of all
  disbursements by loan funds Sub/Unsub/Plus
• Export to Excel
• Keep each type of loan separate
• Need SSN and net disbursements
• We separate
• Save as CSV file

11
(No Transcript)
12
(No Transcript)
13
(No Transcript)
14
(No Transcript)
15

• Delete columns G-L, then D and E, and finally A
  and B (in this order)
• Sort by column A and delete everything without a
  social security number and amount from the top
  and bottom of your report.
• Sort by column B and delete all social security
  numbers without a dollar amount

16

• Once again, sort by column A.
• Format column A as a Social Security Number.
• Format column B as a Number.
• Save Report as an Excel (.xlsx) file
• Save Report as Comma Delimited (.csv) file

17
RECONCILIATION

• COD side
• DSLF-SAS loan level detail
• DSLF11OP (DAT first one for year and then will go
  up so next one would be DSLF11OP.01
• Need to open in Excel and then format so that you
  end up with SSN and net disbursements
• The first several rows are year-to-date, monthly
  and cash totals we delete for detail compare

18
RECONCILIATION

• Total rows can be formatted to get totals which
  would match your DLSAS report

19
RECONCILIATION

• Using Excel-
• Locate file in appropriate drive.
• Always use file with the highestnumber (most
  recent file)
• Choose fixed width

20
RECONCILIATION

• You need to set column breaks as follows
• 4, 10, 18, 24, 30, 32, 34, 42, 50, 59, 60, 62, 68

21
RECONCILATION

• 87, 92, 97, 102, 107, 119, 135

22
RECONCILATION
23
RECONCILATION

• Excel file will pull up the break-downs
• Delete all rows that do not have S, U, or P in
  column K.
• Delete columns A-I.
• The SSN will now be in column A and the loan type
  will be column B.
• Delete columns C-H and then D-G.
• Highlight Spreadsheet and sort by column C.
• Delete all 0s.
• Move Column B to A and A to B so loan type is
  first.

24
RECONCILATION

• Highlight spreadsheet and sort by column A
• Separate each loan type into their own
  spreadsheet and save as that loan type (PLUS COD,
  SUB COD, UNSUB COD) and delete column A (loan
  type) as a xlsx file.

25
RECONCILATION

• For Each separate spreadsheet
• Highlight column A and format as SSN
• Highlight column B and format as a number.
• Click File? Save as ? comma delimited (csv) file.

26
RECONCILIATIONMERGE AND COMPARE

• Open MS Word.
• Choose the review tab.
• Choose compare.

27
MERGE AND COMPARE

• Compare documents will appear
• Pull up the csv documents you want to compare
  (example-COD vs Datatel) and select OK.
• Your report will generate the comparisons.

28
YEARLY CLOSE OUT

• The process by which schools complete final
  processing at the end of a Direct Loan award
  year.
• Extension of the monthly reconciliation Program
  Year Closeout process
• 0 Ending Cash Balance (DL funds received DL
  funds disbursed)
• Final deadline The last processing day in July
  of the year following the end of the award year.
  (7/29/2011 for 2009- 2010).
• Note 30 Day reporting requirement supersedes
  both reconciliation and closeout requirements

29
Yearly total
30
YEARLY CLOSE OUT
31
YEARLY CLOSE OUT
32
YEARLY CLOSE OUT
33
Direct Loan Resources

• Written Materials (IFAP)
• 34 CFR 685
• Direct Loan School Guide
• COD Technical Reference
• Direct Loan Bulletin
• Direct Loan Tools
• Recorded Webinar Direct Loan Reconciliation
• National Direct Student Loan Coalition
• http//www.directstudentloancoalition.org

34
Presenter Contact Information

• Gisella Bakergisella.baker_at_hawkeyecollege.edu319
  -296-4020

35
(No Transcript)