Transcript Document 7654632

National Oceanic and Atmospheric Administration
Award Tracking Report
Users Group – October 12, 2005
Overview
 Report Purpose
 Selecting Parameters
 Reading the Report
 Extraction to Excel (Webinar)
 Manipulating the Output (Webinar)
Page
2
Award Tracking Report - Purpose
 Provides a Management Tool for Planning and Execution of
the Grants Process
 Purpose

Track Award Actions Through Grants Online
 Some Caveats



The Award Tracking Report cannot summarize all of a Program
Office/Officer’s Awards
Requires applications to receive an award number (performed
during conduct negotiations) prior to being listed on the Award
Tracking Report.
Summary information is not available directly from the Award
Tracking Report. To get summary information, you must manipulate
the data after extraction to Excel.
Page
3
Executing The Report
 Access for Internal Users Only
Page
4
Selecting Parameters
Page
5
Selecting Parameters
 Quick Reference Guide

http://www.rdc.noaa.gov/~grantsonline/

> Grants Online Training > Award Tracking Report
 Parameters are Restrictors
 Only 2 Required – Start/End FY
 Some Parameters Dependent on Others

Line Office > Program Office > FPO

Line Office > CFDA
 Select Checkboxes AFTER
Line Office > Program Office > FPO
Page
6
Selecting Parameters
 Start/End Fiscal Year

Last complete year of awards is FY 2003

Some awards go back to 1995
 FPO


A Program Office is required in order to select FPO
May need to run report multiple times to get all
actions
—
Alternatively, run report for the entire LO and sort
results by FPO.
Page
7
Selecting Parameters
 Funded Only


A “Funded Award” is an award that has money, not
one that has been signed
Cannot get “Unfunded Only”
 Recipient Name/City

Use wildcards - %

Not Case Sensitive
 Congressional District

Doesn’t make sense without state

Precede single-digit districts with “0”, e.g. 01
Page
8
Selecting Parameters
 Recipient Type

Unreliable until migrated data is cleaned
—
Target Date: October 1, 2006
 Signed Award Only

Cannot get “Unsigned Award Only”

Withdrawn/rejected actions are NOT reported
 Award Start/End Dates

Start Date on or after entered date

End Date on or before entered date
Page
9
Reading the Report
 Quick Reference Guide

http://www.rdc.noaa.gov/~grantsonline/

> Grants Online Training > Award Tracking Report
Page
10
Reading the Report
Page
11
Reading the Report
 Amendment Number (B)

No number until signed

X – Workflow is on Award File

A – No Award File yet, Application Only

N – Workflow has gone back to Application for
Negotiations
 Award File Id (C)

If you need Help Desk help, they need this!

Application ID for Amendment Number = A
Page
12
Reading the Report
 Fiscal Year (D)

Determination Order
—
Grants Officer Signature Date
—
Affiliated RFA for Application in Award Package
—
Current Fiscal Year
– No Costs will “Jump” FYs from 9/30 to 10/1
 Program Office (F)

Determination Order
—
Workflow-affiliated Organization for Award File
—
Program Office of RFA for Application in Award File
—
Program Office of RFA for Original Award Application
Page
13
Reading the Report
 FPO (G)

Determination Order
—
Workflow on the Award File
—
Assigned PO to the Application
 Application Due Date (J)

Competitive
—

Due date from competition
Non-Competitive
—
Appropriation Signature Date + 60
Page
14
Reading the Report
 Application Received Date (K)


Major Tracking Milestone
For FY2006 apps – GMAC reps will likely be
requested to supply missing data.
Page
15
Reading the Report
 Grants Specialist (L)

Not determined until sent to GMD
 Federal Share (AG)

Determination Order
—
Signed: Award Document Amount
—
Award File in Progress: Sum of CD435s
—
In Negotiation: Sum of Application Funding Amounts
Page
16
Reading the Report
 Non-Federal Share (AH)

Determination Order
—
—
—
Signed: Award Document Amount
Award File in Progress: Formula based on sum of
CD435s
In Negotiation: Sum of Application Funding Amounts
 Award File Complete (AM)


Complete if all documents are through approval at
Line Office level (CD435, NEPA, PO Checklist)
CD435 will show complete, but still have the red X
to indicate that CAMS personnel are not finished
with it.
Page
17
Reading the Report
 Award Action Status (AN)

Values
—
In Progress GMD
—
In Progress Program Office
—
Signed and Complete
—
N/A
– Not in workflow yet OR
– There is a technical issue
» CALL THE HELP DESK
 Multiyear (AP)

Set to “Y” if any application in the award has more
than one Fiscal Year funding line
Page
18
Extraction to Excel
 Webinar
Topic: Grants Online User Group Meeting
Date: Wednesday, October 12, 2005
Time: 2:00 pm, Eastern Daylight Time
Training session password: grantsonline
Teleconference: 866-427-0077
*3348288*
Session Number: 921 384 123
Please click the following link to see more information
about the training session, including its agenda, or to join
the session.
https://bearingpointnoaa.webex.com/bearingpointnoaa/k2
/j.php?ED=86099782&UID=32023807
To contact Mark La Fave, call 1-301-713-1000 or,
send a message to this address:
[email protected]
Page
19
Extraction to Excel
 Template File

http://www.rdc.noaa.gov/~grantsonline/


> Grants Online Training > Award Tracking Report
Download to Desktop
 Template File > Report File

Open Template File and “Save As…”
—
—
Pick a File Name
For the rest of this exercise, this file will be referred to
as MyReport.xls
Page
20
Extraction to Excel
 Execute Report

Select Parameters, Click “Run Report”

Do you want to Open or Save This File?
—
ALWAYS CHOOSE SAVE
Page
21
Extraction to Excel
 Open the Report Output File
(runReport.xls)
 Highlight and Select all the Data

Start at row below the column headings
 Select is CTRL-C
-or- Choose Select
from Edit Pulldown
Menu
Page
22
Extraction to Excel
 Open the Report File You Created from the
Template (MyReport.xls)
 Place cursor in first data cell
 Right-click and select “Paste Special…”
 Choose the Values radio button and click OK.
Page
23
Extraction to Excel
 MyReport.xls now has the report properly formatted
 Save the file
Page
24
Manipulating the Output
 Where’s my stuff?

#1 Reason to Run the Award Tracking Report
 Assumption

You are a Program Officer in multiple Program Offices in
the same Line Office
—
In this case, you are Cynthia Binkley, a Program Officer in
the FHQ and SERO Program Offices under NMFS
 Run the report for the Current Fiscal Year,
selecting only the NMFS Line Office
 Sort the output by Program Officer and delete
all rows before and after Cynthia Binkley.
Page
25
Manipulating the Output
 How many funded awards were made by NOS in
FY 2005?
 Run the report for the single Fiscal Year,
selecting the NOS Line Office and checking the
Funded Only box
 Sort the output by the Grants Officer Signature
Date

Delete the rows with unsigned awards
 Count the rows for number of Funded Awards
(-1 for column headings)
 Sum the Federal Funding column
Page
26
Manipulating the Output
 What awards were made to the Government of Puerto
Rico from June 2004 through May 2005?
 Run the report for:

FYs 2004 and 2005

Funded Only

Recipient State: PR

Grants Officer Sign Date between 06/01/2004 and 05/31/2005
 Do not enter these parameters

Signed award only (unnecessary)

Recipient Type (Target Reliability Date: October 1, 2006)
 Manually remove the rows for the non-Government
Recipients
Page
27
Manipulating the Output
 Excel Data Manipulation Tips

Use Window > Freeze Panes to keep column headings
and/or award numbers visible

DO NOT have data highlighted when sorting

Use Paste Special
—
—
—
Paste Special > Formats to copy formatting from one
sheet to another
Paste Special > Values to get the data from a results sheet
to a preformatted sheet
Both sheets must be open under the same executable for
Paste Special to provide the appropriate menu
Page
28
Manipulating the Output
 AFTER you copy the report data to the sheet

Delete columns of non-interest
—

Column Select > Edit > Delete
Move columns to preferred order
—
Column Select > Cut (CTRL-X)
—
Column Select > Right-Click > Insert Cut Cells
 Save the Report Template as a Template
 Get the Tech Republic “75 Essential Excel Tips”

Available at the Tech Republic Web Site for members
—

Do not visit unless you have spyware protection
Request from [email protected]
Page
29
Additional Resources
 Go to the Grants Online PMO Website at
www.ofa.noaa.gov/~grantsonline to Access:

Grants Online Training/Support Resources

Frequently Asked Questions

[email protected]
Page
30