Excel在财务预测中的应用

Excel在财务猜测中的应用PAGEPAGE1————————————————————————————————作者：————————————————————————————————日期：第4章Excel在财务猜测中的应用4.1财务猜测概述财务猜测，是指对企业将来的收入、成本、利润、现金流量及融资需求等财务指标所作的估量和推想。财务猜测是编制投资和融资 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 的基础，是公司制订成长战略的基本要素。称职的财务管理人员应当能够充分利用公司的有关信息资料，猜测公司的财务需要并做出相应的支配。公司成长主要由销售增长来打算，销售增长需要相应的资产增长，假如企业已经是满负荷运转，不仅流淌资产、而且固定资产都要增长，而资产增长需要相应的融资增长。同时，企业进行对外投资和调整资本结构，也需要筹措资金。企业所需要的这些资金，一部分来自企业内部，另一部分通过外部融资取得。由于对外融资时，企业不但需要查找资金供应者，而且还需做出还本付息的承诺或供应企业盈利前景等信息，使资金供应者确信其投资是平安的并可获利，这个过程往往需要花费较长的时间。因此，企业需要预先知道自身的财务需求，确定资金的需要量，提前支配融资方案，以免影响资金周转。财务猜测有助于改善企业的投资决策。虽然投资是打算筹资与否和筹资多少的重要因素，但是依据销售前景估量出的融资需求，并不肯定能够得到全部满足。这时，就需要依据可能筹措到的资金来支配销售增长以及有关的投资项目，使投资决策建立在可行的基础上。财务猜测一般按以下几个步骤进行。1．销售猜测销售猜测是指依据市场调查所得到的有关资料，通过对有关因素的分析争辩，估计和测算特定产品在将来肯定时期内的市场销售量水平及变化趋势，进而猜测企业产品将来销售量的过程。企业的一切财务需求都可以看作是因销售引起的，销售量的增减变化，将会引起库存量、现金流量、应收与应付账款以及公司其他资产和负债的变化。因此销售猜测在企业猜测系统中处于先导地位，它对于指导利润猜测、成本猜测和资金猜测，进行长短期决策，支配经营方案，组织生产等都起着重要的作用。2．估量收入、费用和利润收入和费用与销售量之间也存在肯定的函数关系，因此，可以依据销售数据估量收入和费用，并确定净利润。净利润和股利支付率，共同打算了内部留存收益所能供应的资金数额。3．估量需要的资产资产通常是销售收入的函数，依据历史数据可以分析出二者之间的函数关系。依据估计销售收入和资产与销售之间的函数关系，可以猜测所需资产的总量。某些流淌负债也是销售收入的函数，相应地也可以猜测负债的自发增长额，这种增长可以削减企业外部融资的数额。4．估量所需融资依据估计资产总量，减去已有的资金来源、负债的自发增长和内部供应的留存收益，可得出所需的外部融资数额。第4章Excel在财务猜测中的应用4.2财务猜测的分析 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 猜测分析的方法有很多种，企业应依据不同的需要选择不同的猜测方法。总的来说，猜测分析方法可分为两大类：定量猜测法和定性猜测法。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）相关检验。对建立的猜测数学模型，必需进行有关的检验，主要是通过计算猜测模型的相关系数、方差（或 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 差）以及显著性等指标，来推断猜测模型的精确     性、是否需要修正、接受何种方法修正等。2．回归模型建立的方法建立回归模型的一般方法是接受最小二乘法，其原理如下：考虑m个自变量x1、x2、…、xm和因变量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.3Excel中的有关猜测函数及其应用(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_y's 因变量y的观测值集合known_x's 自变量x的观测值集合。它可以是一个变量（即一元模型）或多个变量（即多元模型）的集合。假如只用到一个变量，只要known-y's和known-x's维数相同，它们可以是任何外形的选定区域。假如用到不只一个变量，known_y's必需是向量（也就是说，必需是一行或一列的区域）。假如省略known_x's，则假设该数组是{1,2,3...}，其大小与known_y's相同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-1…m2m1b2sensen-1…se2se1seb3r2sey    4Fdf    5ssregssresid    表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_y's，known_x's，const，stats）下面举例说明LINEST函数的应用。1．一元线性回归分析LINEST函数可用于一元线性回归分析，也可以用于多元线性回归分析，以准时间数列的自回归分析。当只有一个自变量x（即一元线性回归分析）时，可直接利用下面的公式得到斜率和y轴的截距值以及相关系数：斜率：INDEX（LINEST（known_y's，known_x's）,1,1）；或INDEX（LINEST（known_y's,known_x's）,1）截距：INDEX（LINEST（known_y's，known_x's）,1,2）；或INDEX（LINEST（known_y's,known_x's）,2）相关系数：INDEX（LINEST（known_y's,known_x's,true,true）,3,1）【例4-1】某企业1～9月份的总成本与人工小时及机器工时的数据如图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_y's,known_x's,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.7729×0.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_y's和known_x's的直线（用最小二乘法），并返回指定数组new_x's值在直线上对应的y值。TREND函数的公式为=TREND(known_y's,known_x's,new_x's,const)式中 new_x's——需要函数TREND返回对应y值的新x值。new_x's与known_x's一样，每个独立变量必需为单独的一行（或一列）。因此，假如known_y's是单列的，known_x's和new_x's应当有同样的列数，假如known_y's是单行的，known_x's和new_x's应当有同样的行数。假如省略new_x's，将假设它和known_x's一样。【例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_x's的参数，故数组{13;14;15}就对应其后的3个月份。4.3.4 GROWTH函数GROWTH函数的功能是返回给定的数据猜测的指数增长值。依据已知的x值和y值，函数GROWTH返回一组新的x值对应的y值。可以使用GROWTH工作表函数来拟合满足给定x值和y值的指数曲线。GROWTH函数的公式为=GROWTH(known_y's,known_x's,new_x's,const)式中，各参数的含义同TREND函数。但需留意的是，假如known_y's中的任何数为零或为负，函数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_x's的参数，故数组{13;14;15}就对应后面的3个月份。4.3.5 FORECAST函数FORECAST函数的功能是依据给定的数据计算或猜测将来值。此猜测值为基于一系列已知的x值推导出的y值。以数组或数据区域的形式给定x值和y值后，返回基于x的线性回归猜测值。FORECAST函数的计算公式为a+bx式中，；。FORECAST函数的公式为=FORECAST(x,known_y's,known_x's)式中　x—需要进行猜测的数据点。需要说明的是：假如x为非数值型，函数FORECAST返回错误值#VALUE!。假如known_y's和known_x's为空或含有不同数目的数据点，函数FORECAST返回错误值#N/A。假如known_x's的方差为零，函数FORECAST返回错误值#DIV/0!。例如：FORECAST(30,{6,7,9,15,21},{20,28,31,38,40})=10.60725。4.3.6 SLOPE函数SLOPE函数的功能是返回依据known_y's和known_x's中的数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的垂直距离与水平距离的比值，也就是回归直线的变化率。SLOPE函数的公式为=SLOPE(known_y's,known_x's)说明：参数可以是数字，或者是涉及数字的名称、数组或引用。假如数组或引用参数里包含文本、规律值或空白单元格，这些值将被忽视。但包含零值的单元格将计算在内。假如known_y's和known_x's为空或其数据点数目不同，函数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_x's和known_y's数据点的线性回归线与y轴的交点。公式为=INTERCEPT(known_y's,known_x's)例如：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中的C4︰E13区域所示。以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.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.0082X2。图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*F8^J7:M7)”（数组公式输入），计算将来第1～4期的猜测值。在影响因素和销售量两列输入历史数据，并在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”（数组公式输入），计算将来第1～4期的猜测值。在影响因素和销售额各列输入历史数据，并在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:M3^2”（数组公式输入），以预备进行一元二次回归分析。图4-26销售成本猜测模型（4）在单元格E9中输入公式：“=IF(B8=1,INDEX(LINEST(B5:M5,B3:M3,TRUE,TRUE),1,2),INDEX(LINEST(B5:M5,B3:M4,TRUE,TRUE),1,3))”，计算系数A。（5）在单元格F9中输入公式：“=IF(B8=1,INDEX(LINEST(B5:M5,B3:M3,TRUE,TRUE),1,1),INDEX(LINEST(B5:M5,B3:M4,TRUE,TRUE),1,2))”，计算系数B。（6）在单元格G9中输入公式：“=IF(B8=1,0,INDEX(LINEST(B5:M5,B3:M4,TRUE,TRUE),1,1))”，计算系数C。（7）在单元格H9中输入公式：“=IF(B8=1,INDEX(LINEST(B5:M5,B3:M3,TRUE,TRUE),3,1),INDEX(LINEST(B5:M5,B3:M4,TRUE,TRUE),3,1))”，计算相关系数R2。（8）在单元格K9:M9中输入公式：“=E9+F9*K8:M8+G9*K8:M8^2”（数组公式输入），计算将来第1～3期的猜测值。这样企业的一元线性及一元非线性成本猜测模型就建立起来了。在实际猜测中，可以先对销售量及销售成本的历史数据作出散点图，依据其趋势来选择一元线性或一元非线性猜测模型。第4章Excel在财务猜测中的应用4.8利润猜测(1)利润猜测是指在销售猜测的基础上，依据企业将来进展目标和其他相关资料，估计、推想、或估算将来应当达到和期望实现的利润水平及其变动趋势的过程。一般可在对成本、业务量（产销量）、利润之间的关系进行争辩的基础上作出利润猜测，这种猜测方法称为本量利分析法。本节主要介绍单一品种状况下确定性本量利分析和不确定性本量利分析的具体方法和步骤，并接受规划求解方法对约束条件下的多品种本量利关系进行分析。4.8.1 确定性条件下单品种利润敏感性分析模型敏感性分析是一种定量分析方法，它争辩当制约利润的有关因素发生某种变化时对利润所产生的影响，这对于利润猜测分析，特殊是对目标利润猜测有着格外乐观的指导意义。在企业只生产单品种产品，且产销平衡的条件下，本量利之间的基本关系为利润=销售量×（单价－单位变动成本）－固定成本在进行利润敏感性分析时，假定条件如下：利润只受销售量、产品单价、单位变动成本和固定成本的影响；上述各因素的变动均不会影响其他因素的变动。【例4-14】依据图4-27所给的资料建立确定性条件下单品种利润敏感性分析模型。下面就建立利润敏感性分析模型的具体步骤进行说明。（1）设计利润分析模型结构，如图4-27所示，这里假设只有一种产品。（2）首先设计销售量的【滚动条】控件。单击【视图】，选择【工具栏】，再单击【窗体】，消灭【窗体】工具栏，单击【滚动条】按钮，然后在工作表的合适位置（这里为E4～F4单元格）拖曳出一个矩形【组合框】控件，并调整其大小。图4-27 利润敏感性分析模型（3）将鼠标移到新建立的【滚动条】控件上，单击鼠标右键，消灭快捷菜单，选择【设置控件格式】，消灭【设置控件格式】对话框，选择【把握】项，在【当前解】栏中输入50，【最小值】栏中输入0，【最大值】栏中输入100，【步长】栏中输入1，【页步长】栏中输入5，在【单元格链接】栏中填入“E4”，然后单击【确定】按钮，这就建立了销售量的【滚动条】控件。这里假设了利润的各个影响因素的变动百分比范围为50%。（4）其他项目的【滚动条】控件可依据上述方法进行设置。其中，单价、单位变动成本和固定成本【滚动条】的【单元格链接】分别为单元格E5、E6、和E7。（5）在单元格D4:D7中建立变动百分比与【滚动条】控件的联系，即选取单元格D4:D7并输入公式“=E4:E7/100-50%”（数组公式输入）。说明：本例中各因素的变动范围为-50%～+50%，而滚动条把握按钮的值的变化范围为0～100，为了使滚动条把握按钮的变化表示为百分数的变化，这里将把握按钮的值除以100后再减去50%，则每次单击滚动条两端的箭头，单元格D4:D7中的变动百分比就变化1%，而当滚动条在中间位置，百分比恰好为零；当单击滚动框与滚动条两端之间时，每单击一次，变动百分比就增（或减）5%。（6）选取单元格C4:C7并输入变化后各项目数值的计算公式“=B4:B7*(1+D4:D7)”（数组公式输入）。（7）在单元格A10中输入估计利润计算公式：“=B4*(B5-B6)-B7”，并复制到单元格B10中，在单元格C10中输入公式“=B10-A10”，在单元格D10输入公式“=C10/A10”。这样，就得到了多因素变动对利润的综合影响。（8）设计单因素变动影响分析表格，如图4-27所示，在单元格B13:B16中输入公式“=A10”（数组公式输入），在单元格C13:C16中输入公式“=D4:D7”，在单元格D13～D16中分别输入销售量、产品单价、单位变动成本和固定成本单独变动时对利润的影响计算公式，分别为：单元格C13：“=C4*(B5-B6)-B7”，单元格C14：“=B4*(C5-B6)-B7”，单元格C15：“=B4*(B5-C6)-B7”，单元格C16：“=B4*(B5-B6)-C7”；在单元格E13:E16中输入公式“=D13:D16-B13:B16”（数组公式输入）；在单元格F13:F16中输入公式“=E13:E16/B13:B16”（数组公式输入）。这样利润敏感性分析模型就建立起来了。单击各个影响因素滚动条的箭头，转变其变动幅度，就可以很便利地了解各个因素变动对利润的单独影响程度以及综合影响程度。图4-27为各个影响因素的变动率及其对利润影响结果的一个例子，由图可见，当销售量单独增加10%时，利润增加12%；当产品单价单独增加10%时，利润增加40%；当单位变动成本削减10%时，利润增加28%；当固定成本单独削减10%时，利润增加2%。因此，产品单价对利润的影响最大，敏感性最强，其次是单位变动成本和销售量，而固定成本对利润的影响最小。当上述4个因素同时朝着使利润增加的方向变动10%时（见图4-27），则利润的增加幅度为88.80%。4.8.2 确定性条件下多品种本量利分析模型在多品种的状况下，假定各产品的品种结构肯定，则可以利用加权平均法进行本量利分析，具体可参阅第8章的有关内容。传统的本量利分析方法存在着以下明显的缺陷：一是产销平衡的假设，实际上，除非企业接到一批确定的订单，并按订单核算企业是否有利润可赚，按订单支配生产，否则产销不平衡是经常存在的，产品结构也将发生变化；二是以各产品估计销售量来确定产品结构，进而接受有关方法来计算各产品的保本额和保本量，但是，估计销售量是企业依据市场来猜测的，它具有很大的不确定性，猜测的销售量也经常是一个区间，而不是一个固定数，故以此来确定产品结构不尽合理；三是没有考虑企业自身条件的限制，由于企业的产品结构受企业本身各种限制条件（如企业的现有生产技术工艺、设备生产力量、企业资源、销售渠道等）的影响，而且这些条件也是时常变化的，无论是为了实现保本平衡还是为了实现目标利润，企业在同时生产销售多种产品时的产品结构都必需依据实际状况进行相应的调整，不依据企业的具体状况及市场状况计算的保本点是没有实际意义的。因此，对多品种盈亏平衡分析的正确方法是考虑各种约束条件，接受规划方法来解决企业的保本平衡、保利平衡、