第10章Excel在财务预测中应用.pptx

《第10章Excel在财务预测中应用.pptx》由会员分享，可在线阅读，更多相关《第10章Excel在财务预测中应用.pptx（26页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、12022年5月30日第10章 Excel在财务预测中应用本章教学要求：本章教学要求：v 掌握财务预测相关的函数v 掌握利用数据分析工具进行财务预测的方法v 掌握建立销售预测模型的方法并进行销售预测22022年5月30日主要内容：10.1 财务预测概述及方法10.2 财务预测相关的函数10.3 利用数据分析工具进行财务预测10.4 销售预测模型第10章 Excel在财务预测中应用32022年5月30日10.1.1 财务预测概述财务预测概述 财务预测是指以企业过去一定时期财务活动资料为依据，结合企业现在面临和即将面临的各种因素变化，运用科学的定量和定性方法，对企业未来的财务活动和财务成果所做出的

2、科学预计和合理判断。 10.1.2 财务预测方法财务预测方法 移动平均法指数平滑法时间序列预测法因果预测法回归分析法定量预测意见汇集法专家小组法德尔菲法定性预测一元线性回归二元线性回归多元线性回归42022年5月30日 在Excel中经常用到的财务预测函数有两类：1.用于进行用于进行线性变动线性变动趋势预测的函数趋势预测的函数v INTERCEPT函数v SLOPE函数v LINEST函数vFORECAST函数vTREND函数2. 用于进行用于进行指数变动指数变动趋势预测的函数趋势预测的函数vLOGEST函数vGROWTH函数。 52022年5月30日（1）INTERCEPT函数v功能：INT

3、ERCEPT函数专门用来计算一次线性回归模型y=a+bx方程中的a的值。v语法格式：INTERCEPT(known_ys,known_xs)参数known_ys为所观察的因变量数据或数据组，known_xs为所观察的自变量数据或数据组。需要注意的是，参数必须是数字、名称、数组或包含有的引用；如果数组或引用参数包含文字、逻辑值，或空白单元格，则忽略其值，但将考虑在内。如果known_ys和known_xs的数据点数目不相等或不包含任何数据点，则函数 INTERCEPT 返回错误值#N/A。 62022年5月30日（2）SLOPE函数v功能：SLOPE函数专门用来计算一次线性回归模型y=a+bx方

4、程中的b的值。v语法格式：SLOPE(known_ys,known_xs) 【例例10-110-1】某企业今年1-6月份各月的实际销售额分别为280万元、300万元、290万元、310万元、320万元、330万元，要求求出拟合一次线性方程。72022年5月30日（3）LINEST函数v 功能：LINEST函数用来计算一次线性回归模型y=a+bx方程中的a、b的值。v 语法格式：LINEST(known_ys,known_xs,const,stats) 参数：Const 为一逻辑值，用于指定是否将常量 a 强制设为 0。如果 const 为 TRUE 或省略，a 将按正常计算。如果 const

5、为 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。82022年5月30日（4）FORECAST函数v功能：FORECAST函数根据给定的数据计算或预测一元线性回归模型的未来值。使用此函数可以对未来销售额、库存需求或消费趋势进行预测。

6、v语法格式：FORECAST(x,known_ys,known_xs)参数说明：vx为需要进行预测的数据点，FORECAST函数给出相应的在x点处的预测值。如果x不是数字，函数FORECAST返回错误值#VALUE！。92022年5月30日如果known_ys和 known_xs为空或含有不同数目的数据点，函数FORECAST返回错误值#N/A。 （5）TREND函数v功能：TREND函数返回依据多元线性回归多元线性回归趋势而得的预测值。它先找到适合给定的数组known_ys和known_xs的直线（用最小二乘法），然后对用户给定的新数组new_xs值返回拟合趋势的y值。v语法 TREND(k

7、nown_ys,known_xs,new_xs,const)见见P287例例10-2102022年5月30日（6） LOGEST函数v功能：LOGEST函数计算最符合观测数据组的指数回归指数回归拟合曲线，并返回描述该曲线的数组。v 回归拟合曲线的方程为：y = b*mx v语法格式：LOGEST(known_ys, known_xs, const, stats) （7）Growth函数 自己看课本本章自己看课本本章P287-289112022年5月30日以上介绍了有关回归分析预测工作表函数，总结如下表所示：以上介绍了有关回归分析预测工作表函数，总结如下表所示： 函数名函数名定定 义义语法格式语

8、法格式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)122022年5月30日10

9、.3.1 利用移动平均工具进行财务预测利用移动平均工具进行财务预测 移动平均分析工具可以基于特定的过去某段时期中变量的平均值对未来值进行预测。移动平均值提供了由历史数据的简单平均之所代表的趋势信息。使用此工具可以预测销售量、库存或其他趋势。【例例10-5】某汽车制造企业在2010年1-12月份销售的汽车数量如表10-5所示。要求在Excel中对2010年各月的汽车销量绘图并据此判断是否可以采用移动平均法对销量进行预测，如果可以的话，利用移动平均工具预测2011年1月的销量。132022年5月30日所建立的模型的结构如图10-1所示：图10-1 利用移动平均分析工具进行预测的结果142022年5

10、月30日10.3.2 利用指数平滑工具进行财务预测利用指数平滑工具进行财务预测 指数平滑分析工具是基于前期预测值到处相应的新预测值，并修正前期预测值的误差。此工具将使用阻尼系数，其大小决定了本次预测对前期预测误差的修正程度。【例例10-6】沿用【例10-5】的已知条件，要求利用Excel数据分析库中的指数平滑工具（阻尼系数=0.4）预测2011年1月份的汽车销量。 所建立的模型的结构如图10-8所示：152022年5月30日图10-8 利用指数平滑分析工具进行预测的结果162022年5月30日10.3.3 利用回归工具进行财务预测利用回归工具进行财务预测回归分析法是指在掌握大量观测数据的基础上

11、，利用数理统计方法建立因变量与自变量之间的回归关系函数表达式并据此进行预测的方法。 【例例10-7】电视台的广告收入与收看该电视台节目的观众人数存在一定的关系，为了研究这一关系，现抽取某电视台2010年1-12月的广告收入和日均观众人数，如表10-6所示，要求根据电视台的广告收入和日均观众人数绘图并据此判断能否进行回归分析，如果可以的话，利用回归分析工具预测2011年1月该电视台的广告收入（当月的日均观众人数为8358万人）。172022年5月30日所建立的模型的结构如图10-11所示：182022年5月30日图10-11 利用回归分析工具进行预测的结果192022年5月30日【例例10-8】

12、现有一家汽车制造企业，专门生产某品牌的家用轿车。该企业的销售经理为了预测2011年度该企业在某地区的销售额，特将2001-2010年度企业的年生产能力、在该地区的广告投入和销售额以及该地区家庭的平均年收入整理汇总如表10-7所示，试图找出销售额与这三个变量之间的关系。要求根据提供的数据，利用回归分析工具建立一个最合适的回归方程对2011年度该企业在本地区的销售额进行预测（假设2011年度企业的年生产能力为60万辆，在该地区的广告投入为380万元，该地区家庭的平均年收入为3.64万元）。202022年5月30日所建立的模型的结构如图10-14所示：212022年5月30日图10-4 预测的最终结

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

14、-9所示，要求预测该公司2011年下半年各月的产品销量并对此进行趋势分析。所建立的模型的结构如图10-24所示：252022年5月30日图10-24 销量预测结果与趋势分析图表262022年5月30日y = mx + b or y = m1x1 + m2x2 + . + b（如果有多个区域的 x 值）统计值统计值说明说明se1,se2,.,sen系数 m1,m2,.,mn 的标准误差值。seb常量 b 的标准误差值（当 const 为 FALSE时，seb = #N/A）r2判定系数。Y 的估计值与实际值之比，范围在 0 到 1 之间。如果为 1，则样本有很好的相关性，Y 的估计值与实际值之间

15、没有差别。如果判定系数为 0，则回归公式不能用来预测 Y 值。有关计算 r2 的方法的详细信息，请参阅本主题后面的“说明”。seyY 估计值的标准误差。FF 统计或 F 观察值。使用 F 统计可以判断因变量和自变量之间是否偶尔发生过可观察到的关系。df自由度。用于在统计表上查找 F 临界值。所查得的值和 LINEST 函数返回的 F 统计值的比值可用来判断模型的置信度。有关如何计算 df，请参阅在此主题中后面的“说明”。示例 4 说明了 F 和 df 的使用。ssreg回归平方和。ssresid残差平方和。 有关计算 ssreg 和 ssresid 的方法的详细信息，请参阅本主题后面的“说明”。返回返回