excel在财务预测中的应用

第 4 章 Excel 在财务预测 中的应用4.1 财务预测概述财务预测，是指对企业未来的收入、成本、利润、现金流量及融资需求等财务指标所作的估计和推测。财务预测是编制投资和融资计划的基础，是公司制订成长战略的基本要素。称职的财务管理人员应该能够充分利用公司的有关信息资料，预测公司的财务需要并做出相应的安排。公司成长主要由销售增长来决定，销售增长需要相应的资产增长，如果企业已经是满负荷运转，不仅流动资产、而且固定资产都要增长，而资产增长需要相应的融资增长。同时，企业进行对外投资和调整资本结构，也需要筹措资金。企业所需要的这些资金，一部分来自企业内部，另一部分通过外部融资取得。由于对外融资时，企业不但需要寻找资金提供者，而且还需做出还本付息的承诺或提供企业盈利前景等信息，使资金提供者确信其投资是安全的并可获利，这个过程往往需要花费较长的时间。因此，企业需要预先知道自身的财务需求，确定资金的需要量，提前安排融资计划，以免影响资金周转。财务预测有助于改善企业的投资决策。虽然投资是决定筹资与否和筹资多少的重要因素，但是根据销售前景估计出的融资需求，并不一定能够得到全部满足。这时，就需要根据可能筹措到的资金来安排销售增长以及有关的投资项目，使投资决策建立在可行的基础上。财务预测一般按以下几个步骤进行。1销售预测销售预测是指根据市场调查所得到的有关资料，通过对有关因素的分析研究，预计和测算特定产品在未来一定时期内的市场销售量水平及变化趋势，进而预测企业产品未来销售量的过程。企业的一切财务需求都可以看作是因销售引起的，销售量的增减变化，将会引起库存量、现金流量、应收与应付账款以及公司其他资产和负债的变化。因此销售预测在企业预测系统中处于先导地位，它对于指导利润预测、成本预测和资金预测，进行长短期决策，安排经营计划，组织生产等都起着重要的作用。2估计收入、费用和利润收入和费用与销售量之间也存在一定的函数关系，因此，可以根据销售数据估计收入和费用，并确定净利润。净利润和股利支付率，共同决定了内部留存收益所能提供的资金数额。3估计需要的资产资产通常是销售收入的函数，根据历史数据可以分析出二者之间的函数关系。根据预计销售收入和资产与销售之间的函数关系，可以预测所需资产的总量。某些流动负债也是销售收入的函数，相应地也可以预测负债的自发增长额，这种增长可以减少企业外部融资的数额。4估计所需融资根据预计资产总量，减去已有的资金来源、负债的自发增长和内部提供的留存收益，可得出所需的外部融资数额。4.2 财务预测的分析方法预测分析的方法有很多种，企业应根据不同的需要选择不同的预测方法。总的来说，预测分析方法可分为两大类：定量预测法和定性预测法。4.2.1 定量预测法定量预测法是指在掌握与预测对象有关的各种要素的定量资料的基础上，运用现代数学方法进行数据处理，从而建立起能够反映有关变量之间关系的各类预测模型的方法。在财务预测中，经常使用的定量预测法主要有以下几种。4.2.1.1 移动平均法移动平均法是一种改良的算术平均法，是一种最简单的自适应预测模型。它根据近期数据对预测值影响较大，而远期数据对预测值影响较小的事实，把平均数逐期移动。移动期数的大小视具体情况而定，移动期数少，能快速地反映变化，但不能反映变化趋势；移动期数多，能反映变化趋势，但预测值带有明显的滞后偏差。常用的移动平均法主要有一次移动平均法和二次移动平均法。1一次移动平均法一次移动平均法是根据时间序列，逐期移动，依次计算包含一定项数的时间序列平均数，形成一个平均时间数序列，并据此进行预测。预测模型为式中 第 t+1 期的预测值；、 、 将被平均的 n 个观测值；n移动平均的项数，即移动期数。在实际预测中，可以多取几个 n 数，并将得到的预测值与实际值进行比较，选用误差最小的n 值。2二次移动平均法二次移动平均法是对时间序列计算一次移动平均数后，再对一次移动平均数序列进行一次移动平均运算。预测模型为。式中 二次移动平均数；第 t+1 期的预测值，即 。二次移动平均法解决了一次移动平均法只能预测下一期的局限性，它可以进行近、短期的预测。但它仍不能解决中长期的预测问题。4.2.1.2 指数平滑法指数平滑法实际上也是一种加权平均法，是一种改良的加权平均法，预测模型为式中 平滑系数，0 1。在指数平滑法中，确定合适的 值和初始值是非常重要的。 越大， t 期的实际值对新预测值的贡献就越大； 越小， t 期的实际值对新预测值的贡献就越小。一般情况下，可以取几个不同的 值进行预测，比较它们的预测误差，选择预测误差最小的 值。4.2.1.3 回归分析预测法回归分析预测法是通过研究两组或两组以上变量之间的关系，建立相应的回归预测模型，对变量进行预测的一种预测方法。1回归分析预测法的基本程序进行回归分析的步骤如下：（ 1） 收 集 有 关 资 料 。 将 各 种 可 能 的 影 响 因 素 的 有 关 数 据 尽 可 能 多 地 收 集 起 来 。（2）判断趋势。根据收集到的数据，判断其变化趋势，从而为建立相应的数学模型做准备。对于变量不多的问题，可以通过绘制散点图来判断变化趋势。（3）建立预测数学模型。根据历史数据的变化趋势，选择相应的描写该问题的数学模型，并采用相关的计算技术来估计数学模型的参数。（4）相关检验。对建立的预测数学模型，必须进行有关的检验，主要是通过计算预测模型的相关系数、方差（或标准差）以及显著性等指标，来判断预测模型的准确性、是否需要修正、采用何种方法修正等。2回归模型建立的方法建立回归模型的一般方法是采用最小二乘法，其原理如下：考虑 m 个自变量 x1、 x2、 、 x m和因变量 y 的关系，现有 n 组观测数据，不同 xki （ k=1,2,m； i=1,2,n）下的 y 的观测值为 yi，函数 y=f(xk)的待估计参数为ak（ k=1,2,m+1，这里，每个自变量有一个待估计系数，还有一个待估计常数，故有 m+1 个待估计参数），通过回归预测模型得到不同 xki下的预测值为 ，则有：残差平方和 SE：剩余标准差 SS：相关系数 R2：y 为观测值 yi的平均值：那 么 ， 最 小 二 乘 法 的 原 理 就 是 寻 找 最 优 的 待 估 计 参 数 ak， 使 残 差 平 方 和 最 小 。3财务预测中常用的几种回归 模型（1）一元线性回归模型当只有两个变量（一个自变量和一个因变量），并且它们之间存在线性关系时，可以用一元线性回归模型来描述。一元线性回归模型为式中 a、 b回归系数，其中 a 代表截距， b 代表斜率。（2）一元非线性回归模型当变量 x 和 y 之间的关系不能用线性关系来描述时，则需要建立一元非线性回归模型。根据变量 x 和 y 之间的关系，一元非线性回归模型常见的几种情况有：对数模型：指数模型：乘幂模型：双曲线模型：以上几种一元非线性模型均可通过数学变换化成一元线性模型。（3）多元线性回归模型当自变量有两个或两个以上，且因变量与这些自变量之间呈线性组合关系时，它们就构成了多元线性回归模型，模型形式为式中 a、 b1、 b2、 bm估计参数；x1、 x2、 xm自变量。（4）多元非线性回归模型多元非线性回归模型用来描述因变量与多个自变量之间呈非线性组合关系的情况。例如，柯柏道格拉斯生产函数就是典型的多元非线性模型：式中： L 和 K 分别为劳动力和固定资本； a、 b、 c 为系数。4.2.1.4 模拟法在企业的实际经济活动中，各种经济参数往往并不是确定的，而是随机变化的，比如产品的销售量往往随市场的变化而变化，在这种情况下，就需要对这些参数的不确定性进行分析，而对其预测也就需要采用与传统的确定性分析不同的方法来进行。一般情况下，可以采用模拟法来解决不确定性情况下的财务预测问题，概率法、蒙特卡罗模拟方法就是较实用的方法。4.2.2 定性预测法定性预测法是由有关方面的专业人员或专家根据自己的经验和知识，结合预测对象的特点进行综合分析，对事物的未来状况和发展趋势作出推测的预测方法。定性预测法由于带有较多的个人主观性，因而在实践中最好作为一种补充的预测方法。4.3 Excel 中的有关预测函数及其应用(1)Excel 提供了关于估计线性模型和指数模型参数的几个预测函数。线性模型和指数模型的数学表达式如下：线性模型：y = mx + b 或 y = m1x1 + m2x2 + + b指数模型：或式中， y 为因变量； x 是自变量； m、 m1、 .、 mn-1、 mn、 b 分别为预测模型的待估计参数。Excel 提供的预测函数主要有 LINEST 函数、LOGEST 函数、TREND 函数、GROWTH 函数、FORECAST 函数、SLOPE 函数和 INTERCEPT 函数，它们所使用的参数都基本相同，现列于表 4-1中，以供参考。表 4-1 预测函数的参数及含义参数 含义known_ys 因变量 y 的观测值集合known_xs 自变量 x 的观测值集合。它可以是一个变量（即一元模型）或多个变量（即多元模型）的集合。如果只用到一个变量，只要 known-ys 和 known-xs 维数相同，它们可以是任何形状的选定区域。如果用到不只一个变量，known_ys 必须是向量（也就是说，必须是一行或一列的区域）。如果省略 known_xs，则假设该数组是 1,2,3 .，其大小与 known_ys 相同const逻辑值，指明是否强制使常数 b 为 0（线性模型）或为 1（指数模型）。 如果 const 为 TRUE 或省略， b 将被正常计算。如果 const 为 FALSE，b 将被设为 0（线性模型）或设为 1（指数模型）stats逻辑值，指明是否返回附加回归统计值。 如果 stats 为 TRUE，则函数返回附加回归统计值，这时返回的数组为 m n，m n-1， .，m 1，b；se n，se n-1， .，se 1，se b，r 2，se y；F，df；ss reg，ss resid。如果 stats 为 FALSE或省略，函数只返回系数预测模型的待估计参数 m、m n、m n-1、 .、m 1和b。附加回归统计值返回的顺序见表 4-2。表 4-2 中的各参数说明见表 4-3。如果要得到附加回归统计值数组中的值，需用 INDEX 函数将其取出表 4-2 附加回归统计值返回的顺序1 2 3 4 5 61 mn mn-1 m2 m1 b2 sen sen-1 se2 se1 se b3 r2 sey 4 F df 5 ssreg ssresid表 4-3 各参数说明参数 说明se1,se2,.,sen系数 m 1,m2, .,mn 的标准误差值Seb 常数项 b 的标准误差值（当 const 为 FALSE 时，se b = #N/A ）参数 说明r2相关系数，范围在 0 到 1 之间。如果为 1，则样本有很好的相关性，Y 的估计值与实际值之间没有差别。反之，如果相关系数为 0，则回归方程不能用来预测 Y 值sey Y 估计值的标准误差FF 统计值或 F 观察值。使用 F 统计可以判断因变量和自变量之间是否偶尔发生过观察到的关系Df自由度。用于在统计表上查找 F 临界值。所查得的值和函数 LINEST 返回的 F 统计值的比值可用来判断模型的置信度ssreg 回归平方和ssresid 残差平方和4.3 Excel 中的有关预测函数及其应用(2)4.3.1 LINEST函数LINEST 函数的功能是使用最小二乘法计算对已知数据进行最佳线性拟合的直线方程，并返回描述此线性模型的数组。因为此函数返回数值为数组，故必须以数组公式的形式输入。函数公式为= LINEST（known_ys，known_xs，const，stats）下面举例说明 LINEST 函数的应用。1一元线性回归分析LINEST 函数可用于一元线性回归分析，也可以用于多元线性回归分析，以及时间数列的自回归分析。当只有一个自变量 x （即一元线性回归分析）时，可直接利用下面的公式得到斜率和 y 轴的截距值以及相关系数： 斜率：INDEX（LINEST（known_ys，known_xs）,1,1）；或INDEX（LINEST（known_ys,known_xs）,1）截距：INDEX（LINEST（known_ys，known_xs）,1,2）；或INDEX（LINEST（known_ys,known_xs）,2）相关系数：INDEX（LINEST（known_ys,known_xs,true,true）,3,1）【例 4-1】某企业 19 月份的总成本与人工小时及机器工时的数据如图 4-1 所示。假设总成本与人工小时之间存在着线性关系，则在单元格 B13 中插入公式“=INDEX(LINEST(B2:B10,D2:D10),2)”，在单元格 B14 插入公式“=INDEX(LINEST(B2:B10,D2:D10),1)”，在单元格 B15 插入公式“=INDEX(LINEST(B2:B10,D2:D10,TRUE,TRUE),3,1)”，即得总成本与人工小时的一元线性回归分析方程为：Y=562.72756+4.41444X 1，相关系数为 R2=0.99801，如图 4-1 所示。图 4-1 一元线性回归分析2多元线性回归分析仍以例 4-1 的数据为例，首先选取单元格区域 A17:D21，再以数组公式方式输入公式“=LINEST(B2:B10,C2:D10,TRUE,TRUE)”，即得该二元线性回归的有关参数如图 4-2 所示，从而得到：图 4-2 二元线性回归分析回归方程：Y = 471.4366+3.6165X 1+3.4323X2相关系数：R 2 =0.9990标准差：Se y =11.7792。4.3.2 LOGEST函数LOGEST 函数的功能是在回归分析中，计算最符合观测数据组的指数回归拟合曲线，并返回描述该指数模型的数组。由于这个函数返回一个数组，必须以数组公式输入。LOGEST 函数的公式为= LOGEST(known_ys,known_xs,const,stats) 【例 4-2】某企业 12 个月某产品的生产量（X）与生产成本（Y）的有关资料如图 4-3 所示，假设它们之间有如下关系： 。选取单元格区域 B15:C18，输入公式“=LOGEST(C2:C13,B2:B13,TRUE,TRUE)”（数组公式输入），即得回归参数，如图 4-3 所示，参数 m=0.8887，参数 b=1891.7729，生产成本与生产量的回归曲线为：Y=1791.77290.8887X，相关系数 R2=0.95885。图 4-3 指数回归回归方程的系数及相关系数也可以利用下面的公式直接计算参数 m：INDEX(LOGEST(C2:C13,B2:B13),1)=0.8887参数 b：INDEX(LOGEST(C2:C13,B2:B13),1,2)=1791.7729相 关 系 数 R2： =INDEX(LOGEST(C2:C13,B2:B13,TRUE,TRUE),3,1)= 0.958854.3.3 TREND函数TREND 函数的功能是返回一条线性回归拟合线的一组纵坐标值（y 值），即找到适合给定的数组 known_ys 和 known_xs 的直线（用最小二乘法），并返回指定数组 new_xs 值在直线上对应的 y 值。TREND 函数的公式为= TREND(known_ys,known_xs,new_xs,const)式中 new_xs 需要函数 TREND 返回对应 y 值的新 x 值。 new_xs 与 known_xs 一样，每个独立变量必须为单独的一行（或一列）。因此，如果 known_ys 是单列的，known_xs 和 new_xs 应该有同样的列数，如果 known_ys 是单行的，known_xs 和 new_xs 应该有同样的行数。如果省略 new_xs，将假设它和 known_xs 一样。【例 4-3】某企业过去一年的销售量为下列数据：300，356，374，410，453，487，501，534，572，621，650，670，将它们保存在单元格A1:A12 中，则下一年的 1、2、3 月的销售量预测步骤为：选中单元格区域 B1:B3，输入公式“=TREND(A1:A12,13;14;15)”（数组公式输入），即得来年的 1、2、3 月份的销售量分别为 710、743 和 777。这个公式默认1;2;3;4;5;6;7;8;9;10;11;12作为 known_xs 的参数，故数组13;14;15就对应其后的 3 个月份。4.3 Excel 中的有关预测函数及其应用(3)4.3.4 GROWTH函数GROWTH 函数的功能是返回给定的数据预测的指数增长值。根据已知的 x 值和 y 值，函数GROWTH 返回一组新的 x 值对应的 y 值。可以使用 GROWTH 工作表函数来拟合满足给定 x 值和 y值的指数曲线。GROWTH 函数的公式为= GROWTH(known_ys,known_xs,new_xs,const)式中，各参数的含义同 TREND 函数。但需注意的是，如果 known_ys 中的任何数为零或为负，函数 GROWTH 将返回错误值 #NUM!。 【例 4-4】以例 4-3 的资料为例，利用 GROWTH 函数预测来年的 1、2、3 月的销售量。预测步骤为：选中单元格区域 B1:B3，输入公式“=GROWTH(A1:A12,13;14;15)”（数组公式输入），即得来年的 1、2、3 月份的销售量分别为 756、811 和 870。这个公式同样默认1;2;3;4;5;6;7;8;9;10;11;12作为 known_xs 的参数，故数组13;14;15就对应后面的 3个月份。4.3.5 FORECAST函数FORECAST 函数的功能是根据给定的数据计算或预测未来值。此预测值为基于一系列已知的 x 值推导出的 y 值。以数组或数据区域的形式给定 x 值和 y 值后，返回基于 x 的线性回归预测值。FORECAST 函数的计算公式为 a+bx式中， ； 。FORECAST 函数的公式为= FORECAST(x,known_ys,known_xs)式中 x需要进行预测的数据点。需要说明的是： 如果 x 为非数值型，函数 FORECAST 返回错误值 #VALUE!。如果 known_ys 和 known_xs 为空或含有不同数目的数据点，函数 FORECAST 返回错误值 #N/A。如果 known_xs 的方差为零，函数 FORECAST 返回错误值 #DIV/0!。例如：FORECAST(30,6,7,9,15,21,20,28,31,38,40) = 10.60725。4.3.6 SLOPE函数SLOPE 函数的功能是返回根据 known_ys 和 known_xs 中的数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的垂直距离与水平距离的比值，也就是回归直线的变化率。SLOPE 函数的公式为 = SLOPE(known_ys,known_xs)说明：参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或引用参数里包含文本、逻辑值或空白单元格，这些值将被忽略。但包含零值的单元格将计算在内。如果 known_ys 和 known_xs 为空或其数据点数目不同，函数 SLOPE 返回错误值 #N/A。例如：SLOPE(2,3,9,1,8,7,5,6,5,11,7,5,4,4) = 0.305556。4.3.7 INTERCEPT函数INTERCEPT 函数的功能是利用已知的 x 值与 y 值计算直线与 y 轴的截距。截距为穿过 known_xs 和 known_ys 数据点的线性回归线与 y 轴的交点。公式为 = INTERCEPT (known_ys,known_xs)例如：INTERCEPT(2, 3, 9, 1, 8, 6, 5, 11, 7, 5) = 0.0483871。4.4 利用数据分析工具解决预测问题(1)除了利用前面介绍的几个预测函数进行回归预测分析外，我们还可以使用 Excel 的数据分析工具库提供的统计观测分析工具来解决回归预测问题。Excel 的数据分析工具库提供了 3 种统计观测分析工具，它们是移动平均法、指数平滑法和回归分析法。下面结合实例来说明这 3 种方法的具体应用。4.4.1 移动平均法【例 4-5】某企业 2000 年 12 个月的销售额如图 4-4 所示，分别按 3 期、5 期和 7 期移动平均所做的预测分析如图 4-4 中的 C4E13 区域所示。以 3 期移动平均为例为例，具体计算步骤如下：图 4-4 一次移动平均法实例（1）从【工具】菜单中选中【数据分析】命令，则弹出【数据分析】对话框，如图 4-5 所示。图 4-5 【数据分析】对话框（2）在【数据分析】对话框中的【分析工具】框中选中【移动平均】选项，则弹出【移动平均】对话框，如图 4-6 所示。图 4-6 【移动平均】对话框（ 3） 在 【 移 动 平 均 】 对 话 框 中 ， 【 输 入 区 域 】 框 中 输 入 “$B$2 $B$13”， 【 间 隔 】 框 中输 入 “3”， 【 输 出 区 域 】 框 中 输 入 “$C$2”， 最 后 选 中 【 图 表 输 出 】 选 项 ；（4）单击【确定】按钮，则运算结果就显示在单元格区域 C4:C13 中，如图 4-4 所示（图中的第 13 行预测数据即为下月即第 13 月的预测值），并自动出现输出图表，如图 4-7 所示。图 4-7 移动期数为 3 时的输出图表用同样的方法，可以分析当移动期数为 5 和 7 时的分析结果，如图 4-4 所示。4.4.2 指数平滑法【例 4-6】某企业的有关销售数据如图 4-8 所示，利用指数平滑法进行预测分析，其步骤如下：图 4-8 指数平滑法实例（1）从【工具】菜单中选中【数据分析】命令，则弹出【数据分析】对话框，在【数据分析】对话框中的【分析工具】框中选中【指数平滑】选项，则弹出【指数平滑】对话框，如图 4-9所示。图 4-9 【指数平滑】对话框（2）在【指数平滑】对话框中，【输入区域】框中输入“$B$2:$B$13”，【阻尼系数】框中输入“0.2”，【输出区域】框中输入“$C$3”，最后选中【图表输出】选项。（3）单击【确定】按钮，则运算结果就显示在单元格区域 C3:C13 中（图中的第 13 行预测数据即为下月即第 13 月的预测值），如图 4-8 所示，并自动出现输出图表，如图 4-10 所示。图 4-10 指数平滑法预测输出图（阻尼系数 0.2）用同样的方法，可以分析当阻尼系数为 0.4 和 0.6 时的分析结果如图 4-8 所示。需要注意的是，【数据分析】中的指数平滑法所使用的阻尼系数并不是 4.2.1.2 节介绍的指数平滑法预测方程中的平滑系数 ，二者的关系为：阻尼系数=1 。4.4 利用数据分析工具解决预测问题(2)4.4.3 回归法利用 Excel 的回归工具进行预测分析有两种方法：一是图表法；二是回归分析法。4.4.3.1 图表法图表法仅能解决一元线性或非线性回归问题，不能解决多元回归问题。【例 4-7】某企业连续 9 年的产品销售收入 Y（万元）与广告支出 X1（万元）和居民平均收入 X2（元）的有关数据如图 4-11 所示，则利用图表法进行回归分析，其方法和步骤如下，这里仅以销售收入 Y（万元）与广告支出 X1（万元）的一元线性关系为例：图 4-11 某企业的有关销售数据（1）选择单元格区域 B2:C10。（2）单击工具栏上的【图表导向】按钮，在【图表导向4 步骤之 1图表类型】中选“XY散点图”，其【子图表类型】选第 1 种，如图 4-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图表位置】对话框，不作任何输入，单击【完成】按钮，则在工作表上看到输出的图形，对其进行必要的调整（如坐标、字体、位置等）。（ 6） 在 系 列 【 数 据 点 】 上 的 任 一 点 上 ， 按 鼠 标 左 键 ， 使 各 数 据 点 出 现 记 号 ， 再 单 击 【 工 具栏 】 上 的 【 图 表 】 按 钮 ， 选 中 【 添 加 趋 势 线 】 项 ， 或 在 数 据 点 上 按 鼠 标 右 键 ， 选 【 添 加 趋 势线 】 项 ， 出 现 【 添 加 趋 势 线 】 对 话 框 ， 如 图 4-15 所 示 。图 4-15 【添加趋势线】对话框（7）在【添加趋势线】中的【类型】对话框中，有【线性】、【对数】、【多项式】、【乘幂】、【指数】和【移动平均】6 个选项。通过观察 XY 散点图可知，产品销售收入与广告支出之间呈明显的线性关系，故这里选【线性】。（8）在【添加趋势线】中的【选项】对话框中，勾选【显示公式】、【显示 R 平方值】，如图 4-16 所示。图 4-16 【添加趋势线】的【选项】设置（9）单击【确定】按钮，则在图形上显示出较粗的预测线、回归方程和 R 平方值，然后进行必要的调整，得到如图 4-17 的结果。图 4-17 输出图形用同样的方法还可以确定销售收入与居民平均收入的关系。4.4 利用数据分析工具解决预测问题(3)4.4.3.2 回归分析法回归分析法可以对一元线性或多元线性以及某些可以转化为线性的非线性问题进行回归分析。1线性回归【例 4-8】仍以例 4-7 的有关资料为例，回归分析的步骤如下：（1）从【工具】菜单中选中【数据分析】命令，则弹出【数据分析】对话框，在【数据分析】对话框中的【分析工具】框中选中【回归】选项，如图 4-18 所示，则弹出【回归】对话框。图 4-18 【数据分析】对话框（2）在【回归】对话框中，【Y 值输入区域】中输入“$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 的检验标准；其他统计检验也达到相应的标准。从而得到回归方程为：Y=229.8409+9.2794X1+0.0082 X2。图 4-20 回归分析的计算机输出当自变量只有一个（即一元回归）时，上述方法同样适用。2非线性回归对于某些可以化为线性关系的非线性问题，同样可以进行回归分析。举例如下。【例 4-9】某地区科研系统近 10 年的净收入 Y（千万元）与研究经费 X1（千万元）和研究人员数 X2（万人）的统计资料，如图 4-21 所示，假设它们之间存在着以下的函数关系：式中， a、 b、 c 为待估计参数。若利用回归工具求解此类非线性问题，解决的办法是将此方程进行数学变换，即对方程两边取对数，得 ，将各个观测值进行变换，如图 4-21 所示，即在单元格 E2:E11 中输入公式“=LN(B2 :B11)”（数组公式输入），然后将单元格 E2:E11 复制到单元格 F2:F11 和 G2:G11 中。图 4-21 某地区科研系统有关资料再 对 变 换 后 的 数 据 利 用 Excel 的 回 归 工 具 进 行 回 归 分 析 ， 具 体 步 骤 可 参 阅 【 例 4-7】 ， 其 中【 Y 值 输 入 区 域 】 中 输 入 “$E$1:$E$11”， 在 【 X 值 输 入 区 域 】 中 输 入 “$F$1:$G$11”， 在【 输 出 选 项 】 中 勾 选 【 输 出 区 域 】 ， 填 入 “$A$12”， 得 到 如 图 4-22 所 示 的 分 析 结 果 ， 最 后得 到 ： a = e0.08214 = 0.9211， b =0.4477， c = 0.6046， 相 关 系 数 为 0.9808（ 注 意 此 相 关系 数 是 变 换 后 的 线 性 方 程 的 相 关 系 数 ， 并 不 是 原 非 线 性 方 程 的 相 关 系 数 ） ， 回 归 方 程 为 ：。图 4-22 回归分析结果4.5 利用规划求解工具解决预测问题虽然我们可以利用 Excel 提供的各种预测分析工具解决大多数财务预测中的实际问题，但这些预测分析工具并不是万能的，其预测误差也随着实际问题的复杂化而增大。比如对于一些非线性预测问题，常常是将其通过变量替换而转换为线性问题。但是，这种变换过程一方面增加了计算工作量，另一方面也可能导致分析精度下降，因为变换后的数据容易使观测数据的性质发生变化，导致自变量与因变量之间的关系发生扭曲，从而影响回归方程的精度，因此，这种将非线性转换为线性的做法是存在一定的缺陷的。此外，有些非线性问题根本无法直接转换为线性问题，除非作出大量的简化，这必然使得到的回归方程严重失真。因此，对于非线性回归问题，最好的方法是直接进行回归分析，即求解使残差平方和最小、或使相关系数最大的回归方程，但非线性回归过程是一个循环寻优过程，需要先设置回归方程系数的初值，然后计算观测值与预测值的残差平方和，不断寻找使残差平方和最小的回归方程系数，这实际上是一个优化问题，因此，可以利用 Excel 的规划求解工具求解非线性回归问题，当然也可以用来求解线性回归问题。在 利 用 规 划 求 解 工 具 直 接 求 解 非 线 性 回 归 问 题 时 ， 需 要 使 用 以 下 几 个 计 算 公 式 ：自由度 df 为式中， n 为观测次数； m 为待估计参数个数。残差平方和 SE 为式中， Yi、 分别为第 i 个观测值和预测值（ i =1,2,n）。剩余标准差 SS 为相关系数 R2为式中， 为观测值的平均值。下面结合实例说明在 Excel 上进行非线性回归的具体方法和步骤。【例 4-10】以例 4-9 的有关资料为例，利用 Excel 的规划求解工具来求解非线性回归问题的方法和步骤如下：（1）如图 4-23 所