Earned Value Management EXCEL💰 4 Steps Project Schedule Variance Analysis Cost at Completion Control

In this video of #engineeringmanagementacademy #EarnedValueManagement for #projectcontrol is tutored by #drmehrdadarashpour
🔔 Subscribe: https://bit.ly/EngineeringManagementA...
❎ Excel workbook to follow along: https://bit.ly/Excel_Earned_Value
▶ RELATED VIDEOS:    • Project Earned Value Analysis  
⌛ TIMESTAMPS
0:00 - Introduction to Earned Value Management (EVM) in Excel
0:21 - Excel’s Template to calculate Schedule & Cost Variances, Schedule Performance Index (SPI), Cost Performance Index (CPI), Estimated Time at Completion (ETC) & Cost Estimate at Completion (EAC)
0:51 - Step 1 (Establish project baseline & calculate planned values based on budgeted cost of activities)
1:42 - Step 2 (Track the actual progress of project activities using site reports & measure actual costs incurred)
2:32 - Step 3 (Measure the work completed (Earned Value) against the planned schedule & budget)
3:26 - Step 4 (Calculate EVA metrics to control project deviations from planned objectives)
5:00 - Interpret estimated time at completion (ETC) & Cost Estimate at Completion (EAC)

✍ Request Next Tutorial Video: https://forms.gle/Frz9U9imCouofdoD8

In a previous video, we discussed how to calculate schedule & cost variances, which can provide early indicators of deviations from planned project baselines. The link to that video:    • Project Earned Value Analysis   Now, because time is money, we want to use EXCEL to express both schedule & cost variances in dollar values. We work on an Excel Template to calculate Schedule Variance (SV), Cost Variance (CV), Schedule Performance Index (SPI), Cost Performance Index (CPI), Estimated Time at Completion (ETC) & Cost Estimate at Completion (EAC). You can follow along by getting the Excel workbook via the link: https://bit.ly/Excel_Earned_Value
Let’s focus on a Data Center project with a planned budget of $8 million.

Step 1 is to establish project baseline & calculate PV based on budgeted costs of activities
There are 5 project activities, starting with installing servers & storage devices and ending with Documentation and Handover of the data center. Project activities are started in July & will be completed by the end of December. We are 3 months into the project & Our analysis date for EVA is at the end of September. We can establish project baseline by finding activity planned values or PV. For the 1st activity, use the “SUM” function to add up monthly costs. We can quickly find project baseline or total planned value of $8 million by adding up activity PVs.

Step 2 is to Track the actual progress of project activities using site reports & measure actual costs incurred
We should monitor project logs and field reports to understand the actual progress rate of activities. In our project, the activity of configuration & setup has 80% progress. Testing & validation have progress of 35%. The following activity of Integration & Interoperability Testing is having 10% progress. In projects, we should review subcontractor invoices & payment requests to validate work completed & accurately find actual costs or AC.

Step 3 is to Measure the work completed (Earned Value) against the planned schedule and budget
We should measure activity Earned Values by multiplying progress percentages & activity planned values. Following that, we can calculate project earned value or EV by using “SUM” function to add up activity EVs, to find $3,387,500 as the project EV. We can also calculate PV by the end of Sep. by adding up all budgeted costs of activities until this point in time. The result is $3,950,000, which is larger than our earned value and warrants a thorough analysis of variances.

Step 4 is to Calculate EVA metrics to control project deviations from planned objectives
Schedule variance or SV is typically calculated in dollar values to integrate cost & schedule performance metrics. We can find SV as EV-PV. The negative SV shows deviations from the planned project schedule & delays.
Next, we can find cost variance or CV, which is EV-AC. The negative CV shows that project suffers from budget overrun. Following CV, we should calculate schedule performance index or SPI, which is EV/PV. An SPI less than 1 means schedule slippage & delays in achieving milestones.
We can calculate cost performance index or CPI, Which is EV/AC.
We predict project completion based on current rate of progress & expected future performance. To calculate ETC or estimated time at completion, divide planned project duration of 6 months by SPI. Results indicate risk of exceeding planned duration of 6 months. We calculate cost estimate at completion or EAC by divideing the planned budget of $8 million by CPI. The high EAC indicates our data center project is expected to exceed its planned budget! This cost overrun could result from unexpected expenses, scope changes, resource constraints or inefficiencies in cost management.
Here is an easy management of project erned value using only 4 simple steps in Microsoft Excel.