Project Controls & Earned Value Management
Earned Value TCPI & Lipke Earned Schedule Formula
Audit unrecoverable cost overruns via To-Complete Performance Index TCPI = (BAC - EV) / (BAC - AC) and fix late-project SPI = 1.00 convergence distortion with Walt Lipke’s Earned Schedule SPI(t) = ES / AT.
Catch two classic EVM blind spots: optimistic BAC forecasts and late-project SPI = 1.00!
Standard Earned Value Management (EVM) tracks Budget at Completion (BAC), Earned Value (EV), and Actual Cost (AC), but suffers two famous blind spots! First, project managers claiming they will still finish on original budget BAC despite early cost overruns must achieve future cost efficiency TCPI_BAC = (BAC - EV) / (BAC - AC) (work remaining divided by budget remaining — empirical DoD studies show whenever TCPI_BAC > 1.10 or AC >= BAC, original BAC is statistically unrecoverable!). Second, dollar-based SPI = EV / PV ALWAYS converges to 1.00 at project completion even if a project finishes 6 months late (because EV eventually equals BAC = PV)! Walt Lipke (2003) solved that distortion with Earned Schedule ES = C + (EV - PV_C) / (PV_{C+1} - PV_C) (the exact time period when current EV WAS scheduled to occur), giving true Time-Based SPI(t) = ES / AT and Independent Duration Estimate IEAC(t) = PD / SPI(t)!
Verify on BAC = 1,000 ($k, B2), Earned_Value EV = 450 ($k, B3), Actual_Cost AC = 550 ($k, B4 -> CPI = 450/550 = 0.8182), Actual_Time AT = 6.0 months (B5), Planned_Duration PD = 10.0 months (B6), and Cumulative Scheduled Baseline PV_4 = 400 ($k at month C = 4, B7) to PV_5 = 500 ($k at month 5, B8): Remaining Work is 550 vs Remaining Budget 450 -> Required TCPI_BAC = 550 / 450 = 1.2222 (> 1.10 Warning!); Lipke Earned Schedule is ES = 4 + (450 - 400) / (500 - 400) = 4.50 months; Time-Based SPI(t) is 4.50 / 6.00 = 0.7500; and Forecast Total Duration is IEAC(t) = 10.0 / 0.7500 = 13.33 months (+3.33 months late)!
| Project Controls Metric (BAC=1000, EV=450, AC=550, AT=6 mo) | Formula | Verified Output |
|---|---|---|
| Required Efficiency to Hit BAC (TCPI_BAC) | (BAC - EV) / (BAC - AC) | 1.2222 (> 1.10 Unrecoverable!) |
| Walt Lipke Earned Schedule (ES) | 4 + (450 - 400) / (500 - 400) | 4.5000 months earned |
| Time-Based Schedule Index SPI(t) | ES / AT = 4.50 / 6.00 | 0.7500 (25.0% Behind Schedule) |
| Forecast Completion Duration IEAC(t) | PD / SPI(t) = 10 / 0.75 | 13.33 months (+3.33 mo Delay) |
To-Complete Performance Index (TCPI_BAC) Formula
Guard the budget-exhausted case AC >= BAC (when EV < BAC): zero remaining funds make hitting BAC mathematically impossible ("Budget Exhausted: Rebaseline to EAC")!
=LET(bac_t, B2, ev_cr, B3, ac_cr, B4, IF(OR(bac_t <= 0, ev_cr < 0, ev_cr > bac_t, ac_cr < 0), "Invalid EVM Input", IF(AND(ev_cr < bac_t, ac_cr >= bac_t), "Budget Exhausted (AC >= BAC)", IF(ev_cr = bac_t, 0, ROUND((bac_t - ev_cr) / (bac_t - ac_cr), 4)))))Lipke (2003) Interpolated Earned Schedule & SPI(t) Formula
Validate PV_C <= EV <= PV_{C+1} with strictly increasing planned value PV_{C+1} > PV_C and elapsed time AT > 0.
=LET(ev_cr, B3, at_mo, B5, c_int, B9, pv_c, B7, pv_n, B8, IF(OR(at_mo <= 0, c_int < 0, pv_n <= pv_c, ev_cr < pv_c, ev_cr > pv_n), "Invalid Earned Schedule Bracket", LET(es_mo, c_int + (ev_cr - pv_c) / (pv_n - pv_c), ROUND(es_mo / at_mo, 4))))Try the example
Use the original values or change them to test your understanding.