Direct Loan Reconciliation Process Guide Monthly Direct Loan Reconciliations are necessary through the closeout of each Direct Loan Program Award Year to ensure that the amount of Direct Loan funds that were received by the University from the U.S. Department of Education through its Common Origination and Disbursement (COD) System can be substantiated in the University’s records. Per Direct Loan School Guide, Chapter 6, published by the U.S. Department of Education, “A school that participates in the DL [Direct Loan] Program is required to reconcile the funds that it has received (from the G5 system) to pay its students with the actual disbursement records that it has forwarded to the Common Origination and Disbursement (COD) system. Reconciliation takes place on a monthly basis, with a final reconciliation that is conducted after the award year ends on June 30.” Also per OMB No. 1845-0021, schools must report all loan disbursements and submit required records to the Direct Loan Servicing System (DLSS) via the COD System within 30 days of disbursement. A monthly reconciliation process helps to identify and resolve discrepancies between COD and the University’s records in Financial Aid, Student Financials, and the General Ledger on a timely basis. We have created this Direct Loan Reconciliation Process Guide to walk you through the steps from obtaining the necessary information from COD and our PeopleSoft system all the way through preparing the Direct Loan Reconciliations.

Page 1 of 22

TABLE OF CONTENTS STEP

Description

Page

STEP 1

Set School Account Summary (SAS) Report Options on the COD Website

3

STEP 2

Run GL Inquiry Report to Obtain Month End Direct Loan Account Balances

4

STEP 3

Receive COD Direct Loan Reconciliation Files

7

STEP 4

Import COD Direct Loan Reconciliation Files into PeopleSoft

8

STEP 5

Run UMS Financial Aid Direct Loan Reconciliation Program

9

STEP 6

Prepare Direct Loan Reconciliations

16

STEP 7

Access COD Drawdown Activity in PeopleSoft Campus Solutions

19

Page 2 of 22

Direct Loan Reconciliation Process Guide

March 2012

STEP 1: Set School Account Summary (SAS) Report Options on the COD Website NOTE: Once SAS Report Options have been set up for a Direct Loan Program’s Award Year, as stated below, skip STEP 1 and go to STEP 2. In order to receive reports from the COD System in formats useful in the reconciliation process, each University needs to set certain report options on the COD website. The report options are program and award year specific and should only have to be set once for each combination of program and award year. It is very important to ensure these options are set correctly at the beginning of an award year because prior period reports cannot be regenerated in the desired format. For those given access to the COD website, Login and click School tab. Enter your University’s Entity ID and click Search. In the left navigation pane under School Information, click Report Selection. Select Program “Direct Loan” and the applicable Award Year, i.e., “’11-‘12”. Under the Award Year Specific Reports section, for the SAS reports, click Modify SAS Options, which will bring you to the SAS Options screen. Set SAS Options as follows: Report

Fixed Length

Include Names of Borrowers

No

Report Activity Type

Both

Additionally, the “Sections of SAS to Receive” option should be set as follows: Summary by Loan Type

Yes

Monthly

Year-to-Date

Disbursement Detail

Yes

Yes

Loan Level (optional)

N/A

Yes

Once all the options have been selected, click Update button.

Page 3 of 22

Direct Loan Reconciliation Process Guide

March 2012

STEP 2: Run GL Inquiry Report to Obtain Month End Direct Loan Account Balances In this step you will obtain the Direct Loan balances in the general ledger by generating a GL Inquiry report. Login to the Financials portion of MaineStreet and navigate to the GL Inquiry page. Financials > University of Maine System > Financials > GL Inquiry On the New GL Inquiry page, click on Find an Existing Value tab. Either enter the name of an existing GL Inquiry Search ID and click on the Search button OR click Search and select a Search ID from the search results. Either option will give you the GL Inquiry Search ID screen. Enter the Fiscal Year and Accounting Period for the month you are reconciling. Enter the Business Unit, Department, and Fund Code for the chartfield combination(s) under which the following Direct Loans Advanced accounts are recorded: 13150 - Direct Loans Advanced – No Class 13150 - Direct Loans Advanced – Class 02 13150 - Direct Loans Advanced – Class 03 13150 - Direct Loans Advanced – Class 04

(Direct Loan - Drawdowns) (Direct Loan – Subsidized) (Direct Loan – Unsubsidized) (Direct Loan – Plus)

Click on Search. The System will return each general ledger chartfield combination that includes the business unit, department, and fund code you used as search criteria. You need to reconcile each returned line that has a Direct Loan Project with an Eff Status – Status other than “I – Closed”. In the following illustration you would need to reconcile projects 1300115 and 1300125 because they both have a status other than “I-Closed”.

Page 4 of 22

Direct Loan Reconciliation Process Guide

March 2012

Click on one of the applicable Direct Loan Projects and the System will bring you to a page showing the current balances in Account 13150 - Direct Loans Advanced for the selected project number (relates to Direct Loan Program Award Year).

D A B

Page 5 of 22

Direct Loan Reconciliation Process Guide

March 2012

Print the above page. Return to the search page and open the other returned lines for Direct Loan Projects with an Eff Status – Status other than “I – Closed” and print the balances.

Page 6 of 22

Direct Loan Reconciliation Process Guide

March 2012

STEP 3: Receive COD Direct Loan Reconciliation Files During the first weekend of the month, COD sends the University (based on the report options at the time the reports are run by COD) the following reports as of the end of the previous month for each open Direct Loan Award Year in which you participate: Monthly School Account Summary (SAS) – Disbursement Detail Report (DSDF12OP) Year-to-Date School Account Summary (SAS) – Disbursement Detail Report (DSDF12OP) Year-to-Date School Account Summary (SAS) Award Loan Level Report (DSLF12OP) Please note that the fifth and sixth characters in the report name indicate the Award Year. For example, report DSLF12OP is for the 2011-2012 Award Year. Please also note that the two SAS Disbursement Detail reports will have the same name. These three files are to be received through your University’s Direct Lending Software (i.e., EDConnect, etc.). A designated individual at your University will receive these files to a specific folder for import into PeopleSoft (discussed in STEP 4). When these files are saved, the following changes need to be made to the COD file names to distinguish between the Monthly SAS Disbursement Detail Report and the Year-to-Date SAS Disbursement Detail Report and to identify the month for which the file was generated: For the Monthly SAS Disbursement Detail Report, modify the COD filename by deleting the “OP” and inserting the COD Sent Date “MMDDYY” followed by an “M” For the Year-to-Date SAS Disbursement Detail Report, modify the COD filename by deleting the “OP” and inserting the COD Sent Date “MMDDYY” followed by a “Y” For the Year-to-Date SAS Award Loan Level Report, modify the COD filename by deleting the “OP” and inserting the COD Sent Date “MMDDYY”. The needed changes are summarized in the following table: COD Filename

File Detail

Description

COD Sent Date

Save File As

DSDF12OP

Smaller file – usually .dat file

Monthly SAS Disbursement Detail Report

02/04/2012

DSDF12020412M.dat

DSDF12OP

Larger file – usually .001 file

Year-to-Date SAS Disbursement Detail Report

02/04/2012

DSDF12020412Y.001

DSLF12OP

.dat file

Year-to-Date SAS Award Loan Level Report

02/04/2012

DSLF12020412.dat

Page 7 of 22

Direct Loan Reconciliation Process Guide

March 2012

STEP 4: Import COD Direct Loan Reconciliation Files into PeopleSoft In this step you will import the data from the three COD files (from STEP 3) into the PeopleSoft Financial Aid system. Login to the Campus Solutions portion of MaineStreet and navigate to the Import Federal Data Files page. Campus Solutions > Financial Aid > File Management > Import Federal Data Files

/opt/soft/CSPRD/user_input/UMS01/dsdf12020412M.dat

Select Run Option – Single File. Select the Directory for your University, for example - UMS01. Locate and select the Monthly SAS Disbursement Detail Report file (from STEP 3) for the month you are reconciling. For example, for UMS01, the January Direct Loan Reconciliation file to be imported into PeopleSoft would be named as follows: DSDF12020412M.dat. Click the Run button. Once this file has finished uploading into PeopleSoft, STEP 4 will need to be performed two more times to import the  Year-to-Date SAS Disbursement Detail Report file (DSDF12020412Y.001) and  Year-to-Date SAS Award Loan Level Report file (DSLF12020412.dat).

Page 8 of 22

Direct Loan Reconciliation Process Guide

March 2012

STEP 5: Run UMS Financial Aid Direct Loan Reconciliation Program In this step you will run the Direct Loan Reconciliation Program “UMFADLRC” that compares what is recorded as ‘disbursed’ in Financial Aid to what has been: 1. Posted in Student Financials. 2. Posted to the General Ledger. 3. Reported by COD as having been disbursed. The differences identified in the generated report need to be researched and resolved, and documented on the General Ledger Reconciliation worksheet (Exhibit II Form) reconciling the General Ledger balances to the amounts reported on the COD reports. To run the reconciliation program,  Within MaineStreet select Campus Solutions and navigate to the Run User SQRs page. Campus Solutions > University of Maine System > Utilities > Run User SQRs  Click on Find an Existing Value tab and click Search.  Select the Run Control ID for “UMFADLRC”.  This program needs to be run for each Direct Loan Program in an open Award Year with the parameters completed as follows: AGGREGATE_AREA

=

AID_YEAR AS_OF_DATE

= =

COD_DATE

=

INSTITUTION

=

Direct Loan Program (DIRECTSUB, DIRECTUNS, or PLUS) Open award year Date for which you are preparing the reconciliation. Generally, it will be the last day of the month. Month and year for which you are preparing the reconciliation. Your University’s PeopleSoft business unit number.

In the illustration on the next page we have selected to run the UMFADLRC program for UMA (UMS01) as of January 31, 2012 for the Award Year 2012 Subidized Direct Loans

Page 9 of 22

Direct Loan Reconciliation Process Guide

March 2012

Once the selected report has run, go to Process Monitor and click View Log/Trace. As can be seen below, three PDF reports will be created specific to the Direct Loan Program specified in the report parameters: Direct_Subsidize_Exceptions_2012.PDF Direct_Subsidize_Summary_2012.PDF and Direct_Subsidize_Summary_Report_2012.PDF

Page 10 of 22

Direct Loan Reconciliation Process Guide

March 2012

Examples of the above three reports are illustrated on the following pages. Open and save these three reports for use in the Direct Loan Reconciliation process.

Page 11 of 22

Direct Loan Reconciliation Process Guide

March 2012

Direct_Subsidize_Exceptions_2012.PDF This report lists only those Direct Subsidized Loans that have a difference between FA/SF, FA/GL or FA/COD.

C B

Page 12 of 22

Direct Loan Reconciliation Process Guide

March 2012

Direct_Subsidize_Summary_2012.PDF This report lists all Direct Subsidized Loans in FA, SF, GL or COD.

Page 13 of 22

Direct Loan Reconciliation Process Guide

March 2012

Direct_Subsidize_Summary_Report_2012.PDF This report (shown on the next page) displays two important totals that will be used later in the reconciliation process (see STEP 6) and will be reported as the COD Balance per Detail: COD Year-Date Detail Summary “Booked Net” totals for Subsidized, Unsubsidized and Plus loans. These totals represent the loans advanced for each Direct Loan Program and will be used in the reconciliation of Account 13150 - Direct Loans Advanced, Classes 02, 03 and 04, respectively. COD Direct Lending “Total Net Cash Receipts”. This total represents total collective drawdowns for all three Direct Loan Programs. This total will be used in the reconciliation of Account 13150 - Direct Loans Advanced, No Class. This report also displays the totals from the last page of the Direct_Subsidize_Summary_2012.PDF report. The COD loan total on this report should always equal the “Booked Net” amount under the COD Year-Date Detail Summary in this same report (in this example, the Subsidized line). If the totals do not agree, it is an indication that not all COD transactions were included in the analyses performed by our internal “UMFADLRC” program.

Page 14 of 22

Direct Loan Reconciliation Process Guide

March 2012

B

E B

Page 15 of 22

Direct Loan Reconciliation Process Guide

March 2012

STEP 6: Prepare Direct Loan Reconciliations Using the following forms and reports, reconcile each of the four Direct Loan accounts (see page 4 of this Guide) for each open Direct Loan Award Year: Exhibit II (located on the web at http://www.maine.edu/system/oft/GeneralLedgerCloseExhibits.php GL Inquiry report from STEP 2 of this Guide – provides the ‘Balance per G/L’ on Exhibit II Summary_Report_20xx from STEP 5 of this Guide - provides the ‘Balance per Detail’ on Exhibit II Exceptions_20xx report from STEP 5 of this Guide - provides the variances between COD and the General Ledger Causes for the variances between COD and the General Ledger need to be identified and reported on Exhibit II. Corrective action should also be taken so that these variances do not exist in future months. In STEP 7 we illustrative how to access certain COD information that can be a useful resource during the reconciliation process. Continuing with our illustration from STEP 5, on the next page we have included the Exhibit II reconciliation for Direct Loans Advanced (Subsidized) Account 13150 - Class 02. The boxed letters refer back to amounts illustrated in earlier steps in this document. We have also illustrated the reconciliation for Account 13150 – No Class which represents the total drawdowns for all three Direct Loan Programs.

Page 16 of 22

Direct Loan Reconciliation Process Guide

March 2012

Sample Reconciliation of Direct Loans Advanced (Subsidized) Account 13150 - Class 02

A

B

B

B

C B

The Direct_Subsidize_Exception_2012 Report previously illustrated provides the breakdown by student of the $7,564 variance reported in the above reconciliation. You need to review that report to determine whether or not further action is needed to ensure that the variances will clear within the next month.

Page 17 of 22

Direct Loan Reconciliation Process Guide

March 2012

Sample Reconciliation of Direct Loans Advanced (Drawdowns) Account 13150 - No Class

E

D

Note: We were able to identify the cause of the above $114 variance by following the process described in STEP 7 of this Guide. Page 18 of 22

Direct Loan Reconciliation Process Guide

March 2012

STEP 7: Access COD Drawdown Activity in PeopleSoft Campus Solutions This step shows how you can access summary and detail COD Direct Loan activity through PeopleSoft Campus Solutions in order to resolve reconciliation variances between COD and the general ledger related to drawdowns (Account 13150 with No Class). In STEP 6 we provided an illustration of the reconciliation for Direct Loans Advanced (Drawdowns) Account 13150 - No Class. In that reconciliation, there was a variance of $114.30 between the ‘Balance per G/L’ and the ‘Balance per Detail’ (a.k.a. COD Direct Lending “Total Net Cash Receipts”). Through PeopleSoft we accessed the cash transactions that make up the COD ‘Balance per Detail’ reported in the reconciliation. Then by comparing these cash transactions to activity in the general ledger, we found the variance to be a $114.00 cash transaction that was posted to the general ledger in January but not posted to COD until February. Here is how you can access the COD Cash Detail and other COD information through PeopleSoft: Login to the Campus Solutions portion of MaineStreet and navigate to the DL School Account Summary page. Campus Solutions > Financial Aid > Loans > DL School Account Summary Select the appropriate DL SAS Import Report for the Award Year you want to view – DLSAS Import 2011-2012. On the Direct Loan Cash Sum Imp page, click on the Find an Existing Value tab. Select Search by “EC Transaction ID”. Click Search button. The result is a listing of the data from the three COD SAS Reports previously imported into PeopleSoft in STEP 4 of this Guide. Please note that the two DSDF12OP files were imported using the same DL Batch ID for a given COD Sent Date. To differentiate between the file that represents monthly activity versus year-to-date activity you need to look at the EC Queue Instance. The lower instance number represents the monthly activity. Sort the listing by clicking on DL Batch ID header. In the Search Results, scroll down the list until you find your University ID Number embedded in the DL Batch ID column and locate the DSDF12OP file with the correct COD Sent Date (embedded directly after the University ID Number in the DL Batch ID) for the month you are reconciling. To get to the January 31, 2012 COD information needed in our illustration, you would locate the file with a COD Sent Date close to the beginning of February 2012, i.e., AS2G0676020120204203342. Page 19 of 22

Direct Loan Reconciliation Process Guide

March 2012

Both of the DSDF12OP files once opened will have tabs for “Batch Header”, “Cash Summary”, “Disbursement Summary”, “Cash Detail”, “Loan Level Detail’, “Disbursement Activity” and “Batch Trailer”. In our illustration we selected the EC Queue Instance “70”; therefore, under the “Cash Detail” tab, we see all COD transactional activity for the Award Year selected, as can be seen on the next page.

Page 20 of 22

Direct Loan Reconciliation Process Guide

March 2012

By comparing the above transactions with the transactions in the general ledger for the chart field combination we are reconciling (we have made the assumption that the reader of this document knows how to use GL Inquiry to view the transactions in the general ledger) we see a -$114.00 draw adjustment not reflected in the above COD transactions. This amount is included on Exhibit II (see STEP 6) as a reconciling item.

Page 21 of 22

Direct Loan Reconciliation Process Guide

March 2012

Page 22 of 22