The table below presents a cost estimate WBS submitted with a proposal to develop a surface water drainage plan for a new water treatment plant. Complete the following exercises regarding the anticipated financial performance of the project. Turn in printouts (neat and well organized) of your work. Personnellem Unit Unit Rate Units/ Items Task Item Surface Water Drainage Plan Item Subtotal Task Subtotal Labor Costs Principal Project Manager Project Engineer Staff Engineer AutoCAD Administrative Assistant hr 180.00 hr 150.000 hr 120.000 90.00 hr 85.00 hr 55.00 Labor Subtotals 3 $ 24 $ 161 $ 20 $ 24 $ 8 $ 95 540 3,600 1,920 1,800 2,040 440 $10,340 Direct Costs Copies, shipping, binding mtls Travel (Site Visit) set day 500.00 60 $ 200.000 1 $ Direct Costs Subtotal 3,000 200 $3,200 Outside Services Aerial Lidar Survey Cultural Resource Survey Is Is 4500.00 1 $ 3900.00 1 $ Outside Services Subtotal 4,500 3,900 $8,400 Subtotal $21,940 Administrative Fee Direct Costs Fee Outside Services Fee 5% 15% 15% $517 $480 $1,260 TOTAL ESTIMATE $24,197 1. The annual salaries of the proposed staff for this task are provided below. Develop a spreadsheet that calculates the labor price (PL), direct cost of labor (DCL), burdened cost of labor (BCL), overhead cost (OH), and profit ($ and %) that will report to the project for each individual and for the project as a whole (e.g. include a column for totals for each employee). Assume an Overhead Multiplier of 1.85? (12 points) Principal Project Manager Project Engineer Staff Engineer AutoCAD Administrative Assistant $130,000 $102,700 $75,000 $58,500 $58,000 $36,000 2. For the analysis in Question 1, assume a Direct Labor Multiplier of 3.2 and calculate a. The target profit margin (%) for your firm (2 points) b. An 'ideal salary will have the firm's target profit margin built in, although in reality and as shown in Question 1, the actual profit margin built into each employee's salary will be variable. Calculate the ideal salary that each of the proposed staff should have to ensure that the project meets the target profit margin for the labor component of the project. (6 points). 3. Evaluate the actual individual salaries of the proposed staff as calculated in Question 1 and identify any that might be negatively impacting your labor margin. Manipulate the figures in your spreadsheet to identify specific remedies that can be taken to bring the project labor margin up to a reasonable range. Be ready to discuss your conclusions in class