1 | P a g e

Edition 3.0, May 16, 2014

O f f i c e o f G r a n t s 

M a n a g e m e n t

 

 

 

USER GUIDE:

Earned Grant Amount

Calculator for Full-time

Fixed Price Grants
2 | P a g e

Edition 3.0, May 16, 2014

The Earned Fixed Amount Grant Calculator

The Earned Fixed Amount Grant Calculator cannot be used on grants where full 

time AmeriCorps Members have been converted to less than full-time service.

The Earned Fixed Amount Grant Calculator (“the tool”) is a spreadsheet template used to 

determine the fixed amount grant that a program has earned based on enrolled AmeriCorps 

members’ service status and hours. The tool establishes the earned grant amount as of the date a 

program’s member roster was extracted from the Trust portal. The tool is can be used to identify 

the earned fixed amount grant for national direct programs without sub-grants, individual subgrants awarded by national direct programs, and individual formula and competitive sub-grants

awarded by State Commissions. The tool can also be used to calculate the earned grant amount 

for a Professional Corps program.

Earned grant amounts are assigned to each enrolled member and the sum of each member’s 

earned grant amount equals the total earned grant amount. The earned grant amount attributed to 

an individual member is calculated as follows: 

 For full time members in Completed status, the grantee is credited 100% of its permember grant award using a calculation that ensures that the required 1700 hour 

minimum service level was reached (otherwise the award is pro-rated proportional to the 

hours served).

 For full time members in Ended Service Early status, the grantee is credited with a prorated share of the grant’s fixed award amount based on the percentage of 1700 hours 

served by the individual member as of the date of testing. 

 For full time members in Active, Transfer IN, Reactivated or Suspended status, the 

grantee is credited a pro-rated share of its per-member grant award based on the number 

of days elapsed since the member was first enrolled relative to the member’s scheduled 

term of service.1

 For members serving in less than full-time positions, the tool identifies an approximate

amount earned but flags a warning that the tool cannot be used to establish the correct 

earned grant amount.

2

 

 

1 A final earned grant amount cannot be determined until all members are in Completed or Ended Service Early 

status. Rosters with Members in Active status will be estimates of earned grant amounts based on days served, not 

actual earned amounts based on hours served.

2 For program year 2012 (2012-2013 service) some grantees were permitted to convert full-time members to less 

than full-time. The tool does not calculate an accurate earned grant amount based on service by less than full time 

members. A future edition of the tool will automatically make the appropriate calculation.
3 | P a g e

Edition 3.0, May 16, 2014

To use the tool:

1. Open the appropriate template file:

Select and open the template you will use based on the maximum number of members 

enrolled in the program, including re-enrollments. Because larger spreadsheets run more 

slowly, templates are designed to calculate for programs with < 500, <1000, <3000, <5000, 

and 7500 or fewer members. The best template to use will be the one for testing the fewest 

number of members. 

Caution – always begin with a new template file. Do not re-use a file because you risk 

using an outdated version of the template. The only circumstance when it is reasonable to 

re-use a template is if you are making determinations for several grants in sequence and 

the risk of a new edition of the tool being published while the work is performed is low.

2. Save the template file, renaming it to identify the grant being tested.

The renamed file is now the record you will use to establish the earned grant amount. 

Observe that the tool has three tabs:

 Tab 1: Drawdown Calculations & Amounts

 Tab 2: Analytical Tests

 Tab 3: Copy & Paste Roster Report Here

3. Prepare for member roster data entry on the third tab “Copy & Paste Roster Report 

Here.”

Click on the third tab, “Copy and Paste Roster Report Here.” You should be viewing a blank 

worksheet tab. This is the worksheet where you will paste the Roster Report for the grant

you are testing. Click on cell A1, make sure that cell A1 is highlighted.
4 | P a g e

Edition 3.0, May 16, 2014

4. Go to the My AmeriCorps Portal to extract and save a Roster Report for the grant or 

subgrant being tested.

Extract a Roster Report in “Comma Separated Value” (CSV) format. Hint – “Program 

Code = grant number; leave program year blank.

Save the Roster Report CSV file. The newly saved Member Roster Excel file will contain a 

single worksheet tab with data. The member roster data will either be for the single national 

direct grant or for a single state commission sub-awards being examined. In the saved 

member roster Excel file, click on the small block located between Column A and Row 1. 

Note – some of the content above and throughout this guide is redacted because member 

rosters contain personally identifiable information (PII). Be sure that only individuals 

authorized to handle PII are accessing the Trust member roster. 

Best Practice - save and resave your file after each step to ensure you do not lose your 

work.
5 | P a g e

Edition 3.0, May 16, 2014

All rows and all columns should now be highlighted. Right click “Copy” to copy the data.

5. Paste the Roster Report data into the tool at tab 3, cell A1.

Return to the tool file which should still be at Tab 3, with cell A1 selected. Re-click in cell 

A1, then Right-click “Paste.” You should see the worksheet at Tab 3 fill in with the same 

Roster Report data you saved in the Roster Report file. 

It is critical to verify that the member roster data has been correctly placed into Tab 3 of the 

tool. Do this by making sure that each member record row shows the Grant Year under 

Column A (e.g., data such as 2010, 2011, 2012, etc.). If Grant Year is not under column A, 

delete the content and try again. 
6 | P a g e

Edition 3.0, May 16, 2014

Do not be concerned that some cells fill with header data from the Roster Report or if some 

columns are not wide enough to display the data.

6. On Tab 1, Enter Date of Calculation.

At the upper left corner is a cell to the right of “ENTER the AS-OF DATE” Enter the date 

that the Member roster was extracted. Omitting this date will cause an error in the 

calculation of earned grant amounts.

7. Enter SLOT award data from the Notice of Grant Award (NGA)

For the award being examined, identify and enter the number of SLOTS awarded for each 

program year. On the tool, at Tab 1, go to the yellow shaded cells near the top left of the 

worksheet. This area is labeled, “Enter SLOT Data Here.” Under the appropriate year 

column(s), enter the corresponding number of full-time SLOTS awarded for that year. In a

green shaded row below your entries you will observe an estimated Awarded MSY per Year 

Based SLOTs figure increasing as you enter each SLOT number. Since you are testing fulltime fixed price awards, the SLOT and MSY figures will be identical.

8. Enter Award Amount per Year from NGA 

In the row shaded yellow labeled, “Enter Approved Amount (Total Budgeted Amount) by 

Year,” Enter the “Approved Amount” found on the Notice of Grant Award for each year the 

grant was funded. Page through all NGAs to ensure that you identify the Approved Amount 

for each funded grant year. Per year, this amount includes any approved carryover 
7 | P a g e

Edition 3.0, May 16, 2014

amount. Omitting the carryover amount will result in a lower, incorrect potential earned 

maximum amount per member.

As you enter the Approved Amount for each year (typically for 3 years), the tool presents the 

“Estimated Award Per MSY Per Year” in a green shaded row directly below your entries. 

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

for that year (light blue row). If the estimated amount exceeds the allowable maximum for 

that year there is a potential problem with the financial parameters of the grant award. 

Investigate and resolve this discrepancy before proceeding. 

9. Enter the total amount drawn as of the date being tested

In the yellow row labeled, “Enter Amount Drawn down as of the Date Being Tested,” enter

the amount that drawn down for this grant from HHS PMS as of the date the roster was 

extracted.
8 | P a g e

Edition 3.0, May 16, 2014

10. Review analysis presented by the tool

At Tab 1, lower left, across from the row heading, “Result of Analysis” the tool will present 

the per year amounts as:

 Total MSY Value by Year

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

90% will be flagged with red highlighting, but has no relevance to validating the 

earned grant amount

 The earned grant amount by year

 Total earned grant amount

 The difference between the earned and drawn amounts. Negative amounts (overdrawn) are shaded red and positive amounts (remainder to draw) are shaded green.

Caution –if the tool reflects over-enrollment in any year, compare the earned grant 

amount for each year to the awarded amount for each year. This comparison is to ensure 

that the tool is not estimating that more money was earned than was awarded for an 

individual program year.

3

11. Warning – Members in Active, Transfer IN, Reactivated, and Suspended Status

The tool credits a grantee with an estimated earned grant amount for members in Active, 

Transfer IN, Reactivated, or Suspended, not the actual earned grant amount. This is because 

Members in these statuses do not have hours served recorded in the Trust. The estimate is 

 

3 A rare but theoretically possible scenario might cause the tool to calculate an earned grant amount greater than the 

awarded total federal dollars for an individual program year. A future version of the tool will automatically protect 

against this unlikely circumstance.
9 | P a g e

Edition 3.0, May 16, 2014

based on days of service which is not a reliable measure of the final earned grant amount for 

these members. 

At the bottom of tab 1 the tool presents counts and percentages of members in Transfer IN, 

Reactivated, or Suspended status. Counts greater than zero, and ratios of 10 percent or more, 

are highlighted red to alert the user that the earned grant amount includes estimated amounts 

for these members and thus the overall earned amount cannot be used for closeout.

12. Warning - Members in 3rd / 4th Terms of Service

Due to a Trust software flaw, in limited cases, members serving a third or fourth term of 

service might be identified on the roster as in “unknown award status” without hours served 

identified. This flaw appears to be limited to members who entered into 3rd/4th terms in the 

early stages of CNCS permitting third and fourth year enrollments. We believe the flaw is 

fixed and subsequently enrolled members in 3rd/4th terms have the hours show up.

If a program has members in the third or fourth term, and the members were enrolled before 

the flaw was fixed, the tool will not automatically assign an earned grant amount to those 

members. If there is proof that a member has served the hours and this technical glitch is not 

recording the member’s hours on the roster, the user edits the “0” hours on roster tab 3 to be 

the actual hours served. This edit will increase the earned grant showing on tab 2 for that 

member, and automatically increate the total earned grant amount on tab 1.

13. Warning: If Less than Full Time Members Enrolled – DO NOT USE THIS TOOL

The analysis will also present information on how many, if any, members were less than fulltime. At this time this tool cannot be used to establish an accurate earned grant amount if 

one or more members were in less than full time service:

If the “Count of Members Other than Full-time” is greater than zero, it is flagged in red. If 

less than full time member count is more than zero, the earned grant amount calculation is 

not valid. Examine the tables on Tab 1 to determine which years less-than-full-time 

members are enrolled for:
10 | P a g e

Edition 3.0, May 16, 2014

Less-than-full-time members can be individually identified by scrolling through the records 

found on tab 2, Analytical Tests where less than full time members are flagged, and on tab 3 

where the roster was entered into the tool. At Tab 2, column 2, less than full time members 

are flagged with a count of “1” highlighted in red:

At Tab 3 there is a member enrollment code for each individual:

If less-than-full-time members have been enrolled work with your CNCS Grants Officer to 

establish the accurate earned grant amount.

14. User Notes

At the bottom of Tab 1, space is provided to enter user notes on how the calculation was 

performed. There are no restrictions on what is entered into this field.
11 | P a g e

Edition 3.0, May 16, 2014

15. Miscellaneous

 Unless noted otherwise, all calculations and results are unrounded even when a limited 

number of decimal places are shown on the spreadsheets. The calculated earned grant 

award per member and overall earned grant amount are rounded to the nearest cent, 

rounding up at 0.005 and higher.

 User feedback is welcome. Please send user questions and feedback to:

Douglas Godesky

Senior Grants Officer

Corporation for National and Community Service

1201 NY Ave, NW

Washington, DC 20525

dgodesky@cns.gov

202.606.6967 (office)

202.528.3947 (mobile)