应用Excel做预测

1、应用Excel做预测4.4利用数据分析工具解决预测问题(1)除了利用前面介绍的几个预测函数进行回归预测分析外，我们还可以使用Excel的数据分析工具库提供的统计观测分析工具来解决回归预测问题。Excel的数据分析工具库提供了 3种统计观测分析工具，它们是移动平均法、指数平滑法和回归分析法。下面结合实例来说明这3种方法的具体应用。4.4.1移动平均法【例4-5】某企业2000年12个月的销售额如图4-4所示，分别按3期、5期和7 期移动平均所做的预测分析如图 4-4中的C4 : E13区域所示。以3期移动平均 为例为例，具体计算步骤如下：> 2 x>3r* usoa 30aSX291

2、 4033 网4-4 一次移动平均法实例>11(1) 从【工具】菜单中选中【数据分析】命令，则弹出【数据分析】对话框,如图4-5所示(2) 在【数据分析】对话框中的【分析工具】框中选中【移动平均】选项，则弹出【移动平均】对话框，如图 4-6所示。图4-6【移动平均】对话框(3) 在【移动平均】对话框中，【输入区域】框中输入“ $B$2 : $B$13”，【间隔】框中输入“ 3”，【输出区域】框中输入“ $C$2”，最后选中【图表输出】选项；(4) 单击【确定】按钮，则运算结果就显示在单元格区域C4:C13中，如图4-4所示(图中的第13行预测数据即为下月即第13月的预测值)，并自动出现输

3、出 图表，如图4-7所示。移动平均1357911数耀点T-实時值 按删说移动平均数据点图4-7移动期数为3时的输出图表用同样的方法，可以分析当移动期数为 5和7时的分析结果，如图4-4所示。442指数平滑法【例4-6】某企业的有关销售数据如图4-8所示，利用指数平滑法进行预测分析,其步骤如下：图4-8指数平滑法实例（1）从【工具】菜单中选中【数据分析】命令，则弹出【数据分析】对话框，在【数据分析】对话框中的【分析工具】框中选中【指数平滑】选项，则弹出【指数平滑】对话框，如图4-9所示。图4-9【指数平滑】对话框（2）在【指数平滑】对话框中，【输入区域】框中输入“ $B$2:$B$13”，【阻尼

4、系数】框中输入“0.2”，【输出区域】框中输入“ $C$3”，最后选中【图表输出】选项（3）单击【确定】按钮，则运算结果就显示在单元格区域 C3:C13中（图中的 第13行预测数据即为下月即第13月的预测值），如图4-8所示，并自动出现输 出图表，如图4-10所示。图4-10指数平滑法预测输出图（阻尼系数 0.2）用同样的方法，可以分析当阻尼系数为 0.4和0.6时的分析结果如图4-8所 示。需要注意的是，【数据分析】中的指数平滑法所使用的阻尼系数并不是421.2节介绍的指数平滑法预测方程中的平滑系数：，二者的关系为：阻尼系数=1-=4.4利用数据分析工具解决预测问题（2）4.4.3 回归法利

5、用Excel的回归工具进行预测分析有两种方法：一是图表法；二是回归分析法。4.4.3.1 图表法图表法仅能解决一元线性或非线性回归问题，不能解决多元回归问题。【例4-7】某企业连续9年的产品销售收入丫（万元）与广告支出X1 （万元） 和居民平均收入X2 （元）的有关数据如图4-11所示，则利用图表法进行回归分 析，其方法和步骤如下，这里仅以销售收入丫（万元）与广告支出X1 （万元）的一元线性关系为例：图4-11某企业的有关销售数据(1) 选择单元格区域B2:C10。(2) 单击工具栏上的【图表导向】按钮，在【图表导向一4步骤之1 图表类型】 中选“XY散点图”，其【子图表类型】选第1种，如图4

6、-12所示。图4-12准备作散点图(3) 单击【下一步】按钮，出现【图表导向一4步骤之2图表源数据】对话框, 单击【系列】，在【名称】栏中填入“销售收入”，在【X值】栏中输入“ =Sheet1!$C$2$C$10”,在【Y 值】栏中输入“ =Sheet1!$B$2:$B$10”(用鼠标拾取单元格区域)，如图4-13所示。图4-13填入源数据（4）单击【下一步】按钮，出现【图表导向一 4步骤之3图表选项】对话框, 填入各标题文字，如图4-14所示。图4-14填入各标题文字（5）单击【下一步】按钮，出现【图表导向一 4步骤之4图表位置】对话框, 不作任何输入，单击【完成】按钮，则在工作表上看到输出

7、的图形，对其进行必 要的调整（如坐标、字体、位置等）。（6）在系列【数据点】上的任一点上，按鼠标左键，使各数据点出现记号，再 单击【工具栏】上的【图表】按钮，选中【添加趋势线】项，或在数据点上按 鼠标右键，选【添加趋势线】项，出现【添加趋势线】对话框，如图4-15所示（7）在【添加趋势线】中的【类型】对话框中，有【线性】、【对数】、【多项式】、【乘幕】、【指数】和【移动平均】6个选项。通过观察XY散点图可知，产品销售收入与广告支出之间呈明显的线性关系，故这里选【线性】。（8）在【添加趋势线】中的【选项】对话框中，勾选【显示公式】、【显示R平方值】，如图4-16所示。广 autcfI图 4-16

8、【添加趋势线】的【选项】设置（9）单击【确定】按钮，则在图形上显示出较粗的预测线、回归方程和R平方值，然后进行必要的调整，得到如图 4-17的结果。rtzt&xi 祝）图4-17输出图形用同样的方法还可以确定销售收入与居民平均收入的关系4.4利用数据分析工具解决预测问题(3)443.2回归分析法回归分析法可以对一元线性或多元线性以及某些可以转化为线性的非线性问题 进行回归分析。1.线性回归【例4-8】仍以例4-7的有关资料为例，回归分析的步骤如下：从【工具】菜单中选中【数据分析】命令，则弹出【数据分析】对话框，在【数据分析】对话框中的【分析工具】框中选中【回归】选项，如图4-18所示，

9、则弹出【回归】对话框。图4-18【数据分析】对话框(2) 在【回归】对话框中，【丫值输入区域】中输入“ $B$1:$B$10',【X值输 入区域】中输入“ $C$1$D$10',在【输出选项】中勾选【输出区域】，填入“ $A$12', 然后根据实际需要，勾选其他需要的选项，如图 4-19所示。图4-19【回归】选项（3）单击【确定】按钮，回归分析的摘要就输出在本工作表上， 如图4-20所示 对这些数据进行分析可知：R平方值为0.9903，说明因变量与自变量之间相关性 很高；F的显著值为2.961E-07,已达0.05的检验标准；其他统计检验也达到相 应的标准。从而得到回

10、归方程为： 丫=229.8409+9.2794Xi+0.0082 X2。图4-20回归分析的计算机输出当自变量只有一个（即一元回归）时，上述方法同样适用。2 非线性回归对于某些可以化为线性关系的非线性问题，同样可以进行回归分析。举例如下。【例4-9】某地区科研系统近10年的净收入丫 （千万元）与研究经费X（千万元）和研究人员数X2 （万人）的统计资料，如图4-21所示，假设它们之间存在着以 下的函数关系：式中，a b、c为待估计参数。若利用回归工具求解此类非线性问题， 解决的办法是将此方程进行数学变换，即对方程两边取对数，得-；1' -1：' ' ': 1，将各

11、个观测值进行变换，如图4-21所示，即在单元格E2E11中输入公式“ =LN（B2B11）”（数组公式输入），然后将单元格E2E11复制到单元格F2F11和G2G11中。19W23S1991 23B1992 25&1W3W419M271.K L * _ -年f& 袴收入¥1>L_>liKV)ItKXJKX0 153S 47Z271 E> stwrre5 O*?9©&65 SCSI77 > &T332：as oenrsoft1753 ?26e« 弓苟轴IVK2?12«41WTaoe3 MB3141WB

12、326ie?56；3l2S5550*00?l$tS6*64；?ST6SL如&2S2?731MS71OUTStt2U4各27S1 tS301S 7«ftM3 5 023046 5 3CS3OS21Z5. 76£?97 5 855OT2 5 2r5*5B6图4-21某地区科研系统有关资料再对变换后的数据利用 Excel的回归工具进行回归分析，具体步骤可参阅【例4-7】，其中【丫值输入区域】中输入“ $E$1:$E$11”，在【X值输入区域】中输 入“$F$1:$G$11”，在【输出选项】中勾选【输出区域】，填入“ $A$12”，得到如图4-22所示的分析结果，最后得到：

13、a = e0.08214 = 0.9211, b =0.4477，c =0.6046,相关系数为0.9808 （注意此相关系数是变换后的线性方程的相关系数，并不是原非线性方程的相关系数），回归方程为：1J1'''*_JC _®_tFaiswlmj ttumrrB V W»5£ £ q-a Kf Afl »OT9Br ao oi?»e1 gMiiHBio53017曽知侨ssrE 卜t £ ,町 * £ Ffel 旧 HthjLi “ 冒:.0如1】7喝IT# 1MB9 O11»E-W

14、T0 OKI0 000X754*<+*9>D IGKTMTTr L.男勺导一TJ“r“ VT. 丁诗审'："L±re' ?5_EIalI *jf «-O OKI 40 3O4%3?9E2O 2fi92Sa K T9S421-O. 8DO1BTSTED-a- «JtMalnClD0 447TIBD 1»>T5462& <E252ft0 <»I42<WP0 &110Ko eiiOMQ 55"0鳩讥停心令"尊S ts舍n ma sk博呻雋o zajewG

15、吟欣4Uf!XJl*AL aurnrrJM 一I 弓 tSATliBD OCOB&941 3KOOZK»I2 5 5屮-U M531?<%43 S EHTSe心0 7«1TX7»« 5 wmen ©oit-O5 5-1 (KKWWt鼻 5 «3BLK-fl* O5|»TO31辽R59M聒? 5 6144*9p ooeo«4«5DD9 5 TIZWIQ OlALMTTOlI IBS3VM&90. J5 TTTT50 CCTMlTi0 5«TK0W4.5利用规划求解工具解决预测

16、问题虽然我们可以利用Excel提供的各种预测分析工具解决大多数财务预测中的实 际问题，但这些预测分析工具并不是万能的， 其预测误差也随着实际问题的复杂 化而增大。比如对于一些非线性预测问题，常常是将其通过变量替换而转换为线 性问题。但是，这种变换过程一方面增加了计算工作量，另一方面也可能导致分析精度下降，因为变换后的数据容易使观测数据的性质发生变化，导致自变量与因变量之间的关系发生扭曲，从而影响回归方程的精度，因此，这种将非线性转 换为线性的做法是存在一定的缺陷的。 此外，有些非线性问题根本无法直接转换 为线性问题，除非作出大量的简化，这必然使得到的回归方程严重失真。因此，对于非线性回归问题，

17、最好的方法是直接进行回归分析， 即求解使残差平 方和最小、或使相关系数最大的回归方程，但非线性回归过程是一个循环寻优过 程，需要先设置回归方程系数的初值，然后计算观测值与预测值的残差平方和， 不断寻找使残差平方和最小的回归方程系数，这实际上是一个优化问题，因此， 可以利用Excel的规划求解工具求解非线性回归问题，当然也可以用来求解线性 回归问题。在利用规划求解工具直接求解非线性回归问题时，需要使用以下几个计算公式:自由度df为-匸式中，n为观测次数；m为待估计参数个数残差平方和SE为式中,AY、A分别为第i个观测值和预测值=1,2, , n）。相关系数r2为一八式中，一为观测值的平均值。F面

18、结合实例说明在Excel上进行非线性回归的具体方法和步骤。【例4-10】以例4-9的有关资料为例，利用Excel的规划求解工具来求解非线性回归问题的方法和步骤如下：(1) 如图4-23所示，单元格G2G4为变动单元格，分别存放待估计参数 a、b、 c，其初值可设为0。(2) 在单元格E2E11中输入预测值公式“ =G2*(C2 C11)P3*(D2: D11FG4' (数组公式输入)TCr%DECS遷a 63372JS#7性菱甲方阿SFiix毛m址| * i h闿10图4-23利用规划求解工具进行非线性回归分析(3) 在单元格G5中输入观测值的平均值公式“ =AVERAGE(BB11)

19、”；在单元 格G6中输入自由度公式“ =COUNT(B2311)-COUNT(G2G4)”；在单元格 G7中输 入残差平方和公式“ =SUM(B2B11-E2: E11F2) ” (数组公式输入)；在单元格 G8中输入剩余标准差公式“ =SQRT(G7/G6” ；在单元格G9中输入相关系数戌 的计算公式“ =1-G7/SUM(B2 B11-G5F2) ” (数组公式输入)。(4) 单击EXCE工具菜单，选择【规划求解】项，出现【规划求解参数】对话 框；（5）在【规划求解参数】对话框中，【设置目标单元格】设置为单元格“$G$7 , 即目标函数为残差平方和；【等于】设置为“最小”；【可变单元格】设

20、置为“$G$2$G$4'。然后单击【求解】，即可得到回归方程的系数a b、c,出现“规划求解结果”对话框，然后单出【确定】按钮，保存规划求解结果。需要注意的是，若系数 a b、c的初值设置不合适的话，则一次求解过程（即在 Excel上进行【工具】 【规划求解】 【求解】【确定】这样一个求解过程）可能得不到最优结果（或得不到解），这时需要进行多次求解，即在第一次 求解结果的基础上，再进行第二次求解，得到第二次求解结果，然后在第二次求 解结果的基础上，再进行第三次求解，得到第三次求解结果，如此继续下去，直 到求出的系数a、b、c的值不再变化、且残差平方和最小为止，即得到最优结果。 在上例中

21、，当a、b、c的初始值设为0时，经过2次求解过程即得到最优结果， 如图4-23所示。若采用非线性转换为线性的方法，如例 4-9所示，可得到有关系数如图4-22所示，将此系数代入回归方程，计算不同 Xi和茨下的预测值，进而计算出残差平方和与剩余标准差分别为157.1139和4.7376，与图4-23的直接非线性回归的结果（残差平方和154.03、剩余标准差4.6909 ）进行比较，可见非线性转换为线性的方法得出的结果误差要大于直接进行非线性回归的误差4.6 销售预测销售预测的准确程度， 对企业的兴衰成败会产生很重要的影响。 销售预测比较准 确，会使企业在有计划的财务安排下顺利运作； 而如果销售预

22、测与实际情况偏离 很远，则会使企业遇到麻烦，甚至陷入困境。因此，销售预测是企业进行财务预 测的首要工作，是企业制定财务计划的基础。销售预测主要应根据市场需求的变化，结合企业的利润目标、实现企业市场份 额的目标，并综合考虑企业内外部的各种限制条件的影响来进行。一般情况下， 可首先分别对未来各期的销售量和销售价格进行预测， 在此基础上， 根据预测的 产品销售价格乘以预测的销售量得到预测的销售额； 也可以直接根据销售额的有 关历史资料，采用适当的方法进行预测。4.6.1 销售预测的基本方法销售预测是一项比较复杂的工作， 需要考虑的因素很多， 作出准确的预测是非常 困难的。通常可利用企业过去的数据进行

23、统计分析， 并结合经济环境对未来市场 的影响以及企业内外部各种条件的限制，作出销售预测。进行销售预测的方法很多，常用的方法包括以下几种。1时间序列预测法时间序列预测法， 是指将观察或记录的一些历史数据， 按时间的先后排列成数据 系列，进行统计分析， 找出过去长期的销售量或销售额的增减变化趋势， 再根据 此变化趋势分析的结果， 预测未来时期的销售量或销售额。 常见的时间序列的预 测方法有简单平均法、 移动平均法、 指数平滑法、 或以时间为自变量的回归分析 法等，这些方法的基本原理可参阅前面的有关内容。2 因果关系预测法因果关系预测法，是指利用有关因素与产品销售量或销售额之间的固有因果关系，通过建

24、立一定的数学模型来预测企业未来的产品销售水平的一种方法。企业产品销售水平的高低，往往受到诸多宏观或微观、外部或内部、客观或主观等因 素的影响，通常可以通过回归分析的方法检验出哪些因素与销售水平之间具有因 果关系，在此基础上可建立回归方程，进行销售预测。有关如何建立回归方程及进行相关检验的方法可参阅前面的有关内容。3 通过生产能力或订货合同进行销售量（销售额）预测企业生产的产品如果在市场占有稳定的份额或供不应求，则可按本企业的生产能力预测产品的销售量，计算公式如下：计划期销售量=计划期初库存量+计划期预计生产量-计划期末预计库存量462销售预测模型及其应用在很多情况下，通过建立企业的销售预测模型

25、，可以很方便地实现销售预测。下 面介绍两个销售预测模型。4.6.2.1 一元线性（非线性）回归预测模型【例4-11】根据图4-24中所给的资料建立一元线性（非线性）回归预测模型。AP1/4H1J匸I11脈1«1L就再和199Q 礙？XOlnos3匸砂123*56T«« Wun*2.61T .T0 ?笳】建官99 210! 55回杆皿或Km ufl端刃«4nT-stem卜如馳创Y-A*BXA B 計13 l<u6Y-AB*L叭阳.: 图4-24 元线性（非线性）回归销售预测模型下面利用线性回归中的LINEST函数和指数回归中的LOGEST函数，来建立

26、一 元线性（非线性）回归预测模型。（1）首先建立销售预测模型，如图 4-24所示，这里以过去12期的销售量为历史数据（可以是以年计算，也可以是以月计算，图4-24为以年计算）。（2） 设置回归模型选择控件，控件的数据源区域为$A$7 :$A$8，单元格链接为 $B$7,下拉显示项数为2。（3）选取单元格区域B3:M3，单击【插入】 【名称】 【定义】命令，或直 接单击编辑栏中的名称框，将影响因素所在的单元格区域 B3:M3定义为“影响 因素序列”；用同样的方法，将销售量所在的单元格区域 B4:M4定义为“销售序 列”。（4）在单元格E8中输入公式“ =IF（B7=1,INDEX（LINEST（

27、销售序列，影响因素序 列，TRUE,TRUE）,1,2）,INDEX（LOGEST（销售序列，影响因素序 列,TRUE,TRUE）,1,2）”，计算系数 A。（5）在单元格F8中输入公式“ =IF（B7=1,INDEX（LINEST（销售序列，影响因素序 列，TRUE,TRUE）,1,1）,INDEX（LOGEST（ 销 售序列，影响 因素序 列,TRUE,TRUE）,1,1）”，计算系数 B。（6）在单元格G8中输入公式“=IF（B7=1,INDEX（LINEST（销售序列，影响因素序 列，TRUE,TRUE）,3,1）,INDEX（LOGEST（ 销 售序列，影响 因素序 列,TRUE,T

28、RUE）,3,1）”，计算相关系数 R2。(7) 在单元格 J&M8 中输入公式“ =IF(B7=1,E8+F8*J7:M7,E8*F8AJ7:M7)”(数 组公式输入），计算未来第14期的预测值。在影响因素和销售量两列输入历史数据，并在J7:M7中输入未来4期的影响因素预测数值后，即可得到回归预测模型及未来的预测值。 通过选择不同的回归模 型，可以分别计算一元线性模型和一元指数模型下的回归结果及预测值。由计算结果可知，采用指数模型（相关系数为0.9742）要比线性模型（相关系数为0.9452） 更为准确。4.622多元线性回归预测模型【例4-12】根据图4-25中所给的资料建立多元线性回归预测模