1 / 30

Award Tracking Report Users Group – October 12, 2005

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Award Tracking ReportUsers Group – October 12, 2005

  2. Overview • Report Purpose • Selecting Parameters • Reading the Report • Extraction to Excel (Webinar) • Manipulating the Output (Webinar)

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

  4. Executing The Report • Access for Internal Users Only

  5. Selecting Parameters

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

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

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

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

  10. Reading the Report • Quick Reference Guide • http://www.rdc.noaa.gov/~grantsonline/ • >Grants Online Training > Award Tracking Report

  11. Reading the Report

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

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

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

  15. Reading the Report • Application Received Date (K) • Major Tracking Milestone • For FY2006 apps – GMAC reps will likely be requested to supply missing data.

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

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

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

  19. 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: mark.lafave@bearingpoint.com

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

  21. Extraction to Excel • Execute Report • Select Parameters, Click “Run Report” • Do you want to Open or Save This File? • ALWAYS CHOOSE SAVE

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

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

  24. Extraction to Excel • MyReport.xls now has the report properly formatted • Save the file

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

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

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

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

  29. 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 Dennis.Seem@noaa.gov

  30. Additional Resources • Go to the Grants Online PMO Website at www.ofa.noaa.gov/~grantsonlineto Access: • Grants Online Training/Support Resources • Frequently Asked Questions • GrantsOnline.QandA@NOAA.gov

More Related