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

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

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

意见汇集法定性预测专家小组法德尔菲法

移动平均法

趋势外推法定量预测指数平滑法

因果预测法——回归分析法10.2财务预测相关的函数在Excel中经常用到的财务预测函数有两类：用于进行线性变动趋势预测的TREND函数、SLOPE函数、INTERCEPT函数、FORECAST函数、LINEST函数以及用于进行指数变动趋势预测的LOGEST函数和GROWTH函数。（1）LINEST函数功能：使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。因为此函数返回数值数组，故必须以数组公式的形式输入。公式：=LINEST(known_y's,known_x's,const,stats)※式中，known_y's是关系表达式y=mx+b中已知的一组y值。如果数组known_y's为一单列，则known_x's的每一列都被当作单独的列变量；如果数组known_y's为一单行，则known_x's的每一行都被当作单独的行变量。known_x's是关系表达式y=mx+b中已知的一组x值。

※const为一逻辑值，指明是否强制使常数b为0。如果const为TRUE或省略，b将被正常计算。如果const为FALSE，b将被设为0，并同时调整m值使y=mx。※stats为一逻辑值，指明是否返回附加回归统计值。如果stats为TRUE，函数LINEST返回附加回归统计值。如果stats为FALSE或省略，函数LINEST只返回系数m和常数项b，忽略统计误差的计算。【例10-1】某企业今年1-6月份各月的实际销售额分别为280万元、300万元、290万元、310万元、320万元、330万元，要求求出拟合一次线性方程。

（2）FORECAST函数功能：根据给定的数据计算或预测一元线性回归模型的未来值。此预测值为基于已知的x值推导出的y值。以数组或数据区域的形式给定x值和y值后，返回基于x的线性回归预测值。使用此函数可以对未来销售收入、库存需求或消费趋势进行预测。公式：=FORECAST(x,known_y’s,known_x’s)※式中，x为需要进行预测的数据点；known_y’s为因变量数组或数据区域；known_x’s为自变量数组或数据区域。如果x为非数值型，函数FORECAST返回错误值#VALUE!；如果known_y's和known_x's为空或含有不同个数的数据点，函数FORECAST返回错误值#N/A；如果known_x's的方差为零，函数FORECAST返回错误值#DIV/0!。（3）TREND函数功能：TREND函数返回依据多元线性回归趋势而得的预测值。它先找到适合给定的数组known_y’s和known_x’s的直线（用最小二乘法），然后对用户给定的新数组new_x’s值返回拟合趋势的y值。公式：=TREND(known_y’s,known_x’s,new_x’s,const)【例10-2】假设某企业想在7月份购买一块土地。选取了该片土地中有代表性的一块土地的12个月（上年3月至本年2月）的价格信息。Known-y’s是已知数据集（￥133,890、￥135,000、￥135,790、￥137,300、￥138,130、￥139,100、￥139,900、￥141,120、￥141,890、￥143,230、￥144,000、￥145,290）的值，这些数据分别保存在B2:B13中。当以垂直数组的形式输入到C2:C6区域时，下面公式返回三月、四月、五月、六月和七月的预测价格：TREND(B2:B13,,{13;14;15;16;17})等于{146172;147190;148208;149226;150244}可以预计七月份将能以￥150,244的价格买到类似的土地。这个预测公式用默认数组{1;2;3;4;5;6;7;8;9;10;11;12}作为known_x‘s参数，分别对应于12个月的销售数据。数组{13;14;15;16;17}分别对应于下五个月。（4）LOGEST函数功能：LOGEST函数计算最符合观测数据组的指数回归拟合曲线，并返回描述该曲线的数组。公式：=LOGEST(known_y’s,known_x’s,const,stats)【例10-3】在走过十个月的销售低谷之后，某公司发现它在市场上推出的新产品使其销售量呈指数增长。在此后六个月中，销售量逐月增加，依次为33,100，47,300，69,000，102,000，150,000和220,000单位。假设这些数据已经保存在一个命名为UnitsSold的包含六个单元格的区域中。输入下列数组公式：“=LOGEST(UnitsSold,{11;12;13;14;15;16},TRUE,TRUE)”，会产生D1:E5单元格区域中的结果，如下表所示（5）GROWTH函数功能：GROWTH函数根据known_y’s和known_x’s给定的数据拟合指数曲线，根据所给定的新new_x’s数组返回其对应的y值。公式：=GROWTH(known_y’s,known_x’s,new_x’s,const)【例10-4】采用【例10-3】中的数据。第11到16个月的销售量分别是33,100、47,300、69,000、102,000、150,000和220,000个单位。假设这些值已经输入到名为“UnitSold”的六个单元格中。以数组公式输入，下面的公式根据前六个月的销售量预测第17和18个月的销售量：GROWTH(UnitsSold,{11;12;13;14;15;16},{17;18})={320,197;468,536}如果按指数趋势增长，第17和第18个月的销售量分别是320,197和468,536个单位。以上介绍了有关回归分析预测工作表函数，总结如下表所示：

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-2所示：图10-2利用指数平滑分析工具进行预测的结果10.3.3利用回归工具进行财务预测回归分析法是指在掌握大量观测数据的基础上，利用数理统计方法建立因变量与自变量之间的回归关系函数表达式并据此进行预测的方法。【例10-7】电视台的广告收入与收看该电视台节目的观众人数存在一定的关系，为了研究这一关系，现抽取某电视台2010年1-12月的广告收入和日均观众人数，如表10-6所示，要求根据电视台的广告收入和日均观众人数绘图并据此判断能否进行回归分析，如果可以的话，利用回归分析工具预测2011年1月该电视台的广告收入（当月的日均观众人数为8358万人）。所建立的模型的结构如图10-3所示：图10-3利用回归分析工具进行预测的结果利用回归分析工具进行多元线性回归分析【例10-8】现有一家汽车制造企业，专门生产某品牌的家用轿车。该企业的销售经理为了预测2011年度该企业在某地区的销售额，特将2001-2010年度企业的年生产能力、在该地区的广告投入和销售额以及该地区家庭的平均年收入整理汇总如表10-7所示，试图找出销售额与这三个变量之间的关系。要求根据提供的数据，利用回归分析工具建立一个最合适的回归方程对2011年度该企业在本地区的销售额进行预测（假设2011年度企业的年生产能力为60万辆，在该地区的广告投入为380万元，该地区家庭的平均年收入为3.64万元）。所建立的模型的结构如图10-4所示：图10-4预测的最终结果10.4销售预测模型销售预测主要是对销售量和销售额进行预测。本节将学习如何使用函数实现销售预测。【例10-9】某汽车制造企业2010年各月的汽车销售额的有关资料如表10-8所示。根据以往的经验，该企业的销售额随着时间的推移有可能呈线性变动趋势或指数