Excel应用实例之二——敏感分析.pdf

5.12 Excel应用实例之二——敏感分析[ 本节提要 ] 本节主要通过投资分析等问题，介绍了Excel 2000的模拟运算表、 方案和单变量求解的应用， 着重说明了单变量模拟运算表和双变量模拟运算表的操作步骤，在模拟运算表的基础上进行敏感分析的方法，以及应用方案和单变量求解工具辅助决策的方法敏感分析也称作 “What -If 分析” ，是在财务、会计、管理、统计等应用领域不可缺少的工具例如在财务分析中，许多指标的计算都要涉及到若干个参数像长期投资项目，其偿还额与利率、付款期数、每期付款额度等参数密切相关又如固定资产的折旧，与固定资产原值、估计残值、固定资产的生命周期、折旧计算的期次以及余额递减速率等密切相关而作为决策者往往需要定量地了解，当这些参数变动时对有关指标的影响 这些分析可以利用Excel 2000的模拟运算表工具实现以下通过投资效益的分析说明有关工具的使用5.12.1模拟运算表所谓模拟运算表 实际上是工作表中的一个单元格区域，它可以显示一个计算公式中某些参数值的变化对计算结果的影响由于它可以将所有不同的计算结果以列表方式同时显示出来， 因而便于查看、 比较和分析 根据分析计算公式中的参数的个数，模拟运算表又分为单变量模拟运算表 和双变量模拟运算表 。

一、单变量模拟运算表单变量模拟运算主要用来分析当其它因素不变时，一个参数的变化对目标值的影响例如，要计算一笔贷款的分期偿还额，可以使用 Excel 2000提供的财务函数之 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) 选择某个单元格区域作为模拟运算表存放区域，在该区域最左列输入假设的利率变化范围数据 因为该数据系列通常是等差或是等比数列，所以可利用Excel 2000 的自动填充功能快速建立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元，大大多于趸交的款额但是对于这种长期投资问题， 还必须要考虑利息的收益和利率变动的影响为此，可以利用Excel 2000 提供的现值函数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方案分析模拟运算表主要用来考查一个或两个决策变量的变动对于分析结果的影响，但对于一些更复杂的问题， 常常需要考查更多的因素 例如为了达到公司的预算目标，可以从多种途径入手可以通过增加广告促销，可以提高价格增收，可以降低包装费、材料费，可以减少非生产开支等等利用 Excel 2000 提供的方案管理器 ，可以模拟为达到目标而选择的不同方式对于每个变量改变的结果都被称之为一个方案， 根据多个方案的对比分析， 可以考查不同方案的优劣， 从中选择最合适公司目标的方案。

例如图 5-12-12 所示的是思创公司1999 年 1 月的损益表，其中包括了各项指标的计算公式管理人员希望分析，通过增加销售收入，减少生产费用，降低销售成本等措施对公司利润总额的影响这可以利用 Excel 2000 的方案工具进行分析，主要包括下述操作图 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) 这时会弹出与 添加方案 一样的 编辑方案 对话框可以根据需要修改方案名称，改变可变单元格以及重新输入可变单元格的变量值三、方案总结上述浏览方式只能一个方案一个方案地查看，如果将所有方案汇总到一个工作表中，然后再对不同方案的影响比较分析，这对。