CPSM知识点

Excel规划求解在供应链管理中的应用 之 销售预测的指数平滑法

负责公司销售预测的同学们，可能了解，常用的定量预测方法包括时间序列方法、因果关系的回归方法和模拟方法这三大类。而时间序列的方法，又包括移动平均、加权的移动平均、指数平滑、线性趋势、季节指数、二次移动平均、二次指数平滑，以及复杂的ARlMA模型（Box-Jenkins法）和三次指数平滑法（Holt-Winters）等。

今天，我们先谈谈指数平滑法。其基本预测公式为：Ft+1= αDt + (1 - α)Ft，其中，Ft为期间t的预测，Ft+1为期间t+1的预测，Dt为期间t的实际需求，α为平滑系数，取值为0~1之间。或者，也有人习惯把公式写成：Ft+1 = Ft + α(Dt - Ft)。两个公式可以相互推导出来。第一种写法，可解读为新的期间预测等于，上一个期间的实际需求和预测需求的加权平均，α和1-α分别为权重。而第二种写法，可解读为新的预测等于，上次预测加上一个调整量，而调整量等于上次的误差打了个α折。

在CPSM（美国采购经理职业认证）的教材中，对使用Excel“数据分析”函数包的“指数平滑”方法做过介绍。注意，“数据分析”在Excel中是免费提供的，但不是默认设置。要使用它，请点击“文件”，然后“选项”，选择“加载项”，“分析工具库”，然后点击“转到”并安装。然后，按照上面的指示，使用“数据分析”来工作。

案情是这样的

如下图表格中的数据，B列是某产品的历史销售需求数据，并假设时间荏苒已过去了24个月。我们现处于第24月末，要根据历史数据，使用指数平滑预测法，做出未来的销售需求预测

解答

当然，如果你有瘾，可以按照咱们前面介绍的指数平滑预测公式，一行一行地练手。而如果你习惯使用Excel“数据分析”（见上图右上角菜单的那个按钮），可点击“数据分析”，并在弹出的对话框中输入所需数据，如下图：

其中

1）输入区域：指我们的历史数据，此处选择B3:B26；

2）阻尼系数：Excel的定义是“1-平滑系数”，即如果我们假设平滑系数α为0.2，则在这里要输入0.8；

3）输出区域：指的是你希望Excel按照指数平滑法预测的数据放在表格的位置，此例我们选择H3:H26。

然后，你就可以很爽地按“确定”按钮，预测结果就如上图H3:H26显示出来。注意，第一个预测单元通常会显示“#N/A”。这是因为按照指数平滑方法预测，第一个数据要根据之前的历史和预测数据，而那时，啥都没有，就如同宇宙混沌之初。通常，我们会默认第一个预测值=当期的原始数据。

肯定有同学会问，为毛选α=0.2呢？是的，很明显，对于指数平滑法，平滑系数α的选择极其重要，不同的系数，预测结果当然就不同了。怎么选？怎么选？

一般的老师，没准儿会告诉你，根据自己的感觉吧？或，拍脑门，或去问老大？可老大怎么选择呢？可别说，昨晚有人托梦啊！

如果你读过我们之前的几篇有关规划求解的文章，应该已经习惯了使用Solver的解题套路？说白了，还是要明确三件事儿：目标、变量和约束条件。我们可以把平滑系数设为变量单元，如上图中的F3，让Excel的Solver帮我们选择吧。然后，我们可把区域C3:C26构建预测模型，比如在单元C4中输入：=C3+$F$3*(B3-C3)，并把该公式复制到该列后面的所有单元。注意，我们必须保证单元C3默认值为B3（见前述）。目标单元，可以选择计算B列原始需求数据与C列预测数据之间的方差（MSE），并在之后Solver设计时追求最小化。

稍微有点儿遗憾的是，“万能”的Excel居然没有方差的公式！But，它有个SUMXMY2这个函数，正好代表方差公式的分子部分。这个好记呢，XMY2是说X减（M=minus）Y的2次方，然后再求和（SUM）。这样，我们就可以在目标单元F5输入：

=SUMXMY2(B4:B26,C4:C26)/COUNT(C4:C26)

而约束条件很简单，就是α≥0，且α≤1。

借助Excel的规划求解（Solver）

据此，我们就可以打开Solver（数据菜单 - 规划求解，英文是Data-Solver），分别设置目标、变量和约束条件，如下图。

最后，屏住呼吸，哆哆嗦嗦地按一下“求解”键，看看结果是否出来了呢？指数平滑预测的最优解，即为图1中的C4:C26单元所示数值。

我们可以顺便做个比较，采用规划求解Solver找到的平滑系数为图1中F3单元所示的0.268，按此预测数据与原始需求数据的拟合，即方差为7.82。而我们之前使用“数据分析”的指数平滑随意输入的阻尼系数0.8（即平滑系数为0.2），所预测的结果H列，与原始数据B列的拟合，方差计算如单元H27所示的7.93，明显不如Solver得出的解最优呢。最后，找到了合理的平滑系数，再预测第25月的需求，就不用我讲了吧？

CPSM简介 　　　 CPSM培训及报名　　　 CPSM知识点　　　 CPSM新闻　　　 CPSM学习园地