用Excel软件做销售预测的三种方法
Office职场训练营2020-07-23 23:47
营长说
在编写业务经营计划和预算时，重点是进行销售的预测。预测往往要以历史数据为依据，分析销售业务的未来趋势。使用Excel软件可以进行初步预测，这里介绍函数预测、图表预测和预测工作表三种方法。
01
函数预测法
在做预测时最常用的方法就是线性预测法，即将历史数据拟合成一条直线，根据直线的走向来预测未来数值。可以根据历史数据对未来销售额、库存需求或消费趋势进行预测。
案例：已知某公司2013年到2018年的销售量数据，利用线性回归法预测公司2019年的销售量。
Excel中线性预测函数有FORECAST和TREND。这两个函数都是根据一条线性回归拟合线返回一个预测值。
先来看看这两个函数的语法结构。
FORECAST函数的语法结构：
FORECAST (X计算值，Y值区间，X值区间）
TREND函数的语法结构：
TREND(Y值区间，X值区间，X计算值）
在单元格C9应用FORECASET函数：
=FORECAST(B9,C2:C7,B2:B7)
在单元格C10应用TREND函数：
=TREND(C2:C7,B2:B7,B10)
这两个函数计算的结果一致，2019年销售量预测都是18316。
02
图表预测法
在Excel常见的柱形图、折线图和散点图中，都可以添加趋势线，也能帮助进行数据预测，还可以直接显示公式和相关系数。
将2013-2018年销售数据绘制成散点图。在图表中选中数据系列，点击【右键】-【添加趋势线】。在趋势线上，点击【右键】-【设置趋势线格式】，可以选择趋势线样式，并勾选显示公式和R平方值。
最终得到线性回归方程y=1233.1x+9684，相关系数为0.9201，说明当前随着年份的增长，收入增长稳定，呈强正相关关系。
利用回归方程计算出2019年的预计销售量，与前面用FORECAST和TREND函数线性预测结果一致，都是18316。
03
预测工作表法
预测工作表是Excel2016新增的功能，可以看做函数与图表的功能组合，从历史数据分析出业务发展趋势，并以图表的形式展现出来，直观地展示业务发展方向或趋势，命令位置如下。
案例：现有2015年-2018年每月的销售数据表，销售数据有周期性，请根据48个月的历史数据，预测2019年1-12月的销售数据。
点击【预测工作表】，弹出的对话框中选择预测结束的日期，还可以展开高级选项，设置预测开始、置信区间、季节性等参数。
预测开始：从历史数据中的哪一期数据开始预测；
置信区间：设置预测值的上限和下限；该值越小，则上下限之间的范围越小；
季节性：一般周期性的规律，可以为自动检测或手动设置，设置一个周期数，比如12，表示每12个数据作为一个周期进行预测，常用于周末数据，月度数据和年度数据等的预测。
预测结果将自动在新的工作表中呈现。
三列数据分别是趋势预测（数字）、置信下限（数字）、置信上限（数字），这分别代表着预测值，预测下限、预测上限。仔细看会发现这里就是用到了FORECAST.ETS函数。
从Excel 2016开始FORECAST函数已经形成了多个分支，用于预测不同类型的数据，允许基于指数平滑进行预测。营长将这几个函数的含义和适用版本整理出来。
可以看出线性回归预测函数更新为FORECAST.LINEAR，增加了指数平滑预测函数FORECAST.ETS，及其预测项目函数，更适合对有季节性或周期性业务数据的预测。
FORECAST.ETS函数结构如下：
FORECAST.ETS（预测时间点，已知数据，已知时间线，季节性周期，完整性，聚合方式）
预测时间点：即目标日期或时间
已知数据：历史数据区间
已知时间线：时间间隔相等，即步长一致
季节性周期：周期长度值，默认为值1，即自动检测季节性，0表示无季节性，即做线性预测。
完整性：时间线有缺失点一般按临近点平均值处理
聚合方式：对相同时间多个数据处理方式，默认0为AVERAGE，即多个数据的平均值。
在D列列出2019年12个月份的日期，在E2单元格输入公式：
=FORECAST.ETS(D2,$B$2:$B$49,$A$2:$A$49,12)
公式是按12个月作为周期长度进行预测的，预测值和之前48个月的历史数据制作成折线图。
今天的教程就是这些，祝周末愉快！
本文节选自新书《Excel高效办公：财务数据管理》。
举报/反馈
0
0
收藏
分享
