| P a g e

Version 3.0 as of June 28, 2016

1

Office of Grants Management

Washington, DC

 

 

 

Education Award Only 

Roster-based Calculator

EAP Grant Closeout
2 | P a g e

Version 3.0 as of June 28, 2016

Calculating Education Award Only (EAP) Earned Grant Amounts for Closeout

Overview: The Education Award Only Roster-based Calculator (“the tool”) is used to 

identify the amount that an EAP grant recipient is permitted to draw down from the Payment 

Management System. This amount is the recipient’s “earned grant amount.” The tool can be 

used during the grant period to monitor the recipient’s maximum to-date earned grant amount, 

and it must be used after the grant period has expired to calculate the final earned grant 

amount. 

A Grants Officer selects and re-saves the appropriately sized tool template, based on the 

number of members funded during the entire grant period. The Grants Officer extracts the 

member roster for the grant or sub-grant being tested, inserts the roster data into the tool, and 

enters the awarded slot and per-member dollar amounts. The tool identifies the earned grant 

amount as-of the date the member roster was extracted. The tool compares the earned grant 

amount to the current drawdown federal funds total to identify whether or not the amount 

drawn to-date is equal to the earned grant amount, or above/below the earned grant amount.1

Usage: The tool is designed to calculate the maximum permitted drawdowns for these grant 

types: 

• Education Award Only fixed amount grants, commonly named “EAP” grants operated 

by national direct programs.

• State Commission formula and competitive portfolio EAP sub-grants. 

The tool is offered in templates to assess EAP grants with 500 or fewer members; 1,000 or 

fewer members; 5,000 or fewer, and 20,000 or fewer. The tool accommodates the following 

member types existing as of the date of publication:

• Full-time (FT) 

• Half-time (HT) 

• Half-time 2-Year First Year (FY) 

• Reduced Part-time (RH) 

• Quarter-time (QT) 

• Minimum-time (MT) 

 

1 Prior to June 28, 2016, when a program achieved 90-percent or higher member enrollment, the 

Office of Grants Management EAP closeout policy permitted the earned grant amount to equal 

the full grant award, i.e., the grantee was deemed to have earned and be eligible to drawdown the 

full federal grant award amount. The tool and this guide have been modified to eliminate this 

practice.
3 | P a g e

Version 3.0 as of June 28, 2016

Roster-based EAP Spreadsheet Closeout Tool Instructions 

The roster-based EAP spreadsheet closeout tool (“the tool”) provides a Grants Officer with an 

automatically calculated closeout analysis identifying the year-by-year maximum dollar 

amounts the grantee is permitted to drawdown, known as the earned grant amount. The

earned grant amount is based on the number of members the program enrolled per year.

Retention following enrollment, and the number of hours served by an individual member, by 

policy have no impact on the determination of the program’s earned grant amount. The tool 

automatically converts less-than-full-time members to FT MSYs for the closeout calculation.

To use the tool: 

1.Identify the appropriate tool template file to use. (Figure 1) 

Go to folder: S:\Grants\Closeout\Closeout Resources\EAP Closeout Tools\EAP 

Closeout Spreadsheets 

Templates exist for closing grants with program years 2000-2010; grants with program years 

2003-2013; and grants with program years 2007-2017. For each program year template, there 

are 4 different files which differ only by how many members would have been enrolled over 

the 3-year life of the grant. Select the template to use based on the total member slots 

awarded for all years of the grant. The files are named appropriately to support up to 500 

members; up to 1,000 members; up to 5,000 members; and up to 20,000 members. 

Open the appropriate template. You should open the template supporting the smallest 

number of members. Templates supporting 5000 and 20000 members run more slowly –

when using these larger templates observe the status bar at the bottom right of Excel to 

recognize file loading and spreadsheet calculation delays. 

2. Re-save the appropriate template with a new file name. Include the grant number in 

the file name and save the file where closeout records are to be retained as designate by 

Operations. This is now your EAP Closeout Spreadsheet for this grant’s closeout 

analysis. 

Take note that the template spreadsheet has three tabs: (Figure 2/3)

•

Tab 1: Closeout Calculations & Amounts 

•

Tab 2: Analytical Tests 

•

Tab 3: Copy & Paste Roster Report Here 

You will first paste member data into Tab 3. Then you will enter award figures into Tab 1. 

You will not enter any information into Tab 2, it exists to support calculations performed by 

the tool. 
4 | P a g e

Version 3.0 as of June 28, 2016

3. Go to Tab 3 to prepare for member roster data entry

In your saved EAP Closeout Spreadsheet, click on Tab 3, “Copy and Paste Roster Report 

Here” so that you can view the unpopulated worksheet where you will be pasting in the 

Roster Report for this grant. 

Click your cursor in Tab 3 cell A1, make sure that cell A1 is highlighted. (Figure 2/3)

4. From the TRUST, extract and separately save a Roster Report for this grant 

In the TRUST, for the grant being closed, extract a Roster Report in “Comma Separated 

Value” or “CSV” file format. Open/save the Roster Report CSV file as an Excel file 

independent of the EAP Closeout Spreadsheet file you previously saved, in the Operations 

designated folder. Using the Roster Report CSV file, click in the small block located 

between Column A and Row 1 of the worksheet – you should see the entire Roster Report of 

data highlight. Right click “Copy.” 

5. Paste the Roster Report data into the EAP Closeout Spreadsheet Tab 3 at cell A1. 

Return to your EAP Closeout Spreadsheet which should still be at Tab 3 with cell A1 

highlighted. Re-click in cell A1, then Right-click “Paste.” You should see Tab 3 populate 

with Roster Report data identical to the Roster Report CSV file you separately saved. Resave the EAP Closeout Spreadsheet now that you have entered the Roster Report to ensure 

no work is lost. 

In Tab 3, you should see one row of member data for each year of that member’s service, 

presented across 18 columns. 

Do not be concerned that some cells fill with header data from the Roster Report. (Figure 4) 

Verify that on a line-by-line basis, member records are formatted at columns A and M with: 

• Grant Year under Column A (e.g., data such as 2005, 2006, 2007, etc.) 

•

Enrollment Type under Column M (e.g., codes such as HT, MT, QT, FT, etc.) 

6. Verify that the EAP Closeout Spreadsheet tool has made automatic calculations for: 

• Actual Enrollment Levels Per Year 

• MSY Value of Actual Enrollment Levels Per Year 

Go to Tab 1, at the far left cells across the middle of the worksheet are shaded light blue. 

(Figure 4) 

These cells should now reflect a count of the enrollment levels by year, by member type (FT, 

HT, etc.). These counts are produced from the Roster Table data you previously entered. 

You should also see corresponding conversions of these member counts to MSY values; e.g., 
5 | P a g e

Version 3.0 as of June 28, 2016

200 HT members will have been converted to an MSY value of 100. The mathematical 

factors used in these conversions are visible on the right side of Tab 1, highlighted in yellow. 

Also on the right side of Tab 1, at the top highlighted in yellow, you will observe counts of 

the members enrolled over the entire grant period by member type. These totals should 

match the totals on the left side of Tab 1 in the blue shaded areas. (Figure 5) 

At this point, if there is any doubt that the Member Roster has correctly pasted into Tab 3, or 

whether or not the tool has correctly performed member enrollment calculations at Tab 1, 

contact your supervisor to review the information. 

7. Enter SLOT award data using the Notice of Grant Award for each year of the grant 

(Figure 6)

Using the annual NGAs for the grant, identify the SLOTs awarded per year. At Tab 1, go to 

the orange highlighted cells near the top left of the worksheet. This area is labeled, “Grants 

Officer Enter SLOT Data Here from NGA Per Year. Under the appropriate year column, 

divided by member type, enter the corresponding number of slots awarded per year by type. 

This information cannot be identified by using the AmeriCorps tab in eGrants, the NGAs 

must be used. 

As you enter each SLOT award by year, in the green shaded row below your entries you will 

observe a calculated Awarded MSY per Year Based on SLOTs figure increasing as you enter 

each SLOT number. This calculated total accounts for converting the SLOTs to MSY, and 

presents the maximum number of MSYs that the grant was approved for by year. 

8. Enter Award Amount per Year from NGA (Figure 7)

Directly below the calculated Awarded MSY per Year Based on SLOTs, there is an orange 

row where you will enter the annual award amount from the NGA. This is the dollars 

awarded for that year, not the cumulative award. 

As you enter this amount, in a green shaded row directly below your entries the tool will 

calculate the Calculated Award Per-MSY-Per-Year based on Award/MSY Awarded. This 

amount should be equal or less than the allowable maximum per MSY for that year shown 

for that year (light blue row). If the calculated amount differs from the allowable maximum 

for that year, consult with your supervisor to determine if there were any special conditions 

or waivers applied to the specific grant. There is no “red flag” formatting of the cell to warn 

you that the amount is in excess of the maximum, you must compare the calculated amount 

to the maximum for that year.
6 | P a g e

Version 3.0 as of June 28, 2016

9. Enter the total amount drawn by the grantee (Figure 8)

In the orange row labeled, “Enter Amount Drawn in Total To-Date” enter the exact total 

amount that the grantee has drawn down for this grant. Obtain this figure from the HHS 

PMS information presented on the final FSR screen in eGrants. 

10. Review closeout analysis presented by the tool (Figure 9)

At Tab 1, lower left, across from the row heading, “Results of Closeout Analysis” the tool 

will present the per year closeout amounts as: 

•

Total MSY Value by Year 

•

Percent of Awarded MSY by Year that the grantee enrolled. Enrollment less than 

90% is flagged with red highlighting as a programmatic performance indicator.

•

The maximum amount by year that the grantee was entitled to retain 

•

The earned grant amount for the entire grant project period

•

The remaining amount that the grantee may draw down (green) or must return to the 

Corporation (red) 
7 | P a g e

Version 2.0 as of September 12, 2015 

Figure 1: Template Folder 

Figure 2/3: Tabs / Highlighted Cell A1 
8 | P a g e

Version 2.0 as of September 12, 2015 

Figure 4: Tab 3 Populated with Roster Record 
9 | P a g e

Version 2.0 as of September 12, 2015 
10 | P a g e

Version 2.0 as of September 12, 2015 

Figure 4: Enrollment Calculations 
11 | P a g e

Version 2.0 as of September 12, 2015 

Figure 5: Member Counts & Calculation Factors by 

Year 

Figure 6: Enter Slot Award Data 
12 | P a g e

Version 2.0 as of September 12, 2015 
13 | P a g e

Version 2.0 as of September 12, 2015 

Figure 7: Award Amount Per Year 

Figure 8: Drawdown Amount 
14 | P a g e

Version 2.0 as of September 12, 2015 
15 | P a g e

Version 2.0 as of September 12, 2015 

Figure 9: Analysis Results