【工具开发】用Excel复现ASML备件预测模型

为啥要折腾这个？

上一篇《【经典导读】ASML基于Installed base的备件销售预测》讲了ASML怎么用“Installed base”信息预测备件需求。按照本人的一贯做法，学了新方法，要验证后才真正算自己的。而验证方案最便捷的就是用Excel，抛开大数据量运算性能的问题，Excel叠加算法&VBA编程能力，堪称方案验证的神器。

下面我把自己验证过的步骤再捋一遍，大家照着做就行。

ASML方案的核心思路

预测需求 = 所有在用的备件 × 它们近期坏掉的概率

公式中“坏掉的概率”用威布尔分布算比较适配备件预测模型，需要不断调整两个参数β和η取值，以实现威布尔分布最逼近真实的概率分布。

从论文原文中我们知道，威布尔分布方法有两种模型：“基于寿命的方法”和“基于需求方法”；考虑到真实场景下的数据可得性，我们选择基于需求的方法进行验证。

“基于需求的方法”的意思就是：用历史耗用数据反推这两个参数，使基于这两个参数计算出的预测最接近真实的耗用。

具体验证步骤

1. Excel里创建4个数据表

Params表放参数（β、η、预测月数）;
InstalledBase放各月各年龄段的装机数量；
Actual放历史实际耗用（需求）；
Evaluation用于计算误差

1）Params表：

维护威布尔分布相关的几个关键参数信息：

可以给B2、B3、B4起个名字：选中B2 → 左上角名称框输入 i_val → 回车；同样，B3输入 beta_val，B4输入 eta_val。下图为选中B2后，设置i_val的示例。

这样做的好处是，eta_val可以在其他表里全局引用，公式里可以直接写eta_val，不用写 =$B$4，清爽多了。

2）InstalledBase表：

按如下方式维护Installed base数据表，Age_0至Age10，表示在装机使用状态下最大寿命为10个月的备件数量；p_0至P_10，表示利用威布尔分布公式计算得到的近期坏掉的概率；

p_0=1-EXP((0/eta_val)^beta_val-((0+i_val)/eta_val)^beta_val )

p_1=1-EXP((1/eta_val)^beta_val-((1+i_val)/eta_val)^beta_val )

....依次类推，直至计算P_10

Pridicted=SUMPRODUCT(B2:L2, M2:W2)

预测数据：

选择相同的公式，只是最大寿命数据可能会增加一个月，出现P_11；

3）Actual历史实际耗用表

By月度统计备件的历史耗用（需求）。

4）Evaluation表

SME=SUM(D2:D8)/SUM(B2:B8)

MAE_model=AVERAGE(E2:E8)

MAE_naive=AVERAGE(ABS(Actual!B3:B25 - Actual!B2:B24))

MASE=MAE_model / MAE_naive

2. 利用Solver自动找最优参数（β&η）

在Params表单创建目标函数min(ABS(SME) + MASE)

然后再Solver中设置如下参数设置，点击“求解”，计算得出最优参数β&η

优化前后对比（示例）

参数组	β	η	SME	MASE
瞎填的	1.2	20	0.71	21.30
优化后	0.5	7.14	≈0	0.85

从21倍误差降到0.85倍，效果很明显。

3. 用最优参数预测未来

假设现在是23-07月底，想预测23-08月。

统计23-08月的备件Installed Base分布：

用最优参数（β=0.5, η=7.14）算条件概率p_0至p_11：

计算23-08月的需求预测=∑(数量 × 概率) = 19.76 ≈ 20件

总结：

这套备件预测方法落地门槛不高，企业只需做好两项基础数据记录：各品类备件月度寿命分布、月度历史消耗数据；然后选择相对简单的模型算法就能达到不错的效果。当前国产化设备装机规模快速攀升的场景下，这套方法适配性和实用价值都很高。

唯一需要注意的是，出保设备的真实寿命数据较难直接从 Fab 获取，后续还需与客户协同配合、共建数据机制才能落地。

👉 关注公众号，后台发消息：“ASML”，即可自动领取下载原论文。