ARRA Recipient Reporting

1
ARRA Recipient Reporting

• The Webinar will begin shortly.
• Jim Maras, Deputy Assistant Administrator Water
  and Environmental Programs, Rural Utilities
  Programs, USDA, Rural Development
• Thanks for joining us today!

2
ARRA Recipient Reporting

• American Recovery and Reinvestment Act of 2009
  (ARRA)
• Section 1512 covers Recipient Reporting

3
Data Elements Reporting Data Entry and
Submission

• Who is required to report under the Recovery Act?
• Prime Recipient Non-Federal entities that
  receive over 25,000 in Recovery Act funding as
  Federal awards in the form of grants, loans, or
  contracts
• Sub Recipients Non-Federal entities awarded
  Recovery funding through a legal instrument from
  the Prime Recipient to support the performance of
  any portion of the substantive project or program
  for which the Prime received the Recovery funding

3
4
Data Elements Reporting Data Entry and
Submission

• Vendor is defined as a dealer, distributor,
  merchant, or other seller providing goods or
  services. Prime recipients or sub-recipients may
  purchase goods or services needed to carry out
  the project or program from vendors. Vendors are
  not awarded funds by the same means as
  sub-recipients and are not subject to the terms
  and conditions of the Federal financial
  assistance award.
• Reporting Data Submission Methods
• Online Data Entry
• Formatted Excel Spreadsheet
• XML Extract

5
Data Elements Reporting Data Entry and
Submission

• Reporting based at the award level.
• Combination Loan and Grant projects must report
  for the Grant and for the Loan (2 reports)
• A Prime Recipient Story
• Kim Roberts, Washington County Service Authority

6
Functional Overview of ARRA In-bound Recipient
Reporting
7
Business Context for In-bound Recipient Reporting
8
Updates

• Reporting data elements updated
• Reference Recipient Reporting Data Model V3.0
• Additional Reporting Functionality
• Allow for Recipients to validate sample XML and
  Excel reports prior to formal submission
• Includes structural and data validation
• Registration launched since August 17th 2009
• Awarding Federal Agency User Registration
• Recipient User Registration
• Recipient Point-of-Contact User automatically
  registration option
• Request for FRPIN (Recipient User only)

9
In-bound Recipient Reporting Timeline and
Activities
No less than 35 days prior to the end of the
quarter
90 days after end of Quarter
30 days after end of Quarter
1 10 days after end of Quarter
11 21 days after end of Quarter
22 29 days after end of Quarter
Agency Review of Data Submitted
Prime Recipients Subs Enter Draft Reporting
Data
Prime Recipients Review Data Submitted By Sub(s)
Recipient Reports Published on Recovery.gov
4
6
2
8
Agency, Prime and Sub Recipient Registration
Next quarterly reporting cycle begins- updates
reflected cumulatively
Initial Submission
Prime Recipients Subs Make Corrections
Prime Recipients Subs Make Corrections
1
3
7
5
10 days after end of Quarter
9
Agency View Only
Agency Comment Period
Recipient Report Adjustments Possible
Draft
Initial Submission
Final Submission
Published
Report Status
10
Key Aspects of FederalReporting.gov
Recipient Focus
11
Phase 1 Day 1 Day 10
Recipient Focus

• Register
• Register with valid DUNS number, appropriate
  personal information and email address
• Select Reporting Format to Use
• Download Formatted Excel Spreadsheet
• Create Report on Online
• Validate XML or Excel Report File
• Available only to FederalReporting.gov Recipient
  Registrants
• Only one Excel file
• Validate file structure and business/data rules
  as specified in the Recipient Reporting Data
  Model V3.0
• Enter Reporting Data and Submit Report
• Requires FRPIN
• Upload Reporting Data via Excel Spreadsheet
• Only supports .xls format
• Submit Reporting Data created Online

12
Screenshot Registration
Recipient Focus
13
Screenshot Registration Contd
Recipient Focus

• Type your Personal Information
• First Name
• Last Name
• Email
• Confirm Email
• Phone Number
• Extension

Enter Word Verification to verify registration is
not computer-generated. Choose audio verification
if preferred.
Choose and answer Security Questions for future
password reset, if needed.
Enter Organization Information. Indicate whether
you are an Award Recipient (and enter
organizational DUNS number)
14
Screenshot Registration Contd
Recipient Focus
Registration Confirmation After selecting
Register Now, you will see the Registration
Confirmation screen. Your confirmation number is
included. You can then check your email to get
your temporary password. Press OK to return to
the Home page.
15
Screenshot FRPIN Request
Recipient Focus
16
Screenshot FRPIN Request Contd
Recipient Focus
Enter DUNS number For each DUNS number for which
you will be reporting, you will need to request
the associated FRPIN
17
Screenshot FRPIN Request Contd
Recipient Focus
Receive FRPIN Once the POC and/or DUNS
Administrator has approved your request for
FRPIN, you will receive the 9-digit FRPIN number.
You will receive a message from the system in
your My Messages inbox within FederalReporting.gov
. You will also receive an email to your email
inbox.
18
Screenshot Create Report Online
Recipient Focus
Create Report Online Once you are logged in to
FederalReporting.gov, you can begin to create
your report. Click the Create Report command
button from the Left Side Bar to begin creating
your report.
19
Screenshot Create Report Online, Contd
Recipient Focus

• You will need to provide the following
• Prime Award Number
• Prime DUNS
• Award Type
• Federally awarded contract
• Grant
• Loan
• Recipient Type
• Prime Recipient Prime Only
• Prime Recipient Prime reporting for self and
  Sub(s)
• Sub Recipient Sub Only

Once you have entered all of the required data,
click Continue Report Creation.
2
20
Screenshot Create Report Online (Prime
Recipient)
Recipient Focus
Continue, entering reporting data online, you
will use the following types of fields and
controls Free text fields Drop-down lists
Code Reference lookups Calendars
1
2
Field Information Page icon The field
information page icon opens a new window
including details regarding the various data
elements from the Recipient Reporting Data Model
V3.0.
21
Screenshot Create Report Online (Vendor tab)
Recipient Focus
In the Vendor Information area, you will enter
all of the appropriate data associated with your
vendor Vendor DUNS Number Vendor Name
Vendor Headquarters (HQ) ZIP Code Vendor HQ ZIP
Code Ext Payment Amount Product and Service
Description
1
When you click Save New Vendor, the system
takes you to the Vendor Reporting results page.
There you can see all of the Vendor reports which
you have entered.
2
22
Screenshot Create Report Online (Recipient
Report Submission)
Recipient Focus
Once you click the Submit button, you will be
prompted to enter the FRPIN for the DUNS number
for which you are reporting
1
Click the Submit button to submit your report
as Initial Submission status. Click Cancel if
you wish to cancel the action of submitting the
Initial Submission. You will be returned to your
report entry screens.
2
23
Screenshot Download Excel and XML
Recipient Focus
You can access functionality to download the
Excel template from two locations within the
FederalReporting.gov system The Download
Templates box located in the Left Side Bar on the
Home Page, or the Downloads Page
1
2
24
Screenshot Validate Excel and XML
Recipient Focus
Follow these steps to validate your file Step 1
Create your sample Excel or XML file to validate
of no greater than 200 megabytes. Step 2 Select
the required fields for Award Type and Recipient
Type. Step 3 Browse and select the file to
validate. Step 4 Click Validate File. Step
5 Confirmation page confirms that your file has
uploaded. Step 6 Within 1 to 24 hours, youll
receive an email with validation results. Only
users who are logged in to FederalReporting.gov
can access the Validation Utility. Your
validation file will not be saved in the
FederalReporting.gov database and is not
considered a report submission.
25
Screenshot Upload Excel and XML
Recipient Focus

• Uploading the completed XML or Excel file
• Step 1 Select the required fields for Award Type
  
• Step 2 Select the required fields for Recipient
  Type.
• Step 3 Browse and select the file to validate.
• File extension should be .xls for Microsoft Excel
  file
• File extension of .xml for the XML file
• Step 4 Enter FRPIN. Remember, each DUNS Number
  is assigned by FederalReporting.gov a unique
  9-digit FRPIN.
• Step 5 Click Upload Report.

1
2
3
4
5
26
Screenshot Upload Excel and XML, Contd(Email
Notification for Excel report)
Recipient Focus

• If your Excel file has been accepted as a valid
  submission, you will receive an email indicating
  successful submission.
• If there are validation errors associated with
  the Excel file, you will receive an email
  describing the errors. Use the information in the
  validation errors email to review the Excel file
  and make all necessary corrections. Errors must
  be corrected and new file uploaded for submission
  to be completed. An Excel file with errors is not
  considered a valid submission.

Example email textExcel file successfully
submitted
1
Award_G278K-L_Grant.xls successfully submitted.
Do not reply to this e-mail. Please contact the
FederalReporting.gov Service Desk or call us at
877-508-7386 TTY 877-881-5186 if you have any
questions.
Example email textExcel file submission
unsuccessful
2
Award_Gs2762940-K7.xls failed on validation as
follows The submitted report contains
validation errors. 1. Error Code
VALUE_NOT_FOUND Error Message Unable to find a
CFDA Number with the value 0000012. Error Code
INVALID_EMAIL Error Message dfadsf_at_ must be a
valid email address3. Error Code
INVALID_DOLLAR_AMOUNT Error Message Amount of
Award must be a value greater than zero with no
more than two decimal places. Do not reply to
this e-mail. Please contact the
FederalReporting.gov Service Desk or call us at
877-508-7386 TTY 877-881-5186 if you have any
questions.
27
Phase 1 Day 1 Day 10 Contd
Recipient Focus

• View Report through My Reports
• Once a report has been submitted, Recipient User
  can view reporting data online
• Filters available to search for Report
• Award Type (All, Grant, Loan, Federally Awarded
  Contract)
• Award Number
• DUNS Number
• Report Status (All, Draft, Submitted)
• Fiscal Year
• Fiscal Quarter
• Review Status (All, Not Reviewed, Reviewed
  Comments, Reviewed No Comments)
• Awarding Agency Code
• CFDA Number
• Government Contracting Office Code
• Deactivate Report Requires FRPIN
• Report can be reactivated through assistance from
  Service Desk

28
Screenshot View Report through My Reports
(Prime Recipient)
Recipient Focus
1
2
29
Screenshot View Report through My Reports (Sub
Recipient)
Recipient Focus
1
2
3
Once you identify the report you need to update,
you will click the Award Number of that report to
view the report. Then you can begin to
update/edit your report.
30
Phase 2 Day 11 Day 21
Recipient Focus

• View Report
• View Reporting Data Online
• Update Report
• Edit Reporting Data Online
• Upload (re-submit) Reporting Data
• Excel Format
• Deactivate Report Requires FRPIN
• Reactivate through assistance from Service Desk
• Create/Submit Report
• Submit a late Report requires justification for
  late submission
• Comment on Report (Prime)
• Prime can comment on Sub Reports
• Comments are sent to the Submitter of the Report
  only
• Notification is provided via email and
  FederalReporting.gov inbox (My Messages)
• Prime and Sub have ability to record their
  comments/response on the report in question

31
Screenshot Edit Reporting Data Online (Prime
Recipient)
Recipient Focus
1
Once the reporting data has been updated, you are
ready to re-submit the report. To re-submit
report, click the Submit button found at both
the top and bottom of the page. NOTE It is
important to understand that each time a report
is submitted, the previous version of that report
is overwritten.
32
Screenshot Edit Reporting Data Online (Sub
Recipient)
Recipient Focus
1
Once the reporting data has been updated, you are
ready to re-submit the report. To re-submit
report, click the Submit button found at both
the top and bottom of the page. NOTE It is
important to understand that each time a report
is submitted, the previous version of that report
is overwritten.
Field Information Page icon The field
information page icon opens a new window
including details regarding the various data
elements from the Recipient Reporting Data Model
V3.0.
2
33
Screenshot Prime Recipient comments on Report
submitted by Sub Recipient
Recipient Focus
Upon opening the report from the Search Result
screen, you will select the View or Add Report
Comments hyperlink to make a comment on the
specific report. By clicking on the hyperlink, a
new browser winder will open, allowing you to
view the report in one window and the commenting
fields in a second window
1
34
Screenshot Prime Recipient comments on Report
submitted by Sub Recipient
Recipient Focus
Upon opening the report from the Search Result
screen, you will select the View or Add Report
Comments hyperlink to make a comment on the
specific report. After providing your comments,
you can click Submit, to save/log the comment.
1
After you complete commenting, click Close
Comments Window to close this browser window.
2
35
Screenshot Sub Recipient respond to Prime
Recipients comments
Recipient Focus
1
As a Sub Recipient, you will be notified via
email that your Prime Recipient has commented on
your report In addition, you and all the users
belonging to your DUNS number will also receive a
new item in My Work Queue will also provide
you wit which shows a comment from the Prime
ready for review.
36
Screenshot Audit Trail of Report Submission
Recipient Focus

• FederalReporting.gov provides you with audit
  train details like
• Created By (Initial Report Submitter)
• Date Created (Report Submission Date and
  Timestamp)
• Last Updated By (User who last update the Report)
• Last Updated On (Date and Timestamp for the
  latest update)

1
37
Phase 3 Day 22 Day 29
Recipient Focus

• View Report
• View Reporting Data Online
• Update Report only after the Report is unlocked
  (Prime and/or Sub)
• Edit Reporting Data Online no draft or new
  reports
• Upload (re-submit) Reporting Data for existing
  reports only no new report
• Excel
• Deactivate Report Requires FRPIN
• Reactivate through assistance from Service Desk
• Comment on Report (Agency)
• Agency can comment on Prime and/or Sub Reports
• Comments are not at the Data Element level
• Comments are sent to the Submitter of the Reports
  only.
• The act of commenting by the Agency unlocks the
  report in question
• Notification is provided via email and
  FederalReporting.gov inbox (My Messages)
• Agency, Prime and Sub can record their
  comments/response on the report in question
• Available sub-status

38
Screenshot Agency reviews and comments on
Recipient Report
Recipient Focus
1
Upon opening the report from the Search Result
screen, your Agency can select the View or Add
Report Comments hyperlink to make a comment on
your report.
39
Screenshot Agency comments on Recipient Report
Recipient Focus
In this new browser window, your Agency can enter
comments communicating to you about any
potential update to your report.
1
40
Screenshot Recipient respond to Agencys
comments
Recipient Focus
Based on the comment made by your Agency, you can
response to the Agency comment logged. You can
respond to the comment, make the report updates
and resubmit the report. Note It is not
mandatory to reply to every single comment. You
can choose not to respond to the comment and
instead just update your report, submitting a new
version.
1
After completing commenting, click Close
Comments Window to close this browser window.
2
41
Screenshot Multiple Comments (the commenting
dialogue)
Recipient Focus
To provide a trail of comments,
FederalReporting.gov provides a sequential list
of all comments made on the report, identifying
the user who made the comment as well as he date
and time of the comment. FederalReporting.gov
does not put any limit on the number of comments
and responses which can be appended to a report.
1
42
Screenshot Upload (re-submit) Excel or XML Report
Recipient Focus

• Re-submitting the Excel file
• Step 1 Select the required fields for Award Type
  
• Step 2 Select the required fields for Recipient
  Type.
• Step 3 Browse and select the file to validate.
• File extension should be .xls for Microsoft Excel
  file
• Step 4 Enter FRPIN. Remember, each DUNS Number
  is assigned by FederalReporting.gov a unique
  9-digit FRPIN.
• Step 5 Click Upload Report.

1
2
3
4
5
43
Screenshot Edit Reporting Data Online (Prime
Recipient)
Recipient Focus
1
Once the reporting data has been updated, you are
ready to re-submit the report. To re-submit
report, click the Submit button found at both
the top and bottom of the page. NOTE It is
important to understand that each time a report
is submitted, the previous version of that report
is overwritten.
44
Screenshot Edit Reporting Data Online (Sub
Recipient)
Recipient Focus
1
Once the reporting data has been updated, you are
ready to re-submit the report. To re-submit
report, click the Submit button found at both
the top and bottom of the page. NOTE It is
important to understand that each time a report
is submitted, the previous version of that report
is overwritten.
Field Information Page icon The field
information page icon opens a new window
including details regarding the various data
elements from the Recipient Reporting Data Model
V3.0.
2
45
Key Aspects of FederalReporting.gov
Agency Focus
46
Phase 1 and Phase 2
Agency Focus

• Phase 1 Day 1 Day 10
• Register with valid Federal email address,
  appropriate personal information and department
  /agency name
• No Agency related activities
• Phase 2 Day 11 Day 21
• View Recipient Report (Specific to an Agency)
• Online
• Data Extract through SFTP
• XML Format
• Tab Delimited Format

Initial Submission
Report Status
47
Screenshot View Recipient Report (Award Type
ALL)
Agency Focus
1
2
48
Screenshot View Recipient Report (Award Type
Loan)
Agency Focus
49
Screenshot View Recipient Report (Example of
Search Filter Type)
Agency Focus
50
Phase 3 Day 22 Day 29
Agency Focus

• View Report
• View Reporting Data Online
• Comment on Report
• Agency can comment on Prime and/or Sub Reports
• Comments are not at the Data Element level
• Comments are sent to the Submitter of the Reports
  only.
• The act of commenting by the Agency unlocks the
  report in question
• Notification is provided via email and
  FederalReporting.gov inbox (My Messages)
• Agency, Prime and Sub can record their
  comments/response on the report in question
• Available report sub-status
• Not Reviewed, Reviewed with Comments, Reviewed
  with No Comments

51
Screenshot Agency view (reviews) a Recipient
Report
Agency Focus
1
Upon opening the report from the Search Result
screen, you will select the View or Add Report
Comments hyperlink to make a comment on the
specific report
52
Screenshot Agency comments on the select
Recipient Report
Agency Focus
Once you (Agency user) has viewed a submitted
report, you may choose to respond to the report
with a comment. By clicking the View or Add
Report Comments hyperlink, a new browser window
opens for you to enter your comments on the
report.
1
53
Screenshot Recipient respond to Agencys
comments (multiple comments the commenting
dialogue)
Agency Focus
To provide a trail of comments,
FederalReporting.gov provides a sequential list
of all comments made on the report, identifying
the user who made the comment as well as he date
and time of the comment. FederalReporting.gov
does not put any limit on the number of comments
and responses which can be appended to a report.
1
54
Key Aspects of FederalReporting.gov
Report Publishing
55
Phase 4 Day 30 Publish to Recovery.gov (before
next quarter)
Report Publishing

• View Report Online on FederalReporting.gov
• Agency, Prime and Sub Recipients can only view
  Report
• Recipients are not able to update Report
• Recipient Reporting Data Published on
  Recovery.gov
• Public has access to reporting data on
  Recovery.gov

56
Additional Information and Assistance
57
FederalReporting.gov
Additional Information and Assistance

• Reference Documentation
• Recipient Reporting Data Model V3.0
• Codes Lookup
• Program Source (TAS)
• Webinars
• User Guides
• Frequently Asked Questions (FAQs)
• Updates and Notices

58
REPORTS

• Technical Assistance
• Errors and Omissions
• Non-reporters
• Jobs
• Data that is cumulative

59
TECHNICAL ASSISTANCE

• National Rural Water Association
• http//www.nrwa.org/sa.htm
• Rural Community Assistance Partnership
• http//www.rcap.org/

60
ERRORS AND OMISSIONS

• The data fields that are of major concern for
  significant errors are
• Federal amount of the award
• Number of jobs retained or created
• Federal award number
• Recipient name
• Material omissions include the following
• Failure to report
• Data not responsive to requested information

61
NON- REPORTERS

• Recipients who have failed to submit a Section
  1512 report as required by the terms of their
  award are considered to be non-compliant.
  Non-compliant recipients, including those who are
  persistently late or negligent in their reporting
  obligations, are subject to Federal action, up to
  and including the termination of Federal funding
  or the ability to receive Federal funds in the
  future. Further, in some cases, intentional
  reporting of false information can result in
  civil and/or criminal penalties.

62
STEP 1 Calculate Quarterly Hours in a Full-Time
Schedule.

• A. Start by determining the standard hours in a
  full-time work week schedule as illustrated
  below. This example uses 40 hours, but other
  standards are possible.
• B. Multiply this amount by 52 weeks to determine
  the annual number of hours for full-time work.
• 40 Hours in full-time work week X 52 weeks per
  year 2080 Total Hours Per Year
• C. Divide this amount by 4 to determine the
  quarterly hours in a full-time schedule
• 2080 Total Hours Per Year / 4 Quarters 520
  Total Quarterly Hours

63
STEP 2 Calculate the FTEs Created or Retained
for this Quarter.

• A. Determine the number of hours worked in
  positions created or saved by the Recovery Act
  within the current quarter. For example, a
  full-time employee working 40 hours per week
  during the entire quarter will work 520 hours in
  the quarterly reporting period.
• B. Divide this number by the Quarterly Hours in
  a Full-Time Schedule number calculated in STEP
  1
• 520 Hours Worked and Funded by Recovery Act / 520
  Quarterly Hours in a Full-Time Schedule 1.0
  FTEs Created or Retained

64
STEP 3 If Needed, Adjust to Reflect Partial ARRA
Funding.

• A. Determine the percentage of hours worked and
  paid for by Recovery Act funds as a percentage of
  all funding. In this example, half (50) of the
  hours worked were paid for by Recovery Act funds.
  
• B. Multiply this number against the number of
  hours reported in Step 2
• 520 hours worked during the reporting period X
  50 of the project paid through Recovery Act
  funds 260 Hours Worked and Funded by Recovery
  Act

65
AGENCY REVIEWS

• 1. Establishment of control totals. Agencies are
  to track the number of individual Federal
  contract awards and Federal assistance awards.
  Prior to the final day of the Federal agency
  review period, each agency must use the template
  and instructions found on a MAX Community web
  page (Federal access only) and report the sum
  total of each. These figures are to serve as
  control totals.
• 2. Range of awards for which reports were not
  submitted. Within three days following the final
  day of the Federal agency review period, each
  agency shall conduct an analysis of the
  differences between the control total of awards
  issued by that agency and the corresponding
  number of reports submitted to the
  FederalReporting.gov solution by recipients.
• 3. Identification of non-compliant recipients.
  Within five days following the final day of the
  Federal agency review period, each agency must
  compile a verified and detailed list of
  recipients who were required to report in the
  current reporting period but failed to do so.
  Using the template and instructions found on a
  MAX Community web page (Federal access only),
  each agency must complete the template.
• 4. Certification. Completed lists of
  non-compliant recipients are to be submitted to
  OMB within five days following the final day of
  the Federal agency review period via
  recovery_at_omb.eop.gov and must be accompanied by a
  certification

66
Webinar Review

• http//www.usda.gov/rus/water/ARRA.htm