“Excel表”在销售预测中的应用

ACCOUNTING LEARNING
91
“Excel 表”在销售预测中的应用
文/谢东闻
摘要：在市场经济“以需定销，以销定产”的条件下，销售预测显得非常重要。

利用EXCEL 常用函数，分别采用加权平均法、指数平滑法、回归直线法和多元线性回归法创建销售预测模型，对管理会计人员销售预测有一定的借鉴意义。

关键词：销售预测；平均法；回归直线法；多元线性回归法
一、引言
销售预测方法有定性分析法和定量分析法。

定性分析法要凭借管理者的经验去判断销售量；定量分析法运用现代数学方法进行数据处理，据以建立能够反映有关变量之间规律性联系的各类预测模型方法体系。

它适用于具备完整的历史资料或有关变量之间具有明显的数量关系等条件下的预测。

二、销售预测模型的创建
本文的销售预测模型是利用Excel 工作表的数据分析功能，根据销售预测的基本原理和方法创建的，用于销售预测的一套计算分析程序，能够大大提高销售预测工作的效率和信息的质量。

销售预测的方法很多，具体使用哪种方法取决于预测的对象、目的、时间及精确程度，预测时应综合考虑有关因素，选择适当的方法进行预测，考虑到基于Excel 工作表的实验平台，仅介绍加权平均法、指数平滑法、回归直线法、多元回归法预测模型的创建。

(一)加权平均法销售预测模型的创建
加权平均法销售预测模型是将若干历史时期的销售量作为观察值，并为各个观察值确定相应的权数，将各个观察值与各自的权数相乘后加总，将加权平均数作为销售量的预测值。

具体应用见图1，图中黄色单元格代表已知的数据，白色单元格代表需要计算的数据。

加权平均法在实际应用过程中要注意期数和权重的选择，模型中选择了1-6月份的销售量来平均，选择了6期数据，如
果选择5期或7期数据平均，7月份的预测销售量就会不同，管理会计人员需要凭借经验选择适中的期数来平均，期数不是越大越好也不是越小越好。

模型中1-6月份的权重选取必须遵循近大远小的原则，与7月份越接近的月份权重应该越大，权重的选择需要凭借管理会计人员的经验来判断，权重选取不一样，预测的销售量也会不同。

7月份预测销售量=650*0.1+660*0.1+680*0.1+700*0.2+710*0.2+730*0.3=700 (台)，利用SUMPRODUCT(加权平均函数)来求更简便。

（见图1）
加权平均法适用于历史数据(1-6月份销售量)波动的情况，没有递增或递减的趋势的时候才能够使用。

这种方法预测出来的结果(7月份销售量)一定处于最大值和最小值之间。

(二)指数平滑法销售预测模型的创建指数平滑法销售预测模型是将若干历史时期的销售量作为观察值，计算指数平滑平均数，计算公式为：
预测期销售量Q t =平滑指数×前期实际销售量+(1-平滑指数)×前期预测销售量=αX t-1+(1-α)Q t-1(t=2, 3, …, n)
从指数平滑法的公式可以看出，这仍然是加权平均法，因为：
Q t =αX t-1+(1-α)Q t-1=αX t-1+α(1-α)X t-2+α(1-α)2X t-3+…+α(1-α)t-2X 1+(1-α)t-1
Q 1
α取值越大，则近期实际销售量对预测结果的影响越大；α取值越小，则近期预测销售量对预测结果的影响越大。

平滑指数α是一个经验数据，其取值范围通常在0.3～0.7之间。

具体应用见图2，α=0.3，选用6期(1-6月份销售量)平均。

1月份预测销售量=1月份实际销售量，2月份预测销售量取决于1月份预测销售量和1月份实际销售量，3月份预测销售量取决于2月份预测销售量和2月份实际销售量，依此类推，4、5、6月份预测销售量可以预测出来，7月份预测销售量=0.3*1350+(1-0.3)*1197=1243(台)。

指数平滑法是一种特殊的加权平均法，因此适用条件和加权平均法一样。

在Excel 平台下，可以调用指数平滑工具来求，输入1月至6月的实际销售量及平滑指数α后，可以一次得出2月至7月的预测销售量。

(三)回归直线法销售预测模型的创建回归分析法也称为一元回归分析法，如果影响销售量的因素只有一个，可以令直线方程 Y=a+bX ，运用数学上的最小二乘法来确定一条误差最小并能正确反映自变量X 和因变量Y 之间关系的直线。

基金项目：2015年广东省本科高校教学质量与教学改革工程项目“应用型卓越会计人才培养计划”(粤教高函[2015]133号)。

图1 销售预测(加权平均法
)
图2 销售预测(指数平滑法)
Copyright©博看网 . All Rights Reserved.
会计研究ACCOUNTING RESEARCH
92具体应用见图3，我们要预计2016
年压缩机预计销售量，首先需要分析2011年至2015年电冰箱销售量(X)与压缩机销售量(Y)这两组数据的拟合优度，如果相关系数(r)＜0.8，说明两组数据的拟合优度不高，不能采用回归直线法。

如果相关系数(r)≥0.8，说明两组数据的拟合优度高可以采用回归直线法，相关系数的计算公式很复杂，可以采用Excel平台中的统计函数CORREL(相关系数)来求，计算结果为0.98973093接近完全正相关，说明电冰箱销售量(X)与压缩机销售量(Y)这两组数据高度正相关，可以采用回归直线法，然后单位变动成本(b)可以用SLOPE(斜率函数)来求，结果为0.31346154，固定成本总额(a)可以用INTERCEPT(截距函数)来求，结果为-12.117308。

最后得出2016年压缩机预计销售量=-12.117308+0.31346154*180 =44.3058(万台)
(四)多元线性回归法销售预测模型的创建
在实际生产经营活动中，影响销售量变动的因素是多种多样的，要预测未来的销售量，必须综合考虑采用多个自变量，建立多元回归方程来预测销售量。

可令多元线性回归方程为Y=a+b1X1+b2X2+…+bnXn(n=1, 2, 3, 4, …)。

影响压缩机销售量(Y)的因素有研发费(X1)、广告费(X2)和电冰箱销售量
(X3)，我们要预计压缩机销售量，首先
要将固定成本(a)、研发费支出系数(b1)、
广告费支出系数(b2)、电冰箱销量系数
(b3)计算出来。

固定成本、研发费支出系数、广告
费支出系数和电冰箱销量系数的计算是
难点，需要采用LINEST(多元回归函
数)来求。

这里LINEST 函数的应用需
要技巧，在电冰箱销量系数下面那一格
点击插入LINEST 函数后，在Known_
y's框内，输入要预测值的历史数据，
即y，在Known_x's里选中所有影响
因素(自变量X1，X2，X3)的历史数
据，然后点击确定，就求出了第一个解
0.304347826，也就是电冰箱销量对压缩
机销量的影响系数，然后选择要计算区
域的单元格，按F2键，再接着按组合键
“Ctrl+Shift+Enter”，后面所有的系数都求
出来了。

组合键一般应用于数组公式的计
算，一般公式的计算用“Enter”就可以。

显示的结果如图4，预计压缩机销售量=-
11.30435+0.260869565*4+0.043478261*5+
0.304347826*170=41.6957(万台)
三、模型的应用问题
(一)一般情况下在Excel工作表菜
单中找不到指数平滑工具，2007年版
Windows系统中需要在“文件”下拉菜
单中选择“选项”中的“加载宏”，在
“分析工具库”前面打√。

点击确定后选
择“数据分析”，“数据分析”才会显示在
“数据”菜单中，我们才能调用指数平滑
工具，才能用指数平滑法预测销售量。

(二)在回归直线法模型里，点击插
入函数以后，先要确定好X 和Y，一般
认为建立模型要预测哪个，哪组历史数
据就是Y，哪些会影响因变量Y 的结果，
哪些就是自变量X。

(三)在多元线性回归法模型里，自
变量系数(b1，b2， b3)排列是有规定的，
从文中可以看到，原始数据里第一行是
研发费，第二行是广告费，然后是电冰
箱销售量，可结果得出的先是电冰箱销
量系数，然后是广告费支出系数和是研
发费支出系数，最后才是固定成本，在
操作过程中这里特别容易出错，就是求
解系数的排列跟原始数据的排列刚好相
反。

在设置公式时，一定严格按照模型
y=a+b1X1+b2X2+…+bnXn计算，意思就
是b1一定要乘X1，b2一定要乘X2，b3
一定要乘X3，各自系数一定要对上，不
能错位，否则预测出来的值就不正确。

(四)如果发现销售预测模型中的销
售量等数据输入有误，只需将正确的资料
输入单元格区域内，模型将自动计算出资
料修正后的销售预测结果；如果预测销售
量的效果不够理想，比如对指数平滑系数
α的取值进行修正，可以预测出不同的销
售量；最后销售预测模型非常实用，不仅
可用于其他项目销售量的预测分析，还可
以用到成本预测、利润预测和资金需求量
预测上。

财
参考文献：
[1]单昭祥，邓雪雅.新编现代管理
会计学[M].大连：东北财经大学出版社，
2014.
[2]马元驹，李百兴.管理会计模拟实
验教程[M].北京：中国人民大学出版社，
2015.
(作者单位：广东海洋大学寸金学院)
图3 销售预测(回归直线法)
图4 销售预测(多元线性回归法)
Copyright©博看网 . All Rights Reserved.。