Excel在经济管理中的应用 第三章

第3章

Excel在财务管理与分析中的应用3.2Excel在财务预测中的应用预测技术是指科学预测过程中所运用的方法和手段的总称。预测技术水平的高低直接影响预测水平，影响决策的成效。在我国，现有的预测技术一般分为定性预测和定量预测两大类。在计算机帮助下，可以很方便地进行定量分析，本节主要介绍几种主要的定量预测方法。3.2.1利用数据分析工具解决预测问题1．移动平均法移动平均法是一种改良的算术平均法，是一种最简单的自适应预测模型。它根据近期数据对预测值影响较大，而远期数据对预测值影响较小的事实，把平均数逐期移动。移动期数的大小视具体情况而定，移动期数少，能快速地反映变化，但不能反映变化趋势；移动期数多，能反映变化趋势，但预测值带有明显的滞后偏差。3.2Excel在财务预测中的应用【例15】某化妆品2001—2008年销售量如图3—26所示，试利用移动平均法预测2009年化妆品销售量（单位：万元）。预测步骤如下：（1）从“工具”菜单中选择“加载宏”命令，打开“加载宏”对话框，在其中选择“分析工具库”选项，单击“确定”，如图3—27所示。（2）从“工具”菜单中选择“数据分析”命令，打开“数据分析”对话框，在其中选择“移动平均”选项，如图3—28所示，单击“确定”，打开“移动平均”对话框。（3）在“移动平均”对话框中，在“输入区域”框中输入“$B$3:$B$10”，“间隔”框中输入“3”表明是进行3期移动平均，“输出区域”框中输入“$C$4”，同时勾选“图表输出”选项，如图3—29所示。（4）单击“确定”，则运算结果就显示在单元格区域C6:C11中，同时图表也显示出来，如图3—30所示。其中，单元格C11中的预测数据415万元即为2009年的预测销售量。3.2Excel在财务预测中的应用2．指数平滑法指数平滑法是一种改良的加权平均法，它根据最近时期的实际数据和预测数据，并借助于平滑系数进行预测。计算公式为：Ft=α·Dt-1+(1-α)·Ft-1或Ft=(1-β)·Dt-1+β·Ft-1参数说明：Ft-1—上一期预测数;Ft—新一期（计划期）的预测数;Dt-1—上期的实际数据;α—平滑系数（0≤α≤1）;β—阻尼系数（0≤β≤1），β=1-α。Excel中的指数平滑法需要使用阻尼系数，阻尼系数越小，近期实际数对预测结果的影响越大，反之，阻尼系数越大，近期实际数对预测结果的影响越小。3.2Excel在财务预测中的应用【例16】现有某汽车零配件公司2008年12个月的销售数据，如图3—31所示，假定阻尼系数为0.9，试利用指数平滑法预测2009年1月的销售量。预测步骤如下：（1）从“工具”菜单中选择“数据分析”命令，打开“数据分析”对话框，如图3—32所示，在其中选择“指数平滑”选项，单击“确定”，打开“指数平滑”对话框。（2）在“指数平滑”对话框中，在“输入区域”框中输入“$B$3:$B$14”，“阻尼系数”框中输入“0.9”，“输出区域”框中输入“$C$4”，同时勾选“图表输出”选项，如图3—33所示。（3）单击“确定”，则运算结果就显示在单元格区域C5:C15中，同时图表也显示出来，如图3—34所示。其中，单元格C15中的预测数据132万件即为2009年1月的预测销售量。3.2Excel在财务预测中的应用3．回归法利用公式法进行回归分析是非常有用的一种预测方法，它既可以对一元线性或多元线形问题进行回归预测分析，也可以对某些可以转化为线性的非线性问题进行回归分析，避免了图表法仅能解决一元线性或非线性回归问题的局限。回归分析（regressionanalysis)是确定两种或两种以上变数间相互依赖的定量关系的一种统计分析方法。运用十分广泛，回归分析按照涉及的自变量的多少，可分为一元回归分析和多元回归分析；按照自变量和因变量之间的关系类型，可分为线性回归分析和非线性回归分析。如果在回归分析中，只包括一个自变量和一个因变量，且二者的关系可用一条直线近似表示，这种回归分析称为一元线性回归分析。如果回归分析中包括两个或两个以上的自变量，且因变量和自变量之间是线性关系，则称为多元线性回归分析。此外，在SPSS的结果输出里，还可以汇报R2，F检验值和T检验值。R2又称为方程的确定性系数（coefficientofdetermination），表示方程中变量X对Y的解释程度。R2取值在0到1之间，越接近1，表明方程中X对Y的解释能力越强。通常将R2乘以100%来表示回归方程解释Y变化的百分比。F检验是通过方差分析表输出的，通过显著性水平（significantlevel）检验回归方程的线性关系是否显著。一般来说，显著性水平在0.05以下，均有意义。（1）线性趋势情况下的预测3.2Excel在财务预测中的应用【例17】现有某造纸厂2008年12个月的销售数据，如图3—35左侧所示。以时间作为自变量，用X表示；销售量作为因变量，用Y表示；并假设它们之间有如下线性关系：Y=a+bX，试利用回归分析法预测2009年1月的销售量。预测步骤如下：①从“工具”菜单中选择“数据分析”命令，打开“数据分析”对话框，如图3—36所示，在其中选择“回归”选项，单击“确定”，打开“回归”对话框。②在“回归”对话框中，在“Y值输入区域”框中输入“$B$3:$B$14”，在“X值输入区域”框中输入“$A$3:$A$14”,勾选“置信度”选项，设置为95%，在“输出选项”中选“输出区域”，输入“$E$1”，然后根据实际需要，勾选其他选项，如图3—37所示。③单击“确定”，回归分析的摘要就输出在该工作表上，如图3—35右侧所示。④由结果可以看出：相关系数R2的值为0.975894283（单元格F5中的数据），说明因变量与自变量之间相关性很高；其他统计检测也达到相应的标准，所以可以使用回归方程Y=a+bX来进行预测。截距a的值在单元格F17中，斜率b的值在单元格F18中，在单元格B15中输入公式“=F17+F18*13”，即得到第13个月（即2009年1月份）的销售量预测值为2848吨。3.2Excel在财务预测中的应用（2）非线性趋势情况下的销售预测当历史数据的变化趋势反映的是一条曲线时，就不能直接用回归工具进行预测分析，此时必须进行相应的变形处理，将非线性问题转化为线性问题后再进行分析。【例18】现有某公司2008年新上市产品的销售量，如图3—38左侧所示。将时间作为自变量，销售量作为因变量，假设销售量与时间的关系为：Y=a×bX,Y为销售量，X为时间，试利用回归分析法预测2009年1月的销售量。预测步骤如下：①通过对数变换将销售量与时间的关系转化为线性关系：ln(Y)=ln(a)+ln(b)·X。在单元格C3中输入公式“=LN(B3)”，并将公式复制到C4:C13单元格中，得到销售量Y的自然对数。②从“工具”菜单中选择“数据分析”命令，打开“数据分析”对话框，如图3—39所示，在其中选择“回归”选项，单击“确定”，打开“回归”对话框。3.2Excel在财务预测中的应用③在“回归”对话框中，在“Y值输入区域”框中输入“$C$3:$C$14”，在“X值输入区域”框中输入“$A$3:$A$14”,在“输出选项”中选“输出区域”，输入“$E$1”，然后根据实际需要，勾选其他选项，如图3—40所示。④单击“确定”，回归分析的摘要就输出在该工作表上，如图3—38右侧所示。⑤由结果可以看出：相关系数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函数的反函数，即返回e的n次幂。3.2Excel在财务预测中的应用3.2.2利用规划求解工具解决预测问题规划问题涉及众多的生产或经营领域的常见问题。例如运输的调度问题，再如原料的恰当搭配问题，还有农作物的合理布局问题。Excel提供的规划求解工具能够非常方便地帮助我们完成这些工作。1．规划求解工具的使用范围规划求解工具可以分析线性、非线性和整型这三类优化问题。3.2Excel在财务预测中的应用2．规划求解工具的使用方法（1）规划求解工具的安装“规划求解”加载宏是Excel的一个可选安装模块，如果在安装MicrosoftOffice时采用“典型安装”，则“规划求解”工具没有被安装，只有在选择“完全安装”或者“定制安装”时才可选择安装这个模块。所以在使用前，务必确认Excel的该功能已被成功安装。在安装完成进入Excel后，单击“工具”菜单，选择“加载宏”项，在“加载宏”对话框中选定“规划求解”复选框，然后单击“确定”按钮，则系统就安装和加载了“规划求解”工具，并可以使用它了。3.2Excel在财务预测中的应用（2）规划求解工具的操作步骤虽然规划问题种类繁多，但是其所要解决的问题大致可以分成两类：一类是确定了某个任务，研究如何使用最少的人力、物力和财力去完成它；另一类是已经有了一定数量的人力、物力和财力，研究如何使它们获得最大的收益。从数学角度来看，规划问题都有下述共同特征：决策变量、约束条件、目标。如果约束条件和目标函数都是线性函数，则称作线性规划；否则为非线性规划。如果要求决策变量的值为整数，则称为整数规划。求解规划问题的首要问题是将实际问题数学化、模型化。即将实际问题通过一组决策变量、一组用不等式或等式表示的约束条件以及目标函数来表示。这是求解规划问题的关键，然后即可应用Excel的规划求解工具求解。3.5

Excel在营运资金管理中的应用3.5.3存货的经济订货批量决策模型存货的决策涉及多方面的内容，包括决定进货项目、选择供货单位、决定进货时间和决定进货批量等，其中最常见的存货决策是确定经济订货批量。所谓经济订货批量是指使存货的总成本最低的一次订货批量。1．基本的经济订货批量模型基本的经济订货批量模型建立在下列假设基础之上：（1）企业能够瞬时补充存货；（2）存货能集中到货；（3）不允许缺货；（4）一定时期的存货总需求量确定；（5）存货的单价保持不变。3.5

Excel在营运资金管理中的应用在这些假设前提下，总存货费用为C，

其中，Q为订货批量；D为一定时期存货的需求量；A为一次订货费；

P为存货单价；K为存货的存储费率，PK为单位存储费用。C对Q求导数，并令=0，即得存货的经济订货批量为Q*

在此基础上，还可以进一步计算出一定时期最佳的订货次数为N*

一定时期存货的最低订储费用（订货费用和储存费用合计）为

T*例44某企业全年需要某种材料5000公斤，一次订货费用20元，材料单价25元/公斤，材料的存储费率为10%，求该材料的经济订货批量、全年订货次数和最低订储费用。3.5

Excel在营运资金管理中的应用4．有数量折扣情况下的经济订货批量决策模型随着企业订货数量的增加，供货商一般会降低单位产品的价格，给予价格上的优惠，这就涉及有数量折扣情况下的经济订货批量决策问题。供应商所提供的数量折扣有两种形式：（1）非连续价格形式的折扣优惠非连续价格形式的折扣优惠指的是当达到一定的订货批量限度后，订货量的单价整体降低的一种折扣优惠形式。在这种情况下，进行订货批量决策的步骤是：首先分别计算不同折扣价格下的经济订货批量（按照传统的经济订货批量模型计算），并判断这些经济订货批量是否有效（即计算出某经济订货批量是否与该折扣区间的价格相符）；其次，计算有效的经济订货批量下的总存货费用，并同时计算不同折扣起点批量下的总存货费用；然后在这些总存货费用中找出最低费用对应的经济订货批量或折扣起点批量；最后，比较此经济订货批量或折扣起点批量与需求量的关系，若小于需求量，则该经济订货批量或折扣起点批量就是最优解，否则，需求量是最优解。例46某企业每年需要配件20000件，每次订货费用1000元，存储费率是零件单价的15%。供货商规定，凡一次性购买3000件以下的价格为8元/件，3000件或以上但6000件以下的价格为7元/件，6000件或以上但8000件以下的价格为6元/件，8000件或以上的价格为5元/件。问企业应如何订货？3.5

Excel在营运资金管理中的应用（2）连续价格形式的折扣优惠连续价格形式的折扣优惠是指当订货量达到或超过折扣限量时，在特定的订货批量范围区间内的订货量可以按折扣优惠价格计价。假设有下列的折扣条件：批量（件） 产品价格（元/件）0＜Q＜Q1 P1Q1≤Q＜Q2 P2Q2≤Q＜Q3 P3 …则可得各个折扣区间的经济订货批量Q*为式中,Qi为折扣区间批量的突变点；Pi为折扣价格的突变点；A为一次订货费；K为存货的存储费率（以存货金额的百分比表示）。当按上述公式求出各个Q*i后，根据其值的大小判断其是否为有效点，并计算累计费用，再除以相应的各个Q*i

，得出各个有效的经济订货批量下的平均订货价格，以此平均订货价格计算存货总费用，以总费用最低的订货批量为经济订货批量。3.6

Excel在投资决策中的应用3.6.1投资决策常用函数1．投资函数（1）PV

返回投资的现值。现值为一系列未来付款当前值的累计和。公式为：

PV(rate,nper,pmt,fv,type)参数说明如下：

rate—各期利率。

nper—总投资（或贷款）期，即该项投资（或贷款）的付款期总数。

pmt—各期所应付给（或得到）的金额，其数值在整个年金期间（或投资期内）保持不变。通常pmt包括本金和利息，但不包括其他费用及税款。

fv—未来值或在最后一次支付后希望得到的现金余额，如果省略fv，则假设其值为零（一笔贷款的未来值即为零）。

type—数字0或1，用以指定各期的付款时间是在期初还是在期末。如果省略type，则假设其值为零，期末付款。说明：应确认所指定的rate和nper单位的一致性。（2）NPV

基于一系列现金流和固定的各期贴现率，返回一项投资的净现值。投资的净现值是指未来各期支出（负值）和收入（正值）的当前值的总和。公式为：

NPV(rate,value1,value2,…）参数说明如下：rate—各期贴现率，是一固定值；

value1,value2,…—代表1~29笔支出及收入的参数值。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的参数和年金函数的详细内容，请参阅函数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中各参数的详细说明及有关年金函数的详细内容，请参阅函数PV的参数说明。3.6

Excel在投资决策中的应用2．偿还率函数（1）RATE返回年金的各期利率。函数RATE通过迭代法计算得出，并且可能无解或有多个解。公式为：RATE(nper,pmt,pv,fv,type,guess)参数说明如下：per—总投资（或贷款）期，即该项投资（或贷款）的付款期总数。pmt—各期付款额，其数值在整个投资期内保持不变。通常pmt包括本金和利息，但不包括其他费用或税金。pv—现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累计和，也称为本金。fv—未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零）。type—数字0或1，用以指定各期的付款时间是在期初还是在期末。如果省略type，则假设其值为零，期末付款。guess—预期利率（估计值），如果省略预期利率，则假设该值为10%；如果函数RATE不收敛，请改变guess的值。通常当guess位于0~1之间时，函数RATE是收敛的。3.6

Excel在投资决策中的应用（2）IRR返回由数值代表的一组现金流的内部收益率。这些现金流不一定要均衡，但作为年金，它们必须按固定的间隔发生，如按月或按年。内部收益率为投资的回收利率，其中包含定期支付（负值）和收入（正值）。公式为：IRR(values,guess)参数说明如下：values—数组或单元格的引用，包含用来计算内部收益率的数字，values必须包含至少一个正值和一个负值，以计算内部收益率。guess—对函数IRR计算结果的估计值：（1）MicrosoftExcel使用迭代法计算函数IRR。从guess开始，函数IRR不断修正收益率，直至结果的精度达到0.00001%。如果函数IRR经过20次迭代，仍未找到结果，则返回错误值＃NUM!。（