从一道“挣值计算”真题出发，手把手教你用Excel搞定项目成本进度分析

从挣值计算真题到实战：用Excel构建项目成本进度监控模型

项目管理中最让人头疼的莫过于回答"钱花到哪里了？进度到底如何？"这两个灵魂拷问。去年辅导一位备考PMP的工程师时，他盯着那道关于费用偏差的真题足足十分钟——题目给出了计划值、实际成本和完成百分比，但就是算不出正确答案。这让我意识到，挣值管理（EVM）这个理论上清晰的概念，在实际应用中却常常成为学习者的"拦路虎"。

本文将从一个典型考题切入，带你在Excel中搭建完整的挣值分析模型。不同于单纯记忆公式，我们会用动态图表实现"输入数据即时可视化"，让你真正理解PV、EV、AC这些抽象指标如何反映项目健康状态。无论你是备考认证的学员，还是需要监控项目进展的经理，这套方法都能直接移植到工作场景中。

1. 挣值管理核心概念解析

在建筑工地上，包工头常通过"完成了几层楼板"来判断进度，这就是挣值思维的雏形。现代项目管理将其系统化为三个基础指标：

• PV（Planned Value）：计划工作的预算价值，好比"计划今天应该浇筑完三层楼板，预算5万元"
• EV（Earned Value）：实际完成工作的预算价值，如"实际只完成两层半，按预算标准值4.2万元"
• AC（Actual Cost）：实际花费成本，可能是"虽然只完成两层半，但因材料涨价花了5.5万元"

这三个值构成的三角关系，能精准诊断项目问题。来看这道改编自真实考题的例题：

某工作包总预算1500元，第5天检查时实际支出1350元，但仅完成2/3工作量。求费用偏差(CV)和进度偏差(SV)。

用公式计算：

PV = 总预算 × 计划完成比 = 1500 × (5/总工期) EV = 总预算 × 实际完成比 = 1500 × 2/3 = 1000 AC = 1350 CV = EV - AC = 1000 - 1350 = -350 SV = EV - PV = 1000 - (假设总工期7天，PV=1071) = -71

注：原题未给出总工期，此处假设7天计算PV

这个结果说明：项目不仅超支350元（CV为负），进度也落后71元的工作量（SV为负）。但单纯数字缺乏冲击力，接下来我们用Excel让这些数据"活起来"。

2. Excel建模四步法

2.1 基础数据架构

新建Excel工作表，按以下结构搭建数据框架：

关键公式示例：

累计PV = SUM($D$2:D2) // 假设日预算在D列 累计EV = 总预算 × 实际完成%

2.2 动态偏差计算

右侧添加分析指标区：

专业提示：当CPI<1时，采用EAC=BAC/CPI公式；若偏差属偶然，可用EAC=AC+(BAC-EV)

2.3 可视化呈现

选择日期、PV、EV、AC四列数据，插入组合图表：

1. 累计值用折线图（带数据标记）
2. 添加警戒线（如CPI=1的参考线）
3. 设置双轴（成本与百分比）

![示例图表：三条曲线逐渐分离，EV低于PV表明进度滞后，AC高于EV显示成本超支]

2.4 情景模拟器

通过数据验证创建下拉菜单，模拟不同场景：

数据 → 数据工具 → 数据验证 → 序列 → 输入"乐观,基准,悲观"

对应设置三组完成率和成本数据，观察指标变化规律。

3. 深度应用技巧

3.1 多工作包整合分析

真实项目往往包含数十个工作包。新建"项目总览"表，用SUMIFS汇总各包数据：

3.2 预警系统搭建

条件格式设置三级预警：

=AND(CPI<0.9, SPI<0.9) → 红色 =OR(CPI<1, SPI<1) → 黄色 其他 → 绿色

3.3 历史数据分析

复制模型到新工作表，修改为周报/月报模式，添加趋势分析：

移动平均 = AVERAGE(OFFSET(当前单元格,-3,0,4,1))

4. 从考题到实战的跨越

某电商平台在双十一筹备项目中，用此模型发现了关键问题：

1. 第3周CPI骤降至0.6，排查发现是服务器采购价超出预期
2. 立即启动备用供应商谈判，最终将EAC控制在预算的110%
3. 通过SPI发现测试环节滞后，调整资源后追赶进度

模型优化建议：

• 添加工时数据列，分析资源效率
• 集成风险登记册，关联偏差与风险事件
• 开发自动化数据采集接口（如对接JIRA）

点击下载文中示例模型：[虚构链接] 提取码: evm1

注：实际应用时建议用真实项目数据替换示例，并定期做基线调整