Docstoc

Walking Spreadsheet

Document Sample
Walking Spreadsheet Powered By Docstoc
					       AMERICAN RECOVERY AND REINVESTMENT ACT                                TRAINING FACILITATION GUIDE – INTERAGENCY SUBRECIPIENTS

                                        Training Facilitation Guide
                                     ARRA Reporting Process Training
                                      for Interagency Subrecipients


Table of Contents
ARRA Background and Overview ........................................................................................................ 2
  ARRA Overview .................................................................................................................................... 2
  ARRA Background ................................................................................................................................ 2
  Document Purpose ............................................................................................................................... 2
  Preparing for the Training Session ....................................................................................................... 2
IA Subrecipient Training Facilitation Steps......................................................................................... 3
   Training Overview ................................................................................................................................. 3
   ARRA Overview .................................................................................................................................... 3
   Accountability and Transparency ......................................................................................................... 3
   Key Points ............................................................................................................................................. 4
   ARRA Reporting Participants ............................................................................................................... 4
   ARRA Reporting Agencies ................................................................................................................... 6
   Review the ARRA IA Subrecipient Reporting Spreadsheet................................................................. 7
   Review Spreadsheet Instructions Tab.................................................................................................. 8
   Fill out Subrecipient Tab and Fields ..................................................................................................... 8
   Fill out Vendor Tab and Fields ............................................................................................................ 11
   Review Additional PoP Addresses Tab and Fields ............................................................................ 11
   Review DOE_Admin Tab and Fields .................................................................................................. 11
   Review................................................................................................................................................. 12
   DOE_Retrofits Tab and Fields............................................................................................................ 12
   Review DOE_ Retrofits End Use Tab and Fields............................................................................... 12
   Monthly ARRA Reporting Process ..................................................................................................... 12
   What happens when data is submitted .............................................................................................. 13
   Training Closeout ................................................................................................................................ 14




ARRA IA Training Facilitation Guide                                                                                                  PAGE 1 OF 14
Rev. 12/23/09
       AMERICAN RECOVERY AND REINVESTMENT ACT                TRAINING FACILITATION GUIDE – INTERAGENCY SUBRECIPIENTS


                                        ARRA Background and Overview


 ARRA Overview              The American Recovery and Reinvestment Act of 2009 (ARRA) is a $787 billion
                            economic stimulus package signed into law by President Barack Obama on Feb. 17,
                            2009. A percentage of the package targets spending (contracts, grants, and loans) and
                            the rest includes tax cuts and entitlements such as Medicaid and Social Security
                            Administration payments. http://www.recovery.gov/FAQ/glossar y/Pages/glossa ryHome.aspx

                            As a direct response to the economic crisis, the Recovery Act has three immediate
                            goals:
                                 Create new jobs as well as save existing ones
                                 Spur economic activity and invest in long-term economic growth
                                 Foster unprecedented levels of accountability and transparency in government
                                    spending http://www.recovery.gov/About/Pages/The_Act.aspx

                            Note: Also Referenced on Page 2 of the ARRA IA Subrecipient R eference Guide



 ARRA                       The American Recovery and Reinvestment Act include strict transparency and
 Background                 accountability requirements. This translates into the Department needing robust
                            tracking and reporting for ARRA funds disbursements and subsequent project
                            performance in accordance with Section 1512 of the Recovery Act. The purpose of the
                            Department’s ARRA Reporting process is to provide a mechanism for robust tracking
                            and reporting on ARRA grants, loans, and contracts. This process includes reporting
                            processes for the Subrecipient.



 Document                   The purpose of this document is to provide the Department’s ARRA Reporting
 Purpose                    Trainers a framework to provide uniform, consistent ARRA reporting training to each
                            Subrecipient. This document should be used to guide the trainer through each ARRA
                            requirement and reporting process step.


 Preparing for the          The following steps are recommended:
 Training Session
                                 1.   When scheduling the ARRA Reporting Training session, schedule it during the
                                      Project Kickoff Meeting.
                                 2.   Request attendance of the person who will be responsible for submitting the
                                      ARRA spreadsheet to the Department (at the Subrecipient level), as this may
                                      not be the Project Manager.
                                 3.   Send out any most updated meeting documents to the Subrecipient prior to the
                                      training session.
                                 4.   It is highly recommended to use a computer and electronic copy of the ARRA
                                      IA Subrecipient Reporting Spreadsheet during the training.




ARRA IA Training Facilitation Guide                                                                    PAGE 2 OF 14
Rev. 12/23/09
       AMERICAN RECOVERY AND REINVESTMENT ACT                     TRAINING FACILITATION GUIDE – INTERAGENCY SUBRECIPIENTS

                                      IA Subrecipient Training Facilitation Steps

 Training Overview            What to Expla in:
                              Provide the Subrecipient with the following documents:
                                  ARRA IA Subrecipient Reporting Spreadsheet
                                  ARRA IA Subrecipient Reference Guide

                              Provide the Subrecipient with an overview of what the training w ill consist of.
                                  ARRA Overview
                                  Accountability and Transparency
                                  Key Points
                                  Key Definitions
                                  ARRA IA Subrecipient Reporting Spreadsheet
                                  Report Submission Steps

                              Note: All references to time throughout this guide are in Pacific Standard Time (PST.)


 ARRA Overview                 What to Expla in: The American Recovery and Reinvestment Act of 2009 (ARRA) is a
                              $787 billion economic stimulus package signed into law by President Barack Obama
                              on Feb. 17, 2009. A percentage of the package targets spending (contracts, grants, and
                              loans) and the rest includes tax cuts and entitlements such as Medicaid and Social
                              Security Administration payments. http:// www.recovery.g ov/FA Q/glo ssary/ Pages/glo ssary Ho me.aspx

                              Note: Also Referenced on Page 2 of the ARRA IA Subrecipient R eference Guide


 Accountability and           What to Expla in: Inform the Subrecipient of the following accountability and
 Transparency
                              transparency requirements for all American Recovery and Reinvestment Act funded
                              projects.

                              The American Recovery and Reinvestment Act, Section 1512 has strict reporting
                              requirements for all distributed funds. Section 1512 was created with two key
                              accountability and transparency goals:
                                  1. Prevent and detect waste, fraud, and abuse
                                  2. Provide transparent reporting of Recovery-related funds as they are
                                     distributed and used

                              Data from all Recovery Act (ARRA) Section 1512 reports, including the ARRA IA
                              Subrecipient Reporting Spreadsheets submitted to the Department, will likely be
                              made available on:
                                  Federal (OMB) website at www.recovery.gov
                                  Department of Energy Recovery website at www.energy.gov/recovery
                                  State of California Recovery website at www.recovery.ca.gov
                                  California Energy Commission Recovery website at
                                      www.energy.ca.gov/recovery




ARRA IA Training Facilitation Guide                                                                              PAGE 3 OF 14
Rev. 12/23/09
       AMERICAN RECOVERY AND REINVESTMENT ACT                     TRAINING FACILITATION GUIDE – INTERAGENCY SUBRECIPIENTS

 Key Points                   What to Expla in: In order to meet the accountability and transparency goals, the
                              ARRA IA Subrecipient Reporting Spreadsheet is due to the Department by the 3 rd
                              calendar day of every month.

                                      1.   The ARRA IA Subrecipient Reporting Spreadsheet (explained during this
                                           training) is in addition to any other existing project and Progress Reporting.
                                           The additional reporting requirements are described in the Department’s
                                           Agreement reporting sections.

                                      2.   The ARRA Reporting Process meets all of the following ARRA reporting
                                           requirements:
                                            Federal Government ARRA reporting (Section 1512, Recovery.gov,
                                               Federalrecovery.gov, OMB)
                                            State of California ARRA reporting (OCIO, CAAT)
                                            Department of Energy Performance Metrics reporting

                                      3.   California is a centralized reporting state for ARRA Reporting (As stated in
                                           RAB 09-12). Subrecipients must NOT register at FederalReporting.gov as
                                           suggested by Recovery.gov. ALL Subrecipients reporting will be done
                                           through the Department.

                                      4.   Minimizing reporting effort: During this training session, the Department will
                                           assist the Subrecipient with filling out the majority of the ARRA R eporting
                                           data fields. Minimal data fields will need to be changed/updated monthly.

                                      5.   OMB released updated ARRA guidance (memoranda_2010/m10 -08)
                                           concerning jobs counting and data quality on 12/21/2009. These updates are
                                           reflected in this Facilitation Guide and in the Reference Guide .


 ARRA Reporting               What to Expla in: Explain the difference between an Interagency Agreement, Prime
 Participants
                              recipient, Consortia, Subrecipient, Vendor and Subrecipient Vendor.
                                   The ARRA reporting participants
                                   Terms used to describe each type of participant
                                   Where the Subrecipient fits into the reporting process

                              Note: Also Referenced on Page 3 and in the Glossary section of the ARRA IA Subrecipient
                              Reference Guide

                              Interagency Agreement: An agreement or partnership between two or more state
                              agencies. Example: The Employment Training Panel (ETP) has an Interagency
                              Agreement with the California Energy Commission.

                              Prime recipient: Non-Federal entity receiving Recovery Act funding (grants, loans or
                              cooperative agreements) directly from the federal government
                              (http://www.recovery.gov/FAQ/glossary/Pages/glossaryHome.aspx), Example: California Energy
                              Commission is the Prime recipient receiving funds directly from the Department of
                              Energy.

                              Consortia/consortium: (type of Subrecipient) There should be a lead for each
                              consortium, which is the agency/organization that signed the agreement. In the case
                              of consortia of many cities, there should be a lead city responsible for reporting the
                              Subrecipient spreadsheet/data for the consortia.

ARRA IA Training Facilitation Guide                                                                        PAGE 4 OF 14
Rev. 12/23/09
       AMERICAN RECOVERY AND REINVESTMENT ACT                              TRAINING FACILITATION GUIDE – INTERAGENCY SUBRECIPIENTS



                                  Federal
                                 Government
                                   (OMB)




                                                          Department of
                               State of California
                                                             Energy
                                    (OCIO)
                                                             (DOE)




                                             Prime Recipient                                    Interagency
                                            (California Energy            Forward              Agreements
                                              Commission)                                    (EDD, ETP, DGS)




                                 Subrecipient                    Vendor             Subrecipient               Vendor




                                 Subrecipient                                       Subrecipient
                                   Vendor                                             Vendor




                              Subrecipient: A non-Federal entity that expends Federal awards received from
                              another entity to carry out a Federal program, but does not include an individual who
                              is a beneficiary of such a program. Subrecipients are awarded Recovery Act funding
                              through a legal instrument from the Prime recipient to support the performance of
                              any portion of the substantive pr oject or program for which the Prime recipient
                              received the Recovery Act funding. Additionally, the Terms and Conditions of the
                              Federal award are carried forward to the Subrecipient. The characteristics of a
                              Subrecipient that make it distinct from a vendor are summarized below (DOE
                              definition). A Subrecipient:
                                   1. Determines who is eligible to receive what Federal financial assistance;
                                   2. Has its performance measured against whether the objects of the Federal
                                        program are met;
                                   3. Has responsibility for programmatic decision making;
                                   4. Has responsibility for adherence to applicable Federal program compliance
                                        requirements; and
                                   5. Use the Federal funds to carry out a program of the organization as compared
                                        to providing goods or services for a program of the pass-through entity.

                              Vendor: A dealer, distributor, merchant or other seller providing goods or services
                              that are required for the conduct of a Federal program. Prime 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 Subrecipients and are not
                              subject to the Terms and Conditions of the Federal financial assistance award.

ARRA IA Training Facilitation Guide                                                                                     PAGE 5 OF 14
Rev. 12/23/09
       AMERICAN RECOVERY AND REINVESTMENT ACT                                        TRAINING FACILITATION GUIDE – INTERAGENCY SUBRECIPIENTS
                              Program compliance requirements normally do not pass through to Vendors. The
                              characteristics of a Vendor that make it distinct from a Subrecipient are summarized
                              below (DOE definition). A Vendor:
                                  1. Provides the goods and services within normal business operations;
                                  2. Provides similar goods and services to many different purchasers;
                                  3. Operates in a competitive environment;
                                  4. Provides goods and services that are ancillary to the operation of the Federal
                                      program; and
                                  5. Is not subject to compliance requirements of the Federal program.

                              Subrecipient Vendor: A dealer, distributor, merchant or other seller providing goods
                              or services that are required for the conduct of a Federal program, for a Subrecipient
                              (also sometimes referred to as a Sub Vendor). See ‚Vendor‛ definition for more
                              details.

                              Note: Also Referenced on Page 3 and in the Glossary section of the ARRA IA Subrecipient
                              Reference Guide



 ARRA Reporting               What to Expla in: Although the Subrecipient will be reporting only to the
 Agencies
                              Department, the following agencies will ultimately receive the ARRA reporting data.



                                       Federal
                                      Government
                                        (OMB)




                                                             Department of
                                  State of California
                                                                Energy                                              Federal
                                       (OCIO)
                                                                (DOE)
                                                                                                                   Government
                                                                                                                     (OMB)


                                                Prime Recipient                          Interagency
                                               (California Energy                       Agreements
                                                 Commission)                          (EDD, ETP, DGS)

                                                                                                                                            Department of
                                                                                                                 State of California
                                                                                                                                               Energy
                                                                                                                      (OCIO)
                                                                                                                                               (DOE)

                                      Subrecipient                  Vendor   Subrecipient               Vendor




                                                                                                                               Prime Recipient
                                      Subrecipient                           Subrecipient
                                        Vendor                                 Vendor                                         (California Energy
                                                                                                                                Commission)




                              Department of Energy (DOE): DOE is the federal agency for the En ergy
                              Commission’s ARRA Grant-level reporting. DOE’s overarching mission is to advance
                              the national, economic, and energy security of the United States; to promote scientific
                              and technological innovation in support of that mission; and to ensure the
                              environmental cleanup of the national nuclear weapons complex.
                              http://www.energy.gov/about/index.htm The DOE’s role in ARRA is to report performance metr ics

                              associated with each federal award to the federal government.

ARRA IA Training Facilitation Guide                                                                                                         PAGE 6 OF 14
Rev. 12/23/09
       AMERICAN RECOVERY AND REINVESTMENT ACT                                  TRAINING FACILITATION GUIDE – INTERAGENCY SUBRECIPIENTS


                              Office of the Chief Informational Officer (OCIO): The OCIO is the State of
                              California for ARRA reporting. The OCIO is responsible for advising the Governor
                              on the strategic management and direction of the state’s IT resources, establishing
                              performance management and ensuring IT services are efficient and effective
                              http://www.itsp.ca.gov/Organizing_for_Success/Role s_and_Responsibilities.asp . The OCIO’s role in the ARR A

                              Reporting Process is to ensure the necessary systems are in place to provide proper
                              oversight, accounting, reporting, and project management controls to ensure all
                              ARRA funds are used efficiently and for the intended purposes. California is a
                              centralized reporting state for ARRA. As such, all State Department data will be
                              collected and submitted through the California reporting systems called the CAAT.
                              http://recovery.ca.gov/Content/Documents/recoveryactbulletins/source/Recovery_Act_Bulletin_09-20.pdf



                              Office of Management and Budgets (OMB ): OMB is the Federal Government agency
                              for ARRA Section 1512 Reporting. OMB oversees and coordinates the
                              Administration's procurement, financial management, information, and regulatory
                              policies. In each of these areas, OMB's role is to help improve administrative
                              management, to develop better performance measures and coordinating mechanisms,
                              and to reduce any unnecessary burdens on the public http://www.whitehouse.gov/omb/organization_role .
                              The OMB’s role in the ARRA R eporting Process is to addresses Federal agency
                              requirements to provide spending and performance data to the ‚Recovery.gov‛
                              website and to deliver a website that allows citizens to hold the government
                              accountable for every dollar spent the law and guidance require Federal agencies to
                              implement mechanisms to accurately track, monitor, and report on taxpayer funds .
                              (http://www.whitehouse.gov/omb/assets/memoranda_fy2009/m09-10.pdf)



                              Note: Also Referenced on Page 3 and in the Glossary section of the ARRA IA Subrecipient
                              Reference Guide


 Review the ARRA              What to Expla in: The Subrecipient may be concerned about the number of data
 IA Subrecipient
                              fields and spreadsheets (tabs) in the Excel file. Explain that the majority of the fields
 Reporting
 Spreadsheet                  will only need to be filled out once (the first time.) For the monthly reporting, many
                              fields will remain the same throughout the life of the project. The specific
                              performance metrics fields (i.e. Number of Jobs, etc.), Vendors (tab) fields, and Places
                              of Performance (POP tab) fields will require data updates on a monthly basis.

                               It is recommended to have a computer available at the training session and use an
                                electronic copy of the ARRA IA Subrecipient Reporting Spreadsheet. (If a
                                computer is not available for the training, have the Subrecipient fill out a paper
                                copy of the ARRA IA Subrecipient Reporting Spreadsheet during training.)

                                      1.   Have the Subrecipient open the ARRA IA Subrecipient Reporting
                                           Spreadsheet on the computer.

                                      2.   The following section of this training guide will assist with w alking through
                                           each spreadsheet (excel tab) and discussing each data field. This should take
                                           approximately 30 minutes.

                                                      Instructions
                                                      Subrecipient

ARRA IA Training Facilitation Guide                                                                                                  PAGE 7 OF 14
Rev. 12/23/09
       AMERICAN RECOVERY AND REINVESTMENT ACT                      TRAINING FACILITATION GUIDE – INTERAGENCY SUBRECIPIENTS
                                                  Vendors
                                                  Additional PoP Addresses
                                                  DOE_Admin
                                                  DOE_Retrofits
                                                  DOE_Retrofits End Use

                                      3.   If possible, have the Subrecipient fill out as many of the data fields during the
                                           walk through as possible to ensure the information is accurate and correct.

                              Note: Also Referenced in the ARRA IA Subrecipient Reporting Spreadsheet and Pages 4-6 of
                              the ARR A IA Subrecipient Reference Guide

 Review
 Spreadsheet                  What to Expla in:        The ‚Instructions‛ tab provides a high-level overview of the
 Instructions Tab
                              Subrecipient’s monthly reporting requirements.

                              Note: Also Referenced in the ARRA IA Subrecipient Reporting Spreadsheet “ Instructions”
                              tab



 Fill out                     What to Expla in: The fields on this tab must be reported on in order to be compliant
 Subrecipient Tab             with the State of California and Federal government. Also, explain that if the
 and Fields
                              Mandatory fields are not complete, their file will be rejected.

                              Many data fields for Section 1512 reporting are considered mandatory by OMB and a
                              record will be rejected if they are not completed, regardless of whether they apply to
                              the program. Even if the data field does not apply to the particular award being
                              entered, a value still must be entered. Also, data entered into the mandatory field
                              should be checked to ensure it follows the required format and is correct.

                              In addition, fields that contain narrative data should be spell checked and reviewed
                              for appropriateness. The narratives, especially those in the award description and
                              activities/project description will be published to the State of California’s and Federal
                              government websites. The narrative should also be written in a style to communicate
                              with the public, i.e. avoid use of statutory and regulatory abbreviations and cross
                              references (09-19 Mandatory Section 1512 Reporting Data Fields).

                              What to Expla in: Total Amount of Subawards Disbursed, Subrecipient Number
                              of Jobs, and Subrecipient Description of Jobs Created are the only fields on this tab
                              that the IA Subrecipient will update monthly after the first initial reporting period.
                              All other fields should remain the same, unless for instance, there is an address
                              change.

                              Review and fill out ‚Subrecipient‛ Spreadsheet. Be sure to talk through the
                              descriptions in the spreadsheet and the additional comments provided below :

                                                          SEP          EECBG           SEEARP                EAPP
                               FBO/CFDA
                               Number                    81.041         81.128           81.127              81.122

ARRA IA Training Facilitation Guide                                                                         PAGE 8 OF 14
Rev. 12/23/09
       AMERICAN RECOVERY AND REINVESTMENT ACT                  TRAINING FACILITATION GUIDE – INTERAGENCY SUBRECIPIENTS
                                                                   Energy
                                                    State        Efficiency                                               Energy
                                                                                      Energy Efficient
                               Federal             Energy            and                                                 Assurance
                                                                                        Appliance
                               Program Title      Program        Conservati                                              Planning
                                                                                      Rebate Program
                                                    (SEP)         on Block                                            Program (EAPP)
                                                                    Grant
                               Award                DE-             DE-
                               Number            EE0000221       EE0000905             DE- EE0001578                  DE-OE0000109



                                Ref#   Field                      Comment                                                Mandatory
                                 11    FBO/CFDA Number            See grid above                                             X
                                11.a   Federal Program Title      See grid above                                             X
                                 6     Award Number               See grid above                                             X
                                 68    Subrecipient Legal         As registered in CCR or D&B
                                       Name
                                 66    Sub recipient DUNS         Must be valid and accurate,                                  X
                                       No.                        otherwise the file will be rejected
                                                                  - Get a DUNS No. from the CCR:
                                                                   www.ccr.gov
                                                                  - It takes 10 days minimum to
                                                                  receive a DUNS No.
                                                                  - The ARRA IA Subrecipient
                                                                  Reporting Spreadsheets do not
                                                                  accept DUNS Numbers with
                                                                  leading zeros. For instances
                                                                  where the DUNS begins with
                                                                  zeros, precede the DUNS entry
                                                                  with an apostrophe (‘). For
                                                                  example, the DUNS Number
                                                                  012345678 would be entered as
                                                                  ‘012345678
                                 67    Sub award Number           From the Department Agreement                                X
                                 69    Subrecipient DBA           The Doing-Business-As (DBA)                                  X
                                       Name                       name for the Subrecipient as
                                                                  defined in the CCR or D&B
                               71-75   Subrecipient Address       Zip code must be zip+4. Zip codes
                                                                  will be validated against the
                                                                  congressional district.
                                 76    Subrecipient               This can be looked up at:
                                       Congressional District     https://writerep.house.gov/writerep/welcome.shtml

                                 77    Subrecipient Type          Drop down box of the
                                                                  Subrecipient’s type of business
                                 78    Amount of Subaward         Cumulative amount by report                                  X
                                                                  date
                                 79    Total Amount of Sub        Leave blank – will be filled out                             X
                                       awards Disbursed           by the IA Department.

                               80      Subaward Date              The execution date of the                                    X


ARRA IA Training Facilitation Guide                                                                                      PAGE 9 OF 14
Rev. 12/23/09
       AMERICAN RECOVERY AND REINVESTMENT ACT                 TRAINING FACILITATION GUIDE – INTERAGENCY SUBRECIPIENTS
                                                                 agreement or most recent
                                                                 amendment date
                               81-89   Project Activity          Primary Place of Performance;                 X
                                       Location                  Zip code must be zip+4. If +4
                                                                 suffix is unknown, zeros or nines
                                                                 can be entered. Look to the
                                                                 ‚Additional PoP Addresses‛
                                                                 instructions in the next section for
                                                                 more details.
                                 90    Subrecipient              If this is answered ‚Yes‛, the top            X
                                       Indication of             5 names and compensation fields
                                       Reporting                 must be completed
                                       Applicability

                               91-92   Subrecipient Highly       Required if not a public entity
                                       Compensated
                                       Compensation/Names
                                92.a   Subrecipient Number       - Monthly data.                               X
                                       of Jobs                   - Look to the FTE Calculator tab
                                                                 for assistance in calculating the
                                                                 FTE value to report.
                                                                 - This is for ARRA funded jobs
                                                                 only. In the case of multi-funded
                                                                 projects, must only include ARRA
                                                                 funded jobs in this reporting.
                                                                 - Enter zero if no jobs have been
                                                                 created/ retained.
                                92.b   Subrecipient              All funds are federal grants so               X
                                       Description of Jobs       this field must always be filled
                                       Created                   out.

                              Note: Also referenced in the ARRA IA Subrecipient Reporting Spreadsheet “Subrecipient“
                              tab and Pages 7-9 of the ARRA IA Subrecipient R eference Guide




ARRA IA Training Facilitation Guide                                                                     PAGE 10 OF 14
Rev. 12/23/09
       AMERICAN RECOVERY AND REINVESTMENT ACT                     TRAINING FACILITATION GUIDE – INTERAGENCY SUBRECIPIENTS

 Fill out Vendor Tab          What to Expla in: Although Subawards over $25,000 are REQUIR ED to be reported
 and Fields
                              on in detail by the Federal government . The Department is REQUESTING all Vendors
                              be reported in detail, including fields that are optional, regardless of the subaward
                              amount (+/- $25,000). The identity of the Subrecipient Vendor must be disclosed by
                              reporting the DUNS number, if available, -OR- the name and zip code of the Vendor’s
                              headquarters. (Subrecipient Vendors are not required to obtain a DUNS number )

                              The Subrecipient must enter the following data for their Vendors:
                                  Award ID
                                  Vendor DUNS Number -OR- Vendor Name & Vendor Zip + 4
                                  Product and Service Description
                                  Payment Amount

                              Note: Also referenced in the ARRA IA Subrecipient Reporting Spreadsheet “Vendor“ tab and
                              Page 9 of the ARRA IA Subrecipient Reference Guide


 Review Additional            What to Expla in: The fields on this tab must be completed for any Additional Places
 PoP Addre sse s
                              of Performance. Place of Performance (PoP) is anywhere else the Subrecipient has
 Tab and Fields
                              completed work on the ARRA project, besides the Primary Place of Performance.

                              Review and fill out all (if known), the PoP tab descriptions in spreadsheet and
                              additional comments provided below:

                                  Field                    Comment
                                  Congressional            https://writere p.house .gov/write re p/we lcome .shtml
                                  District
                                  State Senate District    http://www.se n.ca.gov/ftp/SEN/se nplan/senate .htp
                                  State Assembly           http://www.assembly.ca.gov/de faultte xt.asp
                                  District                 (Click on ‚Find my District‛ on the le ft hand side of
                                                           the page)


                                     It is not a requirement to enter PoP’s for each project location; however, it is
                                      requested by the Department to provide that level of detail since it will be posted
                                      on the California Recovery Website.
                                     The PoP address tab is used so the state and federal recovery websites are able to
                                      show where the work is being done with ARRA funds.
                                     If a Consortia: There should be a lead for each consortium, which is the
                                      agency/organization that signed the agreement. At a minimum, the PoP tab
                                      should have an entry for each city address that is in the consortia.


                              Note: Also referenced in the ARRA IA Subrecipient Reporting Spreadsheet “Additional PoP
                              Addresses“ tab and Page 9 of the ARRA IA Subrecipient Reference Guide



 Review                       What to Expla in: This tab includes performance metrics required by the Department
 DOE_Admin Tab                of Energy. This tab also includes Administrative information for the Department
 and Fields
                              Project Manager and Subrecipient for automated file approval or rejection updates.

ARRA IA Training Facilitation Guide                                                                            PAGE 11 OF 14
Rev. 12/23/09
       AMERICAN RECOVERY AND REINVESTMENT ACT                   TRAINING FACILITATION GUIDE – INTERAGENCY SUBRECIPIENTS

                              Fill out all of the relevant fields on the ‚ DOE_Admin ‛ tab:

                              * Look to the ‚Comments‛ section on the ARRA IA Subrecipient Reporting
                              Spreadsheet on the ‚DOE_Admin ‛ tab for more details

                               Ref#          Field                                 Comment
                               A1-A2         Reporting Period (month and year)
                               B1-B13        Administrative Data                   The Administrative fields
                                                                                   should be filled out by the
                                                                                   Subrecipient and CPM, as
                                                                                   defined in the ARRA IA
                                                                                   Subrecipient Reporting
                                                                                   Spreadsheet.
                               C             Department of Energy and              These are all required fields.
                                             California Energy Commission
                                             Performance Metrics

                               F-V           Department of Energy and              These are project-specific
                                             California Energy Commission          performance metrics. Look to
                                             Performance Metrics                   your Department Agreement
                                                                                   for a list of which fields to fill
                                                                                   out.

                              Note: Also referenced in the ARRA IA Subrecipient Reporting Spreadsheet “Subrecipient“
                              tab and Page 9 of the ARRA IA Subrecipient Reference Guide


 Review                       What to Expla in: The purpose of this tab is to report energy performance measures
 DOE_Retrofits Tab
 and Fields                   regarding EACH building being retrofitting with ARRA funds
                                       Fill out all of the relevant fields on the ‚ DOE_Retrofits‛ tab

                              Note: Also referenced in the ARRA IA Subrecipient Reporting Spreadsheet “DOE_ Retrofits”
                              tab and Page 10 of the ARR A IA Subrecipient Reference Guide


 Review DOE_                  What to Expla in: The purpose of this tab is to report the categories of energy
 Retrofits End Use
                              consuming equipment implemented in a retrofitted building , and the associated costs
 Tab and Fields
                              and energy savings
                                      Fill out all of the relevant fields on the ‚ DOE_Retrofits End Use" tab

                              Note: Also referenced in the ARRA IA Subrecipient Reporting Spreadsheet “DOE_Retrofits
                              End Use” tab and Page 10 of the ARRA IA Subrecipient Reference Guide

 Monthly ARRA
 Reporting Proce ss           What to Expla in: The ARRA R eporting process is required monthly. The process for
                              the Subrecipient to complete the monthly process is as follows:

                              Before the 3 rd calendar day of each month:

                                      1.   The Subrecipient should open a copy of the last submitted ARRA IA

ARRA IA Training Facilitation Guide                                                                      PAGE 12 OF 14
Rev. 12/23/09
       AMERICAN RECOVERY AND REINVESTMENT ACT                     TRAINING FACILITATION GUIDE – INTERAGENCY SUBRECIPIENTS
                                           Subrecipient Reporting Spreadsheet (saved on the Subrecipient’s computer or
                                           shared folder)

                                      2.   The Subrecipient should review and verify that every field in the spreadsheet
                                           is accurate and up to date

                                      3.   The Subrecipient should update the following fields with the correct period to
                                           date data:
                                            Subrecipient Number of Jobs
                                               o   Report the number of direct jobs created and/or retained due to
                                                   ARRA funded projects and activities.
                                               o   Direct jobs are those jobs that are compensated directly through
                                                   ARRA funds. Indirect jobs such as material suppliers or central
                                                   service providers should not be included. Therefore, employees who
                                                   deliver materials to a project site or clerical staff who prepare reports
                                                   should not be included in the reported jobs data according to the
                                                   Recovery Act Bulletin 09-08.
                                               o   The Number of Jobs must be reported as full-time equilvalent (FTE).
                                           A calculator is available on the tab titled ‚FTE Calculator‛ in the Subrecipient
                                           spreadsheet to assist you with this calculation. You will need to input the
                                           ARRA funded employee hours per month into the spreadsheet, and an FTE
                                           value will be provided for you. This value is to be input in field B44 of the
                                           Subrecipient tab.
                                            Subrecipient Description of Jobs Created

                                      4.   The Subrecipient should save the updated spreadsheet, preferably in
                                           Microsoft Excel 2003 file.xls format, on your computer or shared folder

                                      5.   Finally, the Subrecipient should Submit the ARRA IA Subrecipient Reporting
                                           Spreadsheet to the Department by the 3 rd calendar day of every month

                              Note: Also referenced on Pages 6-10 of the ARR A IA Subrecipient Reference Guide



 What happens                 What to Explain: Explain that after submitting the ARRA IA Subrecipient Reporting
 when data is                 Spreadsheet (each month), the Department will inform the Subrecipient if the file was
 submitted
                              one of the following:
                                      A. Rejected: One or more mandatory fields were blank in the ARRA IA
                                         Subrecipient Reporting Spreadsheet and the file must be updated and
                                         resubmitted; -OR-
                                      B. Review Required: There are errors in the submitted data and the file must be
                                         updated and resubmitted; -OR-
                                      C. Approved: The ARRA IA Subrecipient Reporting Spreadsheet passed the data
                                         validation.

                              Note: California is a central ized reporting state for ARRA Repo rting (As stated in R AB 09-
                              12). Subrecipients must NOT register at FederalReporting.gov as suggested by Recovery.gov.
                              ALL Subrecipients reporting will be done th rough the Department.

                              Note: Also referenced on Page 10 of the ARRA IA Subrecipient Reference Guide


ARRA IA Training Facilitation Guide                                                                        PAGE 13 OF 14
Rev. 12/23/09
       AMERICAN RECOVERY AND REINVESTMENT ACT               TRAINING FACILITATION GUIDE – INTERAGENCY SUBRECIPIENTS


 Training Closeout            What to Expla in: Solicit final questions, point out the ARRA IA Subrecipient
                              Reference Guide and training documents, and discuss the Post-Training Checklist
                              requirements.
                              Review the ARRA IA Subrecipient Reference Guide, which includes:
                                   Subrecipient ARRA Reporting Instructions
                                   Post-Training Checklist
                                   Frequently Asked Questions

                              Encourage the use of the Post -Training Checklist by the Subrecipients. Using the
                              Post-Training Checklist will provide the opportunity for the Subrecipient to try out
                              the spreadsheet submittal process, and work with the Department Project Manager in
                              troubleshooting issues.




ARRA IA Training Facilitation Guide                                                                PAGE 14 OF 14
Rev. 12/23/09

				
DOCUMENT INFO
Description: Walking Spreadsheet document sample