1. 这不是Excel练习题，而是一套能直接用在仓库现场的库存决策沙盘

“Inventory Model Simulation with Spreadsheets”——光看标题，很多人第一反应是：“哦，又一个教你怎么在Excel里敲公式的学生作业。”但我在快消品供应链干了12年，带过3个区域仓、管过年流水47亿的SKU池，实话说：真正压垮一线计划员的，从来不是理论模型，而是模型和现实之间那层薄得看不见、却怎么也捅不破的毛玻璃。这个标题背后根本不是“用Excel模拟库存”，而是用一张表，把采购、销售、仓储、财务四拨人扯皮时各自拍脑袋的假设，全拉到同一张纸上，用数字对齐认知。我试过用Python写过整套仿真引擎，也用过标价十几万的商业软件，最后发现，当区域经理拿着iPad蹲在货架前改补货单时，他打开的永远是那个叫“库存推演_v3.2.xlsx”的文件——因为只有它能5秒内告诉你：“如果明天促销多卖200件，后天会不会断货？断货损失算进毛利还剩几个点？”

核心关键词“Inventory Model Simulation”和“Spreadsheets”必须咬死两个事实：第一，“Simulation”不是跑一次就完事的静态计算，而是可交互、可扰动、可归因的动态推演——改一个安全库存系数，下游所有指标（缺货率、周转天数、资金占用）实时联动；第二，“Spreadsheets”不是退而求其次的妥协，恰恰是唯一能把业务逻辑、财务逻辑、操作逻辑揉进同一张二维网格里的载体。你没法在Power BI里直接拖拽修改“供应商交期波动标准差”，也没法在SAP报表里双击某个SKU弹出它的EOQ重算窗口。但Excel可以：F9刷新，Ctrl+Z回滚，Alt+=自动求和，这些肌肉记忆就是业务人员最真实的决策界面。

这篇文章适合三类人：一是刚转行做供应链计划的新手，别急着啃《Production and Operations Management》教材，先把这个表拆开揉碎，搞懂为什么第17行的“Lead Time Demand Variance”要除以4而不是除以n-1；二是干了五六年、天天被销售追着问“能不能加急下单”的计划老鸟，你会在这里找到把“老板说感觉要爆仓”翻译成“安全库存需上浮18%”的量化路径；三是负责搭建数字化底座的IT或数据团队，别再盲目上马RPA或低代码平台了——先看看这张表里哪些逻辑真需要自动化，哪些只是业务方没想清楚的伪需求。全文所有公式、参数、结构，全部来自我亲手落地的7个行业案例（从医疗器械耗材到宠物零食），没有一行是教科书抄来的理想化假设。现在，我们直接掀开这张表的底层逻辑。

2. 为什么非得用Excel做仿真？不是技术落后，而是业务在呼吸

2.1 模型选型背后的血泪教训：从“理论上最优”到“现场能落地”

很多人一上来就想建个“完美模型”：用蒙特卡洛模拟需求波动，用马尔可夫链预测缺货状态转移，甚至引入强化学习动态调参。我2018年在一家医疗器械公司就干过这事——用Python搭了一套基于LSTM的需求预测+随机优化的补货引擎，准确率比老系统高11%，上线当天，仓库主管指着屏幕问我：“王工，这个‘预期缺货概率0.037’，我该让拣货员多备几箱？”没人能回答。问题不在技术，而在模型输出和操作指令之间断层了。0.037是个概率值，但拣货员只认“今天多备5箱”或“不用备”。后来我们把整个模型压缩进Excel，把0.037映射成“安全库存建议值=历史均值×1.37”，再加个红色条件格式标出“>15箱需人工复核”，第二天仓库就接受了。

所以本项目选择Excel仿真，核心逻辑有三层：

• 第一层是认知对齐：采购关心“最小起订量”，销售盯着“现货率”，财务算着“库存持有成本”，三拨人用同一张表输入自己的约束（比如采购填“MOQ=200件”，销售填“目标现货率≥95%”，财务填“年持有成本率=28%”），系统自动算出交集解。这种协作不是靠开会，是靠单元格引用。
• 第二层是迭代成本：在真实业务中，模型参数三个月就得调一次。换季、新品上市、大促节奏变化，都要求快速验证假设。用专业仿真软件改一个参数可能要重启服务、导出日志、等半小时跑完，而Excel里改个数字按F9，0.3秒出结果。我服务过一家宠物食品电商，他们每月1号雷打不动做“下月库存压力测试”：销售给3套销量情景（乐观/中性/悲观），采购给2套交期情景（正常/港口拥堵），财务给2套成本情景（人民币汇率波动±3%），6种组合，20分钟内全部推演完毕——这速度，任何企业级系统都做不到。
• 第三层是责任穿透：当系统报“预计缺货”时，你能立刻点开对应单元格，追溯到是哪个假设导致的——是销售把新品首月销量预估高了200%，还是采购把越南工厂交期填成了15天（实际平均22天）？这种可审计性，在黑盒模型里是奢侈品。Excel里每个公式都是透明的，谁填的数据谁负责。

提示：这不是反对高级工具，而是强调“仿真”的本质是服务决策，不是炫技。就像厨师不会因为有了分子料理设备就放弃铁锅——火候的掌控感，永远在人手与工具的接口处。

2.2 四大核心模型如何被“降维”进一张表：从数学公式到单元格公式

真正的难点，从来不是把公式搬进Excel，而是把抽象模型翻译成业务人员能理解、能干预、能信任的操作界面。我们拆解四个最常用的库存模型，看它们如何被“降维”：

1. 经济订货批量（EOQ）模型
教科书公式：$ Q^* = \sqrt{\frac{2DS}{H}} $

• D=年需求量，S=每次订货成本，H=单位年持有成本
  但在现场，D不是固定值，是滚动12周的加权预测；S不是会计科目里的“采购手续费”，而是包含“采购员跑一趟供应商的交通+时间成本+合同评审人力”；H更复杂，要拆成资金成本（年化利率）、仓储折旧、保险、损耗、管理费五项。
  → Excel实现：在“参数设置”页，我们设5个输入框：
• D_rolling：用=SUM(OFFSET(销售数据!B2,0,0,12,1))动态抓取最新12周销量
• S_procurement：采购员填“每次下单平均耗时2.5小时”，系统自动乘以人力成本单价
• H_capital：财务填“公司加权资金成本率8.5%”，再乘以采购单价
• H_warehouse：仓储经理填“每平米月租金+折旧=32元”，除以库容利用率得出单件成本
• H_risk：用历史数据算出“近半年破损率0.7%”，乘以单价
  最终EOQ公式变成：
  =SQRT((2*D_rolling*(S_procurement+H_capital+H_warehouse+H_risk))/H_total)
  关键点：所有输入项都标注来源部门和更新频率，避免“这个S是谁填的？上周填的还是上月填的？”

2. 安全库存（Safety Stock）模型
经典公式：$ SS = Z \times \sqrt{L \times \sigma_D^2 + D^2 \times \sigma_L^2} $

• Z=服务水平系数，L=交期，σD=需求标准差，σL=交期标准差
  但现实中，销售给的“需求波动”是拍脑袋的“感觉很稳”，采购说的“交期”是“一般20天左右”。
  → Excel实现：我们强制业务填“可信度权重”。比如销售填“未来4周销量预测”，旁边加一列“信心分（1-5分）”，系统自动用加权标准差：
  =SQRT(SUMPRODUCT((预测销量-实际销量)^2,信心分)/SUM(信心分))
  同时，交期数据不是填一个数，而是要求采购上传近3个月的到货记录（日期、订单号、实际到货日），系统自动计算：
• 平均交期=AVERAGE(到货记录!C:C)
• 交期标准差=STDEV.S(到货记录!C:C)
• 交期离散度=STDEV.S(到货记录!C:C)/AVERAGE(到货记录!C:C)（>0.3标红预警）
  这样算出的安全库存，业务员才敢信。

3. （s,S）连续盘点策略
理论：库存降到s时，补货至S。但s和S怎么定？很多公司直接用“最低库存=3天销量，最高库存=15天销量”，完全忽略波动。
→ Excel实现：我们把它拆成两步决策：

• 触发点s= 预期交期需求 + 安全库存（用上面算出的SS）
• 补货点S= s + EOQ（但EOQ要动态重算，因为补货量会影响后续持有成本）
  关键是加入动态缓冲：当系统检测到“近3次补货后7天内又触发补货”，自动提示“交期不稳定，建议s上浮20%”；当“库存连续10天高于S”，提示“需求萎缩，建议重算EOQ”。

4. 缺货损失仿真
这才是业务最痛的点。教科书只说“缺货成本=机会损失+信誉损失”，但怎么量化？
→ Excel实现：我们设计三级损失模型：

• 一级（硬损失）：销售漏单金额 × 毛利率（直接从ERP拉数据）
• 二级（软损失）：缺货期间，同类竞品销量增长量 × 我方份额损失系数（由市场部填0.3-0.8）
• 三级（隐性损失）：缺货SKU的关联SKU（如买猫粮常搭购猫砂）销量下滑比例（用历史相关性分析得出）
  最终缺货总损失 = SUM(一级+二级+三级)，并生成“损失热力图”——哪个时段、哪个品类、哪种原因（需求突增/交期延误/预测偏差）造成的损失最大。

这四大模型，没有一个是孤立存在的。EOQ影响安全库存，安全库存决定(s,S)阈值，(s,S)执行效果又反向修正EOQ参数。Excel的魔力在于，用INDIRECT()、OFFSET()、DATA VALIDATION这些函数，把它们织成一张网——改一个源头参数，全网震动。这才是“仿真”的灵魂。

3. 实操全过程：从零搭建一张能打仗的库存推演表

3.1 表结构设计：五个工作表构成的作战指挥室

一张能实战的库存仿真表，绝不是单张Sheet堆满公式。我采用五页协同架构，每页承担明确角色，像作战室里的不同岗位：

注意：所有跨表引用必须用'参数设置'!B5这种显式命名，禁用Sheet1!B5。曾有个客户把“参数设置”页重命名为“参数”，全表公式崩盘，加班三小时修复。

3.2 关键公式详解：不是复制粘贴，而是理解每一行在干什么

EOQ动态重算模块（位于“3-模型计算”页）

这不是一个静态值，而是随滚动预测实时变化的活体。核心公式如下：

// B2: 当前SKU编码（下拉选择） // C2: 滚动12周销量（从"2-数据源"页抓取） =SUMPRODUCT(('2-数据源'!A:A=B2)*('2-数据源'!B:B>=TODAY()-84)*('2-数据源'!B:B<=TODAY())*'2-数据源'!C:C) // D2: 动态EOQ（考虑MOQ约束） =MAX( ROUNDUP( SQRT( (2*C2*'1-参数设置'!D5)/ // D5=单位持有成本 ('1-参数设置'!D3+'1-参数设置'!D4+'1-参数设置'!D6) // S+H_capital+H_warehouse ),0), VLOOKUP(B2,'1-参数设置'!G:H,2,FALSE) // G:H列是SKU-MOQ对照表 )

为什么这么写？

• SUMPRODUCT比SUMIFS更稳定，避免数组公式兼容问题；
• ROUNDUP(...,0)确保订货量是整数，避免小数箱；
• MAX(..., MOQ)强制不低于最小起订量，这是采购红线；
• 所有参数引用都带工作表名，杜绝误操作。

安全库存动态缓冲模块（同页E列）

// E2: 安全库存 =IF( COUNTIFS('2-数据源'!A:A,B2,'2-数据源'!B:B,">="&TODAY()-90)>10, // 近90天有10+条销售记录才启用动态计算 '1-参数设置'!D12 * // D12=Z值（根据目标现货率查表） SQRT( '1-参数设置'!D8 * // D8=平均交期 STDEV.S(IF('2-数据源'!A:A=B2,'2-数据源'!C:C)) ^2 + AVERAGE(IF('2-数据源'!A:A=B2,'2-数据源'!C:C)) ^2 * STDEV.S(IF('1-参数设置'!A:A=B2,'1-参数设置'!C:C)) ^2 ), AVERAGE(IF('2-数据源'!A:A=B2,'2-数据源'!C:C)) * 0.5 // 否则用经验法则：半周销量 )

实操心得：

• 用COUNTIFS做数据质量门禁，避免历史数据太少时算出荒谬值；
• STDEV.S和AVERAGE嵌套IF是数组公式，必须按Ctrl+Shift+Enter（Excel 365可省略）；
• 最后兜底用“半周销量”，这是给新SKU的生存空间——没数据时，宁可保守。

(s,S)策略自动适配模块（同页F/G列）

// F2: 触发点s ='3-模型计算'!C2 * '1-参数设置'!D8 + '3-模型计算'!E2 // EOQ*交期 + 安全库存 // G2: 补货点S =F2 + '3-模型计算'!D2 // s + EOQ // H2: 智能预警（当库存连续3天低于s，且s<S时触发） =IF( AND( '2-数据源'!D2<F2, // 当前库存<F2 COUNTIFS('2-数据源'!A:A,B2,'2-数据源'!D:D,"<"&F2)>=3, // 连续3天低于 F2<G2 ), "⚠️ 交期风险：建议s上浮20%", "" )

踩过的坑：

• 初期用COUNTIF统计“低于s的天数”，结果把历史所有数据都算进去了。改成COUNTIFS加日期范围限定，才精准捕捉“连续”；
• COUNTIFS第二个条件用"<"&F2而非"<F2"，避免文本比较错误；
• 预警文字加⚠️符号（非emoji，是Unicode字符），确保所有Excel版本兼容。

3.3 数据驱动的推演流程：不是点一下就完事，而是闭环验证

真正的仿真价值，在于把“推演-执行-反馈”串成闭环。我们设计了四步走流程：

第一步：基线推演（每周一上午）

• 计划员打开表，点击“0-首页”的“一键推演”按钮（实际是VBA宏）；
• 宏自动执行：
  1. 从ERP拉取最新库存快照，覆盖“2-数据源”页D列；
  2. 用最新销售数据重算滚动预测（C2公式刷新）；
  3. 重算所有SKU的EOQ、SS、s、S；
  4. 生成“未来30天库存水位图”，标出所有预计低于s的日期；
• 输出物：一份PDF报告，含“TOP10高风险SKU清单”，发给采购、销售、仓储负责人。

第二步：根因分析（每周一下午）

• 针对清单里的SKU，计划员点开“4-推演报告”页，用筛选器定位；
• 查看“缺货归因柱状图”：是需求预测偏差大（销售填的信心分低）？还是交期波动大（采购的交期离散度>0.3）？或是MOQ不合理（EOQ被MOQ强行拉高）？
• 在“1-参数设置”页，针对性修改对应参数（如给该SKU的交期信心分从3调到2）。

第三步：情景压力测试（每周二）

• 销售提供“618大促销量倍增系数”，填入“0-首页”的情景切换器；
• 系统自动加载新参数，重新推演；
• 对比基线报告，看哪些SKU的缺货风险从“低”跳到“高”，提前锁定资源。

第四步：执行反馈闭环（每日晨会）

• 仓储扫描实际出库量，填入“2-数据源”页；
• 系统每晚自动比对：
  • 实际销量 vs 预测销量 → 更新需求信心分；
  • 实际到货日 vs 预计交期 → 更新交期离散度；
• 次日“0-首页”的KPI仪表盘，所有数字已反映最新执行情况。

这个闭环，让Excel不再是“计划员的自嗨工具”，而成了整个供应链的神经末梢。去年服务的一家母婴电商，用这套流程把缺货率从12.7%压到5.3%，关键不是模型多先进，而是每天都有人盯着那个红色预警单元格，然后真的去改参数、调策略、追供应商。

4. 常见问题与排查技巧实录：那些文档里不会写的坑

4.1 公式失效类问题：不是Excel坏了，是你的数据在“撒谎”

问题1：滚动预测（C2公式）突然返回0，但销售数据明明有

• 排查路径：
  1. 检查“2-数据源”页的日期列（B列）是否为Excel日期格式（数值应为44000+，不是文本“2023/05/01”）；
  2. 用ISNUMBER('2-数据源'!B2)验证，若返回FALSE，用DATEVALUE()转换；
  3. 确认TODAY()-84是否超出数据范围（比如数据只到昨天，但公式算到前85天）。
• 独家技巧：在“2-数据源”页加辅助列=IF(ISNUMBER(B2),B2,"无效日期")，用条件格式标红所有“无效日期”，比肉眼找快10倍。

问题2：安全库存（E2）算出来是#NUM!错误

• 根因：SQRT函数里出现了负数，通常因为交期数据全是0或空值，导致STDEV.S返回错误；
• 解决方案：

  // 改写E2公式，加错误捕获 =IFERROR( ...原公式..., AVERAGE(IF('2-数据源'!A:A=B2,'2-数据源'!C:C)) * 0.3 )

• 预防措施：在“1-参数设置”页的交期数据源区块，加数据验证：允许“小数”，最小值0.1，避免填0。

问题3：VBA宏运行一半卡死，任务管理器里Excel进程占满CPU

• 真相：不是宏写错了，是“3-模型计算”页有太多INDIRECT()函数（比如INDIRECT("A"&ROW())），每次刷新都全表重算；
• 急救方案：按Ctrl+Alt+F9强制全表重算，再按Ctrl+Break中断；
• 根治方案：
  1. 把所有INDIRECT()替换成INDEX()（如INDEX(A:A,ROW())）；
  2. 在VBA宏开头加Application.Calculation = xlCalculationManual，结尾加Application.Calculation = xlCalculationAutomatic；
  3. 关键：在“0-首页”加个开关按钮，控制是否启用实时计算（默认关，点“推演”时才开）。

4.2 业务逻辑类问题：当Excel算得对，但业务方说“不对”

问题1：采购抗议“EOQ算太小，不够一车！”

• 表面矛盾：模型算出EOQ=180件，但物流要求整车发运（MOQ=500件）；
• 深层冲突：模型只算了库存成本，没算运输成本；
• 解决路径：
  1. 在“1-参数设置”页新增“运输成本参数”区块：单车运费、单车装载量；
  2. 修改EOQ公式，把S（订货成本）拆成两部分：
     • S_fixed= 采购员处理单据成本（固定）
     • S_transport= 运输成本（随订货量阶梯变化）
  3. 用VLOOKUP查运输阶梯价，例如：

     订货量	单车运费
     <500	800
     500-999	1200
     ≥1000	1500

• 结果：EOQ自动上浮到满足整车运力的临界点。

问题2：销售质疑“安全库存太高，压着钱！”

• 典型场景：系统建议SS=300件，但销售说“这品最近卖不动，放100件就够了”；
• 破解方法：引入“需求衰减系数”。在“1-参数设置”页加一列“近30天销量环比”，公式：
  =AVERAGEIFS('2-数据源'!C:C,'2-数据源'!A:A,B2,'2-数据源'!B:B,">="&TODAY()-30)/AVERAGEIFS('2-数据源'!C:C,'2-数据源'!A:A,B2,'2-数据源'!B:B,">="&TODAY()-60)-1
• 若系数<-0.3（销量腰斩），则安全库存自动×0.7；若>0.5（翻倍增长），则×1.3。让数据说话，而不是拍脑袋。

问题3：财务指出“缺货损失算少了，没算客户流失！”

• 盲区：原模型只算当期损失，没算长期影响；
• 升级方案：在“4-推演报告”页加“客户流失模拟”模块：
  • 基于CRM数据，统计“缺货SKU的购买客户中，30天内未复购的比例”；
  • 设定流失客户终身价值（LTV）= 年均消费×3年；
  • 缺货损失 = 当期损失 + 流失客户数×LTV；
• 关键细节：LTV不是拍的，而是用历史数据回归得出“缺货1次→30天复购率下降X%”，这才是财务认可的算法。

4.3 性能与协作类问题：当表变大，人变多

问题1：表体积超50MB，打开要2分钟，多人编辑冲突

• 根源：所有历史数据堆在“2-数据源”页，且用了太多FILTER()动态数组；
• 手术式优化：
  1. 数据分层：
     • “2-数据源”页只留最近90天销售、30天采购、当日库存；
     • 历史数据移入独立文件“库存历史库.xlsx”，用POWER QUERY建立连接；
  2. 公式瘦身：
     • 把FILTER('2-数据源'!C:C,'2-数据源'!A:A=B2)换成SUMIFS('2-数据源'!C:C,'2-数据源'!A:A,B2)（求和比筛选快10倍）；
     • 删除所有TEXT()函数（如TEXT(TODAY(),"yyyy-mm-dd")），用单元格格式代替；
  3. 协作机制：
     • 禁用共享工作簿（已淘汰），改用OneDrive/SharePoint的“同时编辑”；
     • 关键参数页（“1-参数设置”）设为“只读”，修改需走审批流（用Microsoft Forms收集申请，自动邮件通知审批人）。

问题2：新同事不会用，总问“这个Z值哪来的？”

• 终极方案：把Excel变成教学沙盘
  • 在“1-参数设置”页Z值旁加“？”图标，链接到隐藏的“原理说明”页；
  • 该页用生活化类比解释：

    “Z值就像考试划线——想让95%的人及格（现货率95%），分数线（安全库存）就得定在平均分+1.65个标准差的位置。查标准正态分布表，95%对应Z=1.65。”

  • 加互动练习：输入不同现货率（85%/90%/95%/99%），自动显示Z值和对应的“缺货概率”（1-现货率）；
• 效果：新人20分钟内理解Z值本质，不再机械填数。

5. 从单点工具到组织能力：这张表如何改变一个公司的库存文化

这张表最终的价值，远不止于算出几个数字。在我经手的案例里，它成了撬动组织变革的支点。最典型的是一家华东的工业轴承分销商，年营收8亿，过去库存周转天数常年卡在120天，老板骂“仓库堆成山，缺货还不断”。我们没先动模型，而是做了三件事：

第一件事：把“库存”从财务科目变成销售语言
以前销售说“这个型号要加急”，采购回“没库存”，财务喊“压着钱”。我们把“4-推演报告”页的缺货预警清单，直接嵌入销售CRM的客户详情页。当销售点开一个老客户，页面右侧自动显示：“您常订的SKUXXX，当前库存仅剩12件，按当前销量3天后断货，补货需18天，建议今日下单”。销售第一次意识到，他的“加急”请求，背后是18天的等待。结果，销售主动开始填“需求信心分”，因为分数低会导致系统提高安全库存，占用更多资金——他们开始为自己的预测负责。

第二件事：让采购的“交期”从承诺变成数据
采购总说“交期20天”，但从不定义“20天”指什么。我们在“1-参数设置”页强制要求：

• 交期类型：订单确认日→到货日？还是下单日→到货日？
• 交期数据源：必须上传近3个月到货记录（含订单号、PO号、实际到货日）；
• 系统自动计算：平均交期、标准差、准时率（≤承诺交期的订单占比）。
  当采购看到自己填的“20天”对应的实际标准差是8天（离散度40%），而行业标杆是15%时，他主动推动供应商上线ASN系统，把交期离散度压到12%。数据不撒谎，但需要被看见。

第三件事：把财务的“成本”从年报数字变成操作按钮
财务总抱怨“库存持有成本率28%太高”，但计划员不知道28%怎么来的。我们在“1-参数设置”页的成本参数区块，把28%拆解成：

• 资金成本：12%（公司贷款利率）
• 仓储成本：9%（租金+人工+折旧）
• 风险成本：7%（损耗+过期+跌价）
• 每一项都标注计算依据（如“仓储成本=仓库总面积×单价÷总库存金额”）。
  当计划员调整安全库存时，右侧实时显示“资金占用增加XX万元，年持有成本增加XX万元”。成本，第一次从报表里走到了操作界面上。

半年后，这家公司的库存周转天数从120天降到82天，缺货率从18%降到6%，更关键的是，跨部门会议从“互相指责”变成了“一起看表找根因”。销售不再说“采购不给力”，而是说“看表，交期离散度超标，咱们一起约供应商聊聊”；采购不再说“销售乱预测”，而是说“看表，这个SKU的信心分只有2，咱们下周联合做一次需求探询”。

所以，当你打开“Inventory Model Simulation with Spreadsheets”，你拿到的不是一张表，而是一个组织级的决策操作系统。它不替代专业系统，但为所有系统提供了校准基准；它不取代人的判断，但把判断建立在可追溯、可验证、可归因的数据之上。我见过太多花几百万上的WMS、TMS系统，最后被Excel表格救场——因为再复杂的系统，也得有人来定义“什么是合理库存”。而这张表，就是把定义权，交还给每天和货物打交道的一线人。

最后分享一个小技巧：每次重大参数调整（比如把目标现货率从90%提到95%），不要直接改，而是复制整个工作簿，命名为“库存推演_95%现货率_20231015.xlsx”，存档。半年后回头对比，你会发现，真正驱动库存改善的，往往不是模型多精妙，而是你坚持记录每一次决策的勇气——因为所有进步，都藏在那些被你认真保存的版本号里。