第四章_Excel在财务预测

1第第四四章章EExxcceell在在财财务务预预测测中中的的应应用用第一节财务预测概述财务预测，是指对企业未来的收入、成本、利润、现金流量及融资需求等财务指标所作的估计和推算。财务预测有助于改善企业的投资决策。虽然投资决策是决定筹资与否和筹资多少的重要因素，但是根据销售前景估计出融资需求，并不一定能够得到全部满足。这时，就需要根据可能筹措到的资金来安排销售增长以及有关的投资项目，使投资决策建立在可行的基础上。一、财务预测的一般步骤1.销售量预测售量预测是指根据市场调查所得到的有关资料，通过对有关因素的分析研究，预计和推算特定产品在未来一定时期内的市场销售量水平及变化趋势，进而预测企业产品未来销售量的过程。2.估计收入、费用和利润收入和费用与销售量也存在一定的函数关系，因此，可以根据销售数据估计收入和费用，并确定净利润。3.估计需要的资产资产通常是销售收入的函数，根据历史数据可以分析出二者之间的函数关系。根据预计销售收入和资产与销售之间的函数关系，可以预测所需要资产的总量。某些流动负债也是销售收入的函数，相应的可以预测负债的自发增长额，这种增长可以减少企业外部融资的数额。4.估计所需融资根据预计资产总量，减去已有的资金来源、负债的自发增长和内部提供的留存收益，可得出所需的外部融资数额。二、财务预测的分析方法㈠定量预测方法2.指数平滑法3.回归预测法财务预测中常用的几种回归模型①一元线性回归模型y=mx+b②多元线性回归（式中，y为因变量；x是自变量；m1、m2、…mn、b分别为预测模型的待估计参数）③一元非线性回归模型④多元指数模型（式中，y为因变量；x是自变量；m1、m2、…mn、b分别为预测模型的待估计参数）㈡定型预测方法一次移动平均法二次移动平均法1.移动平均法2第二节Excel中的有关预测函数及其应用Excel提供的预测函数主要有LINEST函数、LOGEST函数、TREND函数、GROWH函数、SLOPE函数、INTERCEPT函数。它们所使用的函数基本相同：Known_y’s：因变量y的观测值集合；Known_x’s：自变量x的观测值集合；Const：指明是否使常数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函数的功能是使用最小二乘法计算对已知数据最佳线性拟合的直线方程，并返回描述此线性模型的数组。参数说明SE1,SE2,…SEn系数m1，m2，mn的标准误差值SEb常数项b的标准差(当CONST为FALSE时，Seb=＃N/A)R2项关系数，范围在0到1之间。SEyy估计值的标准误差FF统计值Df自由度。用在统计上查找F临界值。SSreg回归平方和SSresid残差平方和3语法：LINEST(known_y’s,known_x’s,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行、第1列元素，计算公式为：＝INDEX(LINEST(Y变量单元区域,X变量单元区域,TRUE,TRUE),3,1)例4－1：某企业1～9月份的总成本与人工小时及机器工时的数据见表4－3。假设总成本与人工小时之间存在线性关系，如第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。总成本Y人工小时x110001005011201285512301456213501807014502007515802357816602508018002809219003001006789表4－3数据表机器工时x2月份12345m1b4.414562.7276相关系数R20.998预测标准差SEy14.655下期影响因素的预测值308下期总成本预测值1922.38预测模型系数预测误差分析预测结果4第三步：在单元格J6中输入公式“=INDEX(LINEST(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值。◆例4－2：根据表4-4的数据，设计二元回归方程预总成本。第一步：设置如图4-1所示的多元预测模型结构第二步：在单元格I5中输入公式“=INDEX(LINEST(D5:D13,E5:F13,,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。m2m1b3.4323.617471.4370.99911.779人工小时308机器工时1051945.73预测模型系数预测误差分析相关系数R2预测误差SEy预测结果下期影响因素下期总成本预测值总成本Y人工小时x11000100501120128551230145621350180701450200751580235781660250801800280921900300100表4－4数据表月份机器工时x21234567895第五步：在单元格K8中输入公式“=INDEX(LINEST(D5:D13,E5:F13,,TRUE),3,1)”，计算相关系数R2。第六步：在单元格K9中输入公式“=INDEX(LINEST(D5:D13,E5:F13,,TRUE),3,2)”，计算预测标准差。第七步：在单元格K14中输入公式“=J5*K12+I5*K13+K5”，计算y值。例4-2的设计过程：二、LOGEST函数LOGEST函数的功能是在回归分析中，计算最符合数据组的指数回归拟合曲线，并返回描述该指数模型的数组。由于这个函数返回一个数组，必须以数组公式输入。LOGEST函数的公式为：=LOGEST(known_y,s,known_x,s,const,stats)◆例4－3：某企业12个月某产品的生产量(x)与生产成本(Y)的有关数据如表4－3所示，假设它们之间有如下关系：Y=b·mx。计算该回归函数。第一步：设置如图4-3所示的多元预测模型结构月份生产量(万件)x生产成本(元/件)110.2564.3210.34536.8310.55518.3410.92487.6511.15473.5611.43456.1711.54446.4812.06421.7912.45401.71012.93389.61113.34391.21213.25378.2生产函数模型6第二步：选取单元格区域E3:F7，输入公式¡°=LOGEST(D3:D14,C3:C14,TRUE,TRUE)¡±，计算参数。第三步：按数组生成组合键¡°CTRL+SHIFT+ENTER¡±，生成参数数组，见图4-4。各参数的位置同线性回归参数数表。第三节销售预测一、销售预测的基本方法进行销售预测的方法很多，常用的方法包括以下几种。㈠时间序列预测时间序列预测法，是将观察或记录的一些历史数据，按时间的先后排列成数据系列，进行统计分析，找出过去长期的销售量或销售额的增减变化趋势，在根据此变化趋势分析的结果，预测未来时期的销售量或销售额。如简单平均法、移动平均法、指数平滑法，或以时间为自变量的回归分析法等。㈡因果关系预测法因果分析法，是指利用有关因素与产品销售量或销售额之间故有因果关系，通过建立一定的数学模型预测企业未来的产品销售水平的一种方法。二、销售预测模型及其应用㈠一元线性（非线性）回归预测模型◆例4－4，根据表4－4中所给出的资料建立一元线性（非线性）回归预测模型，并计算未来第1～4期的预测值。建立过程：第一步：建立预测模型，如图4－5所示。时间199419951996199719981999200020012002200320042005影响因素123456789101112销售量44.242.946.952.155.857.263.770.785.392.599.2101.5表4－6产品销售资料区7第二步：设置回归模型选择控件，控件的数据区域为$A$8:$A$9，单元各连接为B9，下拉显示项为2。第三步：在C8中输入公式“=IF(B8=1,Y=m*x+b,Y=b*m^x)”第四步：将单元格区域B4:M4定义名称为“影响因素序列”，将单元格区域B5:M5定义名称为“销售序列”第五步：在单元格E9中输入公式“＝IF(B8=1,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(销售序列,影响因素序列,TRUE,TRUE),3,1))”，计算相关系R2。第八步：在单元格J9:M9中输入公式“=IF(B8=1,E9+F9*J8:M8,E9*F9^J8:M8)”，（数组公式输入），计算未来第1－4器的预测值。例4－4㈡多元线性回归预测模型例4－5，根据表4－5中所给的资料建立多元线性回归预测模型。建立过程同例4－4，结果见图4-6。第一步：建立销售预测模型，如图4-6所示。第二步：选取单元格B3:M4，单击编辑栏中的名称框，将影响因素所在的单元格区域B3:M4定义为“影响因素序列”；用同样的方法，将销售额所在的单元格区域B5:M5定义为“销售序列”。时间123456789101112影响因素1253257275290295296311318327341356372影响因素2160162166169172175178181184187191196销售额235238256263271289298304318321327332第1期第2期第3期第4期影响因素预测值1379386391397bm1m2R2影响因素预测值2200204209213-454.8-0.845.6570.98销售额预测值359.4376.2400.3417.9表4－7产品销售资料区回归模型方程表达式Y=b+m1X1+m2X2系数计算