excel在财务预测中的应用.doc

第4章 Excel在财务预测中的应用4.1 财务预测概述 财务预测，是指对企业未来的收入、成本、利润、现金流量及融资需求等财务指标所作的估计和推测。财务预测是编制投资和融资计划的基础，是公司制订成长战略的基本要素。称职的财务管理人员应该能够充分利用公司的有关信息资料，预测公司的财务需要并做出相应的安排。公司成长主要由销售增长来决定，销售增长需要相应的资产增长，如果企业已经是满负荷运转，不仅流动资产、而且固定资产都要增长，而资产增长需要相应的融资增长。同时，企业进行对外投资和调整资本结构，也需要筹措资金。企业所需要的这些资金，一部分来自企业内部，另一部分通过外部融资取得。由于对外融资时，企业不但需要寻找资金提供者，而且还需做出还本付息的承诺或提供企业盈利前景等信息，使资金提供者确信其投资是安全的并可获利，这个过程往往需要花费较长的时间。因此，企业需要预先知道自身的财务需求，确定资金的需要量，提前安排融资计划，以免影响资金周转。财务预测有助于改善企业的投资决策。虽然投资是决定筹资与否和筹资多少的重要因素，但是根据销售前景估计出的融资需求，并不一定能够得到全部满足。这时，就需要根据可能筹措到的资金来安排销售增长以及有关的投资项目，使投资决策建立在可行的基础上。财务预测一般按以下几个步骤进行。1销售预测销售预测是指根据市场调查所得到的有关资料，通过对有关因素的分析研究，预计和测算特定产品在未来一定时期内的市场销售量水平及变化趋势，进而预测企业产品未来销售量的过程。企业的一切财务需求都可以看作是因销售引起的，销售量的增减变化，将会引起库存量、现金流量、应收与应付账款以及公司其他资产和负债的变化。因此销售预测在企业预测系统中处于先导地位，它对于指导利润预测、成本预测和资金预测，进行长短期决策，安排经营计划，组织生产等都起着重要的作用。2估计收入、费用和利润收入和费用与销售量之间也存在一定的函数关系，因此，可以根据销售数据估计收入和费用，并确定净利润。净利润和股利支付率，共同决定了内部留存收益所能提供的资金数额。3估计需要的资产资产通常是销售收入的函数，根据历史数据可以分析出二者之间的函数关系。根据预计销售收入和资产与销售之间的函数关系，可以预测所需资产的总量。某些流动负债也是销售收入的函数，相应地也可以预测负债的自发增长额，这种增长可以减少企业外部融资的数额。4估计所需融资根据预计资产总量，减去已有的资金来源、负债的自发增长和内部提供的留存收益，可得出所需的外部融资数额。第4章 Excel在财务预测中的应用4.2 财务预测的分析方法 预测分析的方法有很多种，企业应根据不同的需要选择不同的预测方法。总的来说，预测分析方法可分为两大类：定量预测法和定性预测法。4.2.1 定量预测法定量预测法是指在掌握与预测对象有关的各种要素的定量资料的基础上，运用现代数学方法进行数据处理，从而建立起能够反映有关变量之间关系的各类预测模型的方法。在财务预测中，经常使用的定量预测法主要有以下几种。4.2.1.1 移动平均法移动平均法是一种改良的算术平均法，是一种最简单的自适应预测模型。它根据近期数据对预测值影响较大，而远期数据对预测值影响较小的事实，把平均数逐期移动。移动期数的大小视具体情况而定，移动期数少，能快速地反映变化，但不能反映变化趋势；移动期数多，能反映变化趋势，但预测值带有明显的滞后偏差。常用的移动平均法主要有一次移动平均法和二次移动平均法。1一次移动平均法一次移动平均法是根据时间序列，逐期移动，依次计算包含一定项数的时间序列平均数，形成一个平均时间数序列，并据此进行预测。预测模型为式中第t+1期的预测值；、将被平均的n个观测值；n移动平均的项数，即移动期数。在实际预测中，可以多取几个n数，并将得到的预测值与实际值进行比较，选用误差最小的n值。2二次移动平均法二次移动平均法是对时间序列计算一次移动平均数后，再对一次移动平均数序列进行一次移动平均运算。预测模型为。式中二次移动平均数；第t+1期的预测值，即。二次移动平均法解决了一次移动平均法只能预测下一期的局限性，它可以进行近、短期的预测。但它仍不能解决中长期的预测问题。4.2.1.2 指数平滑法指数平滑法实际上也是一种加权平均法，是一种改良的加权平均法，预测模型为式中 平滑系数，01。在指数平滑法中，确定合适的值和初始值是非常重要的。越大，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章 Excel在财务预测中的应用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，则函数返回附加回归统计值，这时返回的数组为 mn，mn-1，.，m1，b；sen，sen-1，.，se1，seb，r2，sey；F，df；ssreg，ssresid。如果 stats为FALSE或省略，函数只返回系数预测模型的待估计参数m、mn、mn-1、.、m1和b。附加回归统计值返回的顺序见表4-2。表4-2中的各参数说明见表4-3。如果要得到附加回归统计值数组中的值，需用INDEX函数将其取出表4-2 附加回归统计值返回的顺序1234561mnmn-1m2m1b2sensen-1se2se1se b3r2sey4Fdf5ssregssresid表4-3 各参数说明参数说明se1,se2,.,sen系数 m1,m2, .,mn 的标准误差值Seb常数项 b 的标准误差值（当 const 为 FALSE时，seb = #N/A ）参数说明r2相关系数，范围在 0 到 1 之间。如果为 1，则样本有很好的相关性，Y 的估计值与实际值之间没有差别。反之，如果相关系数为 0，则回归方程不能用来预测 Y 值seyY 估计值的标准误差FF 统计值或F 观察值。使用F 统计可以判断因变量和自变量之间是否偶尔发生过观察到的关系Df自由度。用于在统计表上查找 F 临界值。所查得的值和函数 LINEST 返回的F统计值的比值可用来判断模型的置信度ssreg回归平方和ssresid残差平方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.41444X1，相关系数为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.6165X1+3.4323X2相关系数：R2 =0.9990标准差：Sey =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.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章 Excel在财务预测中的应用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节介绍的指数平滑法预测方程中的平滑系数a，二者的关系为：阻尼系数=1a。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.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 = e-0.08214 = 0.9211，b =0.4477，c = 0.6046，相关系数为0.9808（注意此相关系数是变换后的线性方程的相关系数，并不是原非线性方程的相关系数）， 回归方程为：。图4-22 回归分析结果第4章 Excel在财务预测中的应用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所示，单元格G2:G4为变动单元格，分别存放待估计参数a、b、c，其初值可设为0。（2）在单元格E2:E11中输入预测值公式“=G2*(C2:C11)G3*(D2:D11)G4”（数组公式输入）。图4-23 利用规划求解工具进行非线性回归分析（3）在单元格G5中输入观测值的平均值公式“=AVERAGE(B2:B11)”； 在单元格G6中输入自由度公式“=COUNT(B2:B11)-COUNT(G2:G4)”；在单元格G7中输入残差平方和公式“=SUM(B2:B11-E2:E11)2)”（数组公式输入）；在单元格G8中输入剩余标准差公式“=SQRT(G7/G6)”；在单元格G9中输入相关系数R2的计算公式“=1-G7/SUM(B2:B11-G5)2)”（数组公式输入）。（4）单击EXCEL工具菜单，选择【规划求解】项，出现【规划求解参数】对话框；（5）在【规划求解参数】对话框中，【设置目标单元格】设置为单元格“$G$7”，即目标函数为残差平方和；【等于】设置为“最小”；【可变单元格】设置为“$G$2:$G$4”。然后单击【求解】，即可得到回归方程的系数a、b、c，出现“规划求解结果”对话框，然后单出【确定】按钮，保存规划求解结果。需要注意的是，若系数a、b、c的初值设置不合适的话，则一次求解过程（即在Excel上进行【工具】【规划求解】 【求解】 【确定】这样一个求解过程）可能得不到最优结果（或得不到解），这时需要进行多次求解，即在第一次求解结果的基础上，再进行第二次求解，得到第二次求解结果，然后在第二次求解结果的基础上，再进行第三次求解，得到第三次求解结果，如此继续下去，直到求出的系数a、b、c的值不再变化、且残差平方和最小为止，即得到最优结果。在上例中，当a、b、c的初始值设为0时，经过2次求解过程即得到最优结果，如图4-23所示。若采用非线性转换为线性的方法，如例4-9所示，可得到有关系数如图4-22所示，将此系数代入回归方程，计算不同X1和X2下的预测值，进而计算出残差平方和与剩余标准差分别为157.1139和4.7376，与图4-23的直接非线性回归的结果（残差平方和154.03、剩余标准差4.6909）进行比较，可见非线性转换为线性的方法得出的结果误差要大于直接进行非线性回归的误差。第4章 Excel在财务预测中的应用4.6 销售预测 销售预测的准确程度，对企业的兴衰成败会产生很重要的影响。销售预测比较准确，会使企业在有计划的财务安排下顺利运作；而如果销售预测与实际情况偏离很远，则会使企业遇到麻烦，甚至陷入困境。因此，销售预测是企业进行财务预测的首要工作，是企业制定财务计划的基础。 销售预测主要应根据市场需求的变化，结合企业的利润目标、实现企业市场份额的目标，并综合考虑企业内外部的各种限制条件的影响来进行。一般情况下，可首先分别对未来各期的销售量和销售价格进行预测，在此基础上，根据预测的产品销售价格乘以预测的销售量得到预测的销售额；也可以直接根据销售额的有关历史资料，采用适当的方法进行预测。4.6.1 销售预测的基本方法销售预测是一项比较复杂的工作，需要考虑的因素很多，作出准确的预测是非常困难的。通常可利用企业过去的数据进行统计分析，并结合经济环境对未来市场的影响以及企业内外部各种条件的限制，作出销售预测。进行销售预测的方法很多，常用的方法包括以下几种。1时间序列预测法时间序列预测法，是指将观察或记录的一些历史数据，按时间的先后排列成数据系列，进行统计分析，找出过去长期的销售量或销售额的增减变化趋势，再根据此变化趋势分析的结果，预测未来时期的销售量或销售额。常见的时间序列的预测方法有简单平均法、移动平均法、指数平滑法、或以时间为自变量的回归分析法等，这些方法的基本原理可参阅前面的有关内容。2因果关系预测法因果关系预测法，是指利用有关因素与产品销售量或销售额之间的固有因果关系，通过建立一定的数学模型来预测企业未来的产品销售水平的一种方法。企业产品销售水平的高低，往往受到诸多宏观或微观、外部或内部、客观或主观等因素的影响，通常可以通过回归分析的方法检验出哪些因素与销售水平之间具有因果关系，在此基础上可建立回归方程，进行销售预测。有关如何建立回归方程及进行相关检验的方法可参阅前面的有关内容。3通过生产能力或订货合同进行销售量（销售额）预测企业生产的产品如果在市场占有稳定的份额或供不应求，则可按本企业的生产能力预测产品的销售量，计算公式如下：计划期销售量=计划期初库存量+计划期预计生产量-计划期末预计库存量4.6.2 销售预测模型及其应用在很多情况下，通过建立企业的销售预测模型，可以很方便地实现销售预测。下面介绍两个销售预测模型。4.6.2.1 一元线性（非线性）回归预测模型【例4-11】根据图4-24中所给的资料建立一元线性（非线性）回归预测模型。图4-24 一元线性（非线性）回归销售预测模型下面利用线性回归中的LINEST函数和指数回归中的LOGEST函数，来建立一元线性（非线性）回归预测模型。（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(销售序列,影响因素序列,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,TRUE),3,1)”，计算相关系数R2。（7）在单元格J8:M8中输入公式“=IF(B7=1,E8+F8*J7:M7,E8*F8J7:M7)”（数组公式输入），计算未来第14期的预测值。在影响因素和销售量两列输入历史数据，并在J7:M7中输入未来4期的影响因素预测数值后，即可得到回归预测模型及未来的预测值。通过选择不同的回归模型，可以分别计算一元线性模型和一元指数模型下的回归结果及预测值。由计算结果可知，采用指数模型（相关系数为0.9742）要比线性模型（相关系数为0.9452）更为准确。4.6.2.2 多元线性回归预测模型【例4-12】根据图4-25中所给的资料建立多元线性回归预测模型。图4-25 多元线性回归销售预测模型当影响销售量（额）的因素不止一个时，就需要建立多元线性回归模型。下面就二元线性回归预测模型的建立进行说明，对于影响因素在两个以上的情况，可参照本模型建立。（1）首先建立销售预测模型，如图4-25所示，这里以过去12期的历史数据为依据（可以是按年计算，也可以是按月计算，图4-25为按月计算）。（2）选取单元格区域B3:M4，单击【插入】【名称】【定义】命令，或直接单击编辑栏中的名称框，将影响因素所在的单元格区域B3:M4定义为“影响因素序列”；用同样的方法，将销售额所在的单元格区域B5:M5定义为“销售序列”。（3）在单元格D9中输入公式“=INDEX(LINEST(销售序列,影响因素序列,TRUE,TRUE),1,3)”，计算系数A。（4）在单元格E9中输入公式“=INDEX(LINEST(销售序列,影响因素序列,TRUE,TRUE),1,2)”，计算系数B。（5）在单元格F9中输入公式“=INDEX(LINEST(销售序列,影响因素序列,TRUE,TRUE),1,1)”，计算系数C。（6）在单元格G9中输入公式“=INDEX(LINEST(销售序列,影响因素序列,TRUE,TRUE),3,1)”，计算相关系数R2。（7）在单元格J9:M9中输入公式“=D9+E9*J7:M7+F9*J8:M8”（数组公式输入），计算未来第14期的预测值。在影响因素和销售额各列输入历史数据，并在J7:M8中输入未来4期的影响因素预测数值后，即可得到回归预测模型及未来的预测值，如图4-25所示第4章 Excel在财务预测中的应用4.7 成本预测 4.7.1 成本预测的方法成本是指企业为生产和销售产品所花费的全部费用。成本可以按很多不同的标准进行分类，如常见的按经济职能分类和按成本性态分类：成本按经济职能划分可分为生产成本和非生产成本两大类。生产成本又称制造成本，包括生产过程中发生的直接材料、直接人工和制造费用三个项目；非生产成本又称非制造成本，包括为销售产品所花费的销售费用和为组织企业的生产所花费的管理费用。成本性态又称成本习性，是指成本总额对业务量（如产销量）总数的依存关系。按成本性态可将全部成本分为变动成本、固定成本和混合成本三类。变动成本是指总额与业务量总数成正比例变动关系的成本，如直接材料、直接人工、变动性制造费用等。固定成本是指在一定的业务量范围内，总额不受业务量增减变动影响的成本，如按直线法计提的固定资产折旧费、管理人员工资等。混合成本是指总额随业务量总数发生变化、但不成正比例变动的成本，通过采用适当的方法可以将混合成本分解为变动成本和固定成本两大类。成本预测是根据企业未来的发展目标和现实条件，参考其他资料，利用专门的方法对企业未来成本水平及其变动趋势进行估算和预测。成本预测可为成本决策和实施成本控制提供有用的信息。成本预测的方法主要有：（1）历史成本法。这种方法主要是根据成本的历史资料来预测未来的成本水平，常用的方法有高低点法和回归分析法。（2）目标利润推算法。这种方法主要是根据有关的经济预测（销售量、销售价格）和企业的目标利润等数据，来反算成本的一种方法。（3）因素分析法。这种方法是根据预期影响成本的各种因素的变化来预测未来成本，它需要企业拥有较为详细的资料。（4）比例推算法。这种方法是利用企业的生产消耗与企业有关重大经营成果指标之间的依存关系，按被确认的报告年度成本与这些指标的比例关系推算预测期的成本水平。这种方法只是一种大概的推算方法。上述的成本预测方法中，回归分析法较为科学也比较常用。下面主要介绍在Excel上利用回归分析法进行成本预测的具体方法。4.7.2 成本预测模型利用回归分析法进行成本预测首先需要建立回归分析模型。回归分析模型就是将总成本分解成与销售量无关的固定成本和与销售量有关的变动成本，从而根据未来的预计销售量对成本进行预测。模型的数学表达式如下：一元一次模型（线性模型）： 销售成本=固定成本+单位变动成本销售量 一元二次模型（非线性模型）：销售成本=固定成本+单位变动成本销售量+混合成本销售量平方【例4-13】建立一元一次（二次）成本预测模型。在Excel上建立销售成本的回归分析模型方法可参阅前面介绍的回归分析方法，具体步骤如下：（1）首先建立成本预测模型，如图4-26所示，这里假设已有过去12期的数据为历史数据；（2）建立线性或非线性模型选择控件，控件的数据源区域为$A$8:$A$9，单元格链接为$B$8，下拉显示项数为2。（3）在单元各B4:M4中输入销售量平方计算公式“=B3:M32”（数组公式输入），以准备进行一元二次回归分析。图4-26 销售成本预测模型（4）在单元格E9中输入公式：“=IF(B8=1,INDEX(LINEST(B5:M5,B3:M3,TRUE,TRUE),1,2),INDEX(LINEST(B5:M5,B3:M4,TRUE,