Excel 案例

38｜Optimization：成本、庫存、交期與服務水準最佳化

一、案例定位

把 Forecasting 再往前推一步：預測需求只是輸入，真正的管理問題是，在採購預算與服務水準限制下，應備多少貨。

二、學習目標

理解 objective function、decision variable 與 constraint。

同時考慮採購、持有與缺貨成本。

比較多個候選備貨量，找出最低成本的可行方案。

辨識『最低成本』與『符合服務水準』可能衝突。

三、案例情境

跨境企業有 6 個 SKU。營運主管要求控制庫存資金，但客服又要求高服務水準，因此不能只照預測需求全額採購，也不能只追求最低採購成本。

Excel 核心表格（Word 完整閱讀版）

下列內容直接整理自本案例對應的 Excel 工作簿。Word 版保留理解案例所需的核心輸入、計算結果與管理輸出；若原始資料筆數很大，正文列示代表性樣本，而模型結果與管理摘要則完整呈現。學生可先只閱讀 Word 完成案例理解，再到 Excel 修改參數與資料進行情境重算。

Excel表 38-1｜Optimization 輸入參數

SKU	預測需求	單位成本	月持有成本	缺貨損失/件	Lead Time天	目標服務水準	最小訂購量	採購預算上限
A-100	326	18.90	0.61	8.05	5	0.97	100	6,289
B-200	492	10.40	0.28	4.14	5	0.90	100	4,935
C-300	343	11	0.24	5.80	20	0.97	80	4,198
D-400	236	16.50	0.57	4.18	10	0.97	80	3,758
E-500	561	11.90	0.40	3.14	13	0.90	80	7,555
F-600	338	22.90	0.50	4.63	6	0.97	80	8,485

Excel表 38-2｜候選備貨量與成本比較

SKU	需求	候選備貨	採購成本	期末剩餘	預估缺貨	持有成本	缺貨成本	總成本	服務達成率	決策
A-100	326	300	5,670	0	26	0	209.30	5,879.30	92.02%	不符合限制
A-100	326	400	7,560	74	0	45.14	0	7,605.14	100.00%	推薦
B-200	492	400	4,160	0	92	0	380.88	4,540.88	81.30%	推薦
B-200	492	500	5,200	8	0	2.24	0	5,202.24	100.00%	不符合限制
B-200	492	600	6,240	108	0	30.24	0	6,270.24	100.00%	不符合限制
C-300	343	320	3,520	0	23	0	133.40	3,653.40	93.29%	不符合限制
C-300	343	400	4,400	57	0	13.68	0	4,413.68	100.00%	推薦
D-400	236	240	3,960	4	0	2.28	0	3,962.28	100.00%	推薦
E-500	561	480	5,712	0	81	0	254.34	5,966.34	85.56%	不符合限制
E-500	561	560	6,664	0	1	0	3.14	6,667.14	99.82%	推薦
E-500	561	640	7,616	79	0	31.60	0	7,647.60	100.00%	不符合限制
F-600	338	320	7,328	0	18	0	83.34	7,411.34	94.67%	推薦
F-600	338	400	9,160	62	0	31	0	9,191	100.00%	不符合限制

Excel表 38-3｜推薦結果

SKU	預測需求	推薦備貨	服務達成率	總成本	採購成本	預算上限	限制狀態
A-100	326	400	100.00%	7,605.14	7,560	6,289	需管理調整
B-200	492	400	81.30%	4,540.88	4,160	4,935	需管理調整
C-300	343	400	100.00%	4,413.68	4,400	4,198	需管理調整
D-400	236	240	100.00%	3,962.28	3,960	3,758	需管理調整
E-500	561	560	99.82%	6,667.14	6,664	7,555	符合
F-600	338	320	94.67%	7,411.34	7,328	8,485	需管理調整

Excel表 38-4｜管理摘要

KPI	結果
SKU數	6
總預測需求	2,296
推薦總備貨	2,320
推薦總成本	34,600.46
平均備貨/需求	1.01
需管理調整SKU	5

四、主要結果

6 個 SKU 總預測需求為 2,296 件，模型推薦總備貨 2,320 件。

推薦組合估計總成本約 34,600.5。

推薦備貨／需求比約 101.0%；若某 SKU 無法同時滿足預算與服務限制，Excel 會標示『需管理調整』。

五、Excel 決策模型

Decision variable：每個 SKU 的備貨量。

Objective：採購成本＋剩餘庫存持有成本＋缺貨損失最小。

Constraints：採購預算上限、目標服務水準、最小訂購量。

Excel 對多個候選量計算總成本，再從可行方案中選最低者。

六、主要結果與管理解讀

Optimization 不是叫 AI 猜一個數字，而是先清楚定義目標與限制。

服務水準提高通常會增加庫存與資金占用；缺貨成本提高則會推升最佳備貨量。

若所有方案都不可行，模型應回報限制衝突，而不是硬給『最佳答案』。

七、What-if

把服務水準從 95% 提高到 98%；把缺貨損失提高 50%；或把採購預算削減 15%，觀察最佳備貨量與不可行 SKU。

八、AI Prompt

你是跨境庫存 Optimization 助理。根據每個 SKU 的需求、單位成本、持有成本、缺貨成本、服務水準、最小訂購量與預算，解釋推薦備貨量。若限制互相衝突，請明確指出，不得虛構可行解。再提出一個降低成本但不明顯傷害服務水準的替代方案。

九、Excel＋AI 分工

Excel：限制、成本函數、候選解與最低成本可行方案。

AI：解釋 trade-off、限制衝突與管理替代方案。

十、課堂討論題

最低成本一定是最佳管理決策嗎？

缺貨成本要如何估？

如果預測本身有誤差，Optimization 應如何保留安全邊際？

十一、資料來源

全部為教材模擬資料。