Excel2000应用案例之十四

第四章 敏感分析

[本章提要]本章主要通过投资分析等问题，介绍了Excel 2000的模拟运算表、方案和单变量求解的应用，着重说明了单变量模拟运算表和双变量模拟运算表的操作步骤，在模拟运算表的基础上进行敏感分析的方法，以及应用方案和单变量求解工具辅助决策的方法。

敏感分析也称作“What-If分析”，是在财务、会计、管理、统计等应用领域不可缺少的工具。例如在财务分析中，许多指标的计算都要涉及到若干个参数。像长期投资项目，其偿还额与利率、付款期数、每期付款额度等参数密切相关。又如固定资产的折旧，与固定资产原值、估计残值、固定资产的生命周期、折旧计算的期次以及余额递减速率等密切相关。而作为决策者往往需要定量地了解，当这些参数变动时对有关指标的影响。这些分析可以利用Excel

2000的模拟运算表工具实现。以下通过投资效益的分析说明有关工具的使用。

4.1 模拟运算表

所谓模拟运算表实际上是工作表中的一个单元格区域，它可以显示一个计算公式中某些参数的值的变化对计算结果的影响。由于它可以将所有不同的计算结果以列表方式同时显示出来，因而便于查看、比较和分析。根据分析计算公式中的参数的个数，模拟运算表又分为单变量模拟运算表和双变量模拟运算表。

4.1.1 单变量模拟运算表

单变量模拟运算主要用来分析当其它因素不变时，一个参数的变化对目标值的影响。例如，要计算一笔贷款的分期偿还额，可以使用Excel

2000提供的财务函数之一PMT。而如果要分析不同的利率对贷款的偿还额产生的影响，则可以使用单变量模拟运算表。

假设某公司要贷款1000万元，年限为10年，目前的年利率为5％，分月偿还。则利用PMT函数可以计算出每月的偿还额。其具体操作步骤如下：

在工作表中输入有关参数，如图4－1所示。

在B5单元格输入计算月偿还额的公式：“=PMT(B3/12,B4*12,B2)”

在上述公式中，PMT函数有三个参数。第一个参数是利率，因为要计算的偿还额是按月计算的，所以要将年利率除以12，将其转换成月利率。第二个参数是还款期数，同样的原因需要乘以12。第三个参数为贷款额。该函数的计算结果为“-106065.52”，即在年利率为5%，年限为10年的条件下，需每月偿还106065.52元。

图4－1

近几年来，国家为了宏观调控经济的发展，多次调整了利率。作为投资决策人员，需要全面了解当利率变动时，对偿贷能力的影响。这可以使用单变量模拟运算表实现。其具体操作步骤如下：

选择某个单元格区域作为模拟运算表存放区域，在该区域的最左列输入假设的利率变化范围数据。因为该数据系列通常是等差或是等比数列，所以可利用Excel

2000的自动填充功能快速建立。

在模拟运算表区域的第2列第1行输入计算月偿还额的计算公式。

选定整个模拟运算表区域。如图4－2所示。

图4－2

单击数据菜单中的模拟运算表命令。

这时将弹出模拟运算表对话框，如图4－3所示。

在模拟运算表对话框的输入引用列的单元格框中输入“$B$3”。单击确定。

所谓引用列的单元格，即模拟运算表的模拟数据（最左列数据）要代替公式中的单元格地址。本例的模拟运算表是关于利率的模拟数据，所以指定$B$3，即年利率所在的单元格为引用列的单元格。为了方便，通常称其为模拟运算表的列变量。

图4－3

模拟运算表的计算结果如图4－4所示。

图4－4

请注意，这时单元格区域B8:B16中的公式为“{=表(,B3)}”，表示其是一个以B3为列变量的模拟运算表。与一般的计算公式相似，当改变模拟数据时，模拟运算表的数据会自动重新计算。

除了用于贷款分析之外，函数 PMT 还可以计算出别的以年金方式付款的支付额。例如，如果需要以按月定额存款方式在20年中存款100000，假设存款年利率为4%，则函数

PMT 可以用来计算月存款额：“＝PMT(4%/12, 20*12, 0, 100000)”，公式计算结果为“272.65”。即向年利率4%的存款账户每月存入272.65元，20年后连本带利可获得100000元。

4.1.2 双变量模拟运算表

当需要其它因素不变时，两个参数的变化对目标值的影响时，需要使用双变量模拟运算表。例如上例，如果不仅要考虑利率的变化，还可以选择贷款年限，这时需要分析不同的利率和不同的贷款期限对贷款的偿还额的影响，这时需要使用双变量模拟运算表。

双变量模拟运算表的操作步骤与单变量模拟运算表类似：

选择某个单元格区域作为模拟运算表存放区域，在该区域的最左列输入假设的利率变化范围数据；在该区域的第一行输入可能的贷款年限数据。

在模拟运算表区域的左上角单元格输入计算月偿还额的计算公式。

选定整个模拟运算表区域。如图4－5所示。

图4－5

单击数据菜单中的模拟运算表命令。

在模拟运算表对话框的输入引用行的单元格框中输入“$B$4”；在输入引用列的单元格框中输入“$B$3”。单击确定。

双变量模拟运算表的计算结果如图4－6所示。其中B8:F16单元格区域的计算公式为“{=表(B4,B3)}”，表示其是一个以B4为行变量，B3为列变量的模拟运算表。

图4－6

4.1.3 敏感分析

利用模拟运算表还可以进一步进行其他方面的敏感分析。下面通过购买某个险种的保险时如何选择缴款方式，来说明有关敏感分析的操作。

设准备购买某保险10万元，可以有两种缴款方式供选择：一种是趸交，即一次付清105,490元；另一种是分30年付款，每年付6,350元。如果单从付款额来说，后一种付款方式累计缴款190,500元，大大多于趸交的款额。但是对于这种长期投资问题，还必须要考虑利息的收益和利率变动的影响。为此，可以利用Excel

2000提供的现值函数PV或未来值函数FV，计算和比较在特定利率情况下两者的收益。再进一步应用模拟运算表分析利率变动的影响。

首先将有关数据输入到工作表中，再利用PV和FV函数计算分期付款方式在特定年利率情况下的现值和未来值。这里设年利率为5％，则计算结果如图4－7所示。

图4－7

从计算结果可以看出，在年利率为5％的情况下，分期付款方式相当于现在一次付款102,495元。也就是说，在年利率为5％的情况下，采用分期付款方式较好。

近几年来，国家为了宏观调控经济的发展，曾多次调整银行利率。为了比较不同利率对保险收益的影响，可建立以年利率3.50％～5.50％为行模拟数据的模拟运算表。如图4－8所示。

图4－8

从模拟运算表中可以看出，当年利率为4.75时，两种缴款方式效果近似，当年利率低于4.75时，宜采用趸交方式；而高于4.75时，宜采用分期付款方式。或者说，如果有其他年利率大于4.75元的投资途径时，采用分期付款方式可以获得更好的收益。

如果还要考查不同支付额的影响，可以使用双变量模拟运算表。图4－9是以年利率3.50％～5.50％为行模拟数据，支付额6,150～6,550为列模拟数据的双变量模拟运算表。

图4－9

在模拟运算表的基础上，还可以进一步进行敏感分析。可以通过改变除行变量和列变量以外其他参数的值，分析其对模拟运算表计算结果的影响；而改变函数名称，则可以方便地得到其他相关指标的的模拟运算表。

例如，在上例的现值分析中，年限都是30年，如果要考查年限为15年或是25年时，各模拟数据的变动情况，可以直接修改年限数据，这时整个模拟运算表会自动重新计算。图4－10即年限为20年时的双变量模拟运算表。

图4－10

如果要分析这笔投资30年后的效益，可以使用FV函数计算其未来值。显然未来值的计算也是同利率、付款额和年限等参数相关。这里只需在原来模拟运算表的基础上，将原来的计算公式中的函数名由“PV”改成“FV”即可。图4－11

即为有关分期付款方式未来值的模拟运算表。如果保险回报与之相比过低，而且风险不大时，可以考虑采取其他投资方式。

图4－11