# Presentation: 871024

## Award Tracking ReportUsers Group – October 12, 2005

## Overview

**Report Purpose**
**Selecting Parameters**
**Reading the Report**
**Extraction to Excel  (Webinar)**
**Manipulating the Output (Webinar)**

## 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.

## Executing The Report

**Access for Internal Users Only **

## Selecting Parameters

## Selecting Parameters

**Quick Reference Guide**
- _[http://www.rdc.noaa.gov/~grantsonline/](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**

## 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.

## 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

## 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

## Reading the Report

**Quick Reference Guide**
- _[http://](http://www.rdc.noaa.gov/~grantsonline/)__[www.rdc.noaa.gov/~grantsonline](http://www.rdc.noaa.gov/~grantsonline/)__[/](http://www.rdc.noaa.gov/~grantsonline/)_
- Grants Online Training  Award Tracking Report

## Reading the Report

## 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

## 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

## 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

## Reading the Report

**Application Received Date (K)**

- Major Tracking Milestone
- For FY2006 apps – GMAC reps will likely be requested to supply missing data.

## 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

## 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.

## 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

## 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](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: **__**[mark.lafave@bearingpoint.com](mailto:mark.lafave@bearingpoint.com)**__** **_

## Extraction to Excel

**Template File**
- _[http://](http://www.rdc.noaa.gov/~grantsonline/)__[www.rdc.noaa.gov/~grantsonline](http://www.rdc.noaa.gov/~grantsonline/)__[/](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

## Extraction to Excel

**Execute Report**
- Select Parameters, Click “Run Report”
- Do you want to Open or Save This File?
**ALWAYS CHOOSE SAVE**

## 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**

## Extraction to Excel

**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 **_**S**_**pecial...”**
**Choose the **_**V**_**alues radio button and click OK.**

## Extraction to Excel

**MyReport.xls now has the report properly formatted**
**Save the file**

## 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.**

## 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**

## 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  **

## Manipulating the Output

**Excel Data Manipulation Tips**
- Use _W_indow  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

## Manipulating the Output

**AFTER you copy the report data to the sheet**
- Delete columns of non-interest
- Column Select  _E_dit  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 _[Dennis.Seem@noaa.gov](mailto:Dennis.Seem@noaa.gov)_

## 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
_**GrantsOnline.QandA@NOAA.gov**_