很多制造企业的财务同仁在做成本分析和报价模拟时，最痛苦的莫过于：ERP导出的或者公司的BOM表单是“断层”的，特别是对于那种BOM层级很深的制造行业。

比如你想看某个成品由于底层某种“特种钢材”涨价带来的成本波动，结果发现中间隔着齿轮、齿轮箱、传动组件等五六层半成品，5、6层还算少的，多的甚至到了几十层，每一层都有不同的用量和损耗。传统的Excel函数处理如VLOOKUP只能处理一级，遇到多级嵌套的“套娃”，除了手工拉表、按计算器，似乎别无他法。而一旦工程研发部门改了bom配方，所有表格模板可能瞬间作废，财务只能连夜加班调整。

今天就带大家跳出传统的“表哥表姐”思维，从数据建模的角度，利用 Excel（PowerBI的也行）自带的 Power Pivot（数据模型）这个神器，给大家拆解演示如何搭建一个全自动的多级BOM成本穿透模拟计算工具，内容非常干。

在动手做这个工具之前，我们要先搞懂业务逻辑。我们以合肥某中小型机械制造代工厂的一个场景进行说明，假设老板问你：“如果特种钢材每公斤涨了2块钱，我们的主打产品‘减速机A’总成本会涨多少？”

要回答这个问题，得先知道减速机A的BOM构成，并且如果你不把这个减速机A的多层级BOM“展平、穿透”，你也根本回答不了这个问题。因为系统里可能只显示减速机的成本，而钢材的成本隐藏在半成品的肚子里。

我们要做的，就是造一台“X光机”，把隐藏在深处的原材料活生生地“拽”到最顶层，直接和最终成品挂钩。一旦底层单价发生变动，顶层成品的成本瞬间联动刷新。这，才叫真正的“报价联动与利润预测”，这才是能辅助老板决策的财务BP的核心价值所在。

为了让大家看透本质，下面模拟了一个制造业最典型的精简版BOM。

假设你的ERP里导出了这样一张原始基础表（共5层数据）：

业务解读：

第一层1台减速机A，需要1个齿轮箱B，1个电机C；

第二层1个齿轮箱B，需要2个大齿轮D，1个传动轴E；

第三层1个大齿轮D，需要5kg特种钢材F。

按照连乘逻辑，做1台减速机A，底层实际消耗了：1 × 1 × 2 × 5 = 10kg 的特种钢材F。这个10kg，就是我们要求出的一台减速机A耗费特种钢材的“穿透定额”，这个数据看起来连乘也很轻松算出，但想像一下如果你公司有 5000 个成品，每个成品有5级BOM，共计上百万行数据呢？

不要觉得这需要买几百万的系统，你电脑里自带的 Excel 就能实现降维打击。跟着小编，分四步走。

①规范原始数据：将上述BOM数据放入Excel。注意：顶级成品（减速机A）的“父级编码”必须为空！选中数据按 Ctrl + T 生成超级表（Excel Table），命名为 t_BOM。

②建立降维辅助表：为了兼容Excel的DAX引擎，我们需要手动建一张层级表。在旁边空白处输入数字 1 到 10（假设你的BOM最深10层），表头命名为 层级。同样按 Ctrl + T，命名为 t_Level。

③加载进Excel数据模型大脑：点击 Excel 菜单栏的【Power Pivot】->【添加到数据模型】，将这两张表都“喂”进后台。

在 Power Pivot 界面中，我们要在 t_BOM 表后新增计算列。我们要让电脑知道，钢材F到底是谁家的孩子。

新建列公式（命名为“血缘路径”）：

小编解读：PATH 是树状结构的核心函数。执行后，特种钢材F 那一行会生成 减速机A|齿轮箱总成B|大齿轮D|特种钢材F 的完整族谱。电脑瞬间摸清了上下级关系。

这是整个模型最核心、最硬核的算力体现。我们需要让电脑顺着刚才的“族谱”，把一路上的用量全部乘起来。

新建列公式（命名为“累计用量”）：

小编解读：不要被这么长的函数吓到。这就好比一台“全自动剥洋葱机”。PRODUCTX 是连乘引擎，它根据层级（1、2、3、4），一层层揪出路径里的名字（减速机、齿轮箱...），然后用 LOOKUPVALUE 去找对应的单耗，最后全部相乘。算完后，钢材F的累计用量自动得出：10kg。

在核算成本时，财务最怕的就是“重复计算”导致出现膨胀错误。大齿轮的成本里已经包含了钢材，如果汇总时把大齿轮和钢材都算进去，成本就会虚高。所以，我们要设一道防火墙：只算最底层原材料的钱。

新建列公式（命名为“层级属性”），找出谁是最底层：

小编解读：这段逻辑极度巧妙。它拿着当前行的名字（即子级编码(child)）去“父级”那一列里扫街，如果能扫到（行数>0），说明有人叫它爸爸，它就是“过程件”；如果扫不到，说明它下面没孩子了，它就是“底层原材料”，比如电机C、传动轴E、特种钢材F，它在父级那一列找不到，所以他只能是底层了。

最后，写出算钱的终极度量值：

小编解读：这条计算函数彻底锁死了“过程件”，它只允许底层材料参与乘以单价的计算。这也是为什么我们在随后的透视表里筛选“过程件”时，结果会是一片空白的原因——它保护了成本的真实性，绝对不会出现重复计算的情况！

关闭 Power Pivot，回到 Excel 画布，插入一个数据透视表。把 血缘路径 拖入行，把 穿透总成本 拖入值。

你不仅会看到“减速机A”的总成本精准算出了 750元，你还拥有了一套无坚不摧的“成本动态模拟器”！

你可以试着去做以下操作，感受什么叫降维打击：

1.采购部通知原材料涨价：直接在bom基础表里把特种钢材从 10元 改成 20元，右击透视表点一下刷新，减速机A的总成本瞬间重算为 850元。

2.工程部更改产品BOM：无论中间砍掉哪层工艺，或者加了几个螺丝钉，只需把最新的BOM贴进基础表，点一下刷新，所有计算逻辑自动重构。

小编洞察：这张小小的透视表，表面上叫“成本汇总”，实际上是一个“单一数据源的动态业务模型”，而这只是简单的给大家演示一下它的威力，实际工作中的业务逻辑和匹配的计算逻辑会更复杂。

这个解决方案让你从一个“只会算历史账，且一改配方就加班到吐”的成本会计，彻底蜕变成了一个“鼠标一点，就能告诉老板未来利润走向的顶级财务BP”，这样的财务BP，老板还能不爱？

技术只是手段，能用技术穿透业务迷雾，重构管理逻辑，这才是财务BP达到顶级“财务架构师”水平的真正护城河。

阅读原文：原文链接