毛利变动差异归因分析模型(附可套用ExcelBI计算模型及可视化看板)

月初经营分析会，最容易卡住的不是有没有毛利表，而是表上已经有一个环比差额，老板还是会问：到底是卖少了、产品组合变了、价格变了，还是成本变了？

如果财务只回答“毛利少了九十四万”，后面的讨论往往会变成各部门各自辩护。采购说材料已经压过价，销售说部分型号提了价，生产说产量并不差，财务却拿不出一张能对上总额的因素拆解。

连环替代要做的，不是再写一篇感想，而是把上期毛利一步一步替换成本期毛利。每一步只换一个因素，前后两个毛利状态的差额，才能明确记到销量、结构、售价或成本上。新品和隔月再售没有上期基准，停售产品又会从本期大盘里消失，这两类必须先剥离开，不能硬塞进四因素。

下面按方法报告来写：先给口径，再给计算链，再用一组教学模拟把账算平。企业、产品和金额均为教学模拟，不对应任何真实客户。毛利指销售收入减销售成本，不等于净利润。

本模型把上期毛利逐步替换为本期毛利。每次只替换一个因素，因此每一步前后毛利的差额可以明确归因。

核心口径可以写成一句话：先把产品分成连续在售、上期未售、本期未售三类。连续在售按“销量 → 结构 → 售价 → 成本”替代；上期未售取本期毛利，本期未售取负的上期毛利。五项合计必须等于毛利总差额。

教学模拟的净结果如下。连续在售四因素合计减少104万元；新品带来40万元，停售带走30万元；全部产品毛利环比减少94万元。

先看这张教学模拟看板。上期毛利630万元，本期536万元，净减少94万元。六项因素加总与总额对平：销量−60万元，结构−50万元，售价＋124万元，成本−118万元，新品＋40万元，停售−30万元。

教学模拟看板把九十四万差额拆到六项因素

当本期毛利与上期不同，管理层通常会问四件事：卖得更多还是更少，产品组合变了没有，售价变了没有，成本变了没有。四因素连环替代，就是把这四类原因从总差额里依次分离。

适用条件也要先说清：

为避免公式读岔，全文符号固定。下标0代表上期，下标1代表本期，下标i代表具体产品。不带产品下标的“总”，表示当前筛选范围内连续在售产品的合计。

读法举例：V1i表示产品i的本期实际销量。V*1i不是实际销量，而是“本期总盘已经变成现在这么大，但仍保持上期产品结构”时，该产品理论上应卖多少。

五个毛利状态、四次替代，顺序固定为销量、结构、售价、成本：

闭环恒等式：

加上进入和退出后：

读每一项时，先看换了什么、没换什么，再用后一个毛利状态减去前一个毛利状态。

销量影响

换了什么：只换总销量。各产品仍按上期份额，把上期销量等比例放大或缩小到本期总盘子。

没换什么：产品占比、售价、单位成本，全部还用上期。

谁减谁：GP1减去GP0。也就是“总盘子已经变成现在这么大、但货还按上期比例卖”的毛利，减去上期实际毛利。

怎么读：正数说明总盘变大增加了毛利，负数说明总盘变小拖累了毛利。单品这一列不是该产品自己卖多卖少的全部影响，份额变化放到下一步结构里。

公式：

其中 w0i ＝ V0i ÷ V0总，V*1i ＝ V1总 × w0i。

结构影响

换了什么：只换产品组合。总盘子已经是本期的，这一步把“按上期比例该卖多少”换成“本期真正卖了多少”。

没换什么：售价、单位成本仍用上期。

谁减谁：GP2减去GP1。

怎么读：整体为正，说明多卖了上期单位毛利更高的货；整体为负，说明组合往低毛利产品倾斜。单品为正，通常是该产品份额升了；若该产品上期本身亏钱，则要反过来看。不需要事先给产品贴“高毛利”或“低毛利”标签，上期单位毛利会自动完成加权。

公式：

售价影响

换了什么：只换平均售价，从上期价换成同期价。

没换什么：各产品已经按本期实际销量卖；单位成本仍用上期。

谁减谁：GP3减去GP2。

怎么读：涨价为正，降价为负。它回答的是已经卖掉的这些数量，单价变了多少，不回答“该不该涨价”。

公式：

成本影响

换了什么：只换单位成本，从上期成本换成本期成本。

没换什么：销量、产品组合、售价都已经是本期。

谁减谁：GP4减去GP3。算完这一步，GP4就是本期连续在售的实际毛利。

怎么读：成本下降为正，成本上升为负。它不能直接等于采购降本，也不能直接等于车间降本，只说明本期销售结转的单位成本变了。

公式：

新品和“上月没卖、本月又卖”在算法上是同一类问题：上期销量为零，没有上期单价、上期成本和上期单位毛利。量、结构、价、本这四步都要用上期基准去替代，这类产品套不进去。

如果硬算，常见后果是：上期单位毛利空白，结构或售价被撑出一整块说不清的金额；或者明细对不上合计。所以必须先剥离开，再对剩下的连续在售产品做四因素。

上期未售包括两种业务情形，公式相同，汇报时可以在明细里加一列“进入原因”区分：新品，以前从未销售，本月第一次出货；隔月再售，不是新品，只是上月销量为零，本月又卖了。

这两项不参与连环替代，也不拆量价结构。不虚构上期单价，不用品类平均毛利去补一个假基准。上期未售影响不是亏损，而是上期没卖、本期卖了的整块本期毛利；正数表示这些产品本月赚到了钱。本期未售影响才是本月停卖少掉的上期毛利。

产品维度和期间对照分别以一对多关系筛选主数据集。透视表中的产品行标签应从产品维度拉取；销量影响和结构影响里的VALUES、ALLSELECTED，也必须使用同一个产品维字段。

统一原则：透视表行标签、度量值迭代字段、清除筛选的字段，都使用产品维度里的产品名称。不要在透视表改拉事实表产品名，否则维度筛选与度量内部产品集合可能不一致。

若透视表出现“空白”行并且带金额，不是无数据分隔行，而是事实表存在空产品键，或事实产品名未匹配产品维度。应先定位并修复映射，不能简单删除有金额的事实行。

期间编号必须连续，并且跨年不断号。用当前期间编号减1取上期，是为了避免源数据把某月写成2号、3号后，按自然月函数取上期失败。多选月份时，最大值只代表最大期间，月环比切片器建议强制单选。

Power Query负责行级别清洗：统一产品名、期间、数量和金额，把两期明细叠成一张事实表。Power Pivot负责受切片器影响的动态汇总：写度量值，做连环替代。不要在单元格里用查找和条件求和硬堆四因素，产品一多、期间一切换，维护成本和出错概率都会上去。

下面度量值名称可按企业习惯调整，逻辑必须保持一致。字段名以当前模型为准。

本期基础

用连续期间编号取上期

上期总收入、上期总成本用同一结构，把被计算的度量值换成对应的本期收入、本期成本即可。

销量影响与结构影响

两条必须同时成立。第一，大盘当期总销量和上期总销量，用ALLSELECTED锁在SUMX外面，并且只合计连续在售。第二，SUMX的循环表必须仍是VALUES('产品维度'[产品名称])，不能改成ALLSELECTED后的产品清单，否则每一行明细都会加总成合计，结构影响会出现“明细全是合计数，或者明细空白、合计却有数”。

售价、成本与闭环

上期未售与本期未售

销量、结构、售价、成本四项，只对连续在售产品循环；大盘总销量也只取连续在售合计。进入和退出由上面两个度量值单独承接。

以下全部为教学模拟。假设某制造事业部只看五个产品，销量单位为件，金额单位为元，均不含可抵扣增值税；两期销售成本已经按企业现行发出计价完成结转；不考虑退货、折让、盘盈盘亏和跌价。

输入数据

连续在售上期总销量V0总 ＝ 10000 ＋ 20000 ＋ 10000 ＝ 40000件。

连续在售本期总销量V1总 ＝ 8000 ＋ 22000 ＋ 6000 ＝ 36000件。

上期结构：甲25%，乙50%，丙25%。

按上期结构分配的本期理论销量：甲9000件，乙18000件，丙9000件。

毛利总差额 ＝ 5360000 − 6300000 ＝ −940000元，即减少94万元。

连续在售：五个毛利状态

GP0 ＝ 甲400万 ＋ 乙100万 ＋ 丙100万 ＝ 600万元。

GP1只换总盘：9000×400 ＋ 18000×50 ＋ 9000×100 ＝ 540万元。

销量影响 ＝ 540 − 600 ＝−60万元。

也可以直接用总盘变化乘上期平均单位毛利：（36000 − 40000）×（6000000 ÷ 40000）＝ −4000 × 150 ＝ −60万元。

GP2再换结构：8000×400 ＋ 22000×50 ＋ 6000×100 ＝ 490万元。

结构影响 ＝ 490 − 540 ＝−50万元。

单品核对：

三项合计−50万元。甲和丙份额下降，乙份额上升；乙上期单位毛利只有50元，甲有400元，组合整体变差。

GP3再换售价：8000×（1100−600）＋ 22000×（520−450）＋ 6000×（800−700）＝ 614万元。

售价影响 ＝ 614 − 490 ＝＋124万元。

单品核对：甲（1100−1000）×8000 ＝ ＋80万元；乙（520−500）×22000 ＝ ＋44万元；丙售价没变，为0。

GP4再换成本，也就是本期连续在售实际毛利：8000×450 ＋ 22000×40 ＋ 6000×80 ＝ 496万元。

成本影响 ＝ 496 − 614 ＝−118万元。

单品核对：甲（600−650）×8000 ＝ −40万元；乙（450−480）×22000 ＝ −66万元；丙（700−720）×6000 ＝ −12万元。

连续在售四因素：−60 − 50 ＋ 124 − 118 ＝−104万元。

496 − 600 ＝ −104万元。两边一致。

进入、退出和五项闭环

上期未售影响：丁本期毛利40万元。

本期未售影响：戊上期毛利的相反数，−30万元。

五项合计：−104 ＋ 40 − 30 ＝−94万元。

如果有人把丁的2000件加进V1总，连续在售总盘会变成38000件，甲乙丙的理论销量会被新品挤占，结构影响就会被污染。本例禁止这种做法。

单产品甲：份额下降，不等于“甲自己卖少了”这么简单

甲本期毛利360万元，上期400万元，差额−40万元。

甲是连续在售产品，上期未售和本期未售均为0。本月毛利环比减少40万元。售价改善80万元，但销量和结构各拖累40万元，成本再拖累40万元。因为甲上期单位毛利为正，负结构可以解释为份额下降。

会上怎么讲

因素金额到这里就结束了。乙成本上升，可能是材料、工费、发出计价、产品结构，也可能是返工。没有单据和现场记录，不能在会上直接判采购或车间的责任。

整体层汇报模板：

套用本例：

产品层汇报模板：

道理讲清楚以后，财务还会遇到一个实际问题：两期产品明细、期间对照、度量值和瀑布图，散在几张表里。这个月手工对齐了，下个月又要重新复制、匹配、核对。

这正是Excel BI建模值得发挥作用的地方。用Power Query整理各期明细，用Power Pivot建立关系并写连环替代度量值，再用透视表或看板呈现结果。在数据源和规则稳定的前提下，月度分析可以从反复拼表，逐步变成刷新数据、核对闭环差异、解释主导因素。

我做的《Excel BI财务数据建模全程陪跑课》，就是围绕这类真实工作任务，采用微信一对一私教陪跑的方式，帮助你把建模方法用进自己的财务分析。课程从数据清洗、关系建模和指标计算，到报表呈现与结果核验逐步推进；具体从哪张表开始，会结合你的业务问题和现有数据来判断。

本文所述的毛利变动差异归因分析模型的搭建及可视化全程实操课程，已加入陪跑课的专题课程，如果你也想把每月反复整理的毛利环比底稿，做成能够持续使用的分析模型，请点击文末阅读原文详询获取，一定帮你把问题讲透、能自己实操。

我是老汪，专注于制造业财务人的专业成长，也致力于推动中小制造企业的业财融合、财务数字化与转型升级。

本文数据为教学模拟，不对应真实企业。因素金额只说明数值来源，不替代现场核查、会计政策和审计判断。

点击下方“阅读全文”了解更多资讯