Washington State Office of Financial Management American Recovery and
Shared by: knp20248
Washington State Office of Financial Management American Recovery and Reinvestment Act WA Section 1512 Reporting Information & Template Instructions For the most current version of these instructions, please see http://www.recovery.wa.gov/documents/reporting/1512_plus_template_instructions.pdf General Information: This Excel template is to be used by Washington state agency prime recipients to report disaggregated information to supplement your state agency's Federal Section 1512 report under the American Recovery and Reinvestment Act of 2009 ("Recovery Act" or "Recovery"). The template will provide OFM with detailed information on the project and activities conducted by the state agency prime recipient and its sub-recipients. This information will be used by the Governor and Recovery Team to communicate the specific and localized uses of Recovery Act funds in Washington State. To reduce duplication of effort, the WA Section 1512 report maintains consistency with the Federal Section 1512 reporting scheme. However, the terms and definitions used in the WA Section 1512 report may differ from the Federal 1512 report. The WA Section 1512 report is supplemental to the Federal Section 1512 report that each state agency prime recipient submits to www.FederalReporting.gov. Report Due Date(s): · WA Section 1512 reports are due at the same time as any Federal Section 1512 report or when correction/adjustments are submitted to www.FederalReporting.gov. Therefore, WA Section 1512 reports are due by 8:59 Pacific Standard Time on the 10th day following the end of a calendar quarter, along with your Federal Section 1512 Report. (Per new guidance issued on December 18, recipients have until January 15, 2010 to report for the second quarter.) If adjustments or corrections are made to your Federal Section 1512 report, the WA Section 1512 needs to be updated and both reports resubmitted to OFM. Reporting Period: Quarterly federal and state Section 1512 reports should have the same reporting period. For example, the first Federal Section 1512 report submitted by October 10, 2009 covered the period of the inception of the Recovery Act through September 30, 2009, and therefore, was cumulative through September 30, 2009. The WA State Section 1512 report would cover the same period of time. Reporting Receipt of New Awards: · If your agency receives a new award, within two business days of receiving the official federal award notice, please send the following information to firstname.lastname@example.org: · An ADA-accessible copy of the award notice; · If not indicated on the official award notice, indicate award number, place of performance and the grant CFDA number; · Two points of contact for the award (name, phone number, email) -- programmatic lead and the accounting and/or reporting lead for federal and state reporting; · Agency DUNS number if this is your first Recovery grant (disregard if this has already been provided to OFM or the Recovery Team). Saving Files: Excel File Naming Convention: Select "save as" to save the file for submission. You may use a file name of your choice or follow the OFM suggested naming convention: Your agency acronym, the award number for which you are reporting, WA1512, and the submission date (MMDDYYYY). For example: COMDE-OE0000060WA151210082009.xls. Be sure to save your file to an .xls extension. Whatever convention you use, please be consistent with the convention used for your Federal Section 1512 report. For example, using the same scheme, that report would be your agency acronym, the award number for which you are reporting, Fed1512, and the submission date (MMDDYYYY). For example: COMDE- OEFed151210082009.xls. Be sure to save your file to an .xls extension and keep a copy of your submission. NOTE: Only completed templates will be accepted for upload. Submitting Updated Federal or WA Section 1512 reports: Because the data between the Federal Section 1512 report and the WA Section 1512 report are linked, updates need to revise both reports even though you may not be submitting a Federal Section 1512 report to www.FederalReporting.gov. You are provided with a Comment box to assist OFM with understanding why you are submitting a Federal or WA Section 1512 report. Choose from the following: · Quarterly Federal and WA Section 1512 report due on Month Day, Year (such as October 10, 2009). · State Agency correction to Federal and WA Section 1512 report due on Month Day, Year (such as October 10, 2009). Include a brief description of the reason for the adjustment. · Federal Awarding Agency Directed Correction to Federal Section 1512 report due on Month Day, Year (such as October 10, 2009). Include a brief description of the reason for the adjustment. NOTE: Make sure you have written documentation from the federal awarding agency regarding the correction. A copy of this documentation needs to be sent to email@example.com and MUST BE AVAILABLE for auditors. Brief descriptions should allow OFM to identify the specific data field(s) that have changed from your prior report. Example: · "Jobs number increased due to new sub-recipient information." · "Sub-recipient deleted from report" · "Total Federal Amount Recovery Funds Received/Invoiced adjusted." · "Federal awarding agency directed us to correct the following information…." Detailed Instructions on Template Enabling Security/Macro: You need to have security or Macro enabled to use the template functionality (such as Insert Project, Add Sub award, etc.). Depending on your Microsoft Office version, the security or Macro button is located in different places. · For Microsoft 2003 see http://office.microsoft.com/en- us/excel/HP052356701033.aspx · For Microsoft 2007 see http://office.microsoft.com/en- us/help/HA100310711033.aspx#12 New - Import Feature: A new feature has been added to the WA Section 1512 template to enable agencies to easily import data from their Federal 1512 report into a WA Section 1512 detail report. This feature should only be used if your agency has only one project to report for Prime and/or Sub-recipients. If your agency meets this criteria, you may select the 'Import 1512' button found on the Prime Recipients Projects tab, navigate to your Federal 1512 report, and open. This feature populates your WA 1512 report and includes a project ID. Pasting Text From Other Files: You can paste text from other files into the WA 1512 detail report; however, to ensure the continued ability to edit the field to which you are pasting, be sure to select 'paste special' then select 'text' as the paste option. Understanding Report Tabs: The template contains two tabs: "Prime Recipient Projects" and "Sub Recipient Projects". · Use the "Prime Recipient Projects" tab to report details on projects and activities conducted by your agency as a prime recipient. · Use the "Sub Recipient Projects" tab to report details on projects and activities conducted by your sub-recipients who received Recovery Act funds as a sub-award from your agency. · AVOID DOUBLE COUNTING EXPENDITURES: The sum of the "Total Federal Amount of Recovery Expenditure" reported on the Prime Recipient (column V) and Sub-Recipient (column R) tabs on the WA 1512 Detail report should match the "Total Federal Amount of Recovery Expenditure" your agency reported on your Federal 1512 report. Report Data Elements: As stated earlier, some terms and definitions used in the WA Section 1512 report may differ from the Federal Section 1512 report. However, none of the information should conflict with the Federal Section 1512 report. WA Section 1512 reports should be more detailed and represent the disaggregation of the Federal Section 1512 report. Note for Required Fields*: * Asterisked fields are required for upload. For numeric fields such as no dollars or no jobs reported, enter 0 in the field. For text fields that are not applicable, enter "Does not apply" in the field. To guide your understanding of the differences between the Federal Section 1512 report and WA Section 1512 report, the following scenario will be used in examples throughout the instructions. The United States Environmental Protection Agency awards the Department of Fish & Wildlife (DFW) $5 million for estuary restoration. The only award requirement is that some of the funds are to be disbursed to local or tribal governments. DFW retains $2.5 million for two projects - $1.5 million to restore an estuary in the Johns River Wildlife Area and $1 million to restore the estuary in the Willapa National Wildlife Refuge. The other funds are distributed to: · King County - $500,000 to restore the Duwamish estuary. · Nisqually Tribe - $1.5 million to restore the estuary at the Nisqually National Wildlife Refuge. · Department of General Administration - $500,000 to return the Capitol Lake to an estuary of the Deschutes Basin. Under the "Prime Recipient Projects" tab: 1. Award Number: Enter the award number found on your federal award document. This number is assigned by the awarding Federal Agency and is the same information provided on your Federal Section 1512 report. OFM is using the Award Number to track both Federal and WA Section 1512 reports. 2. Prime Recipient Project ID: We are asking you to create a unique identifier for each project or activity that you are conducting yourself as a prime recipient. We recommend the following naming convention to uniquely identify each project within an award: Agency acronym (e.g. DFW for Department of Fish & Wildlife, P (for Prime Recipient), and a number (1-99). Example: DFWP1. If you have already established your own naming convention, you may continue to use it so long as it uniquely identifies all projects associated with an award. Whether your agency uses the naming convention recommended by OFM, or uses an already established convention, the important point is DO NOT change it once you have used it in a report for any other project. Because DFW does not have its own naming convention, DFW would enter DFWP1 for the Johns River Wildlife Area project and DFWP2 for the Willapa National Wildlife Refuge project. 3. Amount of Award: Enter amount awarded for each project. Example: DFW would enter $1.5 million for DFWP1 (Johns River Wildlife Area project) and $1 million for DFWP2 (Willapa National Wildlife Refuge project). NOTE: Only report the funds retained for use by you, the prime recipient, here. The other amounts in our example -- King County, Nisqually tribe, and Department of General Administration -- are reported as sub-awards on the next worksheet. 4. Total Number of Sub-Awards less than $25,000/award: This field should only be populated if your federal agency prohibits disaggregation of sub- awards less than $25,000/award. If this is the case, indicate the number of awards less than $25,000/award in this field AND notify OFM at firstname.lastname@example.org of the federal agency prohibiting disaggregation. 5. Total Amount of Sub-Awards less than $25,000/award: This field should only be populated if your federal agency prohibits disaggregation of subawards less than $25,000/award. If this is the case, indicate the amount of awards less than $25,000/award in this field AND notify OFM at email@example.com of the federal agency prohibiting disaggregation. 6. Project Name or Project/Program Title: Unlike the Federal Section 1512 report that requires projects and programs to be described in broad terms, enter a specific name for the each project. The project name must be consistent and relate to the public documents provided to your federal awarding agency, external communications, and GMAP reporting. OFM's intention is not to create a "new" list of projects. Rather, we want to align our WA Section 1512 database to the project and activity descriptions and list already being used by each state agency prime recipient to describe the uses of Recovery Act funds. Example: DFWP1 for the Johns River Wildlife Area project and DFWP2 for the Willapa National Wildlife Refuge project. 7. Project Status: Select project status from the drop down menu. OFM is using the same four options used in the Federal Section 1512 report: 1) not started, 2) less than 50% completed, 3) completed 50% or more, or 4) fully completed. Unlike the Federal Section 1512 report that requires you to average the progress of all projects to determine your selection, the WA Section 1512 report project status should be based on the progress of each project. If your award requires that project status be based on expenditures, then base the progress on the expenditures made for each project. Example: DFWP1 (Johns River Wildlife Area project) was able to quickly begin work and is 40% complete, enter "less than 50% complete". However, DFWP2 (the Willapa National Wildlife Refuge project) will require further environmental review and has not started. Therefore, for DFWP2, enter "not started". 8. Total Federal Amount Recovery Funds Received/Invoiced: For each project, indicate the amount of Recovery Act funds received through draw-down, reimbursement or invoice. Example: DFWP1 (Johns River Wildlife Area project) is 40% complete and has drawn-down 35% of its funds, enter $525,000. DFWP2 (the Willapa National Wildlife Refuge project) has not started; it has no draw-downs yet. Therefore, for DFWP2, enter $0. 9. Number of Jobs: For each project, indicate the number of jobs created/retained. The method for determining the number of jobs is the same as the Federal Section 1512 report. Example: DFWP1 (Johns River Wildlife Area project) has charged 3500 hours to Recovery Act funds. Enter 6.73 FTEs (40 hours/week; 3500 hours ÷ 520 hours in the quarter). DFWP2 (the Willapa National Wildlife Refuge project) has hired a water quality consultant to help with its environmental review. The consultant has charged 300 hours to Recovery Act funds. Therefore, report 0.58 FTE for DFWP2. 10. Description of Jobs Created: For each project, describe the jobs created/retained. Consistent with OFM instructions for the Federal Section 1512 report, use NAICS codes to describe jobs. See a complete listing of codes on the NAICS NTEE tab or 2-Digit-NAICS tab for reference. Example: DFWP1 (Johns River Wildlife Area project) jobs have generally been in activities to remove dikes, trails, and earth. That falls under "demolition contracting" (NAIC 238910). DFWP2 (the Willapa National Wildlife Refuge project) is on water quality, which can be described as "environmental engineering services" (NAIC 541330). 11. Quarterly Activity/Project Descriptions: For each project, provide an expanded description/purpose for each of the projects on your project list. Like the Federal Section 1512 report, this expanded description should describe the overall purpose and expected outputs and outcomes or results of the project. In sum, in plain talk, tell us more about this project and why it is great. Include performance measures when possible. 12. Activity Codes: For each project, provide at least one North American Industry Classification System (NAICS) code or National Center for Charitable Statistics (NTEE-NPC) code to describe each project. Example: Both DFWP1 (Johns River Wildlife Area project) and DFWP2 (the Willapa National Wildlife Refuge project) can be best described with NTEE-NPC Code "C05.04 Marine Conservation". For your convenience, this template allows you to search for Activity (NAICS / NTEE-NPC) codes by name. For example, with your cursor in the cell below "code 1", you may then click the "Lookup Activity by Name" box and select the appropriate name to automatically populate the cell or simply type in the Activity Code if known. The NAICS NTEE tab of this workbook contains full code and descriptive information for reference. 13. Total Federal Amount of Recovery Expenditure: For each project, provide the amount of recovery funds expended. Include all expenditure data, both cash and accruals posted to the official accounting system (AFRS) as of the last day of the reporting period. Example: DFWP1 (Johns River Wildlife Area project) is 40% complete and has expended $700,000; that amount should be entered. DFWP2 (the Willapa National Wildlife Refuge project) has not started, but it has spent $15,000 for its environmental engineer. Therefore, for DFWP2, enter $15,000. NOTE: Drawdowns (step 8) do not necessarily match expenditures. (However, drawdowns should never be for more than what has been expended.) 14. Total Federal Recovery Infrastructure Expenditure: If applicable, for each project, provide the amount of Recovery Act funds expended on infrastructure. This amount should be equal to or less than the Total Federal Amount of Recovery Expenditure. (Step 13) 15. Infrastructure Purpose and Rationale: If applicable, for each project, provide a description of the purpose of the infrastructure funds and the rationale as to why this specific project was selected by WA for Recovery Act funds. 16. Primary Place of Performance: For each project, provide a location for where the project is taking place. If there are multiple performance locations, use a location that best describes the impact of the Recovery Act funds. Example: DFWP1 (Johns River Wildlife Area project) is located in unincorporated Grays Harbor County closest to Aberdeen, WA. Enter zip code 98520-5297. DFWP2 (the Willapa National Wildlife Refuge project) is located in unincorporated Pacific County closest to Raymond, WA. Enter zip code 98577- 2405. Congressional District information is located on the Congressional tab. Note that some counties are in multiple districts. Insert Project: Select the "Insert Project" button if you need to report more projects under the same award. Under the "Sub Recipient Projects" tab: Complete the same field for the projects being conducted by your sub-recipients using the same definitions used for Prime Recipient Projects. Example: King County, the Nisqually Tribe and the Department of General Administration are sub-recipients. Provide the same detailed information for each project being conducted under each sub-award. However, there are two unique data fields for sub-recipients: Sub-Award Number: Enter the same sub-award number entered for the sub- recipient in the Federal Section 1512 report. Sub-Recipient Project ID: Like projects of prime recipients, create a unique identifier for each project or activity that is being conducting by your sub- recipients. If there is only one project for a sub-award, you may use the sub-award number as the sub-recipient project ID. However, if there is more than one project, identify each uniquely. OFM recommends using the following naming convention to uniquely identify each project within a sub-award: Sub-recipient acronym, S (for sub-recipient), and a number (1-99). Example: King County could be KingCoS1, the Nisqually Tribe as NisquallyS1, or General Administration as GAS1. If King County was conducting two separate projects in the Duwamish estuary within its sub-award, you could have KingCoS1 and KingCoS2 to separately identify each project. Continue to use your own naming convention so long as it uniquely identifies all projects associated with a sub-award. Whether your agency uses the naming convention recommended by OFM, uses the sub-award number, or uses an already established convention, the important point is DO NOT change it once you have used it in a report for any other project. Insert Project: Select the "Insert Project" button if you need to report more projects under the same award. Add Sub Award: Select the "Add Sub Award" button if there are more sub- awards under this award.