DL Reconciliation

1. DL Reconciliation WVASFAA Fall Conference October 28, 2010 Dianne Sisler

2. What is reconciliation? • Endng cash balance of zero • Cash drawdowns equal booked disbursements minus any cash refunds (net unbooked disbursements of $0) • If the previous two conditions aren’t met, be able to identify and justify the discrepancies

3. When to reconcile • Every import of DL origination and disbursement data – good business practice to clear discrepancies and errors • Monthly – required by law • Yearly – required by law and must be done by July 31 of year following end of aidyear (0910 deadline is July 31, 2011)

4. HOW??

5. School Account Statement (SAS) • Generated by COD the weekend following the last day of the month • Sent to the SAIG mailbox as DSDFxxOP and DSLFxxOP • After a year is closed out, you should stop receiving SAS files for that year (good way to know you’ve successfully closed out)

6. Key Items on SAS Report • Net Cash Receipts – funds drawn down from G5 minus cash returned by your business office • Net Booked Disbursements – Actual disbursement amounts reported by you and accepted by COD, including downward adjustments

7. COD Website settings • School Information  Report Selection  Modify SAS Options • Report format is Fixed Length (default) for use in Banner. Also available as delimited files for Excel, Access, etc (DSDDxxOP and DSLDxxOP) • Report Activity Type can be Monthly, Year-To-Date, or Both

8. Basic Process • Make sure all response files have been loaded by checking REICODD • Receive DSDF and DSLF files from COD via EdConnect • Transfer the file(s) to the Banner Directory using ftp or Skippy • Upload the file(s) to Banner using RPRDUxx – remember to change the extension to .dat first

9. Reports from RPRDUxx • Set ‘Print Loan Detail on SAS’ Parameter = ‘Y’ to get SAS report • Includes cash detail and unbooked loan detail • Use this report to begin reconciliation

10. Banner Forms • RPACASH – Cash Drawdown Form • Manually record drawdowns – date must be the date the transaction was processed by G5 in order for RPRDUxx to update the batch information. This date is part of the email you receive from G5 after each drawdown.

11. Banner Forms cont. • RPACASH fields updated manually – • Date, amount, Aid Year, Action (‘R’ Receipt or ‘X’ Return), GAPS Control Number, Check number if return. • The rest of the fields are updated by RPRDUxx when uploading SAS file.

12. Banner Forms cont (2) • RPIDLAS – Summary information from SAS. Aid year specific. Summary type is either monthly (T1) or year-to-date (Y1). • RPALORG – Use options menu to get Disbursement Details and Booking Data blocks.

13. Click on ‘booking information’ from Options Menu

14. Banner Reports • RERCDxx – Reports COD activity by document id • Gives details on records in that document • Find records that haven’t been accepted • Shows accept status and payment status • Find records that haven’t been paid and see what’s holding up the disbursement

15. Other Banner Forms • ROAMGMT – Fund award data • RFIBUDG – Budget data • RPACASH – Cash Drawdown form. • FGITBAL – Finance form. General Ledger Trial Balance. • FGIBDST – Finance form. Organization Budget Status.

16. Other Banner Reports • RFRABAL – Pulls ROAMGMT data into a report for easy viewing and printing for documenting reconciliation work • RFRBUDG – Pulls RFIBUDG data into a report for easy viewing and printing for providing documentation of reconciliation • FGRTBAL and FGRGLTA if you are using Banner Finance report data from FGITBAL and FGIBDST

17. Cash Discrepancies • Timing issues • Cash transaction took place in wrong aid year or split between aid years • Funds sent as refunds of cash that should have been payment and vice versa • Funds used in an aid year other than the one they were drawn from • Usually happen on the Finance side

18. Resolve Cash Discrepancies • Were all drawdowns and returns recorded on RPACASH for correct aid year? • Was the correct action code used (R or X)? • Check for a G5 transaction that might have been made the first of the month for the previous month or vice versa

19. Disbursement discrepancies • CRDLxxIN not sent out • CRDLxxOP not loaded • Unresolved rejected records • Unbooked records • Disbursement made on last day of month but not extracted and sent to COD until beginning of new month causes monthly reconciliation to not match

20. Possible resolution for disbursement discrepancies • Review REICODD for extract files without responses and figure out if the crdlxxin was not sent or crdlxxop was not loaded into Banner • Review the unbooked loans report and determine what needs done to complete those loans • Compare the Banner disbursement record with COD information to find mismatches

21. Keeping records • Document why ending cash balance is not zero • Document reasons for unbooked loans • Document reasons for disbursement discrepancies • Keep a listing of discrepancies from month to month to check on future reconciliations

22. Other recommendations • Begin keeping a log of COD files and what action has been taken • Keep a log of action items to watch on next month’s reconciliation

23. Sample Procedure (1) • Step 1: Monthly, COD sends the SAS file as dsdf and dslf. Load to Banner via RPRDUxx. • Step 2: Note the Statement End Date on RPIDLAS. It will be used in DL Tools. • Step 3: Run RCRCPxx to create the extract files DLEXCASH, DLEXLOAN, and DLEXDISB.

24. Sample Procedure (2) • Step 4: Move the files created in Step 3 to the directory that contains DL Tools (probably iam/data). The dsdf and dslf files should already be in this directory. • Step 5: Open DL Tools and import the SAS files. Import Type = ‘SAS’. Import From = ‘File’ (button).

25. Sample Procedures (3) • Step 6: After importing the dsdf and dslf files, run the reports for Cash Summary, Loan and Disbursement Detail. Note: If you didn’t use a date range when running the reports, you get a year-to-date report. Unbooked loans can be a separate report if you choose. Make sure to check ‘include disbursement detail’.

26. Sample Procedures (4) • Step 7: To compare COD and Banner data, do an ‘external add’ import to bring in the Cash Detail (DLEXCASH), Loan Detail (DLEXLOAN) and Disbursement Detail (DLEXDISB) data that we got from Banner earlier.

27. Sample Procedures (5) • Step 8: Run the comparison reports by choosing File/Print Cash Detail Comparison Report Disbursement Detail Comparison Report Loan Detail Comparison Report

28. Sample Procedures (6) • Step 9: On the last day of each month, make a screen shot of RFIBUDG for each loan fund. • Step 10: Go to the COD website and print the summary of financial information for your school. Under your school tab, enter the school name, then choose ‘Direct Loans’ for program and the award year. ‘Summary Financial Info’ should be on the left.

29. Sample Procedures (7) • Step 11: Compare the total of the fund amounts you got from RFIBUDG with the printout of the summary financial info from COD. • Be sure to keep all of the documentation you collected in these steps as well as justification for all discrepancies you found.

30. Questions? Comments? Smooth sailing ahead….