excel敏感性分析

投资工作中，敏感性分析表的应用十分广泛，最常见的是招拍挂项目中，我们需要看到利润率/IRR随着地价的变化，以明晰我们的举牌空间。

当然，敏感性分析表的用途可远不止如此，比如通过它我们还可以做售价-利润率敏感性分析，成本-利润率敏感性分析，售价&地价-利润率敏感性分析，成本&地价-利润率敏感性分析等等。熟练掌握这个技巧以后，我们甚至可以做任何单变量及双变量的敏感性分析。

但是，很多朋友却对敏感性分析表的操作非常陌生，过于依赖测算表格的现有公式链接而无法灵活使用，或者测算公式出现问题以后也不知如何修改。这篇文章我们就一起来系统梳理一下敏感性分析，解决工作中的理解及操作障碍。为了便于大家理解，我从以下4个方面进行阐述：

1. 理解敏感性分析表2. 单变量敏感性分析3. 双变量敏感性分析4. 多变量敏感性分析

以上4个部分由浅到深，重点在2、3、4部分。

01

1、理解敏感性分析表

我们首先通过一个最简单的案例来理解Excel敏感性分析表的基本框架及操作要点。敏感性分析在Excel中主要通过“模拟运算表”实现。

通过计算不同成本、不同售价下的利润率，回报率等测算结果的敏感性分析，在实际的测算和投资工作中较为常见。这类问题的基本操作步骤如下：

（1）输入你需要测算的变量数据，例如：在列填入成本，在行填入售价。

（2）输入你需要的计算公式，例如：测算利润率=（售价-成本）/成本。到这一步形成了你的基本计算模型。

（3）接下来是建立，成本和售价变得的双敏感性分析，也是关键一步。首先建立列和行的变量数据。

（4）在行和列的交叉点，输入=基本计算公式的单元格。我在这里的公式是=D2

（5）全选你的敏感性计算区域，选择数据，插入模拟分析-模拟运算表。

（6）行选择对应的售价，列选择对应的成本，点击确定，大功告成，形成了不同售价，不同成本对应的利润率变化的双敏感性分析表，减少了手动计算的工作量。

这里需要注意，计算公式内的变量和公式一定要和敏感性内的引用源数据保持一致。

这其实就是一个最简单的双变量敏感性分析，理解这个步骤以后，我们再看后续部分就轻松多了。

02

2、单变量敏感性分析

常用的单变量敏感性分析表有：地价-利润率，售价-利润率，成本-利润率；地价-IRR，售价- IRR，成本- IRR。操作基本一致，我们以地价-利润率敏感性分析为例做演示。

（1）输入你需要测算的变量数据，在列填入起始地价130（万元/亩），这里务必注意：要同步将测算表中计算土地成本的亩单价链接到此处的130（万元/亩），否则通过模拟运算表计算出来的利润率不会有变化。

大家可以理解为：敏感性分析表中的地价变化—>测算表中土地成本变化—>测算表中利润率变化—>敏感性分析表中的利润率变化。

在行填入售价；由于我们只需要分析利润率随地价的变化，因此这里的售价我们可不用填写。

（2）输入你需要的计算公式，测算利润率=利润表对应的利润率。

（3）接下来是建立，地价-利润率单敏感性分析，也是关键一步。首先建立列和行的变量数据，列为地价（亩单价）变量数据；行理应为售价数据，在此我们不用填写，保留空格即可。

（4）在行和列的交叉点，输入=基本计算公式的单元格。我在这里的公式是=C2

（5）全选你的敏感性计算区域，选择数据，插入模拟分析-模拟运算表。

（6）行选择对应的售价（此处为“空格”，但仍然要选择），列选择对应的地价，点击确定，形成了不同地价（亩单价）对应的利润率变化的单敏感性分析表。

更换不同的行、列数据，我们便可得出售价-利润率，成本-利润率，地价-IRR，售价- IRR，成本- IRR的单敏感性分析表，大家可自行尝试。

03

3、双变量敏感性分析

双变量敏感性分析的操作步骤同第1部分基本一致，在此就不赘述了。只是需要强调一点，敏感性分析表中所应用到的成本及售价数据，相应地要将测算表中对应的成本及售价数据链接到此处，否则利润率不会变化。

04

4、多变量敏感性分析

理论上，在测算表中是没办法实现多变量敏感性分析的，在这里的多变量敏感性分析主要还是指的双变量敏感性分析，不过是其中的一个变量由多维横向数据构成而已，类似这样的情况：

第3部分的双变量敏感性分析，售价的变化只能体现单个业态的变化，即要么是高层售价和地价构成双变量去反映利润率的变化，要么是商铺售价或者公寓售价。而这种情况是高层、商铺、公寓的售价同时按一定幅度（上面案例为5%）变化后，与地价构成双变量去反映利润率的变化。相比单一业态售价，这种情况更适合我们的实际情况一些，这种敏感性分析操作步骤也更加繁琐一些。接下来，我们演示一下主要步骤：

（1）搭建如下表格框架：

黄色部分表示，当前我们定价为高层15000，商铺25000，公寓20000，我们在此价格基础上按5%的梯度进行调整；蓝色表示地价的变化，此处变化幅度为10万/亩；中间白色部分为利润率的变化情况。

注意，同前面一样，130需要链接到测算表中的地价，85%同样需要链接到测算表中售价，以此来触发测算表中的地价、售价变化。

130如何链接比较简单，关键在于85%的链接，需要将“销售预测表”中的：

高层售价链接为=敏感性分析!$L$6*敏感性分析!I3（其实就是15000*85%）；商铺售价链接为=敏感性分析!$L$7*敏感性分析!I3（其实就是25000*85%）；公寓售价链接为=敏感性分析!$L$8*敏感性分析!I3（其实就是20000*85%）。

这么做的目的，就是为了把85%带入到测算表的计算过程中。

好了，基础工作到这里便差不多了，选中计算范围，点击数据-模拟分析-模拟运算表，设置引用的行、列单元格：

点击确定，便出现结果：

如果出现数据不动的情况，可能是“计算选项”勾选为“手动”的原因，可以依次点击“公式”-“计算选项”-“自动”切换即可。

好了，以上是敏感性分析演示的基本操作，大家不用按部就班，主要是明白其中计算的逻辑，掌握这个逻辑以后，就可以按照自己的需求去设置自己想要的敏感性分析结果了，别小看这个工具，有时候为了能把数据展现得更加清晰，这个工具可以省去大量的人工劳动呢！

总之，能够帮助到大家，提升一丢丢工作效率，我就很开心了。

更多成本资料请咨询QQ1446119784