To:ARRA Reporting Leads

cc:Recovery Coordinating Committee, Secretariat CFOs and Department CFOs,ARRA Steering Committee, Mass Recovery & Reinvestment Office

From:Ramesh Advani, Deputy Director, Mass Recovery & Reinvestment Office
Jennifer Hewitt, Reporting Manager, Mass Recovery & Reinvestment Office

Date:September 21, 2009

Re:ARRA Data Collection – OMB Quarterly Reporting

The Mass Recovery & Reinvestment Office (PMO) is pleased to announce the release of our Quarterly Reporting database for the collection and review of data for the October 10 report to the Office of Management and Budget (OMB) under the American Recovery and Reinvestment Act (ARRA). The data collected will also populate the commonwealth’s online transparency efforts. This memo outlines 1) the timeline for production, submission and review of the report elements; 2) when an ARRA grant will be included in the report to OMB; and 3) the recommended steps to review the MMARS data and populate the additional data fields. An Appendix describes the database fields and how to maneuver between the screens.

Timeline

Sept. 17 Release of Quarterly Reporting database as read-only

Sept. 21Release of Quarterly Reporting database for data-collection

Sept. 23Vendor, Sub-Recipient and Sub-Recipient Vendor data templates due to be submitted for upload to the Quarterly Reporting database. (Email to .)

Sept. 30Spending cut-off in MMARS for October 10 report

Oct. 1FederalReporting.gov opens for data submission

Oct. 2-9PMO to submit data sets to FederalReporting.gov. Agencies to help PMOQA & review results on FederalReporting.gov

Oct. 10Last day for PMO to submit initial information to FederalReporting.gov for quarter ending Sept. 30; PMO will submit a batch interface for all Commonwealth data

Oct. 11-20Additional QA & review of data sets; corrections made by agencies and PMO in Quarterly Reporting database

Oct. 20PMO to submit final batch interface to FederalReporting.gov

Oct. 21-30Federal agencies to review data and send questions to recipients, with updates made directly in FederalReporting.gov, and in Quarterly Reporting database by the PMO after consultation with the agencies.

Inclusion of Grants in OMB and Transparency Reporting

The October 10 report to OMB will only include ARRA awards that had spending on or before September 30. It will also only include grants that are subject to section 1512 of the act, as listed in the OMB master list. However, for Massachusetts’ transparency efforts, data will be collected for all grants, not just those subject to 1512.

OMB’s data templates have the grants broken out by award, further broken out by projects, with vendors and sub-recipients detailed for each project. The PMO has decided to equate a project to MMARSprograms. Therefore, since most grants only have one program, there will only be one project for those grants. Where a grant has more than one program assigned to it, primarily where there is an interagency service agreement, those programs will each be reported as individual projects within the award, and all of the spending and jobs data for those projects/programs will roll up to be reported at the award level.

Data Hierarchy & Terminology for Quarterly Database

The following diagram of the overall data-levels and their corresponding screens may be helpful to refer to as you review the steps to be taken for the quarterly reporting, and read the appendix describing the database in further detail.

Data-Level / Quarterly Reporting Panels
1. Award / Awards and Prime Recipients
2. Program / Programs for Dept-Award
2A. Vendor / Vendors for Program
2B. Sub-Recipient / Sub-Awards and Sub-Recipients for Program
  • Sub-Recipient Vendor
/ Sub-Vendors for Sub-Award
3. Jobs for 2A, 2B and State FTEs / Vendor Jobs-Hours

Recommended Steps for Reviewing and Populating Grant Data

The Quarterly Reporting database has been pre-populated with data from MMARS, which will be refreshed daily through September 30. Fields with MMARS data are colored yellow, and cannot be edited. As new awards and vendor/sub-recipient relationships are created in MMARS, they will be added to the database as they appear in the Information Warehouse. If pre-populated MMARS information is incorrect, it must be updated in MMARS, not the database.

Some data, primarily the number of jobs, is aggregated from information entered at lower levels. The fields that have been aggregated are colored magenta. Adjustments to fields at the lower levels will trigger updates to these fields.

The steps below focus around 2 themes – review the pre-populated MMARS data and complete the non-MMARS fields for narrative information and jobs data.

  1. Open the Quarterly Reporting database from Sp. Plans / H.1 menu. The awards that are assigned to your department will be listed. This list is displayed at the department level, meaning that if an award has been distributed between multiple departments, it will be displayed multiple times – once for each department. However, only the parent department information will be reported at the award level.
  2. Select an award and go to the Award tab to
  3. review the attributes for that award from MMARS (yellow fields) and
  4. add an Award Description (defined in Appendix A).
  5. Go to the Recipient tab to review the MMARS data for your department.
  6. The default for Recipient primary place of performance is the address of the department’s headquarters. If another location should be used for the award, please select the Recipient Primary Place of Performance button, and either select a different location from the options available, or add a new one. Addresses from MMARS (yellowed fields) cannot be edited.
  7. From the Award tab, select the Programs Assigned to this Award button to bring up the Programs panel. All of the programs assigned to the award are listed.
  8. Select a program and go to the Quarterly Report Information tab to
  9. review the program attributes from MMARS (yellow fields) and
  10. complete the following fields(defined in Appendix A):
  11. Project Status
  12. Quarterly Activities – Project Description
  13. Activity Codes (list from NAICS or NTEE-NPC)
  14. Description of Jobs Created
  15. Program Primary Place of Performance
  16. If the federal awarding agency has identified the award as an infrastructure grant, go to the Infrastructure tab and complete the information on infrastructure spending, status, narrative and contact information. At this time, the PMO is not interested in trying to isolate specific expenditures within a grant as being infrastructure or not. If a federal agency has given specific guidance otherwise, please alert the PMO. A known exception is at the U.S. Department of Education. The infrastructure fields are defined in Appendix A.
  17. On the Quarterly Report Information tab, there are buttons for Vendors and Sub-Recipients. Selecting each will bring up either the Vendors or Sub-Recipients panels.
  18. Select the Vendors button to bring up the Vendors panel. All of the Vendors with encumbrances or expenditures in MMARS for this program are listed.
  19. Select a vendor and go to the Edit tab to review the MMARS attributes for the vendor.
  20. Adjust the Vendor Primary Place of Performance if the bulk of the work completed under the contract will not take place in or near the Vendor’s headquarters.
  21. Select the Sub-Recipients button to bring up the Sub-Recipients panel. All of the sub-recipients with encumbrances or expenditures in MMARS for this program are listed.
  22. Select a sub-recipient and go to the Edit tab to review the MMARS attributes for the sub-recipient.
  23. If a sub-recipient receives more than 80% of its funding from the federal government, or does not have its salary information displayed on a publicly available web site, they must report their top 5 highly compensated officers. Select “Yes” from the Reporting Applicability drop-down, and enter thecompensation information on the Highly Compensated Officers tab.
  24. Adjust the Sub-Recipient Primary Place of Performance if the bulk of the work completed under the sub-award will not take place in or near the sub-recipient’s headquarters.
  25. From the Sub-Recipients panel, select the Sub-Recipient Vendor button to bring up the Sub-Recipient Vendor panels. No MMARS data is known for these vendors. It will be up to departments to either provide the information via the Sub-Recipient Vendor template that the PMO will upload into the database, or to data-enter the information directly into the database. To add a new entry, select the Add Sub-Vendor button.
  26. From the Recipients, Vendors, Sub-Recipients or Sub-Recipient Vendors panels, select the Jobs button to open the Jobs panel. This displays the jobs detail for each of the entities. There is one tab for the demographic information, and another that displays jobs by zip code. Data may be revised as needed, except for that of state employees taken from HR/CMS. The FTE values will recalculate once the Save button is selected.
  • See Appendix B for the approach taken to calculate State FTE values. State FTE values have not been calculated yet, but should be available later this week, and will be finalized once payroll runs on September 29.

Additional Information

Once your initial review and data-entry is complete, continue to monitor the database for changes in MMARS information, particularly for new vendors and sub-recipients. The Vendor-Sub-Recipient report, also available from the Sp. Plans/H.1 tab, should be helpful for that since the date that a particular relationship was added to the database is noted.

Reports for all of this data will be released over the next week. We are planning individual reports to show all information at the award, program, vendor, sub-recipient and sub-recipient levels and jobs. A report will also pull the narrative fields for ease of central review and editing.

To access the Platform program, it must first be installed on your desktop. Please go to instructions on how to install the application. After installing it, double-click on the icon on your desktop. To add a new user or if you have trouble accessing the program, please .

Appendix A – Data-Entered Field Definitions

The narrative fields that will require data definitions are listed below. They are primarily taken from the OMB data definitions. Definitions for the Jobs-level fields are captured in the jobs template instructions.

Award Panel

Award Description – Award title and description, with purpose of each funding action if any. The description should capture the overall purpose of the award. (Examples: community development; comprehensive community mental health services to adults with a serious mental illness). 4,000 characters maximum.
It may be helpful for departments to refer to the CFDA website to pull the description from there. Grant letters should also have a description for the grant, and may be useful here.

Program Panel

Project Status – Range to describe completion status of project, activity or award. Available options are Not started; Less than 50% completed; Completed 50% or more; or Fully completed.

Quarterly Activities/Project Description – Description of overall purpose and expected outcomes or results of the award and sub-awards, including significant deliverables and units of measure, if appropriate. For an award that funds multiple projects such as a formula or block grant, the purpose and outcomes or results may be stated in broad terms. 2,000 characters maximum.

Activity Code – A selection from a pre-defined list. Infrastructure projects will choose codes from the North American Industry Classification System (NAICS), with the remainder choosing codes from the NationalCenter for Charitable Statistics (NTEE-NPC). Links to the lists are included on the data-entry panel.
Enter a code, and select the “Add New Code” button. You may enter up to 10 activity codes. For each one, please note the title of the one selected. If the data format does not match the pre-defined OMB list, the title will help us discern which one was intended to be used.

Description of Jobs Created – A narrative description of the employment impact of the Recovery Act funded work. This narrative is cumulative for each calendar quarter and at a minimum, will address the impact on the recipient’s and sub recipients’ and vendors’ workforce.

At a minimum, provide a brief description of the types of jobs created and jobs retained in the United States and outlying areas. ‘‘Jobs or positions created’’ means those new positions created and filled, or previously existing unfilled positions that are filled, as a result of Recovery Act funding. ‘‘Jobs or positions retained’’ means those previously existing filled positions that are retained as a result of Recovery Act funding. This description may rely on job titles, broader labor categories, or the recipient’s existing practice for describing jobs as long as the terms used are widely understood and describe the general nature of the work. 4,000 characters maximum.

Primary Place of Performance–The physical location of the Project. City/town and congressional district will be derived from the ZIP+4 entry.

Infrastructure Purpose and Rationale–For infrastructure grants only, a identify the purpose and describe how the project meets one or more of the goals of the ARRA act. 4,000 characters maximum.

(1) To preserve and create jobs and promote economic recovery.

(2) To assist those most impacted by the recession.

(3) To provide investments needed to increase economic efficiency by spurring technological advances in science and health.

(4) To invest in transportation, environmental protection, and other infrastructure that will provide long-term economic benefits.

(5) To stabilize State and local government budgets, in order to minimize and avoid reductions in essential services and counterproductive state and local tax increases.

Infrastructure Contact Information – Name, phone, email, and address information for the appropriate contact for infrastructure awards. We recommend that since this contact information will be posted on Recovery.gov, the person listed should be someone who is able to address media requests and general calls from the public.
To add an entry, select the Infrastructure Contact button, and Add a Line from the Contacts screen. Then enter the relevant information on the Edit tab.

Appendix B – Counting State ARRA FTEs for OMB

As with Vendors and Subrecipients, state agencies will have to report on the number of FTEs supported by ARRA. To implement this, ANF is taking a straightforward approach and counting only those employees who are actually being paid from ARRA sources. The approach is outlined below.

For all instances where an employee’s payroll (AA, CC) is charged against an ARRA program, at the person level:

  • Capture hourly rate
  • Divide rate into total paid (all object codes) = hours worked
  • Pull 9/30/09 standard hours, or most recent pay period if terminated
  • Divide hours paid by standard hours and then by 13 weeks = OMB FTE

Apply FTE value to demographic categories for employee (pull from current HR/CMS record)

  • Age as of 9/30/09
  • Male/female
  • Race
  • Disability
  • MA resident
  • ZIP

1