Award tracking report users group october 12 2005
This presentation is the property of its rightful owner.
Sponsored Links
1 / 30

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


  • 79 Views
  • Uploaded on
  • Presentation posted in: General

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.

Download Presentation

Award Tracking Report Users Group – October 12, 2005

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


Overview

Overview

  • 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 parameters

Selecting Parameters


Selecting parameters1

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


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

    • http://www.rdc.noaa.gov/~grantsonline/

    • >Grants Online Training > Award Tracking Report


Reading the report1

Reading the 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. https://bearingpointnoaa.webex.com/bearingpointnoaa/k2/j.php?ED=86099782&UID=32023807To 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

    • 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 excel2

Extraction to Excel

  • Execute Report

    • Select Parameters, Click “Run Report”

    • Do you want to Open or Save This File?

      • ALWAYS CHOOSE SAVE


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 www.ofa.noaa.gov/~grantsonlineto Access:

    • Grants Online Training/Support Resources

    • Frequently Asked Questions

    • [email protected]


  • Login