1、第3章 Excel在财务管理与分析中的应用,3.2 Excel在财务预测中的应用,预测技术是指科学预测过程中所运用的方法和手段的总称。预测技术水平的高低直接影响预测水平，影响决策的成效。在我国，现有的预测技术一般分为定性预测和定量预测两大类。在计算机帮助下，可以很方便地进行定量分析，本节主要介绍几种主要的定量预测方法。 3.2.1利用数据分析工具解决预测问题 1移动平均法 移动平均法是一种改良的算术平均法，是一种最简单的自适应预测模型。它根据近期数据对预测值影响较大，而远期数据对预测值影响较小的事实，把平均数逐期移动。移动期数的大小视具体情况而定，移动期数少，能快速地反映变化，但不能反映变化趋
2、势；移动期数多，能反映变化趋势，但预测值带有明显的滞后偏差。,3.2Excel在财务预测中的应用,【例15】某化妆品20012008年销售量如图326所示，试利用移动平均法预测2009年化妆品销售量（单位：万元）。 预测步骤如下： （1）从“工具”菜单中选择“加载宏”命令，打开“加载宏”对话框，在其中选择“分析工具库”选项，单击“确定”，如图327所示。 （2）从“工具”菜单中选择“数据分析”命令，打开“数据分析”对话框，在其中选择“移动平均”选项，如图328所示，单击“确定”，打开“移动平均”对话框。 （3）在“移动平均”对话框中，在“输入区域”框中输入“$B$3:$B$10”，“间隔”框中
3、输入“3”表明是进行3期移动平均，“输出区域”框中输入“$C$4”，同时勾选“图表输出”选项，如图329所示。 （4）单击“确定”，则运算结果就显示在单元格区域C6:C11中，同时图表也显示出来，如图330所示。其中，单元格C11中的预测数据415万元即为2009年的预测销售量。,3.2Excel在财务预测中的应用,2指数平滑法 指数平滑法是一种改良的加权平均法，它根据最近时期的实际数据和预测数据，并借助于平滑系数进行预测。计算公式为： Ft=Dt-1+(1-)Ft-1或Ft=(1-)Dt-1+Ft-1 参数说明： Ft-1上一期预测数; Ft新一期（计划期）的预测数; Dt-1上期的实际数据
4、; 平滑系数（01）; 阻尼系数（01），=1-。 Excel中的指数平滑法需要使用阻尼系数，阻尼系数越小，近期实际数对预测结果的影响越大，反之，阻尼系数越大，近期实际数对预测结果的影响越小。,3.2Excel在财务预测中的应用,【例16】现有某汽车零配件公司2008年12个月的销售数据，如图331所示，假定阻尼系数为0.9，试利用指数平滑法预测2009年1月的销售量。 预测步骤如下： （1）从“工具”菜单中选择“数据分析”命令，打开“数据分析”对话框，如图332所示，在其中选择“指数平滑”选项，单击“确定”，打开“指数平滑”对话框。 （2）在“指数平滑”对话框中，在“输入区域”框中输入“$B
5、$3:$B$14”，“阻尼系数”框中输入“0.9”，“输出区域”框中输入“$C$4”，同时勾选“图表输出”选项，如图333所示。 （3）单击“确定”，则运算结果就显示在单元格区域C5:C15中，同时图表也显示出来，如图334所示。其中，单元格C15中的预测数据132万件即为2009年1月的预测销售量。,3.2Excel在财务预测中的应用,3回归法 利用公式法进行回归分析是非常有用的一种预测方法，它既可以对一元线性或多元线形问题进行回归预测分析，也可以对某些可以转化为线性的非线性问题进行回归分析，避免了图表法仅能解决一元线性或非线性回归问题的局限。 回归分析（regression analysi
6、s)是确定两种或两种以上变数间相互依赖的定量关系的一种统计分析方法。运用十分广泛，回归分析按照涉及的自变量的多少，可分为一元回归分析和多元回归分析；按照自变量和因变量之间的关系类型，可分为线性回归分析和非线性回归分析。如果在回归分析中，只包括一个自变量和一个因变量，且二者的关系可用一条直线近似表示，这种回归分析称为一元线性回归分析。如果回归分析中包括两个或两个以上的自变量，且因变量和自变量之间是线性关系，则称为多元线性回归分析。,此外，在SPSS的结果输出里，还可以汇报R2，F检验值和T检验值。R2又称为方程的确定性系数（coefficient of determination），表示方程中变
7、量X对Y的解释程度。R2取值在0到1之间，越接近1，表明方程中X对Y的解释能力越强。通常将R2乘以100%来表示回归方程解释Y变化的百分比。F检验是通过方差分析表输出的，通过显著性水平（significant level）检验回归方程的线性关系是否显著。一般来说，显著性水平在0.05以下，均有意义。 （1）线性趋势情况下的预测,3.2Excel在财务预测中的应用,【例17】现有某造纸厂2008年12个月的销售数据，如图335左侧所示。 以时间作为自变量，用X表示；销售量作为因变量，用Y表示；并假设它们之间有如下线性关系：Y=a+bX，试利用回归分析法预测2009年1月的销售量。 预测步骤如下：
8、 从“工具”菜单中选择“数据分析”命令，打开“数据分析”对话框，如图336所示，在其中选择“回归”选项，单击“确定”，打开“回归”对话框。 在“回归”对话框中，在“Y值输入区域”框中输入“$B$3:$B$14”，在“X值输入区域”框中输入“$A$3:$A$14”,勾选“置信度”选项，设置为95%，在“输出选项”中选“输出区域”，输入“$E$1”，然后根据实际需要，勾选其他选项，如图337所示。 单击“确定”，回归分析的摘要就输出在该工作表上，如图335右侧所示。 由结果可以看出：相关系数R2的值为0.975894283（单元格F5中的数据），说明因变量与自变量之间相关性很高；其他统计检测也达到
9、相应的标准，所以可以使用回归方程Y=a+bX来进行预测。截距a的值在单元格F17中，斜率b的值在单元格F18中，在单元格B15中输入公式“=F17+F18*13”，即得到第13个月（即2009年1月份）的销售量预测值为2848吨。,3.2Excel在财务预测中的应用,（2）非线性趋势情况下的销售预测 当历史数据的变化趋势反映的是一条曲线时，就不能直接用回归工具进行预测分析，此时必须进行相应的变形处理，将非线性问题转化为线性问题后再进行分析。 【例18】现有某公司2008年新上市产品的销售量，如图338左侧所示。将时间作为自变量，销售量作为因变量，假设销售量与时间的关系为：Y=abX,Y为销售量
10、，X为时间，试利用回归分析法预测2009年1月的销售量。 预测步骤如下： 通过对数变换将销售量与时间的关系转化为线性关系：ln(Y)=ln(a)+ln(b)X。在单元格C3中输入公式“=LN(B3)”，并将公式复制到C4:C13单元格中，得到销售量Y的自然对数。 从“工具”菜单中选择“数据分析”命令，打开“数据分析”对话框，如图339所示，在其中选择“回归”选项，单击“确定”，打开“回归”对话框。,3.2Excel在财务预测中的应用,在“回归”对话框中，在“Y值输入区域”框中输入“$C$3:$C$14”，在“X值输入区域”框中输入“$A$3:$A$14”,在“输出选项”中选“输出区域”，输入“
11、$E$1”，然后根据实际需要，勾选其他选项，如图340所示。 单击“确定”，回归分析的摘要就输出在该工作表上，如图338右侧所示。 由结果可以看出：相关系数R2的值为0.9987914（单元格F5中的数据），说明因变量与自变量之间相关性很高；其他统计检测也达到相应的标准，所以可以使用回归方程 ln(Y)=ln(a)+ln(b)X 来进行预测。截距ln(a)的值在单元格F17中，斜率ln(b)的值在单元格F18中，在单元格B15中输入公式“=EXP(F17+F18*13)”，即得到第13个月即2009年1月的销售量预测值为580.596万件。 这里，EXP函数的功能是计算自然对数ln函数的反函数
12、，即返回e的n次幂。,3.2Excel在财务预测中的应用,3.2.2利用规划求解工具解决预测问题 规划问题涉及众多的生产或经营领域的常见问题。例如运输的调度问题，再如原料的恰当搭配问题，还有农作物的合理布局问题。 Excel提供的规划求解工具能够非常方便地帮助我们完成这些工作。 1规划求解工具的使用范围 规划求解工具可以分析线性、非线性和整型这三类优化问题。,3.2Excel在财务预测中的应用,2规划求解工具的使用方法 （1）规划求解工具的安装 “规划求解”加载宏是Excel的一个可选安装模块，如果在安装Microsoft Office时采用“典型安装”，则“规划求解”工具没有被安装，只有在选
13、择“完全安装”或者“定制安装”时才可选择安装这个模块。所以在使用前，务必确认Excel的该功能已被成功安装。 在安装完成进入Excel后，单击“工具”菜单，选择“加载宏”项，在“加载宏”对话框中选定“规划求解”复选框，然后单击“确定”按钮，则系统就安装和加载了“规划求解”工具，并可以使用它了。,3.2Excel在财务预测中的应用,（2）规划求解工具的操作步骤 虽然规划问题种类繁多，但是其所要解决的问题大致可以分成两类：一类是确定了某个任务，研究如何使用最少的人力、物力和财力去完成它；另一类是已经有了一定数量的人力、物力和财力，研究如何使它们获得最大的收益。从数学角度来看，规划问题都有下述共同特
14、征：决策变量、约束条件、目标。 如果约束条件和目标函数都是线性函数，则称作线性规划；否则为非线性规划。如果要求决策变量的值为整数，则称为整数规划。 求解规划问题的首要问题是将实际问题数学化、模型化。即将实际问题通过一组决策变量、一组用不等式或等式表示的约束条件以及目标函数来表示。这是求解规划问题的关键，然后即可应用Excel的规划求解工具求解。,3. 5 Excel在营运资金管理中的应用,3.5.3 存货的经济订货批量决策模型 存货的决策涉及多方面的内容，包括决定进货项目、选择供货单位、决定进货时间和决定进货批量等，其中最常见的存货决策是确定经济订货批量。 所谓经济订货批量是指使存货的总成本最
15、低的一次订货批量。 1基本的经济订货批量模型 基本的经济订货批量模型建立在下列假设基础之上： （1）企业能够瞬时补充存货； （2）存货能集中到货； （3）不允许缺货； （4）一定时期的存货总需求量确定； （5）存货的单价保持不变。,3. 5 Excel在营运资金管理中的应用,在这些假设前提下，总存货费用为C，其中，Q为订货批量；D为一定时期存货的需求量； A为一次订货费； P为存货单价；K为存货的存储费率，PK为单位存储费用。 C对Q求导数，并令 =0，即得存货的经济订货批量为Q* 在此基础上，还可以进一步计算出一定时期最佳的订货次数为N* 一定时期存货的最低订储费用（订货费用和储存费用合计）
16、为 T*,例44 某企业全年需要某种材料5000公斤，一次订货费用20元，材料单价25元/公斤，材料的存储费率为10%，求该材料的经济订货批量、全年订货次数和最低订储费用。,3. 5 Excel在营运资金管理中的应用,4有数量折扣情况下的经济订货批量决策模型 随着企业订货数量的增加，供货商一般会降低单位产品的价格，给予价格上的优惠，这就涉及有数量折扣情况下的经济订货批量决策问题。供应商所提供的数量折扣有两种形式： （1）非连续价格形式的折扣优惠 非连续价格形式的折扣优惠指的是当达到一定的订货批量限度后，订货量的单价整体降低的一种折扣优惠形式。在这种情况下，进行订货批量决策的步骤是：首先分别计算
17、不同折扣价格下的经济订货批量（按照传统的经济订货批量模型计算），并判断这些经济订货批量是否有效（即计算出某经济订货批量是否与该折扣区间的价格相符）；其次，计算有效的经济订货批量下的总存货费用，并同时计算不同折扣起点批量下的总存货费用；然后在这些总存货费用中找出最低费用对应的经济订货批量或折扣起点批量；最后，比较此经济订货批量或折扣起点批量与需求量的关系，若小于需求量，则该经济订货批量或折扣起点批量就是最优解，否则，需求量是最优解。,例46 某企业每年需要配件20000件，每次订货费用1000元，存储费率是零件单价的15%。供货商规定，凡一次性购买3000件以下的价格为8元/件，3000件或以上
18、但6000件以下的价格为7元/件， 6000件或以上但8000件以下的价格为6元/件， 8000件或以上的价格为5元/件。问企业应如何订货？,3. 5 Excel在营运资金管理中的应用,（2）连续价格形式的折扣优惠 连续价格形式的折扣优惠是指当订货量达到或超过折扣限量时，在特定的订货批量范围区间内的订货量可以按折扣优惠价格计价。假设有下列的折扣条件： 批量（件） 产品价格（元/件） 0QQ1 P1 Q1 QQ2 P2 Q2 QQ3 P3 则可得各个折扣区间的经济订货批量Q*为式中,Qi为折扣区间批量的突变点；Pi为折扣价格的突变点；A为一次订货费；K为存货的存储费率（以存货金额的百分比表示）。
19、 当按上述公式求出各个Q*i后，根据其值的大小判断其是否为有效点，并计算累计费用，再除以相应的各个Q*i ，得出各个有效的经济订货批量下的平均订货价格，以此平均订货价格计算存货总费用，以总费用最低的订货批量为经济订货批量。,3. 6 Excel在投资决策中的应用,3.6.1投资决策常用函数 1投资函数 （1）PV返回投资的现值。现值为一系列未来付款当前值的累计和。公式为：PV(rate, nper, pmt, fv, type) 参数说明如下：rate各期利率。nper总投资（或贷款）期，即该项投资（或贷款）的付款期总数。pmt各期所应付给（或得到）的金额，其数值在整个年金期间（或投资期内）保
20、持不变。通常pmt包括本金和利息，但不包括其他费用及税款。fv未来值或在最后一次支付后希望得到的现金余额，如果省略fv，则假设其值为零（一笔贷款的未来值即为零）。type数字0或1，用以指定各期的付款时间是在期初还是在期末。如果省略type，则假设其值为零，期末付款。说明：应确认所指定的rate和nper单位的一致性。,（2）NPV基于一系列现金流和固定的各期贴现率，返回一项投资的净现值。投资的净现值是指未来各期支出（负值）和收入（正值）的当前值的总和。公式为：NPV(rate, value 1, value 2,） 参数说明如下： rate各期贴现率，是一固定值；value 1, value
21、 2,代表129笔支出及收入的参数值。,3. 6 Excel在投资决策中的应用,（3）FV 基于固定利率及等额分期付款方式，返回某项投资的未来值。公式为：FV(rate, nper, pmt, pv, type) 有关函数FV中各参数的详细内容，请参阅函数PV的参数说明。 （4）PMT 基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。 公式为：PMT(rate, nper, pv, fv, type) 有关函数PMT中参数的详细描述，请参阅函数PV的参数说明. （5）IPMT 基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期间内的利息偿还额。有关函数IPMT的参数和年金函
22、数的详细内容，请参阅函数PV的参数说明。公式为：IPMT(rate, per, nper, pv, fv, type),3. 6 Excel在投资决策中的应用,（6）PPMT 基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期间内的本金偿还额。公式为：PPMT(rate, per, nper, pv, fv, type) 有关函数PPMT中参数的详细内容，请参阅函数PV的参数说明。 （7）NPER 基于固定利率及等额分期付款方式，返回某项投资（或贷款）的总期数。公式为：NPER(rate, pmt, pv, fv, type) 有关函数NPER中各参数的详细说明及有关年金函数的详细内容
23、，请参阅函数PV的参数说明。,3. 6 Excel在投资决策中的应用,2偿还率函数 （1）RATE 返回年金的各期利率。函数RATE通过迭代法计算得出，并且可能无解或有多个解。公式为： RATE(nper, pmt, pv, fv, type, guess) 参数说明如下： per总投资（或贷款）期，即该项投资（或贷款）的付款期总数。 pmt各期付款额，其数值在整个投资期内保持不变。通常pmt包括本金和利息，但不包括其他费用或税金。 pv现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累计和，也称为本金。 fv未来值，或在最后一次付款后希望得到的现金余额，如果省略
24、fv，则假设其值为零（例如，一笔贷款的未来值即为零）。 type数字0或1，用以指定各期的付款时间是在期初还是在期末。如果省略type，则假设其值为零，期末付款。 guess预期利率（估计值），如果省略预期利率，则假设该值为10%；如果函数RATE不收敛，请改变guess的值。通常当guess位于01之间时，函数RATE是收敛的。,3. 6 Excel在投资决策中的应用,（2）IRR 返回由数值代表的一组现金流的内部收益率。这些现金流不一定要均衡，但作为年金，它们必须按固定的间隔发生，如按月或按年。内部收益率为投资的回收利率，其中包含定期支付（负值）和收入（正值）。公式为：IRR(values
25、, guess) 参数说明如下： values数组或单元格的引用，包含用来计算内部收益率的数字，values必须包含至少一个正值和一个负值，以计算内部收益率。 guess对函数IRR计算结果的估计值：（1） Microsoft Excel使用迭代法计算函数IRR。从guess开始，函数IRR不断修正收益率，直至结果的精度达到0.00001%。如果函数IRR经过20次迭代，仍未找到结果，则返回错误值NUM!。（2）在大多数情况下，并不需要为函数IRR的计算提供guess值。如果省略guess，假设它为0.1（即10%）。（3）如果函数IRR返回错误值NUM!，或结果没有靠近期望值，可以给gues
26、s换一个值再试一下。,3. 6 Excel在投资决策中的应用,（3）NRR 返回某一连续期间内现金流的修正内部收益率。函数MIRR同时考虑了投资的成本和现金再投资的收益率。公式为： MIRR(values, finance_rate, reinvest_rate) 参数说明如下：values一个数组或对数字单元格区的引用。这些数值代表着各期支出（负值）及收入（正值）。 finance_rate投入资金的融资利率。 reinvest_rate各期收入净额再投资的收益率。 3折旧函数 参见3.5.4小节的内容。,3. 6 Excel在投资决策中的应用,3.6.2投资决策模型 正确的计算和评价投资项
27、目的经济效益是投资决策的核心问题，利用计算机技术，财务人员可以建立各种投资模型，进行定量分析，及时、准确、有效地计算和评价投资项目的经济效益，为正确投资提供支持。 1投资回收期法模型 用投资回收期法建立投资决策模型，就是要建立投资回收期表结构和计算投资回收期公式，将具体的数据填入表中，利用Excel的计算功能和函数功能计算出各方案的投资回收期，依据投资回收期的长短，选定最优方案。 2净现值和现值指数法模型 用净现值和现值指数法建立投资决策模型，就是要建立净现值和现值指数的计算公式，将具体的数据填入表中，利用Excel的计算功能和函数功能自动计算出各方案的现值及现值指数，依据净现值和现值指数的大小，选定最优方案。 3内部报酬率法模型 用内部报酬率法和修正内部报酬率法建立投资决策模型，就是要建立内部报酬率法和修正内部报酬率法的计算公式，将具体的数据填入表中，利用Excel的计算功能和函数功能直接计算出各方案的内部报酬率及修正内部报酬率，依据其值大小，选定最优方案。,3. 6 Excel在投资决策中的应用,
