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
• Permit valid late report submission
• Recipients require to provide justification for
  late submission
• 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
Key Aspects of FederalReporting.gov
10
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
11
Report Status and System/User Action
Phase 1 (Day 1 10)
Phase 2 (Day 11 21)
Phase 3 (Day 22 29)
Phase 4 (Day 30)
Report Phase
Draft
Initial Submission
Final Submission
Published
Report Status

• End of Day 10 Draft ? Initial Submission
• User Driven
• Recipient is required to click on Submit button
• End of Day 21 Initial Submission ? Final
  Submission
• System Driven
• End of Day 29 Final Submission ? Published
• System Driven
• The cut-of-time for each reporting phase
• 1200am Pacific Time

12
Key Aspects of FederalReporting.gov
Recipient Focus
13
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
  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

14
Screenshot Registration
Recipient Focus
15
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)
16
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.
17
Screenshot FRPIN Request
Recipient Focus
18
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
19
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.
20
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.
21
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
22
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.
23
Screenshot Create Report Online (Sub 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.
Save Edits to re-save the updated report data.
The edits are saved.
3
24
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
25
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
26
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
27
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.
28
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
29
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.
30
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

31
Screenshot View Report through My Reports
(Prime Recipient)
Recipient Focus
1
2
32
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.
33
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

34
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.
35
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
36
Screenshot Late Online Report Submission
Recipient Focus
You will need to enter the FRPIN for the DUNS
Number on which you are reporting. You will also
need to provide a justification for the late
report in the Justification text field.
1
2
FederalReporting.gov recognizes the difference
between a new report submitted as Initial
Submission after the end of the 10th day of the
reporting period and an update to an Initial
Submission report after the 10th. Any new
Initial Submission report submitted from the 11th
through the 21st days of the reporting period
must include a justification for lateness. That
justification will be heavily scrutinized by
Agencies and others charged with compliance and
oversight related to Recovery Act funds.
37
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
38
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
39
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.
40
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
41
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

42
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.
43
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
44
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
45
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
46
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
47
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.
48
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
49
Key Aspects of FederalReporting.gov
Agency Focus
50
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
51
Screenshot Registration
Agency Focus
52
Screenshot Registration Contd
Agency 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 you are
a Federal Agency (and select your Agency /
Department from the drop-down menu).
53
Screenshot Registration, Contd
Agency 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.
54
Screenshot View Recipient Report (Award Type
ALL)
Agency Focus
1
2
55
Screenshot View Recipient Report (Award Type
Loan)
Agency Focus
56
Screenshot View Recipient Report (Example of
Search Filter Type)
Agency Focus
57
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

58
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
59
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
60
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
61
Key Aspects of FederalReporting.gov
Report Publishing
62
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

63
Additional Information and Assistance
64
FederalReporting.gov
Additional Information and Assistance

• User Documentation
• Registration (Currently available on Download
  page)
• Validate Excel (.XLS) or XML file (Available
  September 15th 2009)
• Reporting (Available September 25th 2009)
• XML Schema and Ms-Excel Template
• Reference Documentation
• Recipient Reporting Data Model V3.0
• Agency Codes Lookup
• Program Source (TAS)
• Frequently Asked Questions (FAQs)
• Updates and Notices