标准与实际的成本差异分析（价差与量差的全自动分离模型），月底开会采购和生产总在吵架？财务BP用这套“价差量差分离模型”一招制敌！

上一篇：《多层级BOM动态展开卷算，制造业财务BP搞定成本测算的核心技能，从“事后测算”到“动态模拟”：如何用BI模型实现制造业的报价自动联动？》，我们解决了“标准成本/报价成本怎么算”的问题（图纸上的理论成本）。但现实的制造业车间是极其混乱的：采购没按标准原价买到材料、车间因为机器故障多领了废料、工程部临时用高价料做了替代……等等诸如此类。

一到月底开经营分析会每个月的经营分析会上，只要一提到“毛利下降”，几乎没有一个制造企业不会因此而吵架的。

就拿上一篇合肥这家机械制造企业的减速机A产品来说事吧，如果单产品利润下降老板肯定会问：“减速机A的利润为什么凭空蒸发了好几万？”

• 采购部先喊冤：“真不怪我！最近大宗商品疯涨，特种钢材进价飙升，我也没办法啊！”
• 生产部立马甩锅：“可拉倒吧！你们买的那批钢材质量奇差，废品率极高，我们车间为了凑够产量，硬生生多领了20%的料！”
• 这时候，所有人的目光都看向了财务。

传统财务打开ERP系统，看着那一堆“移动加权平均”后混杂在一起的实际成本，咽了咽口水，根本分不清到底是谁的责任，最后只能唯唯诺诺地把锅背下，成了最无奈的背锅侠。

糊涂账，是企业管理最大的内耗。

上一篇文章，老汪教大家用多级BOM展开，算出了老板心里的底线（标准成本）。今天，老汪带大家进入更加让所有当够了背锅侠的财务们最激动的环节：

我们要利用Excel BI（Power Pivot）徒手捏一个“成本量价差全自动分离沙盘”，让数据自己开口说话。一秒钟把超支的钱，精准劈成【采购买贵的（价差）】和【车间用多的（量差）】，从而能够奖罚分明，一招制敌，让他们再也没办法甩锅了！

一、商业逻辑：什么是真正的“责任溯源”？

在管理会计中，我们管这个叫“标准成本差异分析”。直接给你翻译成大白话公式就是：

一个产品的材料成本超支，无非就是两个原因：买贵了，或者用多了。

1. 价差（归采购管）：
   • 灵魂拷问：同样是你车间领走的这些料，因为采购没守住价格，多花了多少冤枉钱？
   • 老汪公式：(实际买入单价 – 标准定额单价) × 实际领料数量
2. 量差（归生产管）：
   • 灵魂拷问：按照BOM配方，产出这些成品本来只需要100kg，你偏偏去仓库拉了120kg。这多浪费的20kg，按标准价算，败家了多少钱？
   • 老汪公式：(实际领料量-理论应该消耗的数量)×标准单价

数据之美就在于：总超支金额 = 采购价差 + 生产量差。一分都不差！

二、业务模型搭建：我们要解决什么问题？

不要在Excel里用手工单元格函数去痛苦地对齐单据了。我们在Excel里只需要准备两张极度清爽的扁平化基础表，就能把这两方面的成本差异在一秒钟算出，并且动态可视化呈现出来：

表1：标准成本底座表(t_标准穿透BOM)

这就是咱们上一篇文章跑出来的最终成果！我们把多级BOM彻底展平，拿到了每台减速机最底层的纯材料定额。这是老板心里的“标尺”。比如这个减速机A所需要的特种钢材F的穿透定额是10kg。

业务复习：做1台减速机A，底层不管隔了多少层，理论上总共只允许消耗10kg钢材，采购标准价是10元/kg，Ctrl+T转化为超级表后再加载进入PowerPivot数据模型。

表2：车间实际工单表(t_实际工单)

这是月底从ERP系统直接导出，粘贴进来的本月真实发生数据。

老汪带你看透这张表的“猫腻”：

• 产量：车间完工交库了10台。(按理说，10台×穿透定额10kg=理论上只该用100kg钢材。)
• 领料：而车间实际去仓库拉走了120kg。(超领了20kg，生产部败家了！)
• 财务账：财务月末移动加权平均跑成本结账，算出来这批钢材出库总共花了1440元。实际核算出库单价是12元/kg。(比标准的10元贵了2块钱，采购部买贵了！)

我们要解决的问题

理论成本应该是10台×10kg×10元=1000元。

实际花了120kg×12元=1440元（这里实际金额的超支既包括了价差和量差）。

我们要让Power Pivot引擎自动把超支的440元，精准劈成采购的锅和车间的锅！大家请注意，这里440元，仅仅是特种钢材，仅仅是作为演示，实际工作数据更复杂，所以更需要这个自动化计算分离的过程，并且能够快速可视化呈现。

三、技术降维：编写4条王炸DAX度量值

将两张表加载进Power Pivot数据模型后。我们新建这4条度量值，敲下这4段让传统财务看呆的硬核DAX函数：

1.算“理论该花多少钱？”（标准总成本）

=SUMX(
    't_实际工单', 
    't_实际工单'[完工入库量(台)] 
    * LOOKUPVALUE('t_标准穿透BOM'[穿透定额 (QPA)], 't_标准穿透BOM'[顶层成品], 't_实际工单'[生产产品], 't_标准穿透BOM'[底层材料], 't_实际工单'[实际耗用材料]) 
    * LOOKUPVALUE('t_标准穿透BOM'[标准单价 (元/kg/台/件)], 't_标准穿透BOM'[顶层成品], 't_实际工单'[生产产品], 't_标准穿透BOM'[底层材料], 't_实际工单'[实际耗用材料])
)

老汪解密：结果应该是完工10台×定额10kg×单价10元=1000元。老板预期的成本底线稳了。

2.算“实际花了多少钱？”（实际总成本）

=SUM('t_实际工单'[实际总金额(元)])

老汪解密：汇总结果应该是1440元。总共超支440元。接下来，开始“分锅”！

3.算“量差”——追责生产部！（量差金额）

=SUMX(
    't_实际工单',
    -- 实际领了120kg - 理论该领的(10台×10kg) = 多领了20kg
    ('t_实际工单'[实际领料量(kg)] - ('t_实际工单'[完工入库量(台)] * LOOKUPVALUE('t_标准穿透BOM'[穿透定额 (QPA)], 't_标准穿透BOM'[顶层成品], 't_实际工单'[生产产品], 't_标准穿透BOM'[底层材料], 't_实际工单'[实际耗用材料]))) 
    -- 乘以：标准单价(10元)
    * LOOKUPVALUE('t_标准穿透BOM'[标准单价 (元/kg/台/件)], 't_标准穿透BOM'[顶层成品], 't_实际工单'[生产产品], 't_标准穿透BOM'[底层材料], 't_实际工单'[实际耗用材料])
)

老汪解密：剔除价格波动，车间硬生生多废了20kg，按标准价算，量差=+200元。生产部，这200块纯属生产的操作损耗！

4.算“价差”——追责采购部！（价差金额）

=SUMX(
    't_实际工单',
    -- 实际花的1440元 - (实际买的120kg × 标准单价10元) 
    't_实际工单'[实际总金额(元)] - ('t_实际工单'[实际领料量(kg)] * LOOKUPVALUE('t_标准穿透BOM'[标准单价 (元/kg/台/件)], 't_标准穿透BOM'[顶层成品], 't_实际工单'[生产产品], 't_标准穿透BOM'[底层材料], 't_实际工单'[实际耗用材料]))
)

老汪解密：实际花了1440元，但这120kg钢材按标准价本来只要1200元。价差=+240元。采购部，这240块的超支是你们没按住单价导致的！

因为这是一个简单的展示案例，所以我们现在是知道具体的差异金额以及采购和生产各自应该背多少的：生产量差(200)+采购价差(240)=总超支(440)！

一丝不苟，严丝合缝！

四、高阶财务的“无痕取数”与瀑布图

如果到这里你只是拉一个透视表，那还不够惊艳。真正的大神，汇报都是用图表的。但很多财务会发现：Excel默认的瀑布图，不支持直接绑定数据透视表！

所以，我们要先用Excel单元格画布与Power Pivot数据模型对话的桥梁函数——CUBEVALUE函数，来进行无痕取数。

脱离透视表，直接把一根“吸管”插到数据数据模型后台的数据库里吸取数据！

=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[标准总成本]")
=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[价差金额]")
=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[量差金额]")
=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[实际总成本]")

实操魔法解析：

1. 无痕提取：在普通单元格里输入=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[标准总成本]”)，一敲回车，1000这个数字直接从后台蹦出来！如法炮制提取出价差、量差和实际总成本。
2. 插入瀑布图：选中F1:G4这个区域，插入Excel图表-瀑布图。
3. 固定基石（最关键一步）：单独选中第一根柱子（标准总成本）和最后一根柱子（实际总成本），均右键选择“设置为汇总”。

神奇的事情发生了：

图表瞬间变成了一座完美的桥梁（这也是瀑布图被称为桥图的原因）！灰色柱子落地代表总盘，蓝色（或红色）悬空柱子代表变动。从1000块钱的目标成本，因为采购多花了240，生产多废了200，一路阶梯式成本上涨，最终严丝合缝地停在了1440块的实际成本上！

老汪洞察：

在经营会议上，生产部还在嚷嚷是采购买的料差才导致多领料。财务BP不慌不忙调出这张瀑布图，指着屏幕说：“王经理，采购确实买贵了240块。同时我们查了入库质检单，这批钢材的良率和历史均值一致，您车间这个月多领了20kg料，完全是因为新开动的那台车床调试失误导致的报废。这200块的量差柱子，真应该该扣减车间的绩效！”

此刻，全场鸦雀无声。老板在台上，向你投来了极其赞许的目光。

这个时候还没有结束，上面情况是采购和生产都没有守住，都超支了，那么现在我们再来假设采购抓住市场时机，进了几批远低于标准采购价的钢材，月底一算账，钢材的移动加权平均出库价格低于10块的标准，把实际总成本改成900，钢材的移动加权平均出库价格为7.5，然后再去瀑布图这边刷新一下数据模型，标准总成本到实际总成本的影响元素瞬间就体现出来了：采购价差节约了使标准总成本减少300块，生产量差使得标准成本增加了200块，最终实际总成本900块。

五、老汪总结：财务BP的终极护城河

很多财务同仁觉得，公司花几十万上了ERP就能解决一切成本管理难题。但真相是：通用型ERP只能帮你把“实际发生了多少钱”真实准确完整的记录下来就很不错了，它是一个没有感情的记账机器。

只有当你掌握了BI数据建模技术，把“业务物理量（kg）”和“财务价值量（元）”在数据底座中进行碰撞、交火、全自动剥离，你才真正具备了“透视经营、支持决策”的能力。所以：

• 算清总成本，那叫核算会计；
• 把总成本拆解为量差和价差，那叫管理会计；
• 能用自动化模型一键拆解，在几秒钟内找出究竟是谁偷走了利润，并推动业务去降本增效，这才是老板最愿意砸重金聘请的数字化财务架构师！

你们公司现在是怎么做三算对比的？月底开会采购和生产还会扯皮吗？欢迎在评论区留言。如果你想亲自体验这套沙盘，点击文末“阅读原文”获取原始全套文件和函数代码及解释说明，老汪把今天搭建好的源文件发给你，直接套用！

另外，像这样的财务BP计算模型搭建思维和业务管理逻辑在老汪的Excel BI财务数据建模陪跑实战课中比比皆是，如果需要课程，请识别下方二维码加老汪微信详询。

资源下载区