Excel在财务预测中的的应用解析PPT学习教案

1、会计学1Excel在财务预测中的的应用解析在财务预测中的的应用解析2021-9-24-2-第1页/共64页2021-9-24-3-第2页/共64页2021-9-24-4-第3页/共64页2021-9-24-5-移动平均法一次移动平均法二次移动平均法2、指数平滑法第4页/共64页2021-9-24-6-bmxy 多元线性回归bxmxmxmynn2211 式中，y为因变量；x是自变量；m1、m2、mn、b分别为预测模型的待估计参数第5页/共64页2021-9-24-7-xbmy 多元指数模型321321xxxmmmby 式中，y为因变量；x是自变量；m1、m2、mn、b分别为预测模型的待估计参数

2、定型预测方法第6页/共64页2021-9-24-8-第7页/共64页2021-9-24-9-第8页/共64页2021-9-24-10-m mn nm mn-1n-1m m2 2m m1 1b bSESEn nSESEn-1n-1SESE2 2SESE1 1SESEb bR R2 2SESEy yF F D Df fSSSSregregSSSSresidresid回归统计值返回的顺序列号 1 2 n-1 n n+1 12345行号表4-1第9页/共64页2021-9-24-11-表4-2第10页/共64页2021-9-24-12- 一、LINEST( )函数用于直线回归分析 LINEST函数的功

3、能是使用最小二乘法计算对已知数据最佳线性拟合的直线方程，并返回描述此线性模型的数组。 语法： LINEST(known_ys,known_xs,const,stats) 一元线性回归分析 LINEST函数可用于一元线性回归分析，也可用于多元线性回归分析以及时间数列的回归分析。 设一元回归方程为：y=mx+b,则参数和相关系数的求解过程为：第11页/共64页2021-9-24-13- 如:m参数是数组中第1行、第1列元素，因此，m参数的计算公式为： INDEX(LINEST(Y变量单元区域,X变量单元区域,TRUE,TRUE),1,1) b参数是数组中第1行、第2列元素，因此，b参数的计算公式为

4、： INDEX(LINEST(Y变量单元区域,X变量单元区域,TRUE,TRUE),1,2) R2参数是数组中第3行、第1列元素，计算公式为： INDEX(LINEST(Y变量单元区域,X变量单元区域,TRUE,TRUE),3,1)第12页/共64页2021-9-24-14- 例41：某企业19月份的总成本与人工小时及机器工时的数据见表43。假设总成本与人工小时之间存在线性关系，如第10、11、12各期的人工小时为308、335、363。用一元线性回归分析方程预测第10、11、12期的总成本。求解过程第13页/共64页2021-9-24-15-图4-1第14页/共64页2021-9-24-16

5、-。第五步：在单元格J10中输入公式“=INDEX(LINEST(D6:D14,E6:E14,TRUE),3,2)”，计算预测标准差。第六步：在单元格J14中输入公式“=I6*J13+J6”，计算y值。第15页/共64页2021-9-24-17-第16页/共64页2021-9-24-18-图4-2第17页/共64页2021-9-24-19-第18页/共64页2021-9-24-20-第19页/共64页2021-9-24-21- 二、LOGEST函数 LOGEST函数的功能是在回归分析中，计算最符合数据组的指数回归拟合曲线，并返回描述该指数模型的数组。由于这个函数返回一个数组，必须以数组公式输入

6、。 LOGEST函数的公式为： =LOGEST(known_y,s,known_x,s,const,stats) 例43：某企业12个月某产品的生产量(x)与生产成本(Y)的有关数据如表43所示，假设它们之间有如下关系：Y=bmx。计算该回归函数。第20页/共64页2021-9-24-22-月份月份生产量( 万件)x生产量( 万件)x生产成本( 元/ 件)生产成本( 元/ 件)1 110.210.2564.3564.32 210.3410.34536.8536.83 310.5510.55518.3518.34 410.9210.92487.6487.65 511.1511.15473.547

7、3.56 611.4311.43456.1456.17 711.5411.54446.4446.48 812.0612.06421.7421.79 912.4512.45401.7401.7101012.9312.93389.6389.6111113.3413.34391.2391.2121213.2513.25378.2378.2生产函数模型生产函数模型表4-5第21页/共64页2021-9-24-23-图4-3 求解第22页/共64页2021-9-24-24-图4-4第23页/共64页2021-9-24-25-第三节 销售预测 一、销售预测的基本方法 进行销售预测的方法很多，常用的方法包括

8、以下几种。 时间序列预测 时间序列预测法，是将观察或记录的一些历史数据，按时间的先后排列成数据系列，进行统计分析，找出过去长期的销售量或销售额的增减变化趋势，在根据此变化趋势分析的结果，预测未来时期的销售量或销售额。如简单平均法、移动平均法、指数平滑法，或以时间为自变量的回归分析法等。 第24页/共64页2021-9-24-26- 因果关系预测法 因果分析法，是指利用有关因素与产品销售量或销售额之间故有因果关系，通过建立一定的数学模型预测企业未来的产品销售水平的一种方法。 二、销售预测模型及其应用 一元线性（非线性）回归预测模型 例44，根据表44中所给出的资料建立一元线性（非线性）回归预测模

9、型，并计算未来第14期的预测值。第25页/共64页2021-9-24-27- 建立过程： 第一步：建立预测模型，如图45所示。 图4-5第26页/共64页2021-9-24-28-第27页/共64页2021-9-24-29- 第六步：在单元格F9中输入公式 “=IF(B8=1,INDEX(LINEST(销售序列,影响因素序列,TRUE,TRUE),1,1),INDEX(LOGEST(销售序列,影响因素序列,TRUE,TRUE),1,1)”，计算系数B。 第七步： 在单元格G9中输入公式“=IF(D8=1,INDEX(LINEST(销售序列,影响因素序列,TRUE,TRUE),3,1),INDE

10、X(LOGEST(销售序列,影响因素序列,TRUE,TRUE),3,1)”，计算相关系R2。 第八步：在单元格J9:M9中输入公式“=IF(B8=1,E9+F9*J8:M8,E9*F9J8:M8)”，（数组公式输入），计算未来第14器的预测值。 例44第28页/共64页2021-9-24-30- 多元线性回归预测模型 例45，根据表45中所给的资料建立多元线性回归预测模型。建立过程同例44，结果见图4-6。第29页/共64页2021-9-24-31-图4-6第30页/共64页2021-9-24-32-第31页/共64页2021-9-24-33-第32页/共64页2021-9-24-34-第33

11、页/共64页2021-9-24-35-第34页/共64页2021-9-24-36-第35页/共64页2021-9-24-37- 成本预测模型的数学表达式为： 一元一次模型（线性模型） 销售成本固定成本单位变动成本销售量 一元二次模型（非线性） 销售成本固定成本单位变动成本销售量 混合成本销售量平方 在Excel上建立销售成本的回归分析模型 例46，建立一元一次（二次）成本预测模型。 相关资料见表48。第36页/共64页2021-9-24-38- 第一步：建立销售预测模型，如图4-7所示。假设已有过去12期的历史数据。 第37页/共64页2021-9-24-39-第38页/共64页2021-9-

12、24-40-第39页/共64页2021-9-24-41-第40页/共64页2021-9-24-42-第41页/共64页2021-9-24-43-第五节 利润预测 利润预测是指在销售预测的基础上，根据企业未来发展和其他相关资料，预计、推测、或估算未来应该达到和希望实现的利润水平及其变动趋势的过程。一般可在对成本、业务量、利润之间的关系进行研究的基础上作出利润预测，这种预测方法称为本量力分析法。 一、确定性条件下单品种利润敏感性分析模型 敏感性分析是一种定量分析方法，它研究当制约利润的有关因素发生某种变化时对利润所产生的影响，这对于利润预测分析，特别是对目标利润预测有着十分积极的指导意义。 在企业

13、只生产单品种产品，且产销平衡的条件下，本量力之间的基本关系为：第42页/共64页2021-9-24-44- 利润销售量(单价单位变动成本)-固定成本 在进行敏感性分析时，假定条件如下：利润只受销售量、产品单价、单位变动成本和固定成本影响；上述各因素变动均不会影响其它因素的变动。 例47，根据图表48所给出的资料建立确定性条件下单品种利润敏感性分析模型。项目项目数值数值变化后数据变化后数据变动百分比变动百分比销售量10001100.0010.0%60产品单价100110.0010.0%60单位变动成本7063.00-10.0%40固定成本50004500.00-10.0%40变动百分比选择滚动条

14、变动百分比选择滚动条基基 本本 数数 据据图4-8第43页/共64页2021-9-24-45-预计利润变动后利润利润变动额变动幅度25000472002220088.80%项目项目变动前利润变动前利润 因素变动百分比因素变动百分比 变动后利润变动后利润 利润变动额利润变动额 变动幅度变动幅度销售量2500010.00%28000.003000.0012.00%产品单价2500010.00%35000.0010000.0040.00%单位变动成本25000-10.00%32000.007000.0028.00%固定成本25000-10.00%25500.00500.002.00%单因素变动对利润

15、的影响单因素变动对利润的影响多因素变动对利润的综合影响多因素变动对利润的综合影响 创建过程： 1、首先设计销售量的滚动条控件，设计过程参见第三章例34。要求销售量的变动范围为50。控件具体位置见模型。第44页/共64页2021-9-24-46- 2、影响利润的其他因素的股东条设计过程同销售量。 3、在单元格E4:E7中建立变动百分比与滚动条控件的联系，即选取单元格E4:E7并输入公式“F4：F710050”（数组公式）。 4、选取单元格D4:D7并输入变化后各项目数值的计算公式“C4:C7*（1E4:E7）（数组公式） 5、在单元格B10输入预计利润计算公式：“C4*(C5-C6)C7”，并复

16、制到单元格C10中，在单元格D10输入公式“C10B10”，在单元格E10输入公式“D10/B10”。这样，就得到了多因素变动对利润的影响。第45页/共64页2021-9-24-47- 6、设计单因素变动影响分析表格 在C13:C16中输入公式“B10”(数组输入公式)，在单元格D13:D16中输入公式“E4:47”，在单元格E13:E16中分别输入销售量、产品单价、单位变动成本和固定成本单独变动时对利润的影响计算公式。在单元格F13:F16中输入公式“E13:E16D13:D16”（数组公式）；在单元格G13:G16中输入公式“F13:F16C13：C16”(数组公式)。 这样利润的敏感性分

17、析模型就建立起来了。单击各个影响因素滚动条的箭头，改变其变动幅度，就可以很方便地了解各个因素变动对利润的单独影响程度以及综合影响程度。第46页/共64页2021-9-24-48-图4-9第47页/共64页2021-9-24-49-第48页/共64页2021-9-24-50-图4-10第49页/共64页2021-9-24-51-第50页/共64页2021-9-24-52- 第六步：单击求解按钮，即得在满足约束条件下企业实现最大利润的最佳产品结构为：产品A：16429件，产品B：31429件，产品C：35000件，最大利润为416428元，如图4-10所示。第51页/共64页2021-9-24-5

18、3-第六节 资金需要量预测 一、资金需要的预测方法 资金需要量的预测方法有多种，这里重要介绍两种：销售百分比法和资金性态法。 销售百分比法 由于销售额是影响资金需要量的最重要的因素，因此，可通过分析资金各个项目与销售收入总额之间的依存关系，按预期的销售收入的增长情况来预测资金需要量，这就是销售百分比法。 步骤： 第一步：确定敏感性项目与非敏感性项目 敏感性项目是指其金额随销售收入自动成正比例增减变动的项目，包括敏感性资产和负债项目。第52页/共64页2021-9-24-54- 敏感性资产项目一般包括现金、应收帐款、存货等，如企业生产能力没有剩余，那么继续增加销售收入，就需要增加新的固定资产投资

19、，此时固定资产也成为敏感性资产。 敏感性负债项目一般包括应付帐款、应交税金等。长期性负债一般为非敏感性项目。 非敏感性项目是指其金额不随销售收入自动成正比例变动的项目。 第二步：对于敏感性项目，计算其基期的金额占基期销售收入的百分比，并分别计算敏感性项目干销售收入百分比的合计数。第53页/共64页2021-9-24-55- 第三步：根据计划期的销售收入和销售净利率，结合计划期支付股利的比率，确定计划期内部留存收益的增加额。 第四步：根据销售收入的增长额确定企业计划期需要从外部筹措的资金需要量。计算公式为：1101MD1RSSLSSSAM 第54页/共64页2021-9-24-56- 式中式中

20、M M：外部融资需要量；外部融资需要量； D D：股利支付率股利支付率 S S0 0：基期销售额；基期销售额； ( (A/S)A/S)：敏感资产占基期销售额百分比；敏感资产占基期销售额百分比； S S1 1：计划销售额；计划销售额； ( (L/S)L/S)：敏感负债占基期销售额百分比；敏感负债占基期销售额百分比； R R：销售净利率；销售净利率； M M1 1：计划期零星资金需求。计划期零星资金需求。 销售百分比法是资金需要量预测中比较简单销售百分比法是资金需要量预测中比较简单的一种方法，适合于预测较短期的资金变动，但的一种方法，适合于预测较短期的资金变动，但无法对长期资金需要量进行预测。无法

21、对长期资金需要量进行预测。第55页/共64页2021-9-24-57- 例48：某公司2004年度的销售收入为2000000元，销售净利率5，股利支付率40。当年厂房设备利用率已达饱和状态。该公司2004年末的简略资产负债表如图78所示。该公司预计2005年度的销售收入将增至2800000元，销售净利率和股利支付率都和上年保持相同。2005年的零星资金需要量为15000元。预测该公司2005年的外部资金需要量。 步骤：第56页/共64页2021-9-24-58-项目项目是否敏感项目是否敏感项目占基期销占基期销售百分比售百分比项目项目是否敏感项目是否敏感项目占基期占基期销售百销售百分比分比货币资

22、金货币资金是是2.00%2.00% 应付帐款应付帐款是是10.00%10.00%应收帐款应收帐款是是17.00%17.00% 应交税金应交税金是是5.00%5.00%存货存货是是20.00%20.00% 长期负债长期负债否否无意义无意义固定资产固定资产是是30.00%30.00% 普通股本普通股本否否无意义无意义无形资产无形资产否否无意义无意义 留存收益留存收益否否无意义无意义合计合计69.00%69.00% 合计合计15.00%15.00%3630003630002005年资金需要量预测2005年资金需要量预测2005年资金需要量预测( 元)第57页/共64页2021-9-24-59- 第一

23、步：首先对有关项目是否敏感进行判断，填入单元格C4:C8和单元格F4:F8。 第二步：在单元格D4中输入公式“IF(C4=”是“,资料!B5/资料!$F$5,”无意义“)”，然后将其往下复制到单元格D8；并在单元格D9中输入公式“=SUM(D4:D8)”； 第三步：在单元格G4中输入公式“IF(F4=”是“,资料!D5/资料!$F$5,”无意义“)”，然后将其往下复制到单元格G8；并在单元格G9中输入公式“=SUM(G4:G8)”； 第四步：在单元格D11中输入计算公式“=D9*(资料!G5-资料!F5)-G9*(资料!G5-资料!F5)-资料!G5*资料!F6*(1-资料!F7)+资料!G8

24、”，即得该公司2005年的外部资金需要量为363000元。第58页/共64页2021-9-24-60- 二、资金性态法 资金性态法是指根据资金的变动通产销量之间的依存关系预测未来资金需要的一种方法。资金性态法具体包括高低点法和回归分析法。 该方法的前提是把企业的总资金划分为变动资金和固定资金两部分。 高低点法的基本原理是：在销售与资金变动的历史数据中，找出产销量最高和最低的两点及其所对应的资金占用，根据这两对历史数据求出直线方程，作为预测资金需要量的模型。 回归分析法则是根据全部历史数据，利用回归分析法求出直线方程，作为预测资金需要量的模型。第59页/共64页2021-9-24-61- 例49：某企业产销量和资金变化情况如表79所示。2005年预计销售量为750件，是分别用高低点法和回归分析法预测2005年的资金需要量。假设资金需要量与销售量的关系为： 资金需要量(Y)a+b销售量(x) a，b为待估计参数。 第60页/共64页2021-9-24-62-年度年度产销量( x 万件 )产销量( x 万件 )资金占用( Y 万元)资金占用( Y 万元)19991999600600500500200020005505504754752001200150050045045020022002600600500500200320036