Economic Recovery Home > Indian Reservations Roads Program

FHWA Data Validation - ARRA 1512 Reporting

December 11, 2009

Background

Based on OMB Guidance M-09-21 issued on June 22, 2009, OMB established the reporting website FederalReporting.gov to collect data on the use of ARRA funds that will be publicly available for the purpose of government transparency. All final data will be available to the public on www.Recovery.com. According to OMB Guidance M-09-21, FHWA is responsible for:

• Providing advice and programmatic assistance to the recipients of ARRA funds for implementing data quality;
• Performing limited data quality reviews intended to identify material omissions and significant reporting errors, and notifying the recipients of the need to make appropriate and timely changes;
• Applying the definitions of material omission and significant reporting error in given programs areas or across programs and
• Identifying and remediating systemic or chronic reporting problems.

The FHWA will fulfill these responsibilities to support the recipient's data quality by:

• Implementing a variety of automated data quality review tools to detect common reporting errors, such as those listed in Section 4.3 of OMB Guidance M-09-21.

Data Entry Process

Recipients (States) are required to register on the www.FederalReporting.gov website and provide data on a quarterly basis. Registration for the website began on August 17, 2009. The quarterly reporting cycle ended on September 30, 2009, and the website is open for data collection October 1-10, 2009. The data will be entered in one of 3 formats whose templates and descriptions are available on the website. Additional guidance and downloads, including the OMB Recipient Reporting Data Model v3.0, for recipient data entry for the quarter ending September 30, 2009, are available on the www.FederalReporting.gov. The data in the 1512 report is cumulative. Once the final data have been confirmed and/or corrected by the sponsor (recipient) by October 29, 2009, the data cannot be changed. Data will need to be newly resubmitted every quarter and is independent of previous quarters. The detailed steps for this process are as follows:

1. State prepares 1512 reports (Days 1 — 10 of the Reporting Month)
   1. Using RADS, output as XML files that have been 'zipped.'
   2. Using RADS, output as an CSV file that can be uploaded into a central State system
   3. Using State system as either XML or Excel files

      Note: A single XML file covering all 1512 projects is not acceptable. A crosswalk that describes how RADS data are used to produce each 1512 XML file is included as an appendix in the latest version of the RADS guidance. Please send an e-mail to ARRA.Questions@dot.gov if you have any questions about the crosswalk or would like a copy e-mailed to you.

2. State uploads file to FederalReporting.gov website (Days 1 — 10 of the Reporting Month)
   1. One file at a time; or
   2. As a batch upload

      Note: If the State has not already done so, they can request permission to batch upload their 1512 reports by sending an e-mail to: BatchAccount@ratb.gov.

Agency Validation and Outlier Identifier Processes

1. Beginning the 11th of the reporting month, FHWA will be able to view and review each 1512 report in Recovery.gov.
   1. Beginning the first of each reporting month, each Division should verify that their State has submitted reports, and perform an initial review of the data for obvious errors.
   2. The Office of Highway Policy Information ARRA Team (HPPI-AT) will also monitor States' progress uploading 1512 reports.
2. As soon as a State has submitted their 1512 reports, the HPPI-AT will download the reports and load them into RADS.
3. The HPPI-AT will produce regular reports to help determine which 1512 reports have data that needs to be verified.
   1. A summary report will show the number of reports in each State that fall into the following four categories:
      1. Verified (data matches State provided data in RADS);
      2. Not verified but acceptable (date is reasonably close to what the State has provided in RADS);
      3. Not verified and not acceptable (data doesn't match what the State has provided in RADS); or
      4. Modified by the State.
   2. A list by project will show the difference for each data item between the 1512 report and what has been submitted by the States in RADS.
4. Beginning the 11th of the reporting month, the summary report and list by project will be distributed to the Divisions to verify any data that the HPPI-AT has not been able to verify and deemed unacceptable.
5. Following the data verification, the State will either need to resubmit their 1512 report, or revise their data in RADS. This will initially need to be done by the 20th of the reporting month, although the States will have a second opportunity to revise their data following FHWA's initial review and verification (days 11 — 20 of the reporting month).
6. For States that submit revised 1512 reports, steps 2 - 4 will be repeated.

Data Verification Rules

Data verification, as outlined in OMB Guidance M-09-21, is the responsibility of recipients and the Federal agencies. Below are some examples provided by OMB of how recipients and Federal agencies can ensure the ARRA data quality, completeness, and accuracy:

• Establish control totals (e.g., total number of projects subject to reporting, total dollars allocated to projects) and verify that reported information matches the established control totals;
• Create an estimated distribution of expected data along a 'normal' distribution curve and identify outliers;
• Establish a data review protocol or automated process that identifies incongruous results (e.g., total amount spent on a project or activity is equal to or greater than the previous reporting); and
• Establish procedures and/cross-validation of data to identify and/or eliminate potential 'double counting' due to delegation of reporting responsibility to sub-recipient.

To comply with OMB Guidance M-09-21, FHWA has implemented two types of data verification on the data submitted by the States in RADS. The automated data verification occurs when States upload data into RADS. Records that fail any of the verification rules below are not uploaded and the user is provided with a brief message listing the record and what rule(s) failed. The validation tests and reports described later in this document are intended to catch any data quality issues in 1512.

Automated Data Verification Tests in RADS

The following is a screenshot of the portion data import screen that shows the status along with what records were not imported and the reason why. The 'Row #' corresponds to the row number of the file being imported.

Data Import Results Screen in RADS

The Data Validation Report is the second automatic verification test in RADS. It highlights any projects or awards that fail one or more of the following verification rules:

Regular Data Validation Report

Accessing the Data Validation Report in RADS

The report contains a series of columns at the beginning that indicate, by the presence of an 'X', what validation rule or rules failed for each project. The user can export a CSV version of the report by clicking on the 'Export' button at the top of the report. The following is a screenshot of the Validation Report:

Data Validation Report in RADS

1512 Validation Rules

The OMB Review report in RADS compares the data in each State submitted 1512 report against the corresponding data in RADS. The following is the list of validation rules being checked:

1512 Data Validation Rules

The 1512 OMB Review report can be found under the 'OMB Submittal' tab in RADS. The 'Match' column of the report indicates how closely each record matches the data in RADS. A '1' indicates that all data are exactly the same. A '2' indicates that all data are within the tolerances specified in the validation rules. A '3' indicates that one or more data item is outside the specified tolerances. The following is a screenshot of the OMB Review Report:

OMB Review Report in RADS

By clicking on the 'Export' button, the user is able to create an Excel file that compares key data fields in the States 1512 report against the corresponding data in RADS. Below is a portion of the Excel file:

Excel version of OMB Review Report

The rows of the Excel file alternate between the data in the OMB 1512 report and the data in RADS; each pair of rows has the same Federal-aid project number. The following is a list of all the data items compared in the OMB Review Report:

OMB Review Report Data Fields

1512 Review Report

In addition to the various data quality checks in RADS, FHWA developed the 1512 Review Report to highlight the key data fields in each States' 1512 report that appear to be miscoded. Some of the data checks rely only on the data in the 1512 report, while others compare the data in the 1512 report against the corresponding data in RADS, which are reviewed on a monthly basis and have gone through additional data verification checks described earlier in this document. Data that does not correspond between RADS and the 1512 OMB extract will be flagged for comment/review. The reports are produced on a regular basis as data is submitted to FederalReporting.gov during the quarterly 1512 review process and distributed by HPPI-AT to the FHWA Divisions and the State. HPPI-AT will begin producing the review reports on a daily basis from the 11th through the last day of each report month. The 1512 Review Reports are used to produce the comments that FHWA is required to submit to FederalReporting.gov for each 1512 report.

1512 Review Report

The following is a brief description of the logic for each of the checks represented by the first 15 columns of the report along with the FHWA standardized comment. Reports that fail a data check are indicated by the presence of an 'X' in the appropriate column.

• Total Award=0
  • Indicates when the total ARRA award is zero.
  • Comment: ‘ARRA Funds Received is zero.’ ARRA Funds Received is zero. Federal Funding numbers don't match Federal records.
• ARRA Award=0
  • Indicates when the total ARRA award is zero.
  • Comment: 'ARRA Funds Received is zero.'
• Awarding Agency
  • Indicates when the awarding agency code is not equal to '6925'.
  • Comment: 'Awarding Agency Code is not 6925.'
• Funding Agency
  • Indicates when the funding agency code is not equal to '6925.'
  • Comment: 'Funding Agency Code is not 6925.'
• Exp > ARRA Award
  • Indicates when the total amount of ARRA expenditures is greater than the total amount of ARRA obligations.
  • Comment: 'Expenditures greater then ARRA Funds.'
• Jobs=0, Exp>$2,000
  • Indicates when a project has more than $2,000 in ARRA expenditures and no jobs reported.
  • Comment: 'Number of Jobs is zero and Expenditures greater than $2,000.'
• Low # Jobs/Expend.
  • Indicates when the ratio of jobs per $1,000 expended is less than 0.001.
  • Comment: 'Low number of jobs per $1,000 of Expenditure.'
• High # Jobs/Expend.
  • Indicates when the ratio of jobs per $1,000 expended is greater than 0.05.
  • Comment: 'High number of jobs per $1,000 of Expenditure.'
• Missing POP Info
  • Indicates that one or all primary place of performance data are missing.
  • No reports failed this test; no comments provided.
• Missing Infra Contact
  • Indicates one or all infrastructure contact data are missing.
  • No reports failed this test; no comments provided.
• No Match in RADS
  • Indicates that the Award Number does not have a corresponding Federal-aid project number in RADS.
  • Comment: 'Unable to verify Federal Funding.'
• Funding Didn't Match
  • For 1512 reports that do have a corresponding record in RADS, indicates that one or all finance data do not match the data in FMIS.
  • Federal Funding numbers don't match Federal records.
• Jobs < 95% RADS
  • For 1512 reports that do have a corresponding record in RADS, indicates when the number of jobs reported in the 1512 report are less than 95% of the jobs reported in RADS.
  • Comment: 'Number of Jobs less than expected.'
• Jobs > 105% RADS
  • For 1512 reports that do have a corresponding record in RADS, indicates when the number of jobs reported in the 1512 report are more than 105% of the jobs reported in RADS.
  • Comment: 'Number of Jobs greater than expected.'

FHWA Data Validation Concept of Operations Process Modifications

FHWA acknowledges that 1512 data validation will be an evolving process. Changes could be made in the future to improve the timeliness and accuracy of recipient data reporting. Data validation rules could be modified to facilitate the accomplishment of the FHWA responsibilities outlined in OMB Guidance M-09-21.