Award tracking report users group october 12 2005
1 / 30

Award Tracking Report Users Group – October 12, 2005 - PowerPoint PPT Presentation

  • Uploaded on

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.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' Award Tracking Report Users Group – October 12, 2005' - sophia-strong

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Award tracking report users group october 12 2005

Award Tracking ReportUsers Group – October 12, 2005


  • Report Purpose

  • Selecting Parameters

  • Reading the Report

  • Extraction to Excel (Webinar)

  • Manipulating the Output (Webinar)

Award tracking report purpose
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
Executing The Report

  • Access for Internal Users Only

Selecting parameters1
Selecting Parameters

  • Quick Reference Guide


    • >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 parameters2
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 parameters3
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 parameters4
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
Reading the Report

  • Quick Reference Guide


    • >Grants Online Training > Award Tracking Report

Reading the report2
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 report3
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 report4
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 report5
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 report6
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 report7
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 report8
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
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. contact Mark La Fave, call 1-301-713-1000 or, send a message to this address: [email protected]

Extraction to excel1
Extraction to Excel

  • Template File


      • >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 excel2
Extraction to Excel

  • Execute Report

    • Select Parameters, Click “Run Report”

    • Do you want to Open or Save This File?


Extraction to excel3
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 excel4
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.

Extraction to excel5
Extraction to Excel

  • MyReport.xls now has the report properly formatted

  • Save the file

Manipulating the output
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 output1
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 output2
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 output3
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

Manipulating the output4
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]

Additional resources
Additional Resources

  • Go to the Grants Online PMO Website at Access: