FEMA Instructions for using the PW TEMPLATE Excel

  • Slides: 51
Download presentation
FEMA Instructions for using the PW TEMPLATE Excel Spreadsheet Workbook To activate the Tutorial,

FEMA Instructions for using the PW TEMPLATE Excel Spreadsheet Workbook To activate the Tutorial, select the slideshow icon ( PROCEED ) in the lower left corner.

The following is an interactive presentation which provides guidance and instructions on using the

The following is an interactive presentation which provides guidance and instructions on using the PW TEMPLATE Project Worksheet The next screen is an Index of all the “Tabs” within the MS excell program. Simply click on the button to access the instructions for that particular page In the PW workbook. Some instructional screens will have a “NEXT”, “BACK” and “INDEX” button at the bottom. Selecting “NEXT” will take you to the next instructional screen for that “Tab” of the workbook. Selecting “BACK” will take you back to the previous instructional screen. Selecting “INDEX” will take you back to the index screen. On the last instructional screen for the “Tab” you selected, only a “Back” and “INDEX” button are offered. To end the instructional presentation, select “Esc” on your keyboard. PROCEED

INDEX To select an instructional topic, click on the subject button. Buttons are color

INDEX To select an instructional topic, click on the subject button. Buttons are color Coded to match the color of the “tabs” on the PW Workbook. Main Project Worksheet Labor Summary Hazard Mitigation Summary RPA Scope Continuation Equipment Summary Repetitive Loss Backup Do Not Scan Applicant Info Cost Continuation Contract Summary Fringe Benefits Special Considerations Materials Summary FIRMette Photos Payroll Data Site Sheets Rental Equipment Inventory Summary Hazard Mitigation Location Map Back to Beginning of Presentation PNP Questionnaire Sample Exit Brief Not operational yet

Basic Notes Scroll Bars (Click and drag – will move page up / down,

Basic Notes Scroll Bars (Click and drag – will move page up / down, left / right) PW Page Selection Tabs (Left Click with mouse to open page) CAUTION Selecting “Update Cost Codes could result in incorrect cost estimate. These Codes and corresponding Cost are based on National. Each Region may Have different cost. The FEMA Equipment Rate Schedule Is based on National, dated 28 Aug 2005 which is the most current. INDEX

Request for Public Assistance (RPA) Form NOTE: Form must have DHS/FEMA heading, Correct Paperwork

Request for Public Assistance (RPA) Form NOTE: Form must have DHS/FEMA heading, Correct Paperwork Burden Disclosure Statement, and Correct O. M. B. number and date. Form can be completed by typing Information into the shaded field; or can be printed out and filled In by pen. Option: “Applicant” and “County” fields can be automatically filled by completing the “Applicant Info-Fill Out First” Tab. INDEX

“APPLICANT INFO-FILL OUT FIRST” Tab This screen is the base screen used to populate

“APPLICANT INFO-FILL OUT FIRST” Tab This screen is the base screen used to populate all the forms within the PW TEMPLATE workbook. Screen must be filled in to transfer like data, and produce PW / Disaster specific options. VIEW Click the NEXT or VIEW button to proceed to the next instructional screen. INDEX NEXT

WARNING Deleting this page will eliminate all links to the forms. Use the drop

WARNING Deleting this page will eliminate all links to the forms. Use the drop down menu arrow to select disaster type, “DR” or “EM” Date of Site Visit or PW Written Enter the DR or EM number only, ie. 1736 Fields that this entry populates are limited in space. Enter state abbreviation only. 1/15/08 1736 DR MO Buchannan St. Joseph, City of A 123 -34567 -00 SJ-001 Enter County name Enter Applicant name. NOTE: Name should be entered as it appears in NEMIS Enter Applicant FIPS number as it appears in NEMIS Use the drop down menu arrow to select category of work: A, B, C, D, E, F, G. Enter Project Reference Number as assigned by PAC. NOTE: NEMIS is limited to 7 characters, DO NOT exceed 7 characters. BACK INDEX NEXT

For a single site (damaged structure, culvert ect. ) enter the physical location. For

For a single site (damaged structure, culvert ect. ) enter the physical location. For multiple sites/areas, the applicant address should be entered. Entries made in the following fields populate to the appropriate form where signatures or information is needed: PW Prepared By, Preparer Title, Public Assistance Coordinator, State Representative, Applicant Representative Title, Applicant Representative Phone No. Enter the “facility” that sustained damage. NOTE: “Emergency Protective Measures” Is not a facility, that is a category of work. Used for “Completed Work”, enter the inclusive dates that work was performed. NOTE: Dates entered should Correspond to dates on Summary Sheets. Date entered is populated to the Exit Brief form. BACK INDEX NEXT

Only used if disaster declaration has an authorized 100% funding window. Select Category of

Only used if disaster declaration has an authorized 100% funding window. Select Category of Work and 100% funding data fields will activate. Enter funding window as identified in disaster guidance. Use drop down menu to Select “YES” or “NO” Enter dates applicant has selected for their 72 hour window. Once data fields are completed this will generate a standardized statement at the beginning of the Scope of Work on the PW. BACK INDEX NEXT

Insert “Y” into the data field if project is to be written as a

Insert “Y” into the data field if project is to be written as a PA Pilot Program Project, for Category A work, with Force Account Labor Regular-Time Hours Option. Selecting “Y” will allow the Force Account Labor Summary Sheet to calculate cost for regular hours. NOTE: For all other categories of work (B, C, D, E, F, G), leave blank. NOTE: For Category A projects, being Written and NOT using the Regular Hours Labor Option – select “N” BACK INDEX

Basic Data populated from “Applicant Info-Fill Out First” Tab. Use this form if the

Basic Data populated from “Applicant Info-Fill Out First” Tab. Use this form if the applicant plans on using an average benefit rate for ALL employees being claimed on project. Roll mouse cursor over cells for instructions Check blocks for all areas that apply to Overtime Fringe. At a minimum “Social Security” and “Medicare” should be checked. NOTE: Scroll down to bottom of Form for applicant signature block. INDEX

Click to READ CAUTION Inserting a row, will place it at the top of

Click to READ CAUTION Inserting a row, will place it at the top of the form, below sample entry. Use “Add Additional Row” button to add rows if needed. Form will hold up to 10, 000 employees Sorting the list will put all the employee names in alphabetical order – this becomes very beneficial when completing Force Account Labor Summary Sheet. NOTE: This page must be filled out in order for the Labor and Equipment Summary pages to Function as intended. INDEX NEXT

Check YES if average rate is to be used. Fringe Benefit as calculated on

Check YES if average rate is to be used. Fringe Benefit as calculated on Fringe Benefit Tab will populate this page. No entries need to be made for individuals. Check YES only if Exempt employees are eligible for overtime as specified in applicants employee pay policy. If overtime rate is above 1. 5 times regular rate, for ALL employees, as specified in applicant pay policy, change to authorized rate. If individual rates are to be used, check NO, and this area must be filled out for each individual. Enter all employees, job title, pay status, regular pay rate, then “Sort” NOTE: There is no form designed that will accommodate all applicants, or all situations as not all applicant payrolls are simple. Issues such as Comp-Time, different overtime rates for employees, different overtime for holidays, will require hard data entries on the Labor Summary Sheet. This will require un-protecting the Labor Summary Form. See the Advanced Tutorial for these type of issues. BACK INDEX

READ before filling out form Completion of this page provides ease and quick entries

READ before filling out form Completion of this page provides ease and quick entries onto the Force Account Equipment Summary Sheet. Provides choice of either hard entry of equipment, or input through FEMA Equipment Rate search. Provides ease of changing operators through drop down menu selection linked to Payroll Data page. Can list up to 10, 000 pieces of equipment Allows formatting for numerous pieces of same type of equipment, and numerous operators for same piece of equipment. INDEX NEXT

Inputting Vehicle Information Manually Make and Model is optional Vehicle Type entered manually (typed

Inputting Vehicle Information Manually Make and Model is optional Vehicle Type entered manually (typed in) Vehicle HP and FEMA Cost Code entered manually (typed in) Use drop down menu to Select operator NOTE: Selecting the “Sort” option on the Payroll Data tab provides ease of locating employees using the menu. CAUTION Truck, pickup (32)-2 If operator names are not selected using Drop Down Menu, the labor to equipment hours imbedded check will not work. (See Notes on Equipment Summary Form) Identifies Vehicle Number Identifies Second operator of same vehicle BACK INDEX NEXT

Inputting Vehicle Information with Cost Code Search Select Search Scroll to locate equipment, then

Inputting Vehicle Information with Cost Code Search Select Search Scroll to locate equipment, then select equipment Select “Copy to Project Cost” Select “Yes” BACK INDEX NEXT

Use the Insert/Delete Row buttons to add/remove rows if needed. Adding a row will

Use the Insert/Delete Row buttons to add/remove rows if needed. Adding a row will place it at the top of the list, then use the “Sort” to list equipment in alpha-numeric order. Inserting rows manually might not always carry over the correct links on the Equipment Summary Sheet. Using the Sort option will place equipment in alpha-numeric order Line automatically entered using the Cost Code Search Vehicle Identification, and any additional operator designator must be entered manually after Cost Code Search inputs line item Operator selected from drop down menu CAUTION If operator names are not selected using Drop Down Menu, the labor to equipment hours imbedded check will not work. (See Notes on Equipment Summary Form) BACK INDEX NEXT

“VARIOUS” should only be used for equipment that does not require a “Hands-On” operator

“VARIOUS” should only be used for equipment that does not require a “Hands-On” operator for operation. In some cases, “tools”, such as chainsaws are operated by numerous personnel daily – check with PAO / PAC on whether “VARIOUS” will be acceptable. VIEW “Click” for an expanded explanation on multiple pieces of equipment with 1 operator (pickup, trailer, backhoe) “Click” for an expanded explanation on equipment that requires a dual selection of Equipment Codes (bucket truck) BACK INDEX

Scenario: Alan Smith hauls a backhoe to a worksite on a trailer attached to

Scenario: Alan Smith hauls a backhoe to a worksite on a trailer attached to a pickup truck. This is 3 eligible pieces of equipment being operated by 1 person. Since equipment trailer requires a host vehicle it needs to be associated with the “prime” mover, in this case, a Pickup Truck, vehicle No. 32. Since Loader is associated with 1 operator, and haul vehicle – a manual entry into the Comments helps clarify this for personnel doing review. Mr. Smith is entered as the operator for all 3 pieces of Equipment. Identifies equipment as pickup truck # 32, with a second operator over the period covered on the project. BACK INDEX

Equipment Requiring Two Codes Step 1: Locate Item Step 2: Copy to Project Cost

Equipment Requiring Two Codes Step 1: Locate Item Step 2: Copy to Project Cost Step 3: “Yes” NOTE: Identifies the need to use two pieces of equipment to get the rate for the “prime” piece of equipment. Step 4: “OK” Step 5: Select each of the pieces of equipment that makeup the prime equipment. Then select “Copy to Project Cost” BACK INDEX NEXT

Equipment Requiring Two Codes - Continued Manual entry should be placed in the Comments

Equipment Requiring Two Codes - Continued Manual entry should be placed in the Comments Column as to which Codes were used to determine the overall cost Manual entry required in the HP and Capacity/Size Columns BACK INDEX

Allows Regular Time Computation to accommodate PA Pilot Program. Forwards payroll data From PAYROLL

Allows Regular Time Computation to accommodate PA Pilot Program. Forwards payroll data From PAYROLL DATA page. Only eligible hours for eligible work are entered. PS must still verify/check Applicant payroll records (timesheets) To ensure accuracy. INDEX NEXT

Employee is selected from drop down menu. Payroll Data is then populated into blue

Employee is selected from drop down menu. Payroll Data is then populated into blue shaded areas from PAYROLL DATA page. Eligible hours are entered and cost is calculated. BACK INDEX NEXT

CAUTION Us only the “ADD NEW PAGE” button to add pages. Do not COPY

CAUTION Us only the “ADD NEW PAGE” button to add pages. Do not COPY page and create a “new” TAB. The new tab will not link back to the PAYROLL DATA page. Page provides separate computation into Regular and Overtime hours and Pay. New page is added directly below first page. GRAND TOTAL is carried forward from calculation of all pages. If this is a continuation of employees then enter same start date as the first page. If this is a continuation of “days” worked, then enter next date After last date on preceding page. BACK INDEX

Equipment information is populated from EQUIPMENT INVENTORY page Eligible hours are entered and cost

Equipment information is populated from EQUIPMENT INVENTORY page Eligible hours are entered and cost calculated based on the current FEMA Equipment Rate Schedule. INDEX NEXT

New page is added directly below first page. CAUTION Us only the “ADD NEW

New page is added directly below first page. CAUTION Us only the “ADD NEW PAGE” button to add pages. Do not COPY page and create a “new” TAB. The new tab will not link back to the EQUIPMENT INVENTORY page. Select equipment from drop down menu and equipment information is retrieved from EQUIPMENT INVENTORY page. BACK INDEX NEXT

Spreadsheet provides an embedded quick check to compare labor to equipment hours. Can be

Spreadsheet provides an embedded quick check to compare labor to equipment hours. Can be accessed by using the option button or by use of the vertical scroll Bar. Identifies the Labor Sheet line number where labor hours are recorded. Identifies a discrepancy between the labor and equipment hours and date. NOTE: Quick check application is NOT FOOL PROOF. Program will only check 1 employee, against 1 piece of equipment on that day and compare the labor hours recorded against the equipment hours entered. The Program will not take into account if the wrong operator was entered on the EQUIPMENT INVENTORY page. BACK INDEX

Enter information from Invoice. NOTE: Summary form is only used to record ELIGIBLE COMPETED

Enter information from Invoice. NOTE: Summary form is only used to record ELIGIBLE COMPETED WORK. This is not an estimating form for Work to be Completed. Do NOT put Work to be Completed cost on Summary sheet. Enter total invoice amount, then only the eligible cost that pertains the project. Provide a quick comment if invoice and eligible cost are different. Provide a detailed NOTE in the PW as to why there is a difference. INDEX NEXT

New page is added directly below first page. CAUTION Us only the “ADD NEW

New page is added directly below first page. CAUTION Us only the “ADD NEW PAGE” button to add pages. Do not COPY page and create a “new” TAB. The new tab will not bring forward total cost for all pages onto the first page Program will forward total cost from all pages. BACK INDEX

Enter information from Invoice and/ or stock information. NOTE: Summary form is only used

Enter information from Invoice and/ or stock information. NOTE: Summary form is only used to record ELIGIBLE COMPETED WORK. This is not an estimating form for Work to be Completed. Do NOT put Work to be Completed cost on Summary sheet. Enter total invoice amount, then only the eligible cost that pertains the project. Provide a quick comment if invoice and eligible cost are different. Provide a detailed NOTE in the PW as to why there is a difference. INDEX NEXT

New page is added directly below first page. CAUTION Us only the “ADD NEW

New page is added directly below first page. CAUTION Us only the “ADD NEW PAGE” button to add pages. Do not COPY page and create a “new” TAB. The new tab will not bring forward total cost for all pages onto the first page Program will forward total cost from all pages. BACK INDEX

Enter information from invoice. NOTE: Summary form is only used to record ELIGIBLE COMPETED

Enter information from invoice. NOTE: Summary form is only used to record ELIGIBLE COMPETED WORK. This is not an estimating form for Work to be Completed. Do NOT put Work to be Completed cost on Summary sheet. Enter only the eligible cost that pertains the project. Provide a quick comment if needed and Follow up with a detailed NOTE in the PW if needed. INDEX NEXT

New page is added directly below first page. CAUTION Us only the “ADD NEW

New page is added directly below first page. CAUTION Us only the “ADD NEW PAGE” button to add pages. Do not COPY page and create a “new” TAB. The new tab will not bring forward total cost for all pages onto the first page Program will forward total cost from all pages. BACK INDEX

Enter Scope of Work details (materials, estimates) into field. Enter cost line items, Quantity,

Enter Scope of Work details (materials, estimates) into field. Enter cost line items, Quantity, Unit Price. Form will calculate total cost. Signatures are not an approval of the proposal, only a recommendation. INDEX

Check box if hazard mitigation is being recommended. Check box if hazard mitigation is

Check box if hazard mitigation is being recommended. Check box if hazard mitigation is not feasible. Check box if hazard mitigation is considered a good construction practice based on past application by the applicant, or one of the hazard mitigation handbooks. If mitigation is not feasible, state reason(s) why. (Example: Work partially completed and would require retrofit ). Explain mitigation reasoning if it does fall within any of the above reasons. Check boxes for applicable mitigation policy. Enter cost / percentage Information if mitigation is being proposed. Check box if hazard mitigation is being Proposed by applicant. INDEX

Form is used to assist in developing a Cost Benefit Analysis (BCA) when needed.

Form is used to assist in developing a Cost Benefit Analysis (BCA) when needed. Check appropriate blocks if damaged facility has been damaged before. Check type of event in which damaged occurred and date. Check any type of proposed or completed mitigation prior to current event. Enter previous DR, PW, and PW Version number if applicable. Give summary of details of previous damages sustained. Give information regarding any past PWs, mitigation proposals, cost. INDEX

Creates new map page directly first page. Map can be copy and pasted onto

Creates new map page directly first page. Map can be copy and pasted onto form from Streets and Trips INDEX NEXT

Step 1 Use box option then click and drag box over desired map area

Step 1 Use box option then click and drag box over desired map area Step 2 Select EDIT, then COPY MAP. Return return to PW BACK INDEX NEXT

Step 3 Right click with mouse and Select PASTE. Click and drag to size

Step 3 Right click with mouse and Select PASTE. Click and drag to size or position map. BACK INDEX

Creates new map page directly first page. Detailed instructions on inserting a FIRMette Click

Creates new map page directly first page. Detailed instructions on inserting a FIRMette Click YES for a direct access to FEMA Map Center INDEX NEXT

Selecting YES from the Instructions block provides direct link to Map Center NOTE: Detailed

Selecting YES from the Instructions block provides direct link to Map Center NOTE: Detailed step-by-step instructions on creating a FIRMette Can be found on file: “How to Make a FIRMette. ppt” BACK INDEX NEXT

Drag FIRMette from Map Center onto PW page and size or center as necessary.

Drag FIRMette from Map Center onto PW page and size or center as necessary. BACK INDEX

Creates new map page directly first page. Instructions on reducing photo file size to

Creates new map page directly first page. Instructions on reducing photo file size to reduce overall file size of the project. INDEX NEXT

DHS/FEMA heading, OMB No. and date; and DHS/FEMA Disclosure Statement is MANDATORY on the

DHS/FEMA heading, OMB No. and date; and DHS/FEMA Disclosure Statement is MANDATORY on the PW or it is not considered a valid document for filing a claim. The written Project Worksheet is the “OFFICIAL” claim for federal funding. Date entered should be the last Date of “Work Completed” being submitted and match the Last date of entries on the Summary Sheets attached. Blue fields are populated from the “Applicant Info-fill Out First” page. GPS location MUST be included. Enter GPS location using decimal degree. Example: Latitude: 38. 76514 Longitude: -94. 34567 INDEX NEXT Percentage of Work Completed is based off of the dollar amount of Completed Work and total project cost

Step 2 Select INSERT PICTURE Which will open a file search window to locate

Step 2 Select INSERT PICTURE Which will open a file search window to locate photo to insert Step 1 Click inside photo field to activate cell Step 3 Locate photo file and select OPEN Step 4 Photo will automatically size itself to fit within Photo box Step 5 Use text field to give description of photo BACK INDEX

Check appropriate answer as it pertains to project. Used to assist in identification of

Check appropriate answer as it pertains to project. Used to assist in identification of possible 406 Hazard Mitigation projects. Damage Description text field. Damage description should NOT contain Scope of Work or Applicant “action” entries, or entries pertaining to Work or task that the applicant did. (You can view sample Damage Description entries from the INDEX page) Scope of Work text field. Scope of Work narratives should be separated between Work Completed and Work to be Completed. (You can view sample Scope of Work entries from the INDEX page) BACK INDEX NEXT Check appropriate answers. NOTE: DO NOT check any Answers YES, unless you have a YES or UNSURE checked on the Special Considerations form.

Work Completed cost from Summary forms. Each entry into the Project Cost Narrative is

Work Completed cost from Summary forms. Each entry into the Project Cost Narrative is numbered Work Completed and Work to be Completed are separated. Used to bring forward any cost from the Cost Continuation sheet BACK INDEX Cost and Total Project Cost will self calculate From the “Quantity”, “Unit” and “Unit Price” entries Signature Blocks

Blue fields are populated from the “Applicant Fill-Out Info First page. Adds next page

Blue fields are populated from the “Applicant Fill-Out Info First page. Adds next page directly below first page. Text box to continue Damage Description And/or Scope of Work narrations. Also used for Project Specific Notes. INDEX

Adds next page directly below first page. Provides search for Cost Codes NOTE: Cost

Adds next page directly below first page. Provides search for Cost Codes NOTE: Cost for codes are based on National Codes, cells are not protected so “Unit Price” cost can be adjusted for local area or Disaster Specific cost. Use drop down menu To search for item, then Select “Copy to Project Cost. ” Select YES and item will post to next empty line on sheet. INDEX NOTE: Total cost will Transfer to front of PW.

Check appropriate answer for each of the 9 questions. Any question answered YES or

Check appropriate answer for each of the 9 questions. Any question answered YES or UNSURE requires an explanation comment placed in the text field. NOTE: From is required to be submitted with all projects even if all answers to questions are NO. NOTE: If any question is marked as YES or UNSURE, then the “Special Considerations Included” on the front of the Project Worksheet is marked YES. INDEX

This form is tabulation of all the Summary Sheets for Completed Work only. It

This form is tabulation of all the Summary Sheets for Completed Work only. It does not include any cost that might be for Work to Be Completed on the PW or PW Cost Continuation Sheet. NOTE: This form in NOT required in all Regions, but may be required in some. Captures the total cost of All Summary Sheets. Cost Are populated directly from The Labor, Equipment, Contract, Materials, and rental sheets. Text boxes available for any specific details. INDEX