利用EXCEL实现财务预测的回归分析
发布时间：2015-07-09 11:04
关键字：EXCEL 财务预测 回归分析
在企业财务管理工作中，存在着大量的财务预测工作。通过财务预测有助于改善投资决策，提高企业对不确定事件的反应能力，从而减少不利事件出现带来的损失。通常销售百分比法是一种简单和常用的方法，其主要是假设资产、负债、收入、成本与销售额成正比例。但由于规模经济现象和批量购销问题的存在，销售百分比法的假设经常不成立，这使其应用范围受到限制。为了改进财务预测的质量，回归分析则不失为一种有效的方法，利用数理统计的相关原理使数据预测结果更具有说服力。随着Excel电子表的广泛使用，利用其稳定的性能、强大的功能来解决财务预测的回归分析问题则显得十分有效。
一、财务预测的回归分析原理
财务预测的回归分析，是利用一系列的历史资料求得各资产负债表项目和销售额的函数关系，据此预测计划销售额与资产、负债数量，然后预测融资需求。
在财务预测的回归分析中，首先必须收集一些影响被预测对象相关变量的历史资料，然后再将收集到的数据输入计算机进行自动计算得到回归方程和相关参数。计算出的回归方程是否能够作为财务预测的依据取决于对相关参数进行分析，故需要运用数据统计的方法如拟合检验、显着性检验得出检验结果。如果检验结果表明回归方程是可靠的，最后把已拟好的相关变量值代入回归方程得出最终的预测值。下面以销售额的多元回归分析预测为例来说明Excel在财务预测回归分析中的应用。
二、操作方法与步骤
（一）新建工作簿单击”开始”菜单，再在弹出的开始菜单项中，单击”新建office文档”，出现”新建office文档”对话框窗口。
2、在”新建office文档”对话框窗口中的”常用”活页夹中，双击”空工作簿”，出现名为”Book1”的空工作簿。
3、按【Ctrl+S】键：或者在刚刚建立的空工作簿”Book1”中单击磁盘图标：或者单击”文件”菜单并在弹出的菜单中单击”保存”。
4、在”另存为”对话框中将文件名”Book1”改为”财务预测回归分析”，然后单击保存。
（二）定义工作表名称和历史数据双击”sheet1”工作表标签，输入”销售额预测回归分析”后按【Enter】键。
2、选择”销售额预测回归分析”，在A1至D9输入标题（销售额、电视广告费用、报纸广告费用、年份）和相应数据。限于篇幅及仅为说明问题，这里只设8年数据来进行分析。
销售额（万元） 电视广告费用（万元） 报纸广告费用（万元） 年份
960 50 15 1994
900 20 20 1995
950 40 15 1996
920 25 25 1997
950 30 33 1998
940 35 23 1999
940 25 42 2000
940 30 25 2001
(三)定义公式用鼠标选择A11到C15的结果输出区域，输入公式”=LINEST(A2:A9,B2:C9,true,true)”后按【Ctrl+Shift+Enter】，在A11到C15的区域中显示如下结果：
1.300989098 2.290183621 832.3009169
0.320701597 0.304064556 15.73868952
0.9190356 6.425873026 #N/A
28.37776839 5 #N/A
2343.540779 206.4592208 #N/A
说明1：公式LINEST(A2:A9,B2:C9,1,1)中A2:A9是回归方程y = m1*x1+m2*x2 + b 中已知被预测对象y值集合，B2:C9是方程中已知可选变量值x1和 x2的集合。两个true均为逻辑值，前一个true 指明b 将被正常计算，如为false则强制b为0值；后一个true表示指明返回附加回归统计值，如为false则不返回附加回归统计值。
2、为了便于后面对结果进行分析，可将上述结果进行重新表达，使之更为清淅。根据上表中的结果和结果的排列顺序（见说明2），可在A17至E21区域输入对上述结果的解释：
多元回归方程： y=2.290183621*x1+1.300989098*x2+832.3009169
标准差： m1=0.304064556 m2=0.320701597 b=15.73868952
判定系数=0.9191356 y估计值的标准误差=6.425873026
F统计值=28.37776839 自由度=5
回归平方和=2343.540779 残差平方和=206.4592208
说明2：①返回回归分析的结果是按一定顺序排列的，排列顺序如下表：
A B C D E
11 mn mn-1 …… m1 b
12 sen sen-1 …… se1 seb
13 r2 sey
14 F df
15 ssreg ssresid
②上表中se1,se2,...,sen表示系数 m1,m2,...,mn 的标准误差值；Seb表示常数项 b 的标准误差值；r2表示判定系数，可用于拟合检验；Sey表示y 估计值的标准误差；F表示F 统计值或F观察值；df表示自由度；ssreg表示回归平方和；ssresid表示残差平方和。
（四）检验回归方程的可靠性
在上例中，判定系数（或 r2）为 0.9191356（函数 LINEST 的输出单元格 A13 中的值），表明在电视广告费用x1、报纸广告费用x2与销售额y之间存在很大的相关性。然后可以通过 F 统计来确定具有如此高的 r2 值的结果偶然发生的可能性。假设事实上在变量间不存在相关性，但选用 8 年数据作为小样本进行统计分析却导致很强的相关性。”Alpha”表示得出这样的相关性结论错误的概率。如果 F 观测统计值大于 F 临界值，表明变量间存在相关性。假设一项单尾实验的 Alpha 值为 0.05，根据自由度（在大多数F 统计临界值表中缩写成 v1 和 v2）v1 = k = 2，v2 = df=n - (k + 1) = 8 - (2 + 1) =5，其中 k 是回归分析中的变量数，n 是数据点的个数，可以在F 统计临界值表中查到F 临界值为 5.79。而在单元格 A14 中的 F 观测值为 28.37776839，远大于 F 临界值 5.79。由此可以得出结论：此回归方程适用于对销售额的预测。
（五）预测未来的销售额
假设2002年的电视广告费用预算为35万元，报纸广告费用预算为18万元，则根据多元线性回归方程y=2.290183621*x1+1.300989098*x2+832.3009169可计算出2002年的销售额为2.290183621*35+1.300989098*18+832.3009169即913.7583万元。
三、结束语
文中仅以预测销售额为例说明Excel在财务预测的回归分析中应用和实现问题，其他项目可以用同样的方法来预测。比如，假设存货与销售额之间存在直线关系，其直线方程为”存货=a+b×销售额”，根据历史资料和一元回归分析可以求出直线方程的系数a和b，然后根据预计销售额和直线方程预计存货的金额。通过假设销售额与资产、负债等存在线性关系，利用一系列的历史资料求得各资产负债表项目和销售额的函数关系，据此预测计划销售额与资产、负债数量，然后预测融资需求。须注意的是，函数变量的选择直接关系到回归分析结果的正确性和可靠性，只要变量选择恰当，Excel可以为财务管理工作带来极大的效率，使得在手工作业下难以完成的思想和方法得以顺利实施。
在企业财务管理工作中，存在着大量的财务预测工作。通过财务预测有助于改善投资决策，提高企业对不确定事件的反应能力，从而减少不利事件出现带来的损失。通常销售百分比法是一种简单和常用的方法，其主要是假设资产、负债、收入、成本与销售额成正比例。但由于规模经济现象和批量购销问题的存在，销售百分比法的假设经常不成立，这使其应用范围受到限制。为了改进财务预测的质量，回归分析则不失为一种有效的方法，利用数理统计的相关原理使数据预测结果更具有说服力。随着Excel电子表的广泛使用，利用其稳定的性能、强大的功能来解决财务预测的回归分析问题则显得十分有效。
一、财务预测的回归分析原理
财务预测的回归分析，是利用一系列的历史资料求得各资产负债表项目和销售额的函数关系，据此预测计划销售额与资产、负债数量，然后预测融资需求。
在财务预测的回归分析中，首先必须收集一些影响被预测对象相关变量的历史资料，然后再将收集到的数据输入计算机进行自动计算得到回归方程和相关参数。计算出的回归方程是否能够作为财务预测的依据取决于对相关参数进行分析，故需要运用数据统计的方法如拟合检验、显着性检验得出检验结果。如果检验结果表明回归方程是可靠的，最后把已拟好的相关变量值代入回归方程得出最终的预测值。下面以销售额的多元回归分析预测为例来说明Excel在财务预测回归分析中的应用。
二、操作方法与步骤
（一）新建工作簿单击”开始”菜单，再在弹出的开始菜单项中，单击”新建office文档”，出现”新建office文档”对话框窗口。
2、在”新建office文档”对话框窗口中的”常用”活页夹中，双击”空工作簿”，出现名为”Book1”的空工作簿。
3、按【Ctrl+S】键：或者在刚刚建立的空工作簿”Book1”中单击磁盘图标：或者单击”文件”菜单并在弹出的菜单中单击”保存”。
4、在”另存为”对话框中将文件名”Book1”改为”财务预测回归分析”，然后单击保存。
（二）定义工作表名称和历史数据双击”sheet1”工作表标签，输入”销售额预测回归分析”后按【Enter】键。
2、选择”销售额预测回归分析”，在A1至D9输入标题（销售额、电视广告费用、报纸广告费用、年份）和相应数据。限于篇幅及仅为说明问题，这里只设8年数据来进行分析。
销售额（万元） 电视广告费用（万元） 报纸广告费用（万元） 年份
960 50 15 1994
900 20 20 1995
950 40 15 1996
920 25 25 1997
950 30 33 1998
940 35 23 1999
940 25 42 2000
940 30 25 2001
(三)定义公式用鼠标选择A11到C15的结果输出区域，输入公式”=LINEST(A2:A9,B2:C9,true,true)”后按【Ctrl+Shift+Enter】，在A11到C15的区域中显示如下结果：
1.300989098 2.290183621 832.3009169
0.320701597 0.304064556 15.73868952
0.9190356 6.425873026 #N/A
28.37776839 5 #N/A
2343.540779 206.4592208 #N/A
说明1：公式LINEST(A2:A9,B2:C9,1,1)中A2:A9是回归方程y = m1*x1+m2*x2 + b 中已知被预测对象y值集合，B2:C9是方程中已知可选变量值x1和 x2的集合。两个true均为逻辑值，前一个true 指明b 将被正常计算，如为false则强制b为0值；后一个true表示指明返回附加回归统计值，如为false则不返回附加回归统计值。
2、为了便于后面对结果进行分析，可将上述结果进行重新表达，使之更为清淅。根据上表中的结果和结果的排列顺序（见说明2），可在A17至E21区域输入对上述结果的解释：
多元回归方程： y=2.290183621*x1+1.300989098*x2+832.3009169
标准差： m1=0.304064556 m2=0.320701597 b=15.73868952
判定系数=0.9191356 y估计值的标准误差=6.425873026
F统计值=28.37776839 自由度=5
回归平方和=2343.540779 残差平方和=206.4592208
说明2：①返回回归分析的结果是按一定顺序排列的，排列顺序如下表：
A B C D E
11 mn mn-1 …… m1 b
12 sen sen-1 …… se1 seb
13 r2 sey
14 F df
15 ssreg ssresid
②上表中se1,se2,...,sen表示系数 m1,m2,...,mn 的标准误差值；Seb表示常数项 b 的标准误差值；r2表示判定系数，可用于拟合检验；Sey表示y 估计值的标准误差；F表示F 统计值或F观察值；df表示自由度；ssreg表示回归平方和；ssresid表示残差平方和。
（四）检验回归方程的可靠性
在上例中，判定系数（或 r2）为 0.9191356（函数 LINEST 的输出单元格 A13 中的值），表明在电视广告费用x1、报纸广告费用x2与销售额y之间存在很大的相关性。然后可以通过 F 统计来确定具有如此高的 r2 值的结果偶然发生的可能性。假设事实上在变量间不存在相关性，但选用 8 年数据作为小样本进行统计分析却导致很强的相关性。”Alpha”表示得出这样的相关性结论错误的概率。如果 F 观测统计值大于 F 临界值，表明变量间存在相关性。假设一项单尾实验的 Alpha 值为 0.05，根据自由度（在大多数F 统计临界值表中缩写成 v1 和 v2）v1 = k = 2，v2 = df=n - (k + 1) = 8 - (2 + 1) =5，其中 k 是回归分析中的变量数，n 是数据点的个数，可以在F 统计临界值表中查到F 临界值为 5.79。而在单元格 A14 中的 F 观测值为 28.37776839，远大于 F 临界值 5.79。由此可以得出结论：此回归方程适用于对销售额的预测。
（五）预测未来的销售额
假设2002年的电视广告费用预算为35万元，报纸广告费用预算为18万元，则根据多元线性回归方程y=2.290183621*x1+1.300989098*x2+832.3009169可计算出2002年的销售额为2.290183621*35+1.300989098*18+832.3009169即913.7583万元。
三、结束语
文中仅以预测销售额为例说明Excel在财务预测的回归分析中应用和实现问题，其他项目可以用同样的方法来预测。比如，假设存货与销售额之间存在直线关系，其直线方程为”存货=a+b×销售额”，根据历史资料和一元回归分析可以求出直线方程的系数a和b，然后根据预计销售额和直线方程预计存货的金额。通过假设销售额与资产、负债等存在线性关系，利用一系列的历史资料求得各资产负债表项目和销售额的函数关系，据此预测计划销售额与资产、负债数量，然后预测融资需求。须注意的是，函数变量的选择直接关系到回归分析结果的正确性和可靠性，只要变量选择恰当，Excel可以为财务管理工作带来极大的效率，使得在手工作业下难以完成的思想和方法得以顺利实施。
上一篇：标准件经销管理系统