plz just answer Q2 & Q3 .. ((Q1 is given ))

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. Task Item Personnellem Unit Unit Rate Units/ Items Item Subtotal Task Subtotal Surface Water Drainage Plan Labor Costs Principal Project Manager Project Engineer Staff Engineer AutoCAD Administrative Assistant hr 180.00 hr 150.00 hr 120.00 hr 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 6 $ 200.00 1 $ Direct Costs Subtotal 3,000 200 $3,200 Outside Services Aerial Lidar Survey Cultural Resource Survey Is Is 4500.001 11 $ 3900.00 1 $ Outside Services Subtotal 4,500 3,900 $8,400 Subtotal $21,940 Administrative Fee Direct osts Fee Outside Services Foo 5% 159 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. 14th. November 2019 Cost Estimate of Surface water drainage plan DESCRIPTIONS UNIT RATE NOS. AMOUNT(IN $) A) LABOR COST (annual) 1 PRINCIPAL annual 130,000 2 PROJECT MANAGER annual 102,000 3 PROJECT ENGINEER annual 75,000 4 STAFF ENGINEER annual 58,000 5 AUTO CAD annual 58,000 6 ADMINISTRATIVE ASSISTANT annual 36,000 LABOUR PRICE (PL)= annual 459,000 B) DIRECT COST (DCL) COPIES, SHIPPING,BINDING,ETC SET 500 6 3,000 annual 200 1 62,600 TRAVEL (SITE VISIT) - @$200 PER DAY) TOTAL DIRECT COST (DCL) 65,600 C) BURDENED COST OF LABOR (BCL) AERIAL SURVEY Is 4500 1 4,500 CULTURAL RESOURCE SURVEY Is 3900 1 3,900 BURDENED COST OF LABOR (BCL) 8,400 subtotal= 533,000 D) OVERHEAD COST(OH) 1.85% 9,861 TOTAL COST= 542,861 annual 600,000 ASSUMING INCOME OR TURNOVER (as not mentioned)= E) PROFIT= 57,140 PROFIT %= 9.52% ANSWER SUMMARY: 1 LABOR PRICE (PL) 459,000 2 DIRECT COST LABOR(DCL) 65,600 3 BURDENED COST OF LABOR (BCL) 8,400 4 OVERHEAD COST(OH) 9,861 5 TOTAL COST= 9,861 5 PROFIT (IN $) 57,140 6 PROFIT (IN %) 10% 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. Task Item Personnellem Unit Unit Rate Units/ Items Item Subtotal Task Subtotal Surface Water Drainage Plan Labor Costs Principal Project Manager Project Engineer Staff Engineer AutoCAD Administrative Assistant hr 180.00 hr 150.00 hr 120.00 hr 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 6 $ 200.00 1 $ Direct Costs Subtotal 3,000 200 $3,200 Outside Services Aerial Lidar Survey Cultural Resource Survey Is Is 4500.001 11 $ 3900.00 1 $ Outside Services Subtotal 4,500 3,900 $8,400 Subtotal $21,940 Administrative Fee Direct osts Fee Outside Services Foo 5% 159 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. 14th. November 2019 Cost Estimate of Surface water drainage plan DESCRIPTIONS UNIT RATE NOS. AMOUNT(IN $) A) LABOR COST (annual) 1 PRINCIPAL annual 130,000 2 PROJECT MANAGER annual 102,000 3 PROJECT ENGINEER annual 75,000 4 STAFF ENGINEER annual 58,000 5 AUTO CAD annual 58,000 6 ADMINISTRATIVE ASSISTANT annual 36,000 LABOUR PRICE (PL)= annual 459,000 B) DIRECT COST (DCL) COPIES, SHIPPING,BINDING,ETC SET 500 6 3,000 annual 200 1 62,600 TRAVEL (SITE VISIT) - @$200 PER DAY) TOTAL DIRECT COST (DCL) 65,600 C) BURDENED COST OF LABOR (BCL) AERIAL SURVEY Is 4500 1 4,500 CULTURAL RESOURCE SURVEY Is 3900 1 3,900 BURDENED COST OF LABOR (BCL) 8,400 subtotal= 533,000 D) OVERHEAD COST(OH) 1.85% 9,861 TOTAL COST= 542,861 annual 600,000 ASSUMING INCOME OR TURNOVER (as not mentioned)= E) PROFIT= 57,140 PROFIT %= 9.52% ANSWER SUMMARY: 1 LABOR PRICE (PL) 459,000 2 DIRECT COST LABOR(DCL) 65,600 3 BURDENED COST OF LABOR (BCL) 8,400 4 OVERHEAD COST(OH) 9,861 5 TOTAL COST= 9,861 5 PROFIT (IN $) 57,140 6 PROFIT (IN %) 10%