Excel会计与财务管理-理论、方案暨模型（第二版） 桂良军课件第10章 Excel在财务预测中应用

第10章Excel在财务预测中应用本章教学要求：掌握财务预测相关的函数掌握利用数据分析工具进行财务预测的方法掌握建立销售预测模型的方法并进行销售预测主要内容：10.1财务预测概述及方法10.2财务预测相关的函数10.3利用数据分析工具进行财务预测10.4销售预测模型第10章Excel在财务预测中应用10.1财务预测概述及方法

10.1.1财务预测概述财务预测是指以企业过去一定时期财务活动资料为依据，结合企业现在面临和即将面临的各种因素变化，运用科学的定量和定性方法，对企业未来的财务活动和财务成果所做出的科学预计和合理判断。10.1.2财务预测方法

移动平均法指数平滑法时间序列预测法因果预测法——回归分析法定量预测意见汇集法专家小组法德尔菲法定性预测一元线性回归二元线性回归多元线性回归10.2财务预测相关的函数

在Excel中经常用到的财务预测函数有两类：1.用于进行线性变动趋势预测的函数

INTERCEPT函数

SLOPE函数

LINEST函数FORECAST函数TREND函数2.用于进行指数变动趋势预测的函数LOGEST函数GROWTH函数。（1）INTERCEPT函数功能：INTERCEPT函数专门用来计算一次线性回归模型y=a+bx方程中的a的值。语法格式：INTERCEPT(known_y’s,known_x’s)参数known_y’s为所观察的因变量数据或数据组，known_x’s为所观察的自变量数据或数据组。需要注意的是，⑴参数必须是数字、名称、数组或包含有数字的引用；⑵如果数组或引用参数包含文字、逻辑值，或空白单元格，则忽略其值，但零值将考虑在内。⑶如果known_y’s和known_x’s的数据点数目不相等或不包含任何数据点，则函数INTERCEPT返回错误值#N/A。（2）SLOPE函数功能：SLOPE函数专门用来计算一次线性回归模型y=a+bx方程中的b的值。语法格式：SLOPE(known_y’s,known_x’s)【例10-1】某企业今年1-6月份各月的实际销售额分别为280万元、300万元、290万元、310万元、320万元、330万元，要求求出拟合一次线性方程。（3）LINEST函数功能：LINEST函数用来计算一次线性回归模型y=a+bx方程中的a、b的值。语法格式：LINEST(known_y’s,known_x’s,const,stats)参数：Const

为一逻辑值，用于指定是否将常量a强制设为0。如果const为TRUE或省略，a将按正常计算。如果const为FALSE，a将被设为0，并同时调整b值使y=bx。Stats

为一逻辑值，指定是否返回附加回归统计值。如果stats为TRUE，则LINEST函数返回附加回归统计值，这时返回的数组为{mn,mn-1,...,m1,b;sen,sen-,...,se1,seb;r2,sey;F,df;ssreg,ssresid}。如果stats为FALSE或省略，LINEST函数只返回系数b和常量a。（4）FORECAST函数功能：FORECAST函数根据给定的数据计算或预测一元线性回归模型的未来值。使用此函数可以对未来销售额、库存需求或消费趋势进行预测。语法格式：FORECAST(x,known_y's,known_x's)参数说明：x为需要进行预测的数据点，FORECAST函数给出相应的在x点处的预测值。如果x不是数字，函数FORECAST返回错误值#VALUE！。如果known_y’s和known_x’s为空或含有不同数目的数据点，函数FORECAST返回错误值#N/A。（5）TREND函数功能：TREND函数返回依据多元线性回归趋势而得的预测值。它先找到适合给定的数组known_y’s和known_x’s的直线（用最小二乘法），然后对用户给定的新数组new_x’s值返回拟合趋势的y值。（可预测一组值！）语法TREND(known_y’s,known_x’s,new_x’s,const)见P287例10-2（6）LOGEST函数功能：LOGEST函数计算最符合观测数据组的指数回归拟合曲线，并返回描述该曲线的数组。回归拟合曲线的方程为：y=b*m^x语法格式：LOGEST(known_y’s,known_x’s,const,stats)

（7）Growth函数

自己看课本本章P287-289以上介绍了有关回归分析预测工作表函数，总结如下表所示：

函数名定义语法格式INTERCEPT一元线性回归模型b的估计值（截距）INTERCEPT(y,x)SLOPE一元线性回归模型m的估计值（斜率）SLOPE(y,x)LINEST估计一元线性回归模型的参数值LINEST(y,x,const,stats)FORECAST依照一元线性回归模型的预测值FORECAST(new_x,y,x)TREND依照多元线性回归模型的预测值TREND(y,x,new_x,const)LOGEST估计多元指数回归模型的参数值LOGEST(y,x,const,stats)GROWTH依照多元指数回归模型的预测值GROWTH(y,x,new_x,const)10.3利用数据分析工具进行财务预测10.3.1利用移动平均工具进行财务预测移动平均分析工具可以基于特定的过去某段时期中变量的平均值对未来值进行预测。移动平均值提供了由历史数据的简单平均之所代表的趋势信息。使用此工具可以预测销售量、库存或其他趋势。【例10-5】某汽车制造企业在2010年1-12月份销售的汽车数量如表10-5所示。要求在Excel中对2010年各月的汽车销量绘图并据此判断是否可以采用移动平均法对销量进行预测，如果可以的话，利用移动平均工具预测2011年1月的销量。所建立的模型的结构如图10-1所示：图10-1利用移动平均分析工具进行预测的结果10.3.2利用指数平滑工具进行财务预测指数平滑分析工具是基于前期预测值到处相应的新预测值，并修正前期预测值的误差。此工具将使用阻尼系数，其大小决定了本次预测对前期预测误差的修正程度。【例10-6】沿用【例10-5】的已知条件，要求利用Excel数据分析库中的指数平滑工具（阻尼系数=0.4）预测2011年1月份的汽车销量。所建立的模型的结构如图10-8所示：图10-8利用指数平滑分析工具进行预测的结果10.3.3利用回归工具进行财务预测回归分析法是指在掌握大量观测数据的基础上，利用数理统计方法建立因变量与自变量之间的回归关系函数表达式并据此进行预测的方法。【例10-7】电视台的广告收入与收看该电视台节目的观众人数存在一定的关系，为了研究这一关系，现抽取某电视台2010年1-12月的广告收入和日均观众人数，如表10-6所示，要求根据电视台的广告收入和日均观众人数绘图并据此判断能否进行回归分析，如果可以的话，利用回归分析工具预测2011年1月该电视台的广告收入（当月的日均观众人数为8358万人）。所建立的模型的结构如图10-11所示：图10-11利用回归分析工具进行预测的结果利用回归分析工具进行多元线性回归分析【例10-8】现有一家汽车制造企业，专门生产某品牌的家用轿车。该企业的销售经理为了预测2011年度该企业在某地区的销售额，特将2001-2010年度企业的年生产能力、在该地区的广告投入和销售额以及该地区家庭的平均年收入整理汇总如表10-7所示，试图找出销售额与这三个变量之间的关系。要求根据提供的数据，利用回归分析工具建立一个最合适的回归方程对2011年度该企业在本地区的销售额进行预测（假设2011年度企业的年生产能力为60万辆，在该地区的广告投入为380万元，该地区家庭的平均年收入为3.64万元）。所建立的模型的结构如图10-14所示：图10-4预测的最终结果10.4销售预测模型

销售预测主要是对销售量和销售额进行预测。本节将学习如何使用函数实现销售预测。【例10-9】某汽车制造企业2010年各月的汽车销售额的有关资料如表10-8所示。根据以往的经验，该企业的销售额随着时间的推移有可能呈线性变动趋势或指数变动趋势。要求建立一个可以选择销售额变动趋势的组合框控件，根据2010年各月的销售额数据判断并选择一种变动趋势，据此预测2011年1-6月份的汽车销售额。所建立的模型的结构如图10-22所示：图10-22利用函数预测销售额的模型【例10-10】某公司2011年上半年各月的产品销量如表10-9所示，要求预测该公司2011年下半年各月的产品销量并对此进行趋势分析。所建立的模型的结构如图10-24所示：图10-24销量预测结果与趋势分析图表对于：y=mx+bor

y=m1x1+m2x2+...+b（如果有多个区域的x值）统计值说明se1,se2,...,sen系数m1,m2,...,mn的标准误差值。seb常量b的标准误差值（当const为FALSE时，seb=#N/A）r2判定系数。Y的估计值与实际值之比，范围在0到1之间。如果为1，则样本有很好的相关性，Y的估计值与实际值之间没有差别。如果判定系数为0，则回归公式不能用来预测Y值。有关计算r2的方法的详细信息，请参阅本主题后面的“说明”。seyY估计值的标准误差。FF统计或