财务预测中构建Excel回归剖析模型.docVIP

财务预测中构建Excel回归剖析模型 　　［摘要］ 本文研究在销售收入、资金占用、成本费用等财务预测中，利用Excel内置的公式与函数、窗体工具交互选择等功能，构建Excel回归分析模型的技巧。 　　［关键词］ 回归分析；模型构建；Excel函数；窗体工具 　　doi : 10 . 3969 / j . issn . 1673 - 0194 . 2011 . 06 . 006 　　［中图分类号］F232 ［文献标识码］A ［文章编号］1673 - 0194（2011）06 -0011- 04 　　 　　一、导言 　　 　　预测是对未来状况的分析、判断和规划，Ｅｘｃｅｌ中可使用窗体工具的滚动条、微调项、列表框等，对未来状况值进行范围设定，以建立交互选择模型，简化复杂的数据计算。回归分析的运用非常广泛，在单位理财中主要用于销售预测、资金预测和成本费用预测等方面。 　　回归分析一般用直线方程Ｙ ＝ ａ＋ｂＸ预测未来值。其中，截距参数ａ、斜率参数ｂ的确定方法有：（１）最小二乘法公式计算；（２）Ｅｘｃｅｌ截距函数Ｉｎｔｅｒｃｅｐｔ、斜率函数Ｓｌｏｐｅ确定；（３）用直线拟合函数Ｌｉｎｅｓｔ计算，再将其作为数值引用函数Ｉｎｄｅｘ的嵌套函数，以返回参数值等。 　　财务预测时，一般都要用到计数函数ＣＯＵＮＴ、求和函数ＳＵＭ、四舍五入函数ＲＯＵＮＤ、条件函数ＩＦ，以及字符运算、显示公式或计算结果等技术方法，以充分利用Ｅｘｃｅｌ强大的计算、分析、提示等功能。 　　 　　二、销售回归分析的模型构建 　　 　　Ｅｘｃｅｌ回归分析时，可用最小二乘法公式确定ａ、ｂ的参数值，再用Ｙ ＝ ａ＋ｂＸ预测未来销售收入。最小二乘法参数公式如下： 　　式中，ｙ表示销售量或销售额；Ｘ表示时间序列；ａ表示固定不变的销售；ｂ表示随时间序列变动的单位销售；ｎ表示历史数据的期间项数；∑表示求和。 　　Ｅｘｃｅｌ提供了计算期间项数ｎ的计数函数ＣＯＵＮＴ，其函数公式如下： 　　＝ ＣＯＵＮＴ（ｖａｌｕｅ１，ｖａｌｕｅ２，…） 　　式中，ｖａｌｕｅ表示包含或引用的数字型数据的参数，可以设置１至３０个参数。 　　【案例１】 某公司甲产品近５年销量如表１所示。要求用回归直线法预测该产品未来１３年的销售情况。 　　【模型构建】构建的销售预测模型如图１所示。单击Ｂ１２单元格中的组合框下拉箭头，在列表中选定某年度，该年度的销量预测即可显示于Ｄ１２单元格中，并在Ｂ１３单元格中进行提示。该模型的设计方法如下： 　　１．设计表格。键入文字、已知数据，合并单元格区域等。 　　２．求和、乘积与幂运算。在Ｅ３单元格中键入“＝Ｃ３*Ｄ３”，Ｆ３单元格中键入“＝Ｃ３＾２”，Ｃ８单元格中键入“＝ＳＵＭ（Ｃ３：Ｃ７）”；再自动填充Ｅ４：Ｅ７、Ｆ４：Ｆ７和Ｄ８：Ｆ８单元区域的公式。 　　３．确定观察值项数。选定Ｂ８单元格，键入“＝ＣＯＵＮＴ（Ｂ３：Ｂ７）”，如图２所示。由于引用区域有５年，所以计算结果为“５”。 　　４． 输入参数公式。在Ｂ９单元格键入“＝ＲＯＵＮＤ（（Ｄ８－Ｂ１０*Ｃ８）／Ｂ８，１）”。其中ＲＯＵＮＤ是四舍五入函数，参数“１”表示保留一位小数；“（Ｄ８－Ｂ１０*Ｃ８）／Ｂ８”是参数ａ的计算公式。 　　在Ｂ１０单元格中键入对参数ｂ的计算结果四舍五入并保留一位小数的函数公式“＝Ｒｏｕｎｄ（（Ｂ８*Ｅ８－Ｃ８*Ｄ８）／（Ｂ８*Ｆ８－Ｃ８＾２），１）”。 　　５．字符运算提示信息。在Ｃ１１单元格键入“＝＂Ｙ ＝ ＂＆Ｂ９＆＂＋＂＆Ｂ１０＆＂Ｘ＂”。 　　６．设计组合框，方法如下： 　　（１）选择菜单“视图／工具栏／窗体”命令，调出窗体工具栏，如图１上部所示。 　　７．在Ｄ１２单元格中键入Ｙ＝ａ＋ｂＸ的预测公式“＝Ｂ９＋Ｂ１０*（Ｃ７＋Ｂ１２）”；在Ｂ１３单元格中键入信息的动态提示公式“＝预测结论：＆Ｂ７＋Ｂ１２＆年销量＆Ｄ１２＆万台”。 　　８．选择“工具／选项”菜单命令，在“视图”选项卡中勾选“公式”复选框，则单元格中显示公式，如图２所示；取消该选项，则单元格中显示计算结果，如图１所示。 　　三、资金回归分析的模型构建 　　Ｅｘｃｅｌ回归分析时，可采用线性方程截距函数Ｉｎｔｅｒｃｅｐｔ确定参数ａ的值，斜率函数Ｓｌｏｐｅ确定参数ｂ的值，然后用Y ＝ ａ＋ｂＸ预测资金占用额。参数公式如下： 　　＝ Ｉｎｔｅｒｃｅｐｔ（ｋｎｏｗｎ＿ｙ’ｓ，ｋｎｏｗｎ＿ｘ’ｓ） 　　 ＝ Ｓｌｏｐｅ（ｋｎｏｗｎ＿ｙ’ｓ，ｋｎｏｗｎ＿ｘ’ｓ） 　　式中，Ｋｎｏｗｎ＿ｙ’ｓ表示因变量数据组或单元区域；Ｋｎｏｗｎ＿ｘ’ｓ 表示自变量数据点集合。 　　【案例２】某公司近６年销售量与资金占用额如表２所示。该公司未来的销售量可能为２７９万～３７９万台，请用回归直线法