4. 配方成本核算表设计与搭建

配方表回答的是“每吨成品里放什么、放多少”，成本表回答的是“这些物料和工序一共花多少钱、每公斤成品摊多少”。两者之间不是简单复制，而是一套数量映射 + 单价映射 + 损耗映射的转换关系。搭建核算表的核心任务，就是把配方中的百分比、包装规格、工艺损耗和采购单价，通过可追溯的公式串成一条从“配方行”到“总成本”的计算链，并保证任何一个参数变动都能自动传导到最终结果。

一、三张底表与一张汇总表的结构

建议把工作簿拆成四张表，职责分离，避免公式互相缠绕：

• 原料单价表：字段为原料编码、原料名称、规格、采购单价（元/kg）、供应商、生效日期。单价只在这里维护，其他表一律用编码引用，杜绝手工改价。
• 配方表：字段为配方版本、原料编码、投料百分比（%）、功能分类（表面活性剂/助剂/香精/水等）。同一配方版本下各行百分比之和应为100%，可加一行校验公式。
• 包装与工艺参数表：瓶型、单瓶容量、瓶重、泵头/盖成本、纸箱规格与单价、每箱瓶数、灌装损耗率、配料损耗率、水电气与人工的吨成本。
• 成本汇总表：按配方版本汇总原料成本、包装成本、加工成本，输出吨成本与单瓶成本。

这种拆分的意义在于：配方工程师只改配方表，采购只改单价表，核算人员只看汇总表，三方互不覆盖对方的数据。

二、从百分比到吨成本的映射公式

设某原料投料百分比为 p%，采购单价为 u 元/kg，考虑配料损耗率 L（如2%），则每吨成品中该原料的实际用量与成本为：

实际投料量(kg/t) = 1000 × p% ÷ (1 − L)
该原料吨成本(元/t) = 实际投料量 × u
原料成本合计 = Σ 各原料吨成本

注意损耗放在分母：损耗2%意味着投入102.04 kg才能得到100 kg成品，而不是简单乘1.02，两者在小数位上会有差异，规模化生产时不可忽略。若企业习惯按“投料量×单价”粗算，也应在表中注明口径，保证全文一致。

包装成本按“每吨成品对应的包装件数”折算：

每吨瓶数 = 1000 kg ÷ 单瓶净含量(kg)
单瓶包装成本 = 瓶重(kg) × 瓶材单价 + 泵头/盖单价 + 标签单价
每吨包装成本 = 每吨瓶数 × 单瓶包装成本 + 每吨纸箱数 × 纸箱单价

加工成本通常由固定项和变动项组成：灌装线人工、设备折旧、水电气按吨分摊为固定吨成本；灌装损耗、次品率则随产量变动。示例中可假设某洗衣液配方加工成本为 380 元/t，其中固定部分 300 元/t、按产量分摊部分 80 元/t，实际使用时按当月产量替换。

三、映射逻辑的可视化

下图展示从配方行、单价表、包装参数到总成本的传导路径，以及损耗率在链条中的插入位置。箭头方向即数据流向，任一上游参数变化都会沿箭头影响下游结果。

四、Excel 中的关键公式写法

以“原料吨成本”为例，假设配方表在 配方 工作表，A列为原料编码、C列为投料百分比；单价表在 单价 工作表，A列为编码、D列为单价；损耗率放在 参数 表的 B2 单元格。则配方表 D2 可写：

=1000*C2/(1-参数!$B$2)*VLOOKUP(A2,单价!$A:$D,4,FALSE)

用 FALSE 精确匹配，避免编码相近时取错价；用绝对引用锁定损耗率单元格，向下填充时不会漂移。汇总表用 SUMIF 按功能分类汇总，例如表面活性剂合计：

=SUMIF(配方!$B:$B,"表活",配方!$D:$D)

这样能直接看出表活、助剂、香精各占多少成本，为后续配方优化提供抓手。包装成本建议单独建“包装BOM”小表，按瓶型一行，用 每吨瓶数 = 1000/净含量 计算，再与配方版本关联。

五、示例核算与结果判读

假设某浓缩洗衣液配方（示例假设数据）：表活A 18%、单价 9.5 元/kg；表活B 6%、单价 12 元/kg；助剂 5%、单价 3.2 元/kg；香精 0.8%、单价 45 元/kg；水及余量补足。配料损耗 2%。则表活A吨成本 = 1000×18%÷0.98×9.5 ≈ 1744.9 元，表活B ≈ 734.7 元，助剂 ≈ 163.3 元，香精 ≈ 367.3 元，原料合计约 3010 元/t。若包装为 2 kg 瓶、每吨 500 瓶、单瓶包材 1.35 元，包装成本约 675 元/t；加工成本按 380 元/t，则总成本约 4065 元/t，单瓶约 8.13 元。判读时应重点看三项占比：原料约74%、包装约17%、加工约9%，若包装占比异常偏高，优先核查瓶重与箱规是否填错。