Direct Loan ReconciliationDatatel Gisella Baker – Hawkeye Community College
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
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
Reconciliation • Reports to run throughout the month • DCRR – DL COD Reject Report • Run after each batch of originations is received by from COD
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
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
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
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
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
RECONCILIATION • Total rows can be formatted to get totals which would match your DLSAS report
RECONCILIATION • Using Excel- • Locate file in appropriate drive. • Always use file with the highestnumber (most recent file) • Choose fixed width
RECONCILIATION You need to set column breaks as follows: 4, 10, 18, 24, 30, 32, 34, 42, 50, 59, 60, 62, 68
RECONCILATION 87, 92, 97, 102, 107, 119, 135
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 0’s. • Move Column B to A and A to B so loan type is first.
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.
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.
RECONCILIATIONMERGE AND COMPARE • Open MS Word. • Choose the review tab. • Choose compare.
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.
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
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
Presenter Contact Information Gisella Bakergisella.email@example.com