Question

5. You are preparing a project to lay fiber optic cable. The first task is to mobilize the team. You estimate this will take one week and cost $4000. The second task is to lay the cable. This will take three weeks and will cost $30,000. The total length of the cable is 15 miles. The last task, which will take one week, is to terminate and test the cable. You budget $6000 for this. At the end of the first week, you have completed mobilization, but it cost a total of $4500. At the end of the third week, you have completed five miles of cable at a total cost of $28,000 (installation only). It takes you a total of 10 weeks to complete the entire project, for a total cost of $75,000.

a. At the end of week 1: i. What is your SPI? ii. What is your CPI? iii. What is your EAC?

b. At the end of week 3: i. What is your SPI? ii. What is your CPI? iii. What is your EAC?

c. At the end of the project: i. What is your SPI? ii. What is your CPI? iii. What is your EAC?

Can someone show me how to do this on Excel?

Thanks

Answer 1

Total Budget = Budget for mobilization + Budget for laying cable + Budget for termination and testing = 4000 + 30000 + 6000 = $40,000. Similarly planned time for completion = 1 + 3 + 1 = 5 weeks.

(a) After week 1 mobilization is complete as planned. Hence Planned Value = Earned Value = 4000 => SPI = 1. Actual Cost = 4500. CPI = Earned Value/Actual Cost = 4000/4500 = 0.88. EAC = Estimate at completion = Cost incurred till date + Budget for the rest of the project = 4500 + 30,000 + 6000 = $40,500

(b) After 3 weeks 5 miles are completed in addition to mobilization. Plan was to complete 2/3rd of laying in 2 weeks after mobilization . This should have cost 2/3 of 30,000 = 20,000. Hence total planned value for 3 weeks was 20,000 + 4000 = $24,000. Earned value is only Mobilization and 5 miles of cable laying = 4000 + 5/15 * 30,000 = $14,000. Hence SPI = Earned Value / Planned Value = 14,000/24,000 = 0.58.

Cost incurred = 4500 + 28,000 = $32,500. CPI = EV / Actual Cost = 14,000/32,500 = 0.43. To calculate estimate at completion we need to recalculate budget needed for completion of current task. 5 miles has cost 28,000. Hence balance 10 miles will cost 56,000 at current rates. Hence estimate at completion after week 3= 4500 + 28000 + 56,000 + 6000 = $94,500

(c) AT the end of the project Planned Value = Earned Value again = 40,000 Hence SPI = 1. Actual Cost = 75000. Hence CPI = EV/Actual Cost = 70/ 75=0.93. Eastimate at completion is the actual cost since project is complete = 75,000