第四章_excel在财务预测..

《第四章_excel在财务预测..》由会员分享，可在线阅读，更多相关《第四章_excel在财务预测..（14页珍藏版）》请在金锄头文库上搜索。

1、第四章 Excel在财务预测中的应用 第一节 财务预测概述财务预测，是指对企业未来的收入、成本、利润、现金流量及融资需求等财务指标所作的估计和推算。财务预测有助于改善企业的投资决策。虽然投资决策是决定筹资与否和筹资多少的重要因素，但是根据销售前景估计出融资需求，并不一定能够得到全部满足。这时，就需要根据可能筹措到的资金来安排销售增长以及有关的投资项目，使投资决策建立在可行的基础上。一、财务预测的一般步骤1. 销售量预测 售量预测是指根据市场调查所得到的有关资料，通过对有关因素的分析研究，预计和推算特定产品在未来一定时期内的市场销售量水平及变化趋势，进而预测企业产品未来销售量的过程。2. 估计收

2、入、费用和利润 收入和费用与销售量也存在一定的函数关系，因此，可以根据销售数据估计收入和费用，并确定净利润。3. 估计需要的资产 资产通常是销售收入的函数，根据历史数据可以分析出二者之间的函数关系。根据预计销售收入和资产与销售之间的函数关系，可以预测所需要资产的总量。某些流动负债也是销售收入的函数，相应的可以预测负债的自发增长额，这种增长可以减少企业外部融资的数额。 4. 估计所需融资 根据预计资产总量，减去已有的资金来源、负债的自发增长和内部提供的留存收益，可得出所需的外部融资数额。 二、财务预测的分析方法一次移动平均法 定量预测方法 二次移动平均法1.移动平均法 2.指数平滑法 3.回归预

3、测法 财务预测中常用的几种回归模型 一元线性回归模型y=mx+b多元线性回归 （式中，y为因变量；x是自变量；m1、m2、mn、b分别为预测模型的待估计参数） 一元非线性回归模型 多元指数模型（式中，y为因变量；x是自变量；m1、m2、mn、b分别为预测模型的待估计参数） 定型预测方法第二节 Excel中的有关预测函数及其应用Excel提供的预测函数主要有LINEST函数、LOGEST函数、TREND函数、GROWH函数、SLOPE函数、INTERCEPT函数。它们所使用的函数基本相同： Known_ys：因变量y的观测值 集合； Known_xs：自变量x的观测值集合； Const：指明是否

4、使常数b为0(线性模型)或为1(指数模型)，如Const为True或忽略，b将被正常计算。如Const为False，b将被设为0(线性模型)或设为1(指数模型) ； Stats：逻辑值，指明是否返回附加回归统计值。如果Stats为True，则函数返回附加回归统计值(为数组)。如果Stats为False或省略 ，函数只返回预测模型的待估参数。 附加回归统计值返回的顺序见表4-1回归统计值返回的顺序 列号行号12n-1nn+112345表4-1中各参数说明见表4-2。一、LINEST( )函数用于直线回归分析 LINEST函数的功能是使用最小二乘法计算对已知数据最佳线性拟合的直线方程，并返回描述此

5、线性模型的数组。 语法： LINEST(known_ys,known_xs,const,stats) 一元线性回归分析LINEST函数可用于一元线性回归分析，也可用于多元线性回归分析以及时间数列的回归分析。 设一元回归方程为：y=mx+b,则参数和相关系数的求解过程为：如:m参数是数组中第1行、第1列元素，因此，m参数的计算公式为： INDEX(LINEST(Y变量单元区域,X变量单元区域,TRUE,TRUE), 1,1) b参数是数组中第1行、第2列元素，因此，b参数的计算公式为： INDEX(LINEST(Y变量单元区域,X变量单元区域,TRUE,TRUE),1,2) R2参数是数组中第3

6、行、第1列元素，计算公式为： INDEX(LINEST(Y变量单元区域,X变量单元区域,TRUE,TRUE),3,1)例41：某企业19月份的总成本与人工小时及机器工时的数据见表43。假设总成本与人工小时之间存在线性关系，如第10、11、12各期的人工小时为308、335、363。用一元线性回归分析方程预测第10、11、12期的总成本。第一步：设置如图4-1所示的多元预测模型结构，设预测模型为：Y=m1x+b 第二步：在单元格I6中输入公式“=INDEX(LINEST(D6:D14,E6:E14,TRUE),1,1)”，计算参数m。 第三步：在单元格J6中输入公式“=INDEX(LINEST(

7、D6:D14,E4:E16+E4:E14,TRUE),1,2) ”，计算参数b。第四步：在单元格J9中输入公式“=INDEX(LINEST(D6:D14,E6:E14,TRUE),3,1) ”，计算相关系数R2。第五步：在单元格J10中输入公式“=INDEX(LINEST(D6:D14,E6:E14,TRUE),3,2) ”，计算预测标准差。第六步：在单元格J14中输入公式“=I6*J13+J6”，计算y值。例42：根据表4-4 的数据，设计二元回归方程预总成本。第一步：设置如图4-1所示的多元预测模型结构 第二步：在单元格I5中输入公式“=INDEX(LINEST(D5:D13,E5:F13

8、,TRUE),1,1)”，计算参数m2。 第三步：在单元格J5中输入公式“=INDEX(LINEST(D5:D13,E5:F13,TRUE),1,2)”，计算参数m1。 第四步：在单元格K5中输入公式“=INDEX(LINEST(D5:D13,E5:F13,TRUE),1,3)”，计算参数b。 第五步：在单元格K8中输入公式“=INDEX(LINEST(D5:D13,E5:F13,TRUE),3,1)”，计算相关系数R2。 第六步：在单元格K9中输入公式“=INDEX(LINEST(D5:D13,E5:F13,TRUE),3,2)”，计算预测标准差。 第七步：在单元格K14中输入公式“=J5*

9、K12+I5*K13+K5”，计算y值。 例4-2的设计过程：二、LOGEST函数 LOGEST函数的功能是在回归分析中，计算最符合数据组的指数回归拟合曲线，并返回描述该指数模型的数组。由于这个函数返回一个数组，必须以数组公式输入。 LOGEST函数的公式为： =LOGEST(known_y,s,known_x,s,const,stats) 例43：某企业12个月某产品的生产量(x)与生产成本(Y)的有关数据如表43所示，假设它们之间有如下关系：Y=bmx。计算该回归函数。第一步：设置如图4-3所示的多元预测模型结构第二步：选取单元格区域E3:F7，输入公式=LOGEST(D3:D14,C3:

10、C14,TRUE,TRUE)，计算参数。第三步：按数组生成组合键CTRL+SHIFT+ENTER，生成参数数组，见图4-4。各参数的位置同线性回归参数数表。第三节 销售预测一、销售预测的基本方法 进行销售预测的方法很多，常用的方法包括以下几种。 时间序列预测 时间序列预测法，是将观察或记录的一些历史数据，按时间的先后排列成数据系列，进行统计分析，找出过去长期的销售量或销售额的增减变化趋势，在根据此变化趋势分析的结果，预测未来时期的销售量或销售额。如简单平均法、移动平均法、指数平滑法，或以时间为自变量的回归分析法等。 因果关系预测法 因果分析法，是指利用有关因素与产品销售量或销售额之间故有因果关

11、系，通过建立一定的数学模型预测企业未来的产品销售水平的一种方法。二、销售预测模型及其应用 一元线性（非线性）回归预测模型 例44，根据表44中所给出的资料建立一元线性（非线性）回归预测模型，并计算未来第14期的预测值。建立过程： 第一步：建立预测模型，如图45所示。 第二步： 设置回归模型选择控件，控件的数据区域为$A$8:$A$9，单元各连接为B9，下拉显示项为2。 第三步：在C8中输入公式“=IF(B8=1,Y=m*x+b,Y=b*mx)” 第四步：将单元格区域B4:M4定义名称为“影响因素序列”，将单元格区域B5:M5定义名称为“销售序列” 第五步：在单元格E9中输入公式“IF(B8=1

12、,INDEX(LINEST(销售序列,影响因素序列,TRUE,TRUE),1,2),INDEX(LOGEST(销售序列,影响因素序列,TRUE,TRUE),1,2) ”，计算系数m。第六步：在单元格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),INDEX(LOGEST(销售序列,影响因素序列,TR

13、UE,TRUE),3,1)”，计算相关系R2。 第八步：在单元格J9:M9中输入公式“=IF(B8=1,E9+F9*J8:M8,E9*F9J8:M8)”，（数组公式输入），计算未来第14器的预测值。 例44 多元线性回归预测模型 例45，根据表45中所给的资料建立多元线性回归预测模型。建立过程同例44，结果见图4-6。第一步：建立销售预测模型，如图4-6所示。第二步：选取单元格B3:M4，单击编辑栏中的名称框，将影响因素所在的单元格区域B3:M4定义为“影响因素序列”；用同样的方法，将销售额所在的单元格区域B5:M5定义为“销售序列”。第三步：在单元格D9中输入公式“=INDEX(LINEST

14、(销售序列,影响因素序列,TRUE,TRUE),1,3) ”，计算系数b。第四步：在单元格E9中输入公式“=INDEX(LINEST(销售序列,影响因素序列,TRUE,TRUE),1,2) ” ，计算系数 m1。 第五步：在单元格F9中输入公式“=INDEX(LINEST(销售序列,影响因素序列,TRUE,TRUE),1,1) ” ，计算系数 m2。 第六步：在单元格G9中输入公式“=INDEX(LINEST(销售序列,影响因素序列,TRUE,TRUE),3,1) ” ，计算相关系数R2 。第七步：在单元格J9:M9中输入公式“=D9+E9*J7:M7+F9*J8:M8 ”，按数组生成键，计算未来1-4期的预测值。在J7:M7中输入未来4期的影响因素预测数值后，即可得到回归预测模型的预测值，如图4-6所示。第四节 成本预测一、成本预测方法 成本是指企业生产和销售产品所花费的全部费用。成本可以按很多不同的标准进行分类。如按成本性态可将总成本分为变动成本、固定成本和混合成本三类。 成本预测是根据企业未来的发展目