Excel在财务预测中的应用

1、第4章 Exccel在财务务预测中的应应用4.1 财务预预测概述 财务预测，是是指对企业未未来的收入、成成本、利润、现现金流量及融融资需求等财财务指标所作作的估计和推推测。财务预预测是编制投投资和融资计计划的基础，是是公司制订成成长战略的基基本要素。称称职的财务管管理人员应该该能够充分利利用公司的有有关信息资料料，预测公司司的财务需要要并做出相应应的安排。公公司成长主要要由销售增长长来决定，销销售增长需要要相应的资产产增长，如果果企业已经是是满负荷运转转，不仅流动动资产、而且且固定资产都都要增长，而而资产增长需需要相应的融融资增长。同同时，企业进进行对外投资资和调整资本本结构，也需需要筹措资金

2、金。企业所需需要的这些资资金，一部分分来自企业内内部，另一部部分通过外部部融资取得。由由于对外融资资时，企业不不但需要寻找找资金提供者者，而且还需需做出还本付付息的承诺或或提供企业盈盈利前景等信信息，使资金金提供者确信信其投资是安安全的并可获获利，这个过过程往往需要要花费较长的的时间。因此此，企业需要要预先知道自自身的财务需需求，确定资资金的需要量量，提前安排排融资计划，以以免影响资金金周转。财务预测有助于于改善企业的的投资决策。虽虽然投资是决决定筹资与否否和筹资多少少的重要因素素，但是根据据销售前景估估计出的融资资需求，并不不一定能够得得到全部满足足。这时，就就需要根据可可能筹措到的的资金来

3、安排排销售增长以以及有关的投投资项目，使使投资决策建建立在可行的的基础上。财务预测一般按按以下几个步步骤进行。1销售预测销售预测是指根根据市场调查查所得到的有有关资料，通通过对有关因因素的分析研研究，预计和和测算特定产产品在未来一一定时期内的的市场销售量量水平及变化化趋势，进而而预测企业产产品未来销售售量的过程。企企业的一切财财务需求都可可以看作是因因销售引起的的，销售量的的增减变化，将将会引起库存存量、现金流流量、应收与与应付账款以以及公司其他他资产和负债债的变化。因因此销售预测测在企业预测测系统中处于于先导地位，它它对于指导利利润预测、成成本预测和资资金预测，进进行长短期决决策，安排经经营

4、计划，组组织生产等都都起着重要的的作用。2估计收入、费费用和利润收入和费用与销销售量之间也也存在一定的的函数关系，因因此，可以根根据销售数据据估计收入和和费用，并确确定净利润。净净利润和股利利支付率，共共同决定了内内部留存收益益所能提供的的资金数额。3估计需要的的资产资产通常是销售售收入的函数数，根据历史史数据可以分分析出二者之之间的函数关关系。根据预预计销售收入入和资产与销销售之间的函函数关系，可可以预测所需需资产的总量量。某些流动动负债也是销销售收入的函函数，相应地地也可以预测测负债的自发发增长额，这这种增长可以以减少企业外外部融资的数数额。4估计所需融融资根据预计资产总总量，减去已已有的

5、资金来来源、负债的的自发增长和和内部提供的的留存收益，可可得出所需的的外部融资数数额。第4章 Exccel在财务务预测中的应应用4.2 财务预预测的分析方方法 预测分析的方法法有很多种，企企业应根据不不同的需要选选择不同的预预测方法。总总的来说，预预测分析方法法可分为两大大类：定量预预测法和定性性预测法。4.2.1 定量预测法法定量预测法是指指在掌握与预预测对象有关关的各种要素素的定量资料料的基础上，运运用现代数学学方法进行数数据处理，从从而建立起能能够反映有关关变量之间关关系的各类预预测模型的方方法。在财务预测中，经经常使用的定定量预测法主主要有以下几几种。4.2.1.11 移动平平均法移动

6、平均法是一一种改良的算算术平均法，是是一种最简单单的自适应预预测模型。它它根据近期数数据对预测值值影响较大，而而远期数据对对预测值影响响较小的事实实，把平均数数逐期移动。移移动期数的大大小视具体情情况而定，移移动期数少，能能快速地反映映变化，但不不能反映变化化趋势；移动动期数多，能能反映变化趋趋势，但预测测值带有明显显的滞后偏差差。常用的移动平均均法主要有一一次移动平均均法和二次移移动平均法。1一次移动平平均法一次移动平均法法是根据时间间序列，逐期期移动，依次次计算包含一一定项数的时时间序列平均均数，形成一一个平均时间间数序列，并并据此进行预预测。预测模模型为式中第t+1期的预测测值；、将被被

7、平均的n个观测值；n移动平均的的项数，即移移动期数。在实际预测中，可可以多取几个个n数，并将得得到的预测值值与实际值进进行比较，选选用误差最小小的n值。2二次移动平平均法二次移动平均法法是对时间序序列计算一次次移动平均数数后，再对一一次移动平均均数序列进行行一次移动平平均运算。预预测模型为。式中二次移移动平均数；第t+1期的的预测值，即即。二次移动平均法法解决了一次次移动平均法法只能预测下下一期的局限限性，它可以以进行近、短短期的预测。但但它仍不能解解决中长期的的预测问题。4.2.1.22 指数平平滑法指数平滑法实际际上也是一种种加权平均法法，是一种改改良的加权平平均法，预测测模型为式中 平滑

8、滑系数，01。在指数平滑法中中，确定合适适的值和初始始值是非常重重要的。越大大，t期的实际值值对新预测值值的贡献就越越大；越小，t期的实际值值对新预测值值的贡献就越越小。一般情情况下，可以以取几个不同同的值进行预预测，比较它它们的预测误误差，选择预预测误差最小小的值。4.2.1.33 回归分分析预测法回归分析预测法法是通过研究究两组或两组组以上变量之之间的关系，建建立相应的回回归预测模型型，对变量进进行预测的一一种预测方法法。1回归分析预预测法的基本本程序进行回归分析的的步骤如下：（1）收集有关关资料。将各各种可能的影影响因素的有有关数据尽可可能多地收集集起来。（2）判断趋势势。根据收集集到的

9、数据，判判断其变化趋趋势，从而为为建立相应的的数学模型做做准备。对于于变量不多的的问题，可以以通过绘制散散点图来判断断变化趋势。（3）建立预测测数学模型。根根据历史数据据的变化趋势势，选择相应应的描写该问问题的数学模模型，并采用用相关的计算算技术来估计计数学模型的的参数。（4）相关检验验。对建立的的预测数学模模型，必须进进行有关的检检验，主要是是通过计算预预测模型的相相关系数、方方差（或标准准差）以及显显著性等指标标，来判断预预测模型的准准确性、是否否需要修正、采采用何种方法法修正等。2回归模型建建立的方法建立回归模型的的一般方法是是采用最小二二乘法，其原原理如下：考虑m个自变量量x1、x2、

10、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下的预测测值为 ，则有：残差平方和SEE：剩余标准差SSS：相关系数R2：y为观测值yii的平均值：那么，最小二乘乘法的原理就就是寻找最优优的待估计参参数ak，使残差平平方和最小。3财务预测中中常用的几种种回归模型（1）一元线性性回归模型当只有两个变量量（一个自变变量和一个因因变量），并并且

11、它们之间间存在线性关关系时，可以以用一元线性性回归模型来来描述。一元元线性回归模模型为式中a、b回归系数，其其中a代表截距，bb代表斜率。（2）一元非线线性回归模型型当变量x和y之之间的关系不不能用线性关关系来描述时时，则需要建建立一元非线线性回归模型型。根据变量量x和y之间的关系系，一元非线线性回归模型型常见的几种种情况有：对数模型：指数模型：乘幂模型：双曲线模型：以上几种一元非非线性模型均均可通过数学学变换化成一一元线性模型型。（3）多元线性性回归模型当自变量有两个个或两个以上上，且因变量量与这些自变变量之间呈线线性组合关系系时，它们就就构成了多元元线性回归模模型，模型形形式为式中a、b1

12、1、b2、bm估计参数；x1、x2、xm自变量。（4）多元非线线性回归模型型多元非线性回归归模型用来描描述因变量与与多个自变量量之间呈非线线性组合关系系的情况。例例如，柯柏道格拉斯生生产函数就是是典型的多元元非线性模型型：式中：L和K分分别为劳动力力和固定资本本；a、b、c为系数。4.2.1.44 模拟法法在企业的实际经经济活动中，各各种经济参数数往往并不是是确定的，而而是随机变化化的，比如产产品的销售量量往往随市场场的变化而变变化，在这种种情况下，就就需要对这些些参数的不确确定性进行分分析，而对其其预测也就需需要采用与传传统的确定性性分析不同的的方法来进行行。一般情况况下，可以采采用模拟法来

13、来解决不确定定性情况下的的财务预测问问题，概率法法、蒙特卡罗罗模拟方法就就是较实用的的方法。4.2.2 定性预测法法定性预测法是由由有关方面的的专业人员或或专家根据自自己的经验和和知识，结合合预测对象的的特点进行综综合分析，对对事物的未来来状况和发展展趋势作出推推测的预测方方法。定性预预测法由于带带有较多的个个人主观性，因因而在实践中中最好作为一一种补充的预预测方法。第4章 Exccel在财务务预测中的应应用4.3 Exccel中的有有关预测函数数及其应用(1) Excel提供供了关于估计计线性模型和和指数模型参参数的几个预预测函数。线线性模型和指指数模型的数数学表达式如如下：线性模型：y =

14、 mx + b 或 y = m1x1 + m2x2 + + b指数模型：或式中，y为因变变量；x是自变量；m、m1、.、mn-1、mn、b分别为预测测模型的待估估计参数。Excel提供供的预测函数数主要有LIINEST函函数、LOGGEST函数数、TRENND函数、GROOWTH函数数、FOREECAST函函数、SLOOPE函数和和INTERRCEPT函函数，它们所所使用的参数数都基本相同同，现列于表表4-1中，以以供参考。表表4-1 预测函数的的参数及含义义参数含义known_yys因变量y的观测测值集合known_xxs自变量x的观测测值集合。它它可以是一个个变量（即一一元模型）或或多个变

15、量（即即多元模型）的的集合。如果只用到一个个变量，只要要 knowwn-ys 和 knowwn-xs 维数数相同，它们们可以是任何何形状的选定定区域。如果果用到不只一一个变量，kknown_ys 必必须是向量（也也就是说，必必须是一行或或一列的区域域）。如果省省略 knoown_xs，则假设设该数组是 1,2,3.，其大小与与 knowwn_yss 相同const逻辑值，指明是是否强制使常常数b为0（线性模型型）或为1（指数模型型）。 如果consst 为 TRUEE或省略，b将被正常计计算。如果cconst为为FALSEE，b将被设为0（线性模型型）或设为11（指数模型型）stats逻辑值，

16、指明是是否返回附加加回归统计值值。 如果 staats 为 TRUEE，则函数返返回附加回归归统计值，这这时返回的数数组为 mmn，mn-1，.，m1，b；sen，sen-1，.，se1，seb，r2，sey；F，df；ssreg，ssresiid。如果 sttats为FALSEE或省略，函函数只返回系系数预测模型型的待估计参参数m、mn、mn-1、.、m1和b。附加回归统计值值返回的顺序序见表4-22。表4-2中的各各参数说明见见表4-3。如果要得到附加加回归统计值值数组中的值值，需用INNDEX函数数将其取出表4-2 附附加回归统计计值返回的顺顺序1234561mnmn-1m2m1b2se

17、nsen-1se2se1se b3r2sey4Fdf5ssregssresidd表4-3 各各参数说明参数说明se1,se22,.,seen系数 m1,mm2, .,mmn 的标准误差差值Seb常数项 b 的的标准误差值值（当 coonst 为为 FALSSE时，seb = #NN/A ）参数说明r2相关系数，范围围在 0 到 1 之间。如如果为 1，则样本本有很好的相相关性，Y 的估计值与与实际值之间间没有差别。反反之，如果相相关系数为 0，则回归归方程不能用用来预测 YY 值seyY 估计值的标标准误差FF 统计值或FF 观察值。使使用F 统计可以以判断因变量量和自变量之之间是否偶尔尔发生

18、过观察察到的关系Df自由度。用于在在统计表上查查找 F 临界值值。所查得的的值和函数 LINESST 返回的的F统计值的比比值可用来判判断模型的置置信度ssreg回归平方和ssresidd残差平方4.3.1 LINESST函数LINEST函函数的功能是是使用最小二二乘法计算对对已知数据进进行最佳线性性拟合的直线线方程，并返返回描述此线线性模型的数数组。因为此此函数返回数数值为数组，故故必须以数组组公式的形式式输入。函数公式为= LINESST（knownn_ys，knownn_xs，constt，statss）下面举例说明LLINESTT函数的应用用。1一元线性回回归分析LINEST函函数可用

19、于一一元线性回归归分析，也可可以用于多元元线性回归分分析，以及时时间数列的自自回归分析。当只有一个自变变量 x （即一一元线性回归归分析）时，可可直接利用下下面的公式得得到斜率和 y 轴的截截距值以及相相关系数： 斜率：INDEEX（LINESST（knownn_ys，knownn_xs）,1,1）；或INDDEX（LINESST（knownn_ys,knownn_xs）,1）截距：INDEEX（LINESST（knownn_ys，knownn_xs）,1,2）；或INDEEX（LINESST（knownn_ys,knownn_xs）,2）相关系数：INNDEX（LINESST（knownn_

20、ys,knownn_xs,true,true）,3,1）【例4-1】某某企业19月份的总成成本与人工小小时及机器工工时的数据如如图4-1所示。假假设总成本与与人工小时之之间存在着线线性关系，则则在单元格BB13中插入入公式“=INDEEX(LINNEST(BB2:B10,DD2:D10),2)”，在单元格格B14插入公公式“=INDEEX(LINNEST(BB2:B10,DD2:D10),1)”，在单元格格B15插入公公式“=INDEEX(LINNEST(BB2:B10,DD2:D10,TTRUE,TTRUE),3,1)”，即得总成成本与人工小小时的一元线线性回归分析析方程为：YY=562.7

21、27566+4.411444X11，相关系数数为R2=0.999801，如如图4-1所示。图4-1 一一元线性回归归分析2多元线性回回归分析仍以例4-1的的数据为例，首首先选取单元元格区域A117:D21，再以以数组公式方方式输入公式式“=LINEEST(B22:B10,CC2:D10,TTRUE,TTRUE)”，即得该二二元线性回归归的有关参数数如图4-22所示，从而而得到：图4-2 二二元线性回归归分析回归方程：Y = 4711.43666+3.61165X1+3.43323X2相关系数：R22 =0.99990标准差：Seyy =11.7792。4.3.2 LOGESST函数LOGEST

22、函函数的功能是是在回归分析析中，计算最最符合观测数数据组的指数数回归拟合曲曲线，并返回回描述该指数数模型的数组组。由于这个个函数返回一一个数组，必必须以数组公公式输入。LOGEST函函数的公式为为= LOGESST(knoown_ys,knoown_xs,connst,sttats) 【例4-2】某某企业12个月某产产品的生产量量（X）与生产成成本（Y）的有关资资料如图4-3所示，假假设它们之间间有如下关系系：。选取单单元格区域BB15:C18，输入入公式“=LOGEEST(C22:C13,BB2:B13,TTRUE,TTRUE)”（数组公式式输入），即即得回归参数数，如图4-3所示，参参数m

23、=0.8887，参参数b=18891.77729，生产产成本与生产产量的回归曲曲线为：Y=1791.77290.88887X，相关系数数R2=0.955885。图4-3 指指数回归回归方程的系数数及相关系数数也可以利用用下面的公式式直接计算参数m：INDDEX(LOOGEST(C2:C13,BB2:B13),1)=0.8887参数b：INDDEX(LOOGEST(C2:C13,BB2:B13),1,2)=1791.7729：EGCBTT,=54.3.3 TRENDD函数TREND函数数的功能是返返回一条线性性回归拟合线线的一组纵坐坐标值（y 值），即找找到适合给定定的数组 kknown_ys

24、和和 knowwn_xss 的直线（用用最小二乘法法），并返回回指定数组 new_xxs 值在在直线上对应应的 y 值。TREND函数数的公式为= TRENDD(knowwn_yss,knowwn_xss,new_xs,cconst)式中 neww_xs 需要函数 TTREND 返回对应 yy 值的新 x 值。 neww_xs 与 knowwn_xss 一样，每每个独立变量量必须为单独独的一行（或或一列）。因因此，如果 knownn_ys 是单列的，kknown_xs 和和 new_xs 应应该有同样的的列数，如果果 knowwn_yss 是单行的的，knowwn_xss 和 new_xs 应

25、应该有同样的的行数。如果果省略 neew_xss，将假设它它和 knoown_xs 一样。【例4-3】某某企业过去一一年的销售量量为下列数据据：3000，356，374，410，453，487，501，534，572，621，650，670，将将它们保存在在单元格A11:A12中，则则下一年的11、2、3月的销售量量预测步骤为为：选中单元元格区域B11:B3，输入公公式“=TRENND(A1:A12,13;114;15)”（数组公式式输入），即即得来年的11、2、3月份的销售售量分别为7710、743和777。这个个公式默认1;2;33;4;5;6;7;88;9;100;11;112作为kno

26、wwn_xss的参数，故故数组133;14;115就对应应其后的3个月份。4.3.4 GROWTTH函数GROWTH函函数的功能是是返回给定的的数据预测的的指数增长值值。根据已知知的x值和y值，函数GRROWTH返返回一组新的的x值对应的y值。可以使使用GROWWTH工作表表函数来拟合合满足给定xx值和y值的指数曲曲线。GROWTH函函数的公式为为= GROWTTH(knoown_ys,knoown_xs,neww_xs,constt)式中，各参数的的含义同TRREND函数数。但需注意意的是，如果果knownn_ys中中的任何数为为零或为负，函函数 GROOWTH将返返回错误值 #NUM!。

27、【例4-4】以以例4-3的资料料为例，利用用GROWTTH函数预测测来年的1、2、3月的销售量量。预测步骤骤为：选中单单元格区域BB1:B3，输入公公式“=GROWWTH(A11:A12,13;114;15)”（数组公式式输入），即即得来年的11、2、3月份的销售售量分别为7756、811和870。这个个公式同样默默认1;22;3;4;5;6;77;8;9;10;111;12作作为knowwn_xss的参数，故故数组133;14;115就对应应后面的3个月份。4.3.5 FORECCAST函数数FORECASST函数的功功能是根据给给定的数据计计算或预测未未来值。此预预测值为基于于一系列已知知

28、的 x 值推导导出的 y 值。以数组组或数据区域域的形式给定定 x 值和 y 值后，返返回基于 xx 的线性回回归预测值。FORECAST函数的计算公式为 a+bx式中，；。FORECASST函数的公公式为= FORECCAST(xx,knowwn_yss,knowwn_xss)式中x需要要进行预测的的数据点。需要说明的是： 如果 x 为非非数值型，函函数 FORRECASTT 返回错误误值 #VAALUE!。如果 knowwn_yss 和 knowwn_xss 为空或含含有不同数目目的数据点，函函数 FORRECASTT 返回错误误值 #N/A。如果 knowwn_xss 的方差为为零，函数

29、 FORECCAST 返返回错误值 #DIV/0!。例如：FOREECAST(30,66,7,9,15,211,200,28,331,38,40) = 10.607255。4.3.6 SLOPEE函数SLOPE函数数的功能是返返回根据 kknown_ys 和和 knowwn_xss 中的数据据点拟合的线线性回归直线线的斜率。斜斜率为直线上上任意两点的的垂直距离与与水平距离的的比值，也就就是回归直线线的变化率。SLOPE函数数的公式为 = SLOPEE(knowwn_yss,knowwn_xss)说明：参数可以以是数字，或或者是涉及数数字的名称、数数组或引用。如如果数组或引引用参数里包包含文本、

30、逻逻辑值或空白白单元格，这这些值将被忽忽略。但包含含零值的单元元格将计算在在内。如果 knownn_ys 和 knowwn_xss 为空或其其数据点数目目不同，函数数 SLOPPE 返回错错误值 #NN/A。例如：SLOPPE(2,3,9,11,8,7,5,66,5,111,7,5,4,4) = 0.3055556。4.3.7 INTERRCEPT函函数INTERCEEPT函数的的功能是利用用已知的 xx 值与 y 值计算算直线与 yy 轴的截距距。截距为穿穿过 knoown_xs 和 knowwn_yss 数据点的的线性回归线线与 y 轴的交交点。公式为 = INTERRCEPT (know

31、wn_yss,knowwn_xss)例如：INTEERCEPTT(2, 3, 9, 1, 88, 66, 5, 11, 77, 5) = 0.04838871。第4章 Exccel在财务务预测中的应应用4.4 利用数数据分析工具具解决预测问问题(1) 除了利用前面介介绍的几个预预测函数进行行回归预测分分析外，我们们还可以使用用Excell的数据分析析工具库提供供的统计观测测分析工具来来解决回归预预测问题。Excel的数数据分析工具具库提供了33种统计观测测分析工具，它它们是移动平平均法、指数数平滑法和回回归分析法。下下面结合实例例来说明这33种方法的具具体应用。4.4.1 移动平均法法【例4-

32、5】某某企业20000年12个月的销销售额如图44-4所示，分分别按3期、5期和7期移动平均均所做的预测测分析如图44-4中的C4E13区域所所示。以3期移动平均均为例为例，具具体计算步骤骤如下：图4-4 一一次移动平均均法实例（1）从【工具具】菜单中选选中【数据分分析】命令，则则弹出【数据据分析】对话话框，如图44-5所示。图4-5 【数数据分析】对对话框（2）在【数据据分析】对话话框中的【分分析工具】框框中选中【移移动平均】选选项，则弹出出【移动平均均】对话框，如如图4-6所示。图4-6 【移移动平均】对对话框（3）在【移动动平均】对话话框中，【输输入区域】框框中输入“$B$2$B$133

33、”，【间隔】框框中输入“3”，【输出区区域】框中输输入“$C$2”，最后选中中【图表输出出】选项；（4）单击【确确定】按钮，则则运算结果就就显示在单元元格区域C44:C13中，如如图4-4所示（图图中的第133行预测数据据即为下月即即第13月的预测测值），并自自动出现输出出图表，如图图4-7所示。图4-7 移动动期数为3时的输出图图表用同样的方法，可可以分析当移移动期数为55和7时的分析结结果，如图44-4所示。4.4.2 指数平滑法法【例4-6】某某企业的有关关销售数据如如图4-8所示，利利用指数平滑滑法进行预测测分析，其步步骤如下：图4-8 指数数平滑法实例例（1）从【工具具】菜单中选选中

34、【数据分分析】命令，则则弹出【数据据分析】对话话框，在【数数据分析】对对话框中的【分分析工具】框框中选中【指指数平滑】选选项，则弹出出【指数平滑滑】对话框，如如图4-9所示。图4-9 【指指数平滑】对对话框（2）在【指数数平滑】对话话框中，【输输入区域】框框中输入“$B$2:$B$133”，【阻尼系系数】框中输输入“0.2”，【输出区区域】框中输输入“$C$3”，最后选中中【图表输出出】选项。（3）单击【确确定】按钮，则则运算结果就就显示在单元元格区域C33:C13中（图图中的第133行预测数据据即为下月即即第13月的预测测值），如图图4-8所示，并并自动出现输输出图表，如如图4-100所示。

35、图4-10 指数平滑法法预测输出图图（阻尼系数数0.2）用同样的方法，可可以分析当阻阻尼系数为00.4和0.6时的分分析结果如图图4-8所示。需要注意的是，【数数据分析】中中的指数平滑滑法所使用的的阻尼系数并并不是4.22.1.2节节介绍的指数数平滑法预测测方程中的平平滑系数，二二者的关系为为：阻尼系数数=1。4.4.3 回归法利用Excell的回归工具具进行预测分分析有两种方方法：一是图图表法；二是是回归分析法法。4.4.3.11 图表法法图表法仅能解决决一元线性或或非线性回归归问题，不能能解决多元回回归问题。【例4-7】某某企业连续99年的产品销销售收入Y（万元）与与广告支出XX1（万元）

36、和和居民平均收收入X2（元）的有有关数据如图图4-11所示示，则利用图图表法进行回回归分析，其其方法和步骤骤如下，这里里仅以销售收收入Y（万元）与与广告支出XX1（万元）的的一元线性关关系为例：图4-11 某企业的有有关销售数据据（1）选择单元元格区域B22:C10。（2）单击工具具栏上的【图图表导向】按按钮，在【图图表导向4步骤之1图表类型】中中选“XY散点图”，其【子图图表类型】选选第1种，如图4-12所示。图4-12 准备作散点点图（3）单击【下下一步】按钮钮，出现【图图表导向4步骤之2图表源数据据】对话框，单单击【系列】，在在【名称】栏栏中填入“销售收入”，在【X值】栏中输输入“=Sh

37、eeet1!$CC$2:$C$100”，在【Y值】栏中输输入“=Sheeet1!$BB$2:$B$100”（用鼠标拾拾取单元格区区域），如图图4-13所示示。图4-13 填入源数据据（4）单击【下下一步】按钮钮，出现【图图表导向4步骤之3图表选项】对对话框，填入入各标题文字字，如图4-14所示。图4-14 填入各标题题文字（5）单击【下下一步】按钮钮，出现【图图表导向4步骤之4图表位置】对对话框，不作作任何输入，单单击【完成】按按钮，则在工工作表上看到到输出的图形形，对其进行行必要的调整整（如坐标、字字体、位置等等）。（6）在系列【数数据点】上的的任一点上，按按鼠标左键，使使各数据点出出现记号

38、，再再单击【工具具栏】上的【图图表】按钮，选选中【添加趋趋势线】项，或或在数据点上上按鼠标右键键，选【添加加趋势线】项项，出现【添添加趋势线】对对话框，如图图4-15所示示。图4-15 【添加趋势势线】对话框框（7）在【添加加趋势线】中中的【类型】对对话框中，有有【线性】、【对对数】、【多多项式】、【乘乘幂】、【指指数】和【移移动平均】66个选项。通通过观察XYY散点图可知知，产品销售售收入与广告告支出之间呈呈明显的线性性关系，故这这里选【线性性】。（8）在【添加加趋势线】中中的【选项】对对话框中，勾勾选【显示公公式】、【显显示R平方值】，如如图4-166所示。图4-16 【添加趋势势线】的【

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

40、88所示，则弹弹出【回归】对对话框。图4-18 【数据分析析】对话框（2）在【回归归】对话框中中，【Y值输入区域域】中输入“$B$1:$B$100”，【X值输入区域域】中输入“$C$1:$D$100”，在【输出出选项】中勾勾选【输出区区域】，填入入“$A$122”，然后根据据实际需要，勾勾选其他需要要的选项，如如图4-199所示。图4-19 【回回归】选项（3）单击【确确定】按钮，回回归分析的摘摘要就输出在在本工作表上上，如图4-20所示。对对这些数据进进行分析可知知：R平方值为0.9903，说说明因变量与与自变量之间间相关性很高高；F的显著值为为2.9611E-07，已已达0.055的检验标

41、准准；其他统计计检验也达到到相应的标准准。从而得到到回归方程为为：Y=2229.84009+9.22794X11+0.00082 X22。图4-20 回归分析的的计算机输出出当自变量只有一一个（即一元元回归）时，上上述方法同样样适用。2非线性回归归对于某些可以化化为线性关系系的非线性问问题，同样可可以进行回归归分析。举例例如下。【例4-9】某某地区科研系系统近10年的净收收入Y（千万元）与与研究经费XX1（千万元）和和研究人员数数X2（万人）的的统计资料，如如图4-211所示，假设设它们之间存存在着以下的的函数关系：式中，a、b、c为待估计参参数。若利用回归工具具求解此类非非线性问题，解解决的

42、办法是是将此方程进进行数学变换换，即对方程程两边取对数数，得，将各各个观测值进进行变换，如如图4-211所示，即在在单元格E22:E11中输入入公式“=LN(BB2:B11)”（数组公式式输入），然然后将单元格格E2:E11复制到到单元格F22:F11和G2:G11中。图4-21 某某地区科研系系统有关资料料再对变换后的数数据利用Exxcel的回回归工具进行行回归分析，具具体步骤可参参阅【例4-7】，其中中【Y值输入区域域】中输入“$E$1:$E$111”，在【X值输入区域域】中输入“$F$1:$G$111”，在【输出出选项】中勾勾选【输出区区域】，填入入“$A$122”，得到如图图4-22所

43、示示的分析结果果，最后得到到：a = e0.008214 = 0.99211，b =0.44477，c = 0.6046，相相关系数为00.98088（注意此相相关系数是变变换后的线性性方程的相关关系数，并不不是原非线性性方程的相关关系数）， 回归方程为为：。图4-22 回归分析结结果第4章 Exccel在财务务预测中的应应用4.5 利用规规划求解工具具解决预测问问题 虽然我们们可以利用EExcel提提供的各种预预测分析工具具解决大多数数财务预测中中的实际问题题，但这些预预测分析工具具并不是万能能的，其预测测误差也随着着实际问题的的复杂化而增增大。比如对对于一些非线线性预测问题题，常常是将将其

44、通过变量量替换而转换换为线性问题题。但是，这这种变换过程程一方面增加加了计算工作作量，另一方方面也可能导导致分析精度度下降，因为为变换后的数数据容易使观观测数据的性性质发生变化化，导致自变变量与因变量量之间的关系系发生扭曲，从从而影响回归归方程的精度度，因此，这这种将非线性性转换为线性性的做法是存存在一定的缺缺陷的。此外外，有些非线线性问题根本本无法直接转转换为线性问问题，除非作作出大量的简简化，这必然然使得到的回回归方程严重重失真。因此，对于非线线性回归问题题，最好的方方法是直接进进行回归分析析，即求解使使残差平方和和最小、或使使相关系数最最大的回归方方程，但非线线性回归过程程是一个循环环寻

45、优过程，需需要先设置回回归方程系数数的初值，然然后计算观测测值与预测值值的残差平方方和，不断寻寻找使残差平平方和最小的的回归方程系系数，这实际际上是一个优优化问题，因因此，可以利利用Exceel的规划求求解工具求解解非线性回归归问题，当然然也可以用来来求解线性回回归问题。在利用规划求解解工具直接求求解非线性回回归问题时，需需要使用以下下几个计算公公式：自由度df为式中，n为观测测次数；m为待估计参参数个数。残差平方和SEE为式中，Yi、 分别为第i个观测值和和预测值（ii =1,2,n）。剩余标准差SSS为相关系数R2为为式中，为观测值值的平均值。下面结合实例说说明在Exccel上进行行非线性

46、回归归的具体方法法和步骤。【例4-10】以例4-9的有关关资料为例，利利用Exceel的规划求求解工具来求求解非线性回回归问题的方方法和步骤如如下：（1）如图4-23所示，单单元格G2:G4为变动单单元格，分别别存放待估计计参数a、b、c，其初值可可设为0。（2）在单元格格E2:E11中输入预预测值公式“=G2*(C2:C11)G3*(DD2:D11)G4”（数组公式式输入）。图4-23 利利用规划求解解工具进行非非线性回归分分析（3）在单元格格G5中输入观观测值的平均均值公式“=AVERRAGE(BB2:B11)”； 在单元格G66中输入自由由度公式“=COUNNT(B2:B11)-COUN

47、TT(G2:G4)”；在单元格格G7中输入残残差平方和公公式“=SUM(B2:B11-EE2:E11)2)”（数组公式式输入）；在在单元格G88中输入剩余余标准差公式式“=SQRTT(G7/GG6)”；在单元格格G9中输入相相关系数R2的计算公式式“=1-G77/SUM(B2:B11-GG5)2)”（数组公式式输入）。（4）单击EXXCEL工具具菜单，选择择【规划求解解】项，出现现【规划求解解参数】对话话框；（5）在【规划划求解参数】对对话框中，【设设置目标单元元格】设置为为单元格“$G$7”，即目标函函数为残差平平方和；【等等于】设置为为“最小”；【可变单单元格】设置置为“$G$2:$G$4

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

49、差差平方和最小小为止，即得得到最优结果果。在上例中中，当a、b、c的初始值设设为0时，经过2次求解过程程即得到最优优结果，如图图4-23所示示。若采用非线性转转换为线性的的方法，如例例4-9所示，可可得到有关系系数如图4-22所示，将将此系数代入入回归方程，计计算不同X1和X2下的预测值值，进而计算算出残差平方方和与剩余标标准差分别为为157.11139和4.73776，与图4-223的直接非非线性回归的的结果（残差差平方和1554.03、剩剩余标准差44.69099）进行比较较，可见非线线性转换为线线性的方法得得出的结果误误差要大于直直接进行非线线性回归的误误差。第4章 Exccel在财务务

50、预测中的应应用4.6 销售预预测 销售预测的的准确程度，对对企业的兴衰衰成败会产生生很重要的影影响。销售预预测比较准确确，会使企业业在有计划的的财务安排下下顺利运作；而如果销售售预测与实际际情况偏离很很远，则会使使企业遇到麻麻烦，甚至陷陷入困境。因因此，销售预预测是企业进进行财务预测测的首要工作作，是企业制制定财务计划划的基础。 销售预测主主要应根据市市场需求的变变化，结合企企业的利润目目标、实现企企业市场份额额的目标，并并综合考虑企企业内外部的的各种限制条条件的影响来来进行。一般般情况下，可可首先分别对对未来各期的的销售量和销销售价格进行行预测，在此此基础上，根根据预测的产产品销售价格格乘以

51、预测的的销售量得到到预测的销售售额；也可以以直接根据销销售额的有关关历史资料，采采用适当的方方法进行预测测。4.6.1 销售预测的的基本方法销售预测是一项项比较复杂的的工作，需要要考虑的因素素很多，作出出准确的预测测是非常困难难的。通常可可利用企业过过去的数据进进行统计分析析，并结合经经济环境对未未来市场的影影响以及企业业内外部各种种条件的限制制，作出销售售预测。进行销售预测的的方法很多，常常用的方法包包括以下几种种。1时间序列预预测法时间序列预测法法，是指将观观察或记录的的一些历史数数据，按时间间的先后排列列成数据系列列，进行统计计分析，找出出过去长期的的销售量或销销售额的增减减变化趋势，再

52、再根据此变化化趋势分析的的结果，预测测未来时期的的销售量或销销售额。常见见的时间序列列的预测方法法有简单平均均法、移动平平均法、指数数平滑法、或或以时间为自自变量的回归归分析法等，这这些方法的基基本原理可参参阅前面的有有关内容。2因果关系预预测法因果关系预测法法，是指利用用有关因素与与产品销售量量或销售额之之间的固有因因果关系，通通过建立一定定的数学模型型来预测企业业未来的产品品销售水平的的一种方法。企企业产品销售售水平的高低低，往往受到到诸多宏观或或微观、外部部或内部、客客观或主观等等因素的影响响，通常可以以通过回归分分析的方法检检验出哪些因因素与销售水水平之间具有有因果关系，在在此基础上可

53、可建立回归方方程，进行销销售预测。有关如何建立回回归方程及进进行相关检验验的方法可参参阅前面的有有关内容。3通过生产能能力或订货合合同进行销售售量（销售额额）预测企业生产的产品品如果在市场场占有稳定的的份额或供不不应求，则可可按本企业的的生产能力预预测产品的销销售量，计算算公式如下：计划期销售量=计划期初库库存量+计划期预计计生产量-计划期末预预计库存量4.6.2 销售预测模模型及其应用用在很多情况下，通通过建立企业业的销售预测测模型，可以以很方便地实实现销售预测测。下面介绍绍两个销售预预测模型。4.6.2.11 一元线线性（非线性性）回归预测测模型【例4-11】根据图4-224中所给的的资料

54、建立一一元线性（非非线性）回归归预测模型。图4-24 一元线性（非非线性）回归归销售预测模模型下面利用线性回回归中的LIINEST函函数和指数回回归中的LOOGEST函函数，来建立立一元线性（非非线性）回归归预测模型。（1）首先建立立销售预测模模型，如图44-24所示示，这里以过过去12期的销售售量为历史数数据（可以是是以年计算，也也可以是以月月计算，图44-24为以以年计算）。（2）设置回归归模型选择控控件，控件的的数据源区域域为$A$77:$A$8，单单元格链接为为$B$7，下下拉显示项数数为2。（3）选取单元元格区域B33:M3，单击【插插入】【名称称】【定义】命命令，或直接接单击编辑栏

55、栏中的名称框框，将影响因因素所在的单单元格区域BB3:M3定义为“影响因素序序列”；用同样的的方法，将销销售量所在的的单元格区域域B4:M4定义为“销售序列”。（4）在单元格格E8中输入公公式“=IF(BB7=1,IINDEX(LINESST(销售序序列,影响因素序序列,TRUUE,TRUUE),1,2),INNDEX(LLOGESTT(销售序列列,影响因素序序列,TRUUE,TRUUE),1,2)”，计算系数数A。（5）在单元格格F8中输入公公式“=IF(BB7=1,IINDEX(LINESST(销售序序列,影响因素序序列,TRUUE,TRUUE),1,1),INNDEX(LLOGESTT(

56、销售序列列,影响因素序序列,TRUUE,TRUUE),1,1)”，计算系数数B。（6）在单元格格G8中输入公公式“=IF(BB7=1,IINDEX(LINESST(销售序序列,影响因素序序列,TRUUE,TRUUE),3,1),INNDEX(LLOGESTT(销售序列列,影响因素序序列,TRUUE,TRUUE),3,1)”，计算相关关系数R2。（7）在单元格格J8:M8中输入公公式“=IF(BB7=1,EE8+F8*J7:M7,E88*F8JJ7:M7)”（数组公式式输入），计计算未来第114期的预测值值。在影响因素和销销售量两列输输入历史数据据，并在J77:M7中输入未未来4期的影响因因素预

57、测数值值后，即可得得到回归预测测模型及未来来的预测值。通通过选择不同同的回归模型型，可以分别别计算一元线线性模型和一一元指数模型型下的回归结结果及预测值值。由计算结结果可知，采采用指数模型型（相关系数数为0.97742）要比比线性模型（相相关系数为00.94522）更为准确确。4.6.2.22 多元线线性回归预测测模型【例4-12】根据图4-225中所给的的资料建立多多元线性回归归预测模型。图4-25 多元线性回回归销售预测测模型当影响销售量（额额）的因素不不止一个时，就就需要建立多多元线性回归归模型。下面面就二元线性性回归预测模模型的建立进进行说明，对对于影响因素素在两个以上上的情况，可可参

58、照本模型型建立。（1）首先建立立销售预测模模型，如图44-25所示示，这里以过过去12期的历史史数据为依据据（可以是按按年计算，也也可以是按月月计算，图44-25为按按月计算）。（2）选取单元元格区域B33:M4，单击【插插入】【名称称】【定义】命命令，或直接接单击编辑栏栏中的名称框框，将影响因因素所在的单单元格区域BB3:M4定义为“影响因素序序列”；用同样的的方法，将销销售额所在的的单元格区域域B5:M5定义为“销售序列”。（3）在单元格格D9中输入公公式“=INDEEX(LINNEST(销销售序列,影响因素序序列,TRUUE,TRUUE),1,3)”，计算系数数A。（4）在单元格格E9中

59、输入公公式“=INDEEX(LINNEST(销销售序列,影响因素序序列,TRUUE,TRUUE),1,2)”，计算系数数B。（5）在单元格格F9中输入公公式“=INDEEX(LINNEST(销销售序列,影响因素序序列,TRUUE,TRUUE),1,1)”，计算系数数C。（6）在单元格格G9中输入公公式“=INDEEX(LINNEST(销销售序列,影响因素序序列,TRUUE,TRUUE),3,1)”，计算相关关系数R2。（7）在单元格格J9:M9中输入公公式“=D9+EE9*J7:M7+F99*J8:M8”（数组公式式输入），计计算未来第114期的预测值值。在影响因素和销销售额各列输输入历史数据

60、据，并在J77:M8中输入未未来4期的影响因因素预测数值值后，即可得得到回归预测测模型及未来来的预测值，如如图4-255所示第4章 Exccel在财务务预测中的应应用4.7 成本预预测 4.7.1 成本预测的的方法成本是指企业为为生产和销售售产品所花费费的全部费用用。成本可以以按很多不同同的标准进行行分类，如常常见的按经济济职能分类和和按成本性态态分类：成本按经济职能能划分可分为为生产成本和和非生产成本本两大类。生生产成本又称称制造成本，包包括生产过程程中发生的直直接材料、直直接人工和制制造费用三个个项目；非生生产成本又称称非制造成本本，包括为销销售产品所花花费的销售费费用和为组织织企业的生产