Excel模拟运算表在财务模型中的应用

中国华电科工集团有限公司

引言

财务模型主要用于大型投资项目的经济分析，方案比较和敏感性分析是财务模型的重要组成部分。鉴于Excel的灵活性，财务模型一般使用Excel编制。财务模型的方案比较和敏感性分析通常使用VBA编程实现，这要求模型的编制者具备一定的VBA编程基础。而Excel提供的模拟运算表无须编程，能够更简单、直观地进行计算分析，无疑是更好的选择。本文介绍了模拟运算表的功能，详细说明了其在财务模型的方案比较和敏感性分析中的应用。

一、模拟运算表功能简介

模拟运算表是Excel提供的一种假设分析工具，用于观察公式中某一个或两个变量值的变化对计算结果的影响。根据观察变量的个数，模拟运算表分为单变量模拟运算表和双变量模拟运算表。

（一）单变量模拟运算表

单变量模拟运算表是在工作表中列出一个观察变量的多个值，分析这些值对计算结果的影响[1]。例如某公司计划投资一个项目，需贷款1000万元，还款期5年、等额本息方式还款，求年利率分别为5.0%、5.5%、6.0%、6.6%、7.0%时年还款金额各是多少。此时可以先将贷款1000万元，还款期5年、利率5%作为基准情形，然后再将各个利率列于模拟运算表中，用单变量模拟运算表计算对应利率下的年还款金额，见图1。

图1 单变量模拟运算表

图1中C8～F8行是各个利率值，即需要观察的变量。单元格B9直接引用B5的计算结果。选择B8～F9区域然后使用模拟运算表，不同利率下的年还款金额就会显示在C9～F9区域中。

（二）双变量模拟运算表

双变量模拟运算表是在工作表中列出两个观察变量的多个值，分析这些值对计算结果的影响[1]。模拟运算表最多只能分析两个变量的变化情况。上例中如果增加一个观察变量，例如需要分析在利率5.0%、5.5%、6.0%、6.6%、7.0%和还款期4年、5年、6年的情况下年还款金额的情况，则应该使用双变量模拟运算表，如图2所示。

图2 双变量模拟运算表

图2中F2～J2行对应利率，E3～E5列对应还款期，行列的交叉点E2单元格引用B5的计算结果。在“输入引用行的单元格”输入基准情形下利率的绝对引用$B$4，在“输入引用列的单元格”输入基准情形下还款期的绝对引用$B$3。此时将生成一个二维表格，给出不同利率和还款期组合下的年还款金额。

二、方案比较

财务模型的输入的假设条件通常有二三十个，关键假设条件一般也有七八个。为分析不同假设条件下项目的经济性，通常需要将一组关键假设组合为一个方案，再对不同方案下财务模型的输出结果进行比较。模拟运算表本身最多只能计算两个变量的情况，而一个方案中包含的假设条件往往多于两个，因此不能直接使用模拟运算表进行方案比较。但是，通过设置辅助表，模拟运算表就能够突破两个变量的限制，进行含有任意多个假设条件的方案比较。

例如计划投资建设一电厂项目，已建立财务模型，现需要比较不同EPC金额、利率、还款期和电价条件下的总投资、收益率和净现值，各种方案的假设条件见图3。

图3 方案比较

图3中A2～F6区域为辅助表，列出了各个方案的假设条件组合。B2单元格表示当前选择方案的编号，虚线框包含的B3～B6列显示了当前所选择方案的各项假设条件，可称为“当前方案列”，列中的数据需设置为与右侧对应方案的数据相同。

在编制财务模型时，所有需要引用辅助表中假设条件的公式都要链接到“当前方案列”，这一设置是使用模拟运算表进行方案比较的关键。B9单元格直接引用B2单元格，表示当前选择的是第几个方案。B10～B12列是当前方案下的计算结果，是模拟运算表的辅助列。需要注意的是，根据财务模型的结构，辅助列中的总投资、收益率和净现值的具体计算可能分布于财务模型的不同表中，在进行方案比较时B10～B12列只需将计算结果从别的表中引用过来，而不应在辅助列中直接计算，这是使用模拟运算表进行方案比较的第二个关键设置。

对B9～F12区域使用模拟运算表，Excel计算时会将B2单元格的数值依次替换为C9～F9区域的数值，每替换一次，当前方案列的数据就会被替换为该数值对应的方案的假设条件，财务模型就会进行一次对应方案的计算，并将计算结果依次输出至C10～F12区域。通过上述方法，方案的数量及每个方案包含的假设条件的个数可以任意增减，突破了模拟运算表只能计算两个变量的限制，并且计算结果能够随假设条件的变化动态更新。

三、敏感性分析

敏感性分析是指从多个不确定性因素中找出对项目经济效益指标有重要影响的敏感性因素，并分析其对项目经济效益指标的影响程度和敏感性程度。仍以上述电厂项目为例，分析项目的收益率对EPC金额的敏感性。保持其他假设条件不变，考虑在EPC金额增减5%和10%的情况下对收益率的影响。

如图4所示，本例中在基准情形下EPC金额为5.4亿元，收益率为14.8%。将基准情形的数据列于H7～K7行，在I8～I11列直接用公式计算出不同变化率下的EPC金额，J7单元格直接引用I3单元格，再对I7～J11区域使用单变量模拟运算表，计算结果显示于J7～J11列。此时已经得到不同EPC金额对应的收益率，然后再增加一列K6～K11，设置公式直接计算出不同EPC金额下的收益率相对于基准情形下收益率的影响。

图4 敏感性分析

结语

虽然模拟运算表能够简单、直观、快速地进行假设分析，但因为财务模型和模拟运算表自身的特性，在使用模拟运算表进行方案比较和敏感性分析时需要注意以下几点：

（1）不要破坏财务模型的总体逻辑结构。使用模拟运算表进行方案比较和敏感性分析时，应在单独的表中进行设置和计算，不能影响基本假设条件下财务模型的计算。

（2）合理地设置辅助表。财务模型中所有需要引用假设条件的公式都应直接或间接链接到辅助表的“当前方案列”，模拟运算表的辅助列中的数据应该从财务模型的相应位置引用过来，而不应直接在辅助列中计算。

（3）关注Excel的运行性能。财务模型含有大量公式，并且通常需要进行迭代计算，随着方案数量的增加计算量将成倍增加，这会影响Excel的运行速度。此时可在Excel“数据”选项卡的“计算选项”中选择“除模拟运算表外，自动重算”，当完成了财务模型的修改，需要进行方案比较时再把“计算选项”改为“自动”，这时所有方案的计算结果将自动更新。

综合上述分析与实例，可以看出使用模拟运算表进行方案比较和敏感性分析无须VBA编程，可以简化财务模型的结构，降低编制难度，还能针对假设条件的变化动态更新，方便、快速地展示计算结果，提高财务模型的运行效率和可读性。