广告

用excel做一次移动平均法(移动平均法简单例题excel)插图

「蔚云科技」通过数字商业洞察、数字商业技术、数字商业运营三大核心引擎,为客户提供商业增长解决方案

数据预估,是数据分析最重要的一个课题。

对于行业的销售、店铺和品牌的销售预估,是每个运营和数据分析师都会遇到的问题。

你是否只会通过行业的历史增长率来预估数据?

今天我们通过使用淘宝生意参谋行业数据作为案例,教大家两个预估短期数据很好用的方法,让你不再只是使用同比、复合增长预估数据。

这两种方法都是预估时间序列数据的模型,时间序列简单理解就是时间相关的一列数据。比如近3个月每天的流量,近3年每个月的销售。

一个是Holt-Winters加法模型会简单介绍该模型的原理和公式。

Excel里面有这个模型的公式,可以直接套用,非常方便,但我们仍要知其所以然。

另一个,用比较简单的方式来处理周期特性,经过测试,也能比较准确预估到市场数据。

跟上面模型其实底层逻辑是一致的,指数平滑的逻辑。

这个在于我们如果遇到一些上述模型的无法很好处理的数据,可以更灵活地使用的组合拳。

预测的第一步得先观察数据,不一样的数据特性有可能需要采取不同的预估模型。

用excel做一次移动平均法(移动平均法简单例题excel)插图1

很明显,该数据具有增长趋势,还有一定的周期特性,所以非常适合采用三次指数平滑中的Holt-Winters加法模型来对这个数据进行预测。

Holt-Winters 加法模型

该模型有一个预测方程和三个平滑方程。

用excel做一次移动平均法(移动平均法简单例题excel)插图2

用excel做一次移动平均法(移动平均法简单例题excel)插图3

是水平平滑方程,可以理解为基础的数据分量

用excel做一次移动平均法(移动平均法简单例题excel)插图4

是增长平滑方程,可以理解为增长的数据分量

用excel做一次移动平均法(移动平均法简单例题excel)插图5

是季节性平滑方程,可以理解为季节/周期的数据分量

使用我们的例子来理解,就是月度销售=基础销售+增长的销售+该月的周期销售

周期性,举个例子,就是双11的销售是要好于一般月份的销售的,所以需要周期分量的加成。

完整模型方程组如下:

用excel做一次移动平均法(移动平均法简单例题excel)插图6

我们今天只介绍原理,不做方程的构建和推理。

知道原理后,我们来看看如果通过Excel实现这个模型的预估。

Excel实现

选择数据,格式要一列是时间序列,一列是数值。

用excel做一次移动平均法(移动平均法简单例题excel)插图7

在数据选项卡下点击预测工作表:

用excel做一次移动平均法(移动平均法简单例题excel)插图8

设置一些参数,“季节性”就是周期特性,我们是月度数据,所以周期是12个月。

同理如果是季度数据,这里就应该设置4。

用excel做一次移动平均法(移动平均法简单例题excel)插图9

点击创建后,会生成两个表格和一个图表。一个表格是历史数据和预估的数据,一个表格是参数信息和预估误差信息。

用excel做一次移动平均法(移动平均法简单例题excel)插图10

预估数据显示如下,至此数据预估完成。熟悉的同学也可以直接用FORECAST.ETS这个公式进行预估。

用excel做一次移动平均法(移动平均法简单例题excel)插图11

这个方法的一个缺点就是模型参数都是系统自动计算的,我们没办法根据实际来进行调整。

下面看看第二种方法。

累计年度销售增长预估法

第二种方法没有很复杂的公式,采用累计年数据+累计年数据增长率+线性回归解决预估的问题。

都是一些比较好理解的内容。

首先是计算累计年数据

为什么计算累计年数据?主要是我们数据是以年为一个周期,这一步相当是做了一次指数平滑,消除了这一年的周期特性。

再计算累计年数据的增长率

这一步,主要预估累计年数据,因为从上面的数据得知,累计年数据是有增长性的。

又相当于是做了一次指数平滑。还记不记得,上面的模型是三次指数平滑模型的一种。

线性回归预估增长率

这一步可以用其它方法预估,比如加权移动平均等

Excel实现

第一步,是计算前12个月的累计数据,公式如图所示,向下复制,就计算出每个月向前12个月的滚动累计年销售额。

滚动累计年销售额就变成了一条平滑向上的线,这下能理解为什么叫指数平滑了不?

用excel做一次移动平均法(移动平均法简单例题excel)插图12

由上面变成下面

第二步,计算年累计销售额的增长率

用excel做一次移动平均法(移动平均法简单例题excel)插图13

第三步,计算增长率的线性回归参数。K是线性回归分析方程的斜率,b是线性回归方程的截距。

用excel做一次移动平均法(移动平均法简单例题excel)插图14

第四步,根据线性回归参数计算增长率的预估值。这里黄色底色的为预估值。

用excel做一次移动平均法(移动平均法简单例题excel)插图15

第五步,使用预估的增长率,计算预估的累计年销售额,再反算月度交易金额的增长。到此,整个预估完成。

用excel做一次移动平均法(移动平均法简单例题excel)插图16

用excel做一次移动平均法(移动平均法简单例题excel)插图17

最后我们来对比两个预估跟实际的差异

用excel做一次移动平均法(移动平均法简单例题excel)插图18

可以看到第一种方法明显要比第二种方法误差率更低

但是为什么我们还要用第二种方法呢?

其实数据分析最重要的是需要从实际出发

从底层逻辑来讲,两种方法底层逻辑类似。

第一种方法,因为是经过大量的研究和验证,一个完善的模型,对于比较稳定和有规律的市场来说,预估准确率是较高的。

但是仍可以看到4月份和5月份误差较大。

因为这个时候市场是有变动的。

4月份是有疫情的影响,地区发不了货,所以预估值要大于实际值。

5月份是因为今年618的开门红,放在了5月31日的晚上,所以预估值小于实际值。

市场的变化导致了预估的误差较大。从另一个角度出发,这个预估方法可以预估特定市场变动对于市场的影响有多大。

第二种方法预估出来的误差率较高。

但是区别于第一种方法是使用模型直接运算,参数无法进行调整

第二种方法我们可以根据预知市场的变动进行调整。

比如,例子中,1、2月的误差很大。

其实原因在于,淘宝上面1、2月的销售额是跟农历新年相关的,春节周期靠近哪个月份,那个月份的销售就会降低。

如下图是近三年的周期规律。

用excel做一次移动平均法(移动平均法简单例题excel)插图19

可以看到,19年和20年的2月销售额高于1月份,而21年的1月份的销售额要远高于2月份,21年的春节周期是在2月份。

我们的方法中,周期特性是跟21年是一致的

但是今年的春节周期在两个月份中间,每个月各承担一部分,所以两个月份的销售额比较接近。

我们的预估中,两个月分开看,差异会很大,但是合计起来,预估值差异是较小的。

所以如果我们选择以2019年的累计年销售作为增长基数的预估,准确率就会更高一些(相应的增长率需要做出调整)。

但是,第一种方法就无法做如此的调整(这里第一种方法一二月份的预估比较准确,可以理解为模型的周期特性是三个周期的加权平均的)。

所以,第一种方法适合较为稳定的市场。

第二种方法比较灵活,适合变化的市场,可以根据实际的情况调整我们的预估模型。

所有的预估模型都是基于过去,选择合适的模型再加上对市场的理解分量,才是万能的市场预估方式。

赶快用起来吧,不要再用简单的同比增长预估数据了。

~~~~~~~~~~~~~~~ 作者分割线 ~~~~~~~~~~~~~~

用excel做一次移动平均法(移动平均法简单例题excel)插图20

Wayne Chen,数据分析专家

同时我们会陆续在后续的文章分享,关注我们及时阅读干货内容

~~~~~~~~~~~~~公司介绍分割线 ~~~~~~~~~~~

「蔚云科技」专注成为一家数字商业运营服务商,我们通过数字商业洞察、数字商业技术、数字商业运营三大核心引擎,为客户提供具有远见卓识的商业增长解决方案。

聚焦用“大数据”为企业提供「选品赋能」服务,赋能企业。

广告