搜索
EXCEL在财务管理中的应用学习目标学习完本章后，学生应当能够独立运用分析工具、函数、规划求解等工具进行财务预测模型设计，包括：一元线性回归模型、一元非线性回归模型、多元线性回归模型，并在设计过程中绘制相应的拟合图形。第7章财务预测模型设计EXCEL在财务管理中的应用第1单元预测模型设计－分析工具•财务预测，是指对企业未来的收入、成本、利润、现金流量及融资需求等财务指标所作的估计和推测。财务预测是编制投资和融资计划的基础，是公司制订成长战略的基本要素。•Excel的数据分析工具库提供了3种统计观测分析工具，它们是移动平均法、指数平滑法和回归分析法。EXCEL在财务管理中的应用⒈移动平均法•移动平均法是一种改良的算术平均法，是一种最简单的自适应预测模型。它根据近期数据对预测值影响较大，而远期数据对预测值影响较小的事实，把平均数逐期移动。移动期数的大小视具体情况而定，移动期数少，能快速地反映变化，但不能反映变化趋势；移动期数多，能反映变化趋势，但预测值带有明显的滞后偏差。EXCEL在财务管理中的应用•移动平均法根据预测时使用的各元素的权重不同，可以分为：简单移动平均和加权移动平均。EXCEL在财务管理中的应用⒉指数平滑法•指数平滑法是在移动平均法基础上发展起来的一种时间序列分析预测法，它是通过计算指数平滑值，配合一定的时间序列预测模型对现象的未来进行预测。其原理是任一期的指数平滑值都是本期实际观察值与前一期指数平滑值的加权平均。EXCEL在财务管理中的应用⒊回归分析预测法回归分析预测法是通过研究两组或两组以上变量之间的关系，建立相应的回归预测模型，对变量进行预测的一种预测方法。EXCEL在财务管理中的应用第2单元预测模型设计－函数法•Excel提供了关于估计线性模型和指数模型参数的几个预测函数。线性模型和指数模型的数学表达式如下：•线性模型：y=mx+b或y=m1x1+m2x2+…+bEXCEL在财务管理中的应用指数模型：式中，y为因变量；x是自变量；m、m1、...、mn-1、mn、b分别为预测模型的待估计参数。•Excel提供的预测函数主要有LINEST函数、LOGEST函数、TREND函数、FORECAST函数、SLOPE函数和INTERCEPT函数，它们所使用的参数都基本相同，如表7－1、7－2、7－3所示。EXCEL在财务管理中的应用表7－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。附加回归统计值返回的顺序见表7-2。表4-2中的各参数说明见表7-3。如果要得到附加回归统计值数组中的值，需用INDEX函数将其取出EXCEL在财务管理中的应用表7－2回归统计值返回的顺序1234561mnmn-1…m2m1b2sensen-1…se2se1seb3r2sey4Fdf5ssregssresidEXCEL在财务管理中的应用表7－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残差平方和EXCEL在财务管理中的应用•知识点讲解⒈LINEST函数LINEST函数是常用的统计函数之一，它是使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。因为此函数返回数值数组，所以必须以数组公式的形式输入。•函数语法：•LINEST(known_y's,known_x's,const,stats)•Known_y's是关系表达式y=ax+b中已知的y值集合。EXCEL在财务管理中的应用如果数组known_y's在单独一列中，则known_x's的每一列被视为一个独立的变量。如果数组known-y's在单独一行中，则known-x's的每一行被视为一个独立的变量。•Known_x's是关系表达式y=ax+b中已知的可选x值集合。•数组known_x's可以包含一组或多组变量。如果只用到一个变量，只要known_y's和•known_x's维数相同，它们可以是任何形状的区域。如果用到多个变量，则known_y's必须为向量（即必须为一行或一列）。EXCEL在财务管理中的应用如果省略known_x's，则假设该数组为{1,2,3,...}，其大小与known_y's相同。•Const为一逻辑值，用于指定是否将常量b强制设为0。如果const为TRUE或省略，b将按正常计算。如果const为FALSE，b将被设为0，并同时调整m值使y=mx。•Stats为一逻辑值，指定是否返回附加回归统计值。如果stats为TRUE，则LINEST函数返回附加回归统计值，这时返回的数组为{mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}。如果stats为FALSE或省略，LINEST函数只返回系数m和常量b。EXCEL在财务管理中的应用函数说明：•可以使用斜率和y轴截距描述任何直线：•斜率(a)：通常记为a，如果需要计算斜率，则选取直线上的两点，(x1,y1)和(x2,y2)；斜率等于(y2-y1)/(x2-x1)。EXCEL在财务管理中的应用•Y轴截距(b)：•通常记为b，直线的y轴的截距为直线通过y轴时与y轴交点的数值。•直线的公式为y=ax+b。如果知道了a和b的值，将y或x的值代入公式就可计算出直线上的任意一点。还可以使用TREND函数。•当只有一个自变量x时，可直接利用下面公式得到斜率和y轴截距值：•EXCEL在财务管理中的应用斜率：=INDEX(LINEST(known_y's,known_x's),1)Y轴截距：=INDEX(LINEST(known_y's,known_x's),2)EXCEL在财务管理中的应用数据的离散程度决定了LINEST函数计算的精确度。数据越接近线性，LINEST模型就越精确。LINEST函数使用最小二乘法来判定最适合数据的模型。EXCEL在财务管理中的应用回归分析时，MicrosoftExcel计算每一点的y的估计值和实际值的平方差。这些平方差之和称为残差平方和(ssresid)。然后MicrosoftExcel计算总平方和(sstotal)。当const=TRUE或被删除时，总平方和是y的实际值和平均值的平方差之和。当const=FALSE时，总平方和是y的实际值的平方和（不需要从每个y值中减去平均值）。回归平方和(ssreg)可通过公式ssreg=sstotal-ssresid计算出来。残差平方和与总平方和的比值越小，判定系数r2的值就越大，r2是表示回归分析公式的结果反映变量间关系的程度的标志。r2等于ssreg/sstotal。EXCEL在财务管理中的应用函数示例：表7-1123456AB已知y已知x10945273公式公式=LINEST(A2:A5,B2:B5,,FALSE)EXCEL在财务管理中的应用当以数组输入时，将返回斜率2和y轴截距1。函数示例：表7-2简单线性回归1234567AB月销售131002450034400454005750068100公式说明（结果）=SUM(LINEST(B2:B7,A2:A7)*{9,1})估算第9个月的销售值(11000)EXCEL在财务管理中的应用⒉LOGEST函数LOGEST函数是在回归分析中，计算最符合数据的指数回归拟合曲线，并返回描述该曲线的数值数组。因为此函数返回数值数组，故必须以数组公式的形式输入。此曲线的公式为：y=b*m^x或y=(b*(m1^x1)*(m2^x2)*_)（如果有多个x值）•其中因变量y是自变量x的函数值。m值是各指数x的底，而b值是常量值。•注意：公式中的y、x和m均可以是向量，LOGEST函数返回的数组为{mn,mn-i,...,m1,b}。EXCEL在财务管理中的应用函数语法：LOGEST(known_y's,known_x's,const,stats)Known_y's满足指数回归拟合曲线y=b*m^x的一组已知的y值。如果数组known_y's在单独一列中，则known_x's的每一列被视为一个独立的变量。如果数组known-y's在单独一行中，则known-x's的每一行被视为一个独立的变量。Known_x's满足指数回归拟合曲线y=b*m^x的一组已知的x值，为可选参数。EXCEL在财务管理中的应用known_x's数组可以包括一组或多组自变量。如果仅使用一个变量，那么只要known_x's和known_y's具有相同的维数，则它们可以是任何形状的区域。如果使用多个变量，则known_y's必须是向量（即具有一列高度或一行宽度的单元格区域）。如果省略known_x's，则假设该数组为{1,2,3,...}，其大小与known_y's相同。Const为一逻辑值，用于指定是否将常数b强制设为1。EXCEL在财务管理中的应用如果const为TRUE或省略，b将按正常计算。如果const为FALSE，则常量b将设为1，而m的值满足公式y=m＾x。Stats为一逻辑值，指定是否返回附加回归统计值。如果stats为TRUE，函数LOGEST将返回附加的回归统计值，因此返回的数组为{mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}。如果stats为FALSE或省略，则函数LOGSET只返回系数m和常量b。EXCEL在财务管理中的应用函数说明：由数据绘出的图越近似于指数曲线，则计算出来的曲线就越符合原来给定的数据。正如LINEST函数一样，LOGEST函数返回一组描述数值间相互关系的数值数组，但LINEST函数是用直线来拟合数据，而LOGEST函数则以指数曲线来拟合数据。•当仅有一个自变量x时，可直接用下面的公式计算出斜率(m)和y轴截距(b)的值。EXCEL在财务管理中的应用斜率(m)：INDEX(LOGEST(known_y's,known_x's),1)Y轴截距(b)：INDEX(LOGEST(known_y's,known_x's),2)可用y=b*m＾x公式来预测y的值，但是MicrosoftExcel另外提供了可以预测因变量y值的GROWTH函数。对于返回结果为数组的公式，必须以数组公式的形式输入。当需要输入一个数组常量（如known_x's）作为参数时，以逗号作为同