夜雨聆风
毛利率影响模型(Excel动态单品测算表)
毛利率影响模型（单品测算）Excel动态测算表
一、模型设计原则（专业依据）
|
原则 |
说明 |
|
权责发生制 |
收入和成本在同一期间匹配 |
|
完全成本法 |
单位成本包含直接材料、直接人工、变动制造费用、固定制造费用分摊 |
|
价税分离 |
增值税不影响毛利率（不含税口径） |
|
可验证性 |
归因公式符合财务分析中的连环替代法 |
|
敏感性分析 |
支持价格/成本单独或联动变化 |
二、Excel工作表完整结构
工作表名称：毛利率影响模型_单品测算
|
区域 |
列 |
内容 |
|
输入区 |
A1:C5 |
基准数据 + 变动后数据 |
|
计算区 |
A7:E10 |
单位毛利、毛利率 |
|
归因区 |
A12:F15 |
价格影响、成本影响、交互影响、合计验证 |
|
敏感性区 |
A17:G22 |
价格/成本变动对毛利率的敏感度矩阵 |
三、完整公式表（含中文说明）
3.1 输入区（用户填写）
|
单元格 |
内容 |
示例值 |
|
B2 |
基准单价（不含税） |
506.00 |
|
C2 |
基准单位成本（完全成本） |
296.40 |
|
B3 |
新单价（不含税） |
600.00 |
|
C3 |
新单位成本（完全成本） |
300.00 |
3.2 计算区
|
单元格 |
英文函数公式 |
中文公式说明 |
|
D2 |
=B2-C2 |
= 基准单价 – 基准单位成本 |
|
E2 |
=IFERROR |
= 基准单位毛利 ÷ 基准单价，错误时返回0 |
|
D3 |
=B3-C3 |
= 新单价 – 新单位成本 |
|
E3 |
=IFERROR |
= 新单位毛利 ÷ 新单价，错误时返回0 |
|
D4 |
=D3-D2 |
= 新单位毛利 – 基准单位毛利 |
|
E4 |
=E3-E2 |
= 新毛利率 – 基准毛利率（百分点） |
|
B4 |
=B3-B2 |
= 新单价 – 基准单价 |
|
C4 |
=C3-C2 |
= 新单位成本 – 基准单位成本 |
3.3 归因分析区（连环替代法 / LMDI）
专业说明：采用连环替代法，按“先价格、后成本”顺序拆解毛利率变动。
|
单元格 |
英文函数公式 |
中文公式说明 |
|
B13 |
=(B3-B2)*(1-C2/B2) |
价格影响= (单价变动额) × (1 − 基准成本率) |
|
C13 |
=-(C3-C2)*(1/B2) |
成本影响= −(成本变动额) × (1 ÷ 基准单价) |
|
D13 |
=D3-D2 – B13 – C13 |
交互影响= 总毛利变动 − 价格影响 − 成本影响 |
|
E13 |
=B13+C13+D13 |
合计验证= 价格影响 + 成本影响 + 交互影响 |
|
F13 |
=IF(E13=D4-D2,”验证通过“,”验证失败“) |
一致性校验：若合计等于总毛利变动则通过 |
税务师提示：上述公式中的单价和成本均为不含增值税金额。若含税，需先除以(1+税率)还原。
3.4 毛利率变动百分比拆解（专业版）
|
单元格 |
英文函数公式 |
中文公式说明 |
|
B15 |
=B13/D2 |
价格影响对毛利变动的贡献率 |
|
C15 |
=C13/D2 |
成本影响对毛利变动的贡献率 |
|
D15 |
=D13/D2 |
交互影响对毛利变动的贡献率 |
|
E15 |
=SUM(B15:D15) |
合计贡献率（应为100%） |
3.5 敏感性分析矩阵（双因素）
用途：分析价格和成本同时变动时，新毛利率的变化。
|
行\列 |
G17=成本变动率 |
H17 |
I17 |
J17 |
|
行标题 |
-5% |
0% |
+5% |
+10% |
|
F18=价格变动率 |
||||
|
F19=-5% |
公式见下方 |
|||
|
F20=0% |
… |
|||
|
F21=+5% |
… |
|||
|
F22=+10% |
… |
G19单元格公式（可拖动填充）：
=IFERROR( (B2*(1+$F19) – C2*(1+G$17)) / (B2*(1+$F19)), 0 )
中文说明：
= (基准单价 × (1 + 价格变动率) − 基准成本 × (1 + 成本变动率)) ÷ (基准单价 × (1 + 价格变动率))
四、专业校验规则（CPA/CTA必须检查）
|
校验项 |
公式 |
通过标准 |
|
毛利率≤100% |
=E2<=1 |
TRUE |
|
单位毛利≥0 |
=D2>=0 |
TRUE（正常经营） |
|
归因合计验证 |
=ABS(E13-(D3-D2))<0.01 |
TRUE |
|
不含税价验证 |
=AND(B2>0,C2>0) |
TRUE |
|
贡献率和为100% |
=ABS<0.01 |
TRUE |
五、完整中文公式汇总（可直接复制到Excel注释）
【基准单位毛利】= 基准单价 – 基准单位成本
【基准毛利率】= 基准单位毛利 ÷ 基准单价
【新单位毛利】= 新单价 – 新单位成本
【新毛利率】= 新单位毛利 ÷ 新单价
【毛利率变动（百分点）】= 新毛利率 – 基准毛利率
【价格对毛利的影响】= (新单价 – 基准单价) × (1 – 基准单位成本 ÷ 基准单价)
【成本对毛利的影响】= -(新单位成本 – 基准单位成本) × (1 ÷ 基准单价)
【交互影响】= 总毛利变动 – 价格影响 – 成本影响
【价格贡献率】= 价格影响 ÷ 基准单位毛利
【成本贡献率】= 成本影响 ÷ 基准单位毛利
【交互贡献率】= 交互影响 ÷ 基准单位毛利
【含税还原】（若输入为含税价）：
不含税单价 = 含税单价 ÷ (1 + 增值税率)
不含税成本 = 含税成本 ÷ (1 + 可抵扣税率)
六、税务师特别提示（必读）
1.增值税影响
o毛利率分析必须使用不含税口径
o若进项税不能全额抵扣（如简易计税），需单独处理
2.所得税考量
o毛利≠净利，决策时需考虑期间费用和所得税
o本模型用于经营分析，非税务申报表
3.转移定价
o关联交易中的毛利率需符合独立交易原则
o异常波动可能触发税务机关调整
4.存货计价
o单位成本受存货计价方法影响（FIFO / 加权平均）
o模型假设为标准成本或实际成本
七、可直接复制的Excel模板代码（非VBA，纯公式）
您可以直接按以下坐标填入Excel：
A1:【输入区】
A2:基准单价 B2:506
A3:基准成本 B3:296.4
A4:新单价 B4:600
A5:新成本 B5:300
A7:【计算区】
A8:项目 B8:基准 C8:新 D8:变动额
A9:单位毛利 B9:=B2-B3 C9:=B4-B5 D9:=C9-B9
A10:毛利率 B10:=IFERROR C10:=IFERROR D10:=C10-B10
A12:【归因分析–连环替代法】
A13:价格影响 B13:=(B4-B2)(1-B3/B2)
A14:成本影响 B14:=-(B5-B3)(1/B2)
A15:交互影响 B15:=C9-B9-B13-B14
A16:合计验证 B16:=SUM(B13:B15)
A17:校验结果 B17:=IF(ABS(B16-(C9-B9))<0.01,”通过“,”失败“)
A19:【贡献率分析】
A20:价格贡献率 B20:=B13/B9
A21:成本贡献率 B21:=B14/B9
A22:交互贡献率 B22:=B15/B9
A23:合计贡献率 B23:=SUM(B20:B22)
适用场景（专业全覆盖）
该模型适用于以下6 大类业务场景：
1. 定价与调价决策
新品定价对毛利的影响预测
促销降价后的盈亏平衡点测算
2. 成本控制与分析
原材料涨价对单品利润的敏感度
生产工艺优化带来的毛利提升量化
3. 产品组合与SKU管理
高/低毛利产品替换决策
产品生命周期中的毛利监控
4. 财务预算与偏差分析
实际 vs 预算的毛利率差异归因
量、价、成本三维变动拆解
5. 经营复盘与绩效评价
产品经理/采购的毛利达成分析
销售折扣对整体利润的影响
6. 管理报表与汇报模板
月度经营分析会中的单品毛利卡片
向管理层展示“毛利率变动是谁造成的”
精品榜
完
朕已阅≠寡人做
合作微信：190086825
【免责声明】本文信息源自公开及合法授权，仅供参考，不构成任何决策依据。