| PublicationDate: 7/27/2000 Summary: Reconciliation for the Federal Pell Grant Program Author: General Manager: SFA School Channel Posted on July 27, 2000 TO: All Destination Points FROM: U.S. Department of Education SUBJECT: Reconciliation for the Federal Pell Grant Program THIS MESSAGE: Announces the availability of a 1999-2000 Reconciliation Tool for the Federal Pell Grant Program Provides Instructions on how to use the RFMS 1999-2000 Reconciliation File Discusses Reconciliation Options for EDExpress Users In response to concerns of the community about reconciling for the Federal Pell Grant Program for the 1999-2000 Award Year, Pell Grant Processing has developed a unique RFMS Reconciliation file to aid institutions in the reconciling process. This RFMS Reconciliation file is intended to particularly aid institutions that use homegrown or third party software that may have limitations in their ability to provide reconciliation tools. EDExpress users should follow the instructions below, under IF YOU ARE AN EDEXPRESS USER, in order to use the enhanced functionality of the List -Processed Disbursements- Year to Date report available in the 5.4.2 version of EDExpress. This RFMS Reconciliation file will automatically be placed in all institutions TIVWAN mailboxes. We will begin sending the file out on July 31. All of the files should be distributed to institution mailboxes by August 8. You can begin to check for the file during this time period. Follow the instructions below to use this file as you determine best to help you reconcile. There are a number of options for using this file including: - Printing the file as it was received - Importing the file into software, such as a spreadsheet or database software package which will allow you to sort and format the data as best suits your needs - Importing the file into EDExpress The RFMS Reconciliation File is produced in Attended Campus, then Student Name order (Last, then First). The fields in the file include: Recipient Name, Origination ID (which includes, SSN, Name Code, Year Indicator, Attending Pell ID and Origination Sequence Number), Application Transaction Number, Scheduled Award as calculated by RFMS, Origination Award Amount as accepted by RFMS, and YTD Disbursement Total as accepted by RFMS. All the information in this file is taken from information that exists in the standard Pell Year to Date Record that can be requested through your regular software. Please note that the file is 80 characters long, so it may be easily printed on standard 8-1/2x11 inch paper. The file will include a Grant Header (which can be ignored), a Title Line which contains labels for the columns, one line for each accepted origination record for your school, which includes the YTD disbursed amount for that Origination, and a Grant Trailer (which contains totals for the number of recipients, total amount for Origination Award Amount, and total amount for accepted YTD Disbursements). You will receive this file via Title IV WAN message class PGTX00OP (ASCII Text File format). Download the RFMS Reconciliation file from Title IV WAN as you would download a file of this message class through EDConnect, mainframe-to-mainframe, or your own software. Please note that this file is sent in compressed format, so you must decompress it just as you do any other file received from Title IV WAN. Also, remember the name and subdirectory under which you saved the file. If this is the first PGTX00OP file you have received, it will have a .DAT extension, with additional files being named .001, then .002, etc.). You may want an updated reconciliation file in late August or September to further aid you in reconciling. If you would like an updated reconciliation file for 1999-2000, please make your request via email to pell_systems@ed.gov. Please use the subject line Request for updated 1999-2000 reconciliation file. After download, you may print the file or import the file into a spreadsheet using the following steps: (1) Mainframe users may want to download the file to their PC to view it, print it, or import the file into Microsoft Excel. For instance, printing can be done on a PC using the WORDPAD accessory (Start|Programs|Accessories|WordPad) that comes with any version of Windows (use Print Preview under the FILE tab before printing to determine if you need to change your margins to be safe, use PAGE SETUP to set left and right margins to 0 to allow the printing to have one recipient per line.) (2) For those who wish to import the file into a spreadsheet using a program like Microsoft Excel, click File | Open after starting Excel. When the Open windows appears, type in the name that you saved the downloaded file under and click Open. Since the file is not in XLS (spreadsheet format), the program will automatically prompt you to convert it. Follow the directions first, respond that the fields are fixed width, start import at row 3, and then click on Next. Continue by clicking on Next again and finally on Finish. These instructions have been tested using Microsoft Excel for Windows 97 and for Windows 2000. Once you have imported the file into a spreadsheet, you will probably want to increase the column width for the Recipient Name to 20 characters and Origination ID to 23 characters (by choosing Format | Column | Width ) for each so you may see all of the data. Also, set the format for the Origination Award Amount and YTD Disbursement Total to numeric with two decimal places in case any of your recipients have pennies in their award amounts. Choose Format | Cells | Number and clicking on Number. Before printing, use File | Print Preview to see if the report fits across a single page. If not, click on Margins under Print Preview to change the left and right margins to .25 each. (3) Import the file into EDExpress by going to File | Import | Pell | ASCII Text File (last option). EDExpress then will ask you for the name of the file (PGTX00OP.DAT, or the file name you entered when downloading the file). You can print the report to your printer, the screen, or a file. If you have any problems, or need assistance please call the Pell Grant Customer Hotline at 1-800-4PGRANT (1-800-474-7268) or via e-mail at pell_systems@ed.gov. IF YOU ARE AN EXPRESS USER To reconcile your 1999-2000 EDExpress Pell information with RFMS using a Pell Year-to-Date file (PGYR00OP), follow the procedure below: 1) You should first ensure you are running 1999-2000 EDExpress Version 5.4.2. If you have not upgraded to Version 5.4.2 (or Version 5.4.1), you can acquire the software install file from the Department's SFAdownload site at http://fsadownload.ed.gov/index.htm. Once you are running Version 5.4.2, and have received a recent PGYR00OP file, follow the steps below: 2) Resolve all pending records: You may wish to create a query for pending origination records. For example: -- Go to Tools | Query | Pell and click on Add. Title the query a name recognizable to you, such as "Originations Pending Resolution" -- In Field, choose Origination Status. In Operator, choose =. In Value, enter N. Click on Append; -- Click on OR; -- In Field, choose Origination Status. In Operator, choose =. In Value, enter R. Click on Append; -- Click on OR; -- In Field, choose Origination Status. In Operator, choose =. In Value, enter B. Click on Append; -- Click on OR; -- In Field, choose RFMS Origination Status. In Operator, choose =. In Value, enter E. Click on Append. Save the query. Print an Origination List. Look through the records. You do not need to delete the records with the Origination status of "N" (Not Ready), but you may choose to do so, as this makes the Compare and Print Exception YTD Import Edit Report easier to navigate. You may wish to complete the cycle on all "R" (Ready) and "B" (Batched) records to reduce differences between your database and the PGYR00OP file data. If the status is "E" (Rejected), review the Edit Comment codes by opening the record and going to View | Processed Edits/Comments on the Pell Grant tab, or by printing the Edits/Comment by Student list. If the Edit/Comment is 302, Duplicate Origination, these records can be rebuilt to the accepted/corrected status while importing a current PGYR00OP file by using the "Update Selected Records" option. Resolve all other pending records. You may also wish to create a query for pending disbursement records. For example: -- Go to Tools | Query | Pell. Click on Add. Title the query a name recognizable to you, such as "Disbursements Pending Resolution"; -- In Field, choose Disbursement Status. In Operator, choose =. In Value, enter R. Click on Append; -- Click on OR; -- In Field, choose Disbursement Status. In Operator, choose =. In Value, enter B. Click on Append; -- Click on OR; -- In Field, choose RFMS Disbursement Status. In Operator, choose =. In Value, enter E. Click on Append. Save the query. Print a Disbursement List. Look through the records. You may wish to complete the cycle on all "R" (Ready) and "B" (Batched) records to reduce differences between your database and the PGYR00OP file data. If the status is "E" (Rejected), review the Edit Comment codes by opening the record and going to View | Processed Edits/Comments on the Pell Grant tab, or by printing the Edits/Comment by Student list. If the Edit/Comment is 403 (Disbursement Reference Number Not Valid) or 504 (Disbursement with this Reference Already Exists ---Duplicate Special Disbursement) these records can be rebuilt to the accepted/corrected status while importing a current PGYR00OP file by using the "Update Selected Records" option. Resolve all other pending records. 3) Order PGYR (Pell Grant Year to Date) files for all attended campuses: PGYR files are batched by attended campus. You should request a PGYR00OP file for each attended campus. You may do this through EDExpress if you choose. To use your EDExpress to request a Pell YTD file, please follow these steps: -- Go to File | Export and choose the Pell tab; -- For Export Type, choose Data Request; -- Check the Year-To-Date Records checkbox, and make sure your Reporting and Attended Campus IDs are in the appropriate boxes; -- Click OK to export the file; -- Transmit the resulting PGRQ00IN (PGRQ01IN for 2000-2001) file through EDconnect; -- RFMS will return a PGYR00OP (PGYR01OP) file in response to the Year-to-Date data request. 4) Import the PGYR00OP file(s) you receive: When you have received the PGYR file(s) for each attended campus, go to File | Import | YTD Data. Mark "Compare and Print Exceptions." You may choose the Update-Rebuild All or Selected records if you have some Edit/Comment 302, 403 or 504 records to rebuild, or if you are concerned about missing records in your EDExpress Database. The Compare and Print Exceptions import edit report is divided into two sections: the Origination Exceptions section and the Disbursement Exceptions Section. Only records in the PGYR00OP file that are not matched on your EDExpress database will appear on the report. You may also wish to review the totals given at the end of the disbursement exception section. If the Total YTD Disbursement Amount equals the Total School Disbursement Amount, your payment totals match those of RFMS. If there are any discrepancies, run step 1 again. 5) Run YTD totals report: Print the List - Processed Disbursements Year-to-Date report for each attended campus. Ensure the totals per student match your other records. If you have any questions regarding the Pell module of EDExpress, please contact CPS Customer Service at 800/330-5947, option 4, or via email at CPS@NCS.COM. |