Excel应用实例之二敏感分析.docx
《Excel应用实例之二敏感分析.docx》由会员分享，可在线阅读，更多相关《Excel应用实例之二敏感分析.docx（16页珍藏版）》请在冰豆网上搜索。
Excel应用实例之二敏感分析
5.12Excel应用实例之二——敏感分析
[本节提要]本节主要通过投资分析等问题，介绍了Excel2000的模拟运算表、方案和单变量求解的应用，着重说明了单变量模拟运算表和双变量模拟运算表的操作步骤，在模拟运算表的基础上进行敏感分析的方法，以及应用方案和单变量求解工具辅助决策的方法。
敏感分析也称作“What-If分析”，是在财务、会计、管理、统计等应用领域不可缺少的工具。
例如在财务分析中，许多指标的计算都要涉及到若干个参数。
像长期投资项目，其偿还额与利率、付款期数、每期付款额度等参数密切相关。
又如固定资产的折旧，与固定资产原值、估计残值、固定资产的生命周期、折旧计算的期次以及余额递减速率等密切相关。
而作为决策者往往需要定量地了解，当这些参数变动时对有关指标的影响。
这些分析可以利用Excel2000的模拟运算表工具实现。
以下通过投资效益的分析说明有关工具的使用。
5.12.1 模拟运算表
所谓模拟运算表实际上是工作表中的一个单元格区域，它可以显示一个计算公式中某些参数值的变化对计算结果的影响。
由于它可以将所有不同的计算结果以列表方式同时显示出来，因而便于查看、比较和分析。
根据分析计算公式中的参数的个数，模拟运算表又分为单变量模拟运算表和双变量模拟运算表。
一、单变量模拟运算表
单变量模拟运算主要用来分析当其它因素不变时，一个参数的变化对目标值的影响。
例如，要计算一笔贷款的分期偿还额，可以使用Excel2000提供的财务函数之PMT。
而如果要分析不同的利率对贷款的偿还额产生的影响，则可以使用单变量模拟运算表。
假设某公司要贷款1000万元，年限为10年，目前的年利率为5%，分月偿还。
则利用PMT函数[PMT（rate,nper,pv,fv,type）]可以计算出每月的偿还额。
其具体操作步骤如下：
（1）在工作表中输入有关参数，如图5-12-1所示。
（2）在B5单元格输入计算月偿还额的公式：
“=PMT（B3/12,B4*12,B2）”
在上述公式中，PMT函数有三个参数。
第一个参数是利率，因为要计算的偿还额是按月计算的，所以要将年利率除以12，将其转换成月利率。
第二个参数是还款期数，同样的原因需要乘以12。
第三个参数为贷款额。
该函数的计算结果为“-106065.52”，即在年利率为5%，年限为10年的条件下，需每月偿还106065.52元。
图5-12-1
近几年来，国家为了宏观调控经济的发展，多次调整了利率。
作为投资决策人员，需要全面了解当利率变动时，对偿贷能力的影响。
这可以使用单变量模拟运算表实现。
其具体操作步骤如下：
（1）选择某个单元格区域作为模拟运算表存放区域，在该区域最左列输入假设的利率变化范围数据。
因为该数据系列通常是等差或是等比数列，所以可利用Excel2000的自动填充功能快速建立。
（2）在模拟运算表区域的第2列第1行输入计算月偿还额的计算公式。
（3）选定整个模拟运算表区域。
如图5-12-2所示。
图5-12-2
（4）单击数据菜单中的模拟运算表命令。
这时将弹出模拟运算表对话框，如图5-12-3所示。
（5）在模拟运算表对话框的输入引用列的单元格框中输入“$B$3”。
单击确定。
所谓引用列的单元格，即模拟运算表的模拟数据（最左列数据）要代替公式中的单元格地址。
本例模拟运算表是关于利率的模拟数据，所以指定$B$3，即年利率所在的单元格为引用列的单元格。
为了方便，通常称其为模拟运算表的列变量。
图5-12-3
模拟运算表的计算结果如图5-12-4所示。
图5-12-4
请注意，这时单元格区域B8:
B16中的公式为“{=表（,B3）}”，表示其是一个以B3为列变量的模拟运算表。
与一般的计算公式相似，当改变模拟数据时，模拟运算表的数据会自动重新计算。
除了用于贷款分析之外，函数PMT还可以计算出别的以年金方式付款的支付额。
例如，如果需要以按月定额存款方式在20年中存款100000，假设存款年利率为4%，则函数PMT可以用来计算月存款额：
“＝PMT（4%/12,20*12,0,100000）”，公式计算结果为“272.65”。
即向年利率4%的存款账户每月存入272.65元，20年后连本带利可获得100000元。
二、双变量模拟运算表
当需要其它因素不变时，两个参数的变化对目标值的影响时，需要使用双变量模拟运算表。
例如上例，如果不仅要考虑利率的变化，还可以选择贷款年限，这时需要分析不同的利率和不同的贷款期限对贷款的偿还额的影响，这时需要使用双变量模拟运算表。
双变量模拟运算表的操作步骤与单变量模拟运算表类似：
（1）选择某个单元格区域作为模拟运算表存放区域，在该区域的最左列输入假设的利率变化范围数据；在该区域的第一行输入可能的贷款年限数据。
（2）在模拟运算表区域的左上角单元格输入计算月偿还额的计算公式。
（3）选定整个模拟运算表区域。
如图5-12-5所示。
图5-12-5
（4）单击数据菜单中的模拟运算表命令。
（5）在模拟运算表对话框的输入引用行的单元格框中输入“$B$4”；在输入引用列的单元格框中输入“$B$3”。
单击确定。
双变量模拟运算表的计算结果如图5-12-6所示。
其中B8:
F16单元格区域的计算公式为“{=表（B4,B3）}”，表示其是一个以B4为行变量，B3为列变量的模拟运算表。
图5-12-6
三、敏感分析
利用模拟运算表还可以进一步进行其他方面的敏感分析。
下面通过购买某个险种的保险时如何选择缴款方式，来说明有关敏感分析的操作。
设准备购买某保险10万元，可以有两种缴款方式供选择：
一种是趸交，即一次付清105,490元；另一种是分30年付款，每年付6,350元。
如果单从付款额来说，后一种付款方式累计缴款190,500元，大大多于趸交的款额。
但是对于这种长期投资问题，还必须要考虑利息的收益和利率变动的影响。
为此，可以利用Excel2000提供的现值函数PV[PV（rate,nper,pmt,fv,type）]或未来值函数FV[FV（rate,nper,pmt,pv,type）]，计算和比较在特定利率情况下两者的收益。
再进一步应用模拟运算表分析利率变动的影响。
首先将有关数据输入到工作表中，再利用PV和FV函数计算分期付款方式在特定年利率情况下的现值和未来值。
这里设年利率为5%，则计算结果如图5-12-7所示。
图5-12-7
从计算结果可以看出，在年利率为5%的情况下，分期付款方式相当于现在一次付款102,495元。
也就是说，在年利率为5%的情况下，采用分期付款方式较好。
近几年来，国家为了宏观调控经济的发展，曾多次调整银行利率。
为了比较不同利率对保险收益的影响，可建立以年利率3.50%～5.50%为列模拟数据的模拟运算表。
如图5-12-8所示。
图5-12-8
从模拟运算表中可以看出，当年利率为4.75时，两种缴款方式效果近似，当年利率低于4.75时，宜采用趸交方式；而高于4.75时，宜采用分期付款方式。
或者说，如果有其他年利率大于4.75元的投资途径时，采用分期付款方式可以获得更好的收益。
如果还要考查不同支付额的影响，可以使用双变量模拟运算表。
图5-12-9是以年利率3.50%～5.50%为行模拟数据，支付额6,150～6,550为列模拟数据的双变量模拟运算表。
图5-12-9
在模拟运算表的基础上，还可以进一步进行敏感分析。
可以通过改变除行变量和列变量以外其他参数的值，分析其对模拟运算表计算结果的影响；而改变函数名称，则可以方便地得到其他相关指标的的模拟运算表。
例如，在上例的现值分析中，年限都是30年，如果要考查年限为15年或是25年时，各模拟数据的变动情况，可以直接修改年限数据，这时整个模拟运算表会自动重新计算。
图5-12-10即年限为20年时的双变量模拟运算表。
图5-12-10
如果要分析这笔投资30年后的效益，可以使用FV函数计算其未来值。
显然未来值的计算也是同利率、付款额和年限等参数相关。
这里只需在原来模拟运算表的基础上，将原来的计算公式中的函数名由“PV”改成“FV”即可。
图5-12-11即为有关分期付款方式未来值的模拟运算表。
如果保险回报与之相比过低，而且风险不大时，可以考虑采取其他投资方式。
图5-12-11
5.11.2 方案分析
模拟运算表主要用来考查一个或两个决策变量的变动对于分析结果的影响，但对于一些更复杂的问题，常常需要考查更多的因素。
例如为了达到公司的预算目标，可以从多种途径入手。
可以通过增加广告促销，可以提高价格增收，可以降低包装费、材料费，可以减少非生产开支等等。
利用Excel2000提供的方案管理器，可以模拟为达到目标而选择的不同方式。
对于每个变量改变的结果都被称之为一个方案，根据多个方案的对比分析，可以考查不同方案的优劣，从中选择最合适公司目标的方案。
例如图5-12-12所示的是思创公司1999年1月的损益表，其中包括了各项指标的计算公式。
管理人员希望分析，通过增加销售收入，减少生产费用，降低销售成本等措施对公司利润总额的影响。
这可以利用Excel2000的方案工具进行分析，主要包括下述操作。
图5-12-12
一、创建方案
方案是方案分析的关键，应根据实际问题的需要和可行性来创建一组方案。
在创建方案之前，为了使创建的方案能够明确地显示有关变量，以及为了将来进行方案总结时便于阅读方案总结报告，需要先给有关变量所在的单元格命名。
其具体操作步骤是：
（1）在存放有关变量数据的单元格右侧单元格中输入相应指标的名称。
（2）选定要命名的单元格区域和单元格名称区域。
如图5-12-13所示。
图5-12-13
（3）单击“插入”菜单中的名称命令，然后单击指定子命令。
这时将出现指定名称对话框，如图4-14所示。
图5-12-14
（4）选定最右列复选框，单击确定按钮。
此时方案分析中需要用到的C3:
C15单元格全部被用D3:
D15单元格的内容命名。
这时可按下述步骤逐个创建所需的方案。
（5）单击“工具”菜单中的方案命令，将弹出方案管理器对话框。
由于现在还没有任何方案，所以方案管理器对话框中间显示“未定义方案”的信息。
根据提示，单击添加按钮。
出现添加方案对话框，如图5-12-15所示。
图5-12-15
（6）在方案名框中键入方案的名称，这里键入“增加收入”。
然后指定销售收入和营业外收入所在的单元格为可变单元格，单击确定。
出现方案变量值对话框，如图5-12-16所示。
图5-12-16
（7）框中显示原来的数据。
在相应的框中键入模拟数值。
单击确定。
“增加收入”方案创建完毕，相应的方案自动添加到方案管理器的方案列表中。
（8）按照上述步骤再依次建立“减少费用”和“降低成本”两个方案。
这时的方案管理器对话框如图5-12-17所示。
图5-12-17
二、浏览、编辑方案
方案创建好以后，可以根据需要查看每个方案对利润总额数据的影响。
其具体操作步骤是：
（1）在方案管理器对话框的方案列表中，选定要查看的方案。
（2）单击方案管理器对话框的显示按钮，再单击关闭。
（3）这时工作表中将显示该模拟方案的计算结果。
如果需要修改某个方案，其具体操作步骤是：
（1）在方案管理器对话框的方案列表中，选定要修改的方案。
（2）单击方案管理器对话框的编辑按钮。
（3）这时会弹出与添加方案一样的编辑方案对话框。
可以根据需要修改方案名称，改变可变单元格以及重新输入可变单元格的变量值。
三、方案总结
上述浏览方式只能一个方案一个方案地查看，如果将所有方案汇总到一个工作表中，然后再对不同方案的影响比较分析，这对于帮助决策人员综合考查各种方案效果更好。
Excel2000的方案工具可以根据需要对多个方案创建方案总结，以便决策人员做出更明智的决策。
具体操作步骤如下：
（1）单击工具菜单中的方案命令，将弹出方案管理器对话框。
（2）单击方案管理器对话框中的总结按钮，将弹出方案总结对话框，如图5-12-18所示。
图5-12-18
（3）根据需要在方案总结对话框中选择适当的结果类型，一般情况下可选择方案总结，如果需要对报告进一步分析，可选方案数据透视表。
在结果单元格框中指定利润总额所在的单元格C15。
单击确定按钮。
方案总结如图5-12-19所示。
图5-12-19
在方案总结中，“当前值”列显示的是在建立方案汇总时，可变单元格原来的数值。
每组方案的可变单元格均以灰色底纹突出显示。
根据各方案的模拟数据计算出的目标值也同时显示在总结中（单元格区域D13:
G13），便于管理人员比较分析。
比较三个方案的结果单元格“利润总额”的数值，可以看出“降低成本”方案效果最好，“减少费用”方案次之，“增加收入”方案对目标值的影响最小。
5.12.3 目标搜索
“What-If”分析方法主要采用模拟计算的方法解决不同因素或不同方案对目标的影响。
这对于计划人员、决策人员都是常用的工具。
但是对于生产的组织和实施人员来说，经常遇到的是相反的问题。
例如，根据上机有关部门制定的某个目标，分析要实现该目标，需要实现的具体指标，再逐一落实。
当然也可以根据每个具体指标，进一步分析要达到的更详细的指标。
在进行这样的分析时，往往由于计算方法较为复杂或是许多因素交织在一起而很难进行。
这可以利用Excel2000的目标搜索技术实现。
一、单变量求解
仍以上一节的思创公司损益表为例。
假设该公司下个月的利润总额指标定为145000，要考查当其他条件基本保持不变的情况下，销售收入需要增加到多少。
由于利润总额与销售收入的关系不是简单的同量增加的关系（即不是销售收入增加1元，利润总额也增加1元），也不是简单的同比例增长关系（即不是销售收入增加1元，利润总额按70%比例增加0.7元），而可能要涉及到其它多方面因素。
比如说，销售收入增加，可能需要增加销售人员的奖金、差旅费、运输费和装卸费等开支等等。
所以手工计算是比较复杂的，需要根据工作表中的计算公式一项一项的倒推计算。
而Excel2000提供的目标搜索技术，即单变量求解命令可以方便计算出来。
首先将有关数据和公式输入到工作表中，如上例的图5-12-12所示。
请注意，使用单变量求解命令的关键是在工作表上建立正确的数学模型，即通过有关的公式和函数描述清楚相应数据之间的关系。
例如该表中产品销售利润、营业利润和利润总额分别是按下述公式计算的：
产品销售利润＝产品销售收入－产品销售成本－产品销售费用－产品销售税金
营业利润＝产品销售利润＋其它业务利润－管理费用－财务费用
利润总额＝营业利润＋投资收益＋营业外收入－营业外支出
而产品销售成本、产品销售费用等数据也是根据产品销售收入按一定公式计算的。
这是保证分析结果有效和正确的前提。
应用单变量求解命令的具体操作步骤如下：
（1）选定目标单元格C15，单击工具菜单中的单变量求解命令。
（2）这时弹出单变量求解对话框，如图5-12-20所示。
图5-12-20
Excel2000自动将当前单元格的地址“C15”填入到目标单元格框中；在目标值框中输入预定的目标“145000”；在可变单元格框中输入产品销售收入所在的单元格地址“C3”，也可指定可变单元格后，直接单击该C3单元格。
单击确定。
（3）这时弹出单变量求解状态对话框，说明已找到一个解，并与所要求的解一致。
单击确定按钮，可以看到求解的结果如图5-12-21所示。
图5-12-21
从图中可以看出，在其他条件基本保持不变的情况下，要使利润总额增加到145000元，即增加3545元，其产品销售收入需增加到1441020元，即增加38320元。
二、图上求解
目标搜索技术还可以利用图形直观地进行。
例如上例，如果要分析使利润总额增加到146000元，相应的销售收入需增加到多少元，可按下述步骤操作：
（1）选定销售收入和利润总额等数据所在的单元格，这里选定销售收入、销售利润、营业利润和利润总额等数据。
单击图表向导按钮，按提示制作一个柱形图。
为了便于查看，在步骤之2中，选定系列选项卡，在分类（X）轴标志选项中选定销售收入、销售利润、营业利润和利润总额标志所在的单元格。
（2）慢双击利润总额数据系列，如图5-12-22所示。
图5-12-22
（3）将鼠标指向利润总额数据系列的上沿，并向上拖拽，直到其显示数据为所需的146000为止，注意这时在Y坐标轴相应的数据位置会出现一个横线，指示当前数据的大小。
如图5-12-23所示。
图5-12-23
这时将弹出单变量求解对话框，Excel2000自动将利润总额单元格的地址“C15”填入到目标单元格框，将“146000”填入到目标值框中。
在可变单元格框中输入销售收入所在的单元格地址“C3”，也可指定可变单元格后，直接单击C3单元格。
单击确定。
计算结果如图5-12-24所示。
图5-12-24
从图中可以看到销售收入需增加到1451831元，即增加49131元，才能使利润总额增加4545元，达到利润总额增加到146000元的目标。
三、其他应用
利用目标搜索技术可以求解许多类似的问题。
例如，利用PMT函数，可以根据贷款额、利率和周期方便地计算出每期的付款额。
但是反过来，已知某企业近5年每月偿还贷款的能力为100000元，要计算其可以承受的贷款额度，就需要掌握更多的函数和计算方法。
但是使用目标搜索技术则可以直接求解。
再如在宏观经济分析中要求控制投资规模，在固定资产投资总额降低5%的目标下，相应的自筹投资应控制在多少。
这可能需要涉及到诸多因素，例如预算内投资、贷款投资、利用外资投资、国民生产总值、物价指数等等，而且这些因素之间还存在着相互制约的关系，用手工计算是相当复杂的。
利用目标搜索技术，只要在工作表中建立了上述方程也可以直接求解。
上述这些问题归纳起来都是数学上的求解反函数问题，即对已有的函数
，给定
的值，反过来求解
。
一般情况下可以按照
与
的依赖关系，构造一个反函数
。
但是当变量之间的依赖关系较为复杂，特别是对于非线性函数，构造反函数的工作也是较为复杂繁琐的。
而利用目标搜索技术，则可以利用直接函数方便地完成反函数的计算。
利用目标搜索技术还可以直接求各种方程，特别是非线性方程的根。
在数值分析中解任意方程通常有叠代法、割线法、半间距法等多种算法求解，但大多较为复杂。
而利用Excel2000的单变量求解命令则是求解方程的方便工具。
例如要求解下述非线性方程的根：
其具体操作步骤如下：
设用A1单元格存放
的解。
选定A1单元格，并将其命名为X。
选定B1单元格，并在其中输入公式：
“=2*X^3-5*X^2+7*X-10”。
此时因为A1单元格的值为空，故X的值按0计算，所以B1单元格的值为“-10”。
单击工具菜单中的单变量求解命令。
在单变量求解对话框中，Excel2000自动将当前单元格B1填入目标单元格框；在目标值框中输入“0”；指定可变单元格为A1。
如图5-12-25所示。
单击确定。
图5-12-25
这时出现单变量求解状态对话框，显示已求得一个解。
这时A1单元格显示的是求得的方程的一个根，近似为2。
如图5-12-26所示。
图5-12-26
通过本节内容的学习，应掌握模拟运算表、方案和单变量求解等辅助决策分析工具的应用。
通过上述工具的应用使得管理决策数量化、科学化，提高管理水平。
升级会员 