利用EXCEL建立季节预测模型

杨 桦

摘要:Microsoft Excel是Microsoft Office应用软件包中的一员,是一个功能强大、技术先进、使用方便的表格式数据综合管理和分析软件。它采用电子表格方式进行数据处理,通过丰富的内置函数和分析工具可以进行各种数据处理、统计分析、辅助决策,并具有强大的制图功能,可以方便地绘出各种专业图表,实现了图、文、表三者的完美结合。近几年来,人们广泛利用其工具来进行财务预测,本文主要阐述了如何利用Excel来建立季节预测模型的方法,相信通过这些方法,有助于我们科学的分析市场,准确的预测市场,从而更加灵敏的反应市场,更加有效的满足市场,有利于提升市场营销主体的基本能力。

关键词:EXCEL;季节预测;模型

季节指数是一种以相对数表示的季节变动变化大小的衡量指标。季节指数广泛用于受季节影响而出现销售的淡季和旺季之分的具有季节性变动规律的商品的预测过程中,掌握了季节变动规律,就可以利用它来对季节性的商品进行市场需求量的预测。实际预测中通常需要掌握和运用三年以上的分季历史数据。季节指数的周期可以是季度,也可以是月度(月度季节指数)。

根据某企业过去4年的A产品销量的基础数据,首先判断是否可用季节指数法进行销量预测,如可用的话,则用季节指数法预测第五年的产品销量,并用Excel实现整个判断和预测过程。

在用季节指数模型进行产品的销量预测时要遵循的原则是:许多时间序列不仅含有趋势成分,而且含有季节成分,对于这一类型的时间序列,首先需要剔除季节影响,然后再看这个消除季节影响的时间序列是否存在长期趋势,这种对时间序列进行分解的方法,一般可建立在乘法模型的基础上。由于随机成分的不可预测,因此预测值就可表示为趋势成分和季节成分的乘积。

用EXCEL 建立季节预测模型,一般的操作步骤是:(1)计算每一季的季节指数;(2)用时间序列的每一个观测值除以季节指数,消除季节影响;(3)对消除了季节影响的时间序列建立适当的趋势模型,并用这个模型进行预测;(4)将预测值乘以季节指数,计算出最终的受季节影响的预测值。

具体操作步骤如下:

(1)新建一工作簿,激活Sheet1工作表,分别在A、B、C列输入年、季度和销售量。

(2)计算四季度移动平均数与移正平均数

选择“工具”菜单→单击“数据分析”命令→在弹出的“数据分析”对话框中选择“移动平均”命令→在“移动平均”对话框的“输入区域”选择“C2:C17”,“间隔”为“4”,“输出区域”选择“D4:D16”→单击“确定”按钮,即可计算出四季度移动平均数。

因为四个季度是偶数,还需再计算移正平均数,单击E4单元格,在编辑栏中输入公式“=(D4+D5)/2”复制公式至E15单元格即可。

(3)计算季节不规则值

单击F4单元格,在编辑栏输入公式“=C4/E4”,复制公式至F15单元格即可。

(4)计算季节指数

单击I2单元格,在编辑栏输入公式“=AVERAGE(F2,F6,F10,F14)”,复制公式至I5单元格即可,这样分别计算出了1至4季度的季节指数。计算结果如图1所示。

(5)计算消除季节影响的销售量

激活Sheet2工作表,复制Sheet1中A、B、C列的数值,并将A列的“年”,改为相应的“序号”。

单击D2:D5单元格区域,在编辑栏输入公式“Sheet1!$I$2:$I$5”,按“Ctrl+Shift+Enter”键确认,复制公式至D18:D21单元格区域即可。

注:“Ctrl+Shift+Enter”三键同按,是数组公式输入的方法。

单击E2单元格,在编辑栏输入公式“=C2/D2”,复制公式至E17单元格,这样就计算出了消除季节影响的销售量。

(6)计算趋势预测值

单击F2单元格,在编辑栏输入公式“=FORECAST(A2,$E$2:$E$17,$A$2:$A$17)”,复制公式至E21单元格即可。

其中FORECAST是根据已有的数值计算或预测未来值函数。A2 为需要进行预测的数据点、$E$2:$E$17 为因变量数据区域、$A$2:$A$17 为自变量数据区域。

(7)建立消除季节影响的折线图,并建立一元线性趋势预测公式。

利用图表向导,选择数据点折线图,并添加趋势线,如图2所示。可直接利用公式进行预测。

(8)计算季度预测值

单击G2单元格,在编辑栏输入公式“=F2*D2”,复制公式至G21单元格即可。这样就计算出了消除季节影响的预测值。计算结果如图3所示。

(9)制作XY散点图

利用图表向导,建立实际值与预测值的XY散点图,可以清晰地反映出各季指标值的变化情况。如图4所示。

参考文献:

[1]Excel中的有关预测函数及其应用http://www.goodsgy.com/article/3/23/html/25501.html.