Award Tracking Report Users Group – October 12, 2005

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