在Excel中构建安全库存与再订货点模型,基于波动与服务水平计算参数

🎯 开篇引入.

大家好，我是甜姐姐.
今天咱们来聊个很实用的东西.
在Excel里搞定“安全库存”和“再订货点（ROP）”模型，基于需求波动和服务水平来计算参数.
别怕，看完你就能在老板面前淡定展示，顺便让库存别再乱跑.

第一部分：规划数据仪表盘 📊.

场景：你手上有历史日/周/月需求数据，供应提前期不稳定，老板要 低缺货率又不想压太多库存.

规划思路指导.

1. 明确目标：控制缺货风险，优化库存成本.
2. 输入数据：历史需求序列（如日/周/月），平均提前期，提前期波动，目标服务水平（如95%）.
3. 输出指标：安全库存、再订货点、平均库存估算.

仪表盘基本结构.

• 左侧：输入区（需求数据，提前期参数，服务水平）.
• 中间：计算区（需求均值、标准差、提前期影响）.
• 右侧：结果展示（安全库存、ROP、图表）.

实用建议.
别一上来就瞎折腾图表，先把数据弄规整.
把原始数据做成表格，Ctrl + T 建表，方便后续引用.

第二部分：图表制作 📈.

动态柱状图 — 应用场景.

• 展示每期需求变化，让大家直观看出波动.

操作步骤.

1. 选中需求数据，插入→图表→簇状柱形图.
2. 设置图表为表格动态引用，图表范围用表格列名.
3. 快捷键：Alt + N， V 打开插入图表菜单.

最终效果.
随数据增加，柱状图自动扩展.
老板一看就知道需求哪几期峰值异常.

动态环形图 — 应用场景.

• 展示库存组成，或安全库存占比.

操作步骤.

1. 准备两列：安全库存、正常周转库存.
2. 插入→图表→环形图，右键格式化数据标签显示百分比.

最终效果.
环形图清晰显示安全库存在总库存中的占比，便于评审是否合理.

第三部分：交互功能 🔧.

切片器概念引入.

• 切片器能让你快速按产品/区域/时间筛选，动态刷新所有计算和图表.

具体操作步骤.

1. 把数据做成数据透视表，插入→数据透视表.
2. 选择数据透视表，插入→切片器，勾选需要的字段.
3. 将图表基于数据透视表建立，切片器联动图表.

实用技巧.
小技巧提醒.
切片器可以设置多选或单选，长按 Ctrl 多选.
想看不同SKU的安全库存对比，直接选SKU切片器即可.

第四部分：整体整合 📝.

布局安排.

• 顶部放关键输入（服务水平、提前期平均和标准差）.
• 左中放需求图表，右中放结果数值卡片（大字体）.
• 底部放说明和计算公式，方便审阅.

美化建议.
别让仪表盘太花哨.
配色用 2-3 种主色，重点数据用对比色突出.
字体尽量统一，数字用千分位格式，方便阅读.

实际效果.
完整仪表盘能让老板一眼看到缺货风险和库存占比.
还能通过切片器钻取到某个产品或某个区域的具体情况.

计算核心（示例公式）📌.

场景：基于正态分布近似，安全库存 = Z * σLT.
步骤（在Excel里）.

1. 计算需求均值：=AVERAGE(需求范围).
2. 计算需求标准差：=STDEV.S(需求范围).
3. 根据提前期（LT）处理：若LT以期为单位且需求独立，σLT = σ需求 * SQRT(LT平均).
4. Z值由服务水平决定，例如95%→Z≈1.645，99%→Z≈2.33. Excel可用函数 =NORMSINV(服务水平).
5. 安全库存 = NORMSINV(服务水平) * σ需求 * SQRT(平均提前期).
6. 再订货点 ROP = 平均需求 * 平均提前期 + 安全库存.

代码/公式示例（简洁）.

• 需求均值：=AVERAGE(B2：B100) ； // B列为需求
• 需求标准差：=STDEV.S(B2：B100) ；
• Z值：=NORMSINV(0.95) ； // 服务水平95%
• 安全库存：=NORMSINV(C1) C2 SQRT(C3) ； // C1服务水平，C2σ需求，C3平均提前期
• ROP：=C4*C3 + C5 ； // C4平均需求，C5安全库存.

小技巧提醒.
别忘了把时间单位统一，需求是日数据，提前期也用天.
如果提前期也有波动，需要合并方差：σtotal = SQRT(σ需求^2 * LT平均 + (平均需求^2) * σLT^2).
这步容易犯错，多做几组样例验证结果是否合理.

总结梳理 🧾.

要点回顾.

• 先规划，再计算，最后美化和交互.
• 安全库存关键在于 需求波动 和 服务水平.
• ROP = 平均需求×平均提前期 + 安全库存.

练习任务（做一下就能上手）.

1. 用你公司最近 6 个月的周需求数据，计算平均需求和标准差.
2. 设服务水平 95%，提前期 2 周，计算安全库存和ROP.
3. 用数据透视表和切片器，做一个按产品切换的仪表盘.

结尾激励 🌟.

别怕，按着甜姐姐的步骤一步一步来就行.
多练几次，你会发现库存不再像鬼画符那样飘忽.
加油，老板的赞赏就在前方等着你！