CVA协会

一文解锁Excel模拟运算表的所有隐藏功能：多纬度与多项目批量估值新方法

﹀

作者简介：

任君

注册估值师（CVA），FMT持证人，FRM二级。远景能源有限公司资产管理部项目估值建模分析师。

掌握各类新能源项目估值模型的搭建，为公司国内外项目投资提供工具支持；熟练运用Visual Basic等，为公司大批量项目投决进行速算分析。

模拟运算表是Excel中的一项非常强大的工具，广泛应用于财务建模和商业分析。然而，其机制上存在两大限制：其一，只能处理二维以内的因素；其二，每个输入因素只能限定在单一单元格中。本文除了介绍模拟运算表在估值建模中的常见用法，还将进一步探索突破模拟运算表上述局限性的方法，从而满足复杂场景的实际需求。同时，我们也将讨论当模拟运算表运行效率低下时的解决方案，并提供相应的Visual Basic代码。

一

常见用法1：双因素单一结果的敏感性分析

双因素敏感性分析是模拟运算表最典型的应用场景，适用于分析两个输入因素的变化对单一输出结果的影响。不限于以下情景：

a）价格和销量的变化对NPV的影响。

b）WACC和永续增长率对每股价格的影响。从CVA协会官网上，我们下载一份科目二样题，简单将其作为本文研究案例，介绍模拟运算表的使用。

如图所示，首先，设计好WACC和永续增长率影响下的二维模拟运算表，左上角红框K31链接输出项的单元格每股价格I42，L31:Q31输入引用行单元格数据（即WACC）的变化范围（5.5%~8%），K32:K38输入引用列单元格数据（即永续增长率）的变化范围（0.5%~3.5%）。其次，选中整个K31:Q38，点击Excel上方的数据→模拟分析→模拟运算表。再次，对应输入引用行和列的单元格，点击确定即可。

需要注意，计算时容易出现行和列颠倒输入的错误。敏感性分析表中，行代表横向变化范围，是矩阵上边的蓝框区；纵代表竖向变化范围，是矩阵左边的绿框区。此外，敏感性分析表必须和输入单元格在同一张工作表Sheet中。

二

常见用法2，单因素多结果的敏感性分析

模拟运算表也可对单因素进行敏感性分析，且输出多个结果，不限于以下场景：

a）价格的变化同时对EBITDA、NPV、IRR的影响。

b）退出乘数的变化同时对股权价值、每股价格的影响。仍以科目二样题为例，分析退出乘数对股权价格和每股价值的影响，如下图所示。

若将矩阵的上边作为输入，则代表输入引用行的单元格变化范围（蓝框），那么矩阵的左边就作为输出项的链接范围（红框）。同样地，若将矩阵的左边作为输入，则代表输入引用列的单元格变化范围，那么矩阵的上边就作为输出项的链接范围。

三

拓展用法1，当某变动因素涉及多个单元格时应如何处理？——场景编号法

财务建模中，某些因素的输入会涉及多个单元格而非单一单元格。例如，在跨越多年的估值模型中，销售收入增长率、销货成本占营收的比重等假设，既可输入在某具体单元格中（即假设每年均等，或后续年与首年存在简易函数关系）；也可逐年输入在多个单元格中（即假设每年不同，且首年与后续年并不存在简易函数关系）。

以销售收入增长率为例，若预测期每年的增长率均不同，就需多个单元格输入，从而形成销售收入的增长率曲线。

在实操中，经常需快速分析乐观、中观、悲观场景下的销售收入增长率曲线，对NPV、IRR或每股收益等指标的影响。传统的模拟运算表操作无法实现，因此我们需要引入“场景编号法”，即通过一个中介单元格作为场景编号的输入单元格，再结合Choose或Lookup或Index&Match等索引公式实现不同场景与该输入单元格的联动。

联动后，直接将场景编号单元格（单一单元格）作为模拟运算表“输入引用行（或列）的单元格”，即可快速实现不同销售收入增长率曲线下的指标分析。

四

拓展用法2，当变动因素超过二维时，应如何处理？——测算方案编号法

同一个项目，可能存在多种不同输入下的测算方案，实操中，其所涉及的输入维度往往超过二维。如何快速分析出，哪一种测算方案的收益是最优的呢？这个问题，其实也可以通过模拟运算表得出结论，我们引入“测算方案编号法”。

在永续法下，普遍会计算不同WACC对每股价格的影响。而随着公司的发展和市场环境的变化，资本结构、债务综合成本、无风险利率、贝塔、市场风险溢价等因素的变动，都会成为WACC变化的原因。若想分析这些因素的变动对净现值或每股价格的影响，就需敏感性分析。

以相对简单的3维因素为例，若每维因素均多假设出1种变化可能性，就需完成2×2×2=8次测算，二维敏感性分析表无法实现，可尝试使用测算方案编号法。第一步，先构建融资方案表格I10:L19，去清晰展现每一个测算方案下各因素的数值。再将模型中各因素的原输入单元格，变成索引单元格，即通过Lookup或Index&Match等索引函数，将输入单元格与该测算编号所属单元格K5进行联动。

联动后，直接将测算方案编号单元格（单一单元格）作为模拟运算表“输入引用行（或列）的单元格”，即可观察不同因素变化所导致的指标变化情况。

五

拓展用法3，当需速算多个项目的价值时，应如何处理？——测算项目编号法

在项目估值的实操中，往往涉及对多个项目（如资产池/包）的价值进行批量化测算。常见的，我们有如下几种解决方式：

（一）解决方式：N个项目构建N个工作簿，每1个工作簿设计多个sheet。

a）优点：精细化、特征化每个项目的价值；每张Sheet职能清晰，便于分析。

b）缺点：若项目数量非常多，输入输出的工作量大、耗时长；项目间跨工作簿的对比分析相对繁琐，增加外部链接，容易导致Excel运行卡顿。

（二）解决方式：N个项目构建1个工作簿，每1个Sheet代表1个项目。

a）优点：一张Excel集成式管理；无需跨工作簿对比分析不同项目，减少外部链接与卡顿；输入逻辑和底层计算逻辑保持一致可有效避免操作失误。

b）缺点：一张Sheet发挥了多种计算功能（因素输入、三表、指标输出等），其导致该Sheet的行数非常冗长，一来对于建模者而言，公式引用不便；二来对于使用者而言，审阅不便。

虽在大批量测算的前提下，方式二的优势明显，可节省非常大的时间成本，但一张Sheet的行数很可能超过几百行甚至几千行，无疑增加了建模和使用的繁琐度。此外，在CVA协会发布的《财务建模规范指南CAFE准则》中也明确指出，建议用一个工作簿来创建财务模型，至少包括封面，输入参数，模型计算，输出结果和备注五个部分，即官方仍推荐用方式一，其在模型的结构设计上，比方式二更好。

在几年的批量化工作经验下，我向大家介绍一种扬长避短的新方法，我将其称为“测算项目编号法”。一方面，这种方法遵循了CAFE对模型结构的要求，仍选择用一个工作簿和多张工作表，反映单一资产的输入、计算和输出的模型架构；而另一方面，为了实现多个项目的批量化速算，我们将在模型的输入页前单独新增一个项目输入数据库页，该数据库页集成了所有项目的所有输入因素信息，并要求在该页的A列对所有项目进行编号。

在前几种模拟运算表拓展用法的铺垫下，相信此刻，绝大部分的估值同行已了然后续的操作。即我们会用一个单元格，作为项目编号的输入单元格，然后在模型的输入页中，把原先纯数据性的输入，变成Lookup或Index&Match等索引性公式，将项目编号与数据库链接，即实现选择项目1号时，该估值模型是1号项目的输入与输出；在选择项目N号时，该估值模型所体现的是N号项目的输入与输出。

若要同时分析这N个项目的经济性，可在编号单元格所在的同一张Sheet中构建单一因素模拟运算表，即项目编号是模拟运算表的唯一输入项，而项目价值是模拟运算表的输出项。

若需观察不同场景对该资产池各项目价值的影响，也可构建双因素敏感性分析表，将项目编号和场景编号，作为模拟运算表的输入双因素，项目价值是模拟运算表的输出项。

该解决方式的优点是，哪怕有几十个方案，也可通过模拟运算表快速输出结果，非常便于公司做资产池管理、或年度财务预算等。而若要看某个具体项目的经济性或三张财务报表时，也可通过在编号单元格中，输入该项目的编号即可实现需求。

当然该解决方式也有一定的局限性，即假设项目的底层测算逻辑基本一致，具有相似的影响因素，仅输入数据是不同的而已。若项目的底层测算逻辑不一致，则该方式不可使用。

六

如何解决敏感性分析表卡顿的问题？

当敏感性分析涉及大量计算（如数据表行、列数非常大），模型可能会变得卡顿，以下是两种有效的解决方案：

（一）选择部分计算，键盘Fn+F9——适用于熟悉估值模型的使用者操作

默认状态下，选择不激活模拟运算表的自动计算功能。具体操作是，在工作簿的文件→更多→选项→公式→计算选项→工作簿计算，点击“部分”。当需进行敏感性分析时，点击Fn+F9键触发计算。

当然在Excel的传输过程中，时常发生自动变回自动重算的情形。若估值同行还需使用Visual Basic计算其他内容（如Debt Sizing、循环单变量求解等），打开状态的敏感性分析表会使其运行卡顿。故在运行前，增加“关闭自动重算”的代码非常重要。

代码如下：

Application.Calculation = xlCalculationSemiautomatic

（二）直接用VB设计敏感性分析表——适用于不熟悉估值模型的其他使用者操作

对于不熟悉Excel的人而言，使用模拟运算表仍具有一定难度，且横、纵易操作错误。因此，建模人可直接用Visual Basic设计出一个敏感性分析表的使用模块。该模块可让其他使用者自由选择，模拟运算表的横输入因素、纵输入因素，以及输出因素是什么（可通过数据验证设计成下拉选项），再提供这些因素变化范围的输入空间。然后使用者点击VB运行按钮，即可得到模拟运算结果。