1 / 51

Instructions for using the PW TEMPLATE Excel Spreadsheet Workbook

FEMA. Instructions for using the PW TEMPLATE Excel Spreadsheet Workbook. To activate the Tutorial, select the slideshow icon ( ) in the lower left corner. PROCEED. The following is an interactive presentation which provides guidance and

alcina
Download Presentation

Instructions for using the PW TEMPLATE Excel Spreadsheet Workbook

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. 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

  3. Site Sheets Backup Do Not Scan PNP Questionnaire Sample Exit Brief 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. Hazard Mitigation Summary Labor Summary Main Project Worksheet RPA Equipment Summary Repetitive Loss Scope Continuation Contract Summary Applicant Info Location Map Cost Continuation Fringe Benefits Special Considerations Materials Summary FIRMette Rental Equipment Payroll Data Photos Hazard Mitigation Equipment Inventory Summary Not operational yet Back to Beginning of Presentation

  4. 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

  5. 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

  6. “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 VIEW VIEW VIEW Click the NEXT or VIEW button to proceed to the next instructional screen. INDEX NEXT

  7. 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 Enter County name DR MO Enter Applicant name. NOTE: Name should be entered as it appears in NEMIS Buchannan St. Joseph, City of A 123-34567-00 Enter Applicant FIPS number as it appears in NEMIS SJ-001 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 NOTexceed 7 characters. BACK INDEX NEXT

  8. 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, Applicant Representative Title, Applicant Representative Phone No. For a single site (damaged structure, culvert ect.) enter the physical location. For multiple sites/areas, the applicant address should be entered. 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

  9. 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

  10. 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

  11. 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

  12. Click to READ CAUTION Use “Add Additional Row” button to add rows if needed. Inserting a row, will place it at the top of the form, below sample entry. Form will hold up to 10,000 employees Sorting the list will put all the employee names in alphabetical order – this becomes very beneficialwhen 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

  13. 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

  14. 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

  15. Truck, pickup (32)-2 Inputting Vehicle Information Manually Make and Model is optional Vehicle HP and FEMA Cost Code entered manually (typed in) Vehicle Type 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 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

  16. 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

  17. 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

  18. “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 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

  19. 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

  20. 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

  21. 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

  22. 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

  23. 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

  24. 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

  25. 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

  26. 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. New page is added directly below first page. Select equipment from drop down menu and equipment information is retrieved from EQUIPMENT INVENTORY page. BACK INDEX NEXT

  27. Identifies the Labor Sheet line number where labor hours are recorded. Identifies a discrepancy between the labor and equipment hours and date. 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. 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

  28. 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

  29. 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 New page is added directly below first page. Program will forward total cost from all pages. BACK INDEX

  30. 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

  31. 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 New page is added directly below first page. Program will forward total cost from all pages. BACK INDEX

  32. 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

  33. 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 New page is added directly below first page. Program will forward total cost from all pages. BACK INDEX

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

  35. Check box if hazard mitigation is being recommended. Check box if hazard mitigation is not feasible. If mitigation is not feasible, state reason(s) why. (Example: Work partially completed and would require retrofit ). 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. 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

  36. 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

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

  38. 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

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

  40. 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

  41. 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

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

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

  44. 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 form 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. Percentage of Work Completed is based off of the dollar amount of Completed Work and total project cost GPS location MUST be included. Enter GPS location using decimal degree. Example: Latitude: 38.76514 Longitude: -94.34567 INDEX NEXT

  45. 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

  46. 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) Check appropriate answers. NOTE: DO NOT check any Answers YES, unless you have a YES or UNSURE checked on the Special Considerations form. 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

  47. Cost and Total Project Cost will self calculate From the “Quantity”, “Unit” and “Unit Price” entries 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 Signature Blocks BACK INDEX

  48. 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

  49. 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. Provides search for Cost Codes Adds next page directly below first page. 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. NOTE: Total cost will Transfer to front of PW. INDEX

  50. 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

More Related