Alpha Project Status Templatexlsx [Protected View] - Excel File Home Insert Page Layout Formulas Data Review View Help Acrobat Analytic Solver @ PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing B9 - Fe 50 A B C D E F G H ] K L M 1 Alpha Project Status Report 2 Complete ALL Green Cells and submit as part of Assignment 7. | 3 1 2 3 4 5 6 7 Schedule Percent Planned Value Earned Value Actual Cost Cost Variance | Schedule Variance 4 | Activity | Complete (pv) (EV) (AC) =EV-AC =EV-PV 5 AB 100 5 23,500 6 AC 70 5 19,200 7 AD 75 5 37,500 8 BC 75 5 8,000 9 50 s 5,500 10 Total: s - |38 - 18 93,700 | $ S - 11 12 Cost Variance =Total Column 4 - Total Column 5 = 13 Schedule Variance (5) = Total Column 4-Total Column 3 = Schedule Variance (Weeks) = Schedule Variance ($) / Average Weekly Budget $ 14 = Schedule Variance ($) / (Total Column 3 / 8 weeks) = 15 Time-to-Complete (Weeks) = Cost-at-Completion = Rate of Spending x Total Budget 16 =Total Column 5 / Total Column 4 * Total Budget= 17 Cost-to-Complete = Cost-at-Complete - AC= 18 19 20 21 22 23 24 25 Chapters: 13 & 14 Due: Sunday, 11:59 PM, Week 7 Answer the following questions in MS Word, 1-2 pages max in length, single-spaced, 12-pt font, 1" margins. 1. On June 12, 2002, Alpha Corporation was awarded a $160,000 contract for testing a product. The contract consisted of $143,000 for labor and materials, and the remaining $17,000 was profit. The contract had a scheduled start date of July 3. The network logic, as defined by the project manager by the customer resulted in the following project planning budget: Activity 2 3 4 5 6 7 8 9 10 11 12 13 14 15 AB 2000 | 2000 | 3000 | 3000 | 4000 4000 | 3000 AC 3000 3000 3000 4000 4000 4000 4000 | 2000 2000 1000 AD 5000 | 5000 6000 4000 4000 | 4000 3000 1000 BC 3000 4000 4000 5000 BE 6000 6000 CF 2000 | 3000 3000 DF 3000 | 3000 3000 4000 4000 EF 2000 2000 FG 3000 On August 27, 2002, the executive steering committee received the following report indicating the status of the project at the end of the eighth week: Activity % Complete Actual Cost Time Remaining Based on Weeks) (Weeks AB 100 $23,500 0 AC 70 $19.200 3 AD 75 $37,500 2 BC 75 $8.000 BE 50 $5.500 The steering committee could not identify the real status of the project from the brief report. Even after comparing this brief status report with the project planning budget above, the real status was not clear. Management instructed the project manager to prepare a better status report that depicted the true status of the project at the end of week eight. Your assignment is to prepare a table using the template available in the Blackboard assignment folder labelled "Alpha Project Status Report." Submit the completed Excel spreadsheet along with the rest of your assignment. (16pts)2. In a status report, executives want to know not only where we are today, but also where we will end up. Calculating where we will end up finaneially iz not as easy as it sounds. Selecting the wrong formula can leave the executives and customers with a faulty impression. There are several formulas available for calculation of the estimated cost at completion (EAC). For simplicity, consider the following three formulas: i EAC ={AC(Total) EV(Total)) x (budget at completion) 1. EAC ={(AC(Total))EV(Total)) x (PV for work completed and in progress)) + (planned or revised planned costs of work packages not yet begun) 1. EAC =(actual to date) + (all remaining work, including work in progress, to be completed at the planned or budgeted costs) a. Using the table below, determine the value of EAC for each of the three formulas. Aszsume that A, B, and C are the only work packages in the project, and PV({Total) is the total value for PV for each work package rather than PV for the reporting peniod. Similar to PV{Total), calculate AC(Total). Use the following formula for calculating EV(Total): EV(Total) = Sum of all EV(X), where X = A B .C. EV(X)=% Complete * PV(X). __ b. Considering only activity B, if the reason for the cost overrun is attributed to a one-time occurrence, which of the three formulas would be best to use? c. Ifthe reason for the overrun in activity B 1s because of the higher than expected salanes of the assigned employees and these same employees will be assigned to activity C as well, which of the three formulas would be best to use? d. Considering only activity B, if the reason for the overrun is attributed to overtime and the overtime will continue but only through the completion of activity B, which of the three formulas would be best to use? e. Considering yvour answers to the above four parts, should a company be willing to change the formula for calculating EAC during the execution of the project as well as at each reporting period or gate review meeting? (Epts) BUS 505 Application of Project Management Principles Assignment 7 3. The data identified below was listed in a project's latest status report: oan op PV =$36.000 EV =$30.000 AC =33,000 BAC =$120,000 Original length of the project 10 months Using these data, calculate the following: oan o What are the values for CPI and SPI? What 15 the expected cost at completion (EAC)? How much money will be needed from the time of the report to complete the project? What 1z the cost variance at completion (VAC)? Using SPI, what 1s the new expected length of the project? (6pts)