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

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

□财会月刊·全国优秀经济期刊

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

谷增军

（山东工商学院 山东烟台 264005）

【摘要】 本文介绍了实务中用得最多的数据分析工具——Excel 模拟运算表，举例说明了模拟运算表在财务分析中的 应用，希望起到抛砖引玉的作用，提高财务工作的效率。

【关键词】 模拟运算表 变量 筹资决策

Excel 模拟运算表是一种只需一步操作就能计算出所有 数值变化的模拟分析工具。它可以显示公式中某些值的变化

第二步，选定包含变量和公式的单元格区域 D2：E7。 第三步，从“数据”菜单中选择“模拟运算表”选项，出现

对计算结果的影响，为同时求解某一运算中所有可能的变化 “模拟运算表”对话框。

值组合提供了捷径。另外，模拟运算表还可以将所有不同的计 算第四步，“模拟运算表”在对话框中有两个编辑框，分别为 结果同时显示在工作表中，便于查看和比较。本文介绍了 Excel “输入引用行的单元格”和“输入引用列的单元格”。如果变量 模拟运算表的功能、用法，并详细说明了其在财务分析 中的应的一组值放在同一行中，则在“输入引用行的单元格”编辑框 用。

一、模拟运算表的分类 模拟运算表有两种类型，即单变量模拟运算表和双变量

中输入变量所在的第一个单元格左边的单元格；如果变量的 一组值放在同一列中，则在“输入引用列的单元格”编辑框中 输入变量所在的第一个单元格上面的单元格。本例中的一组 变量放在 D 列，所以应该在“输入引用列的单元格”编辑框中 输入 D2 单元格，如图 1 所示。

第五步，单击“确定”选项，模拟运算表就会根据不同的变 量值进行计算，同时将计算结果填充到运算表中相应的位置。

模拟运算表。

单变量模拟运算表是在工作表中输入一个变量的多个 不同值，分析这些不同变量值对一个或多个公式计算结果的 影响。

双变量模拟运算表用于分析两个变量的几组不同的数 值变化对公式计算结果的影响。在应用时，这两个变量的变化 值 分别放在一行与一列中，而两个变量所在的行与列交叉的 那 个单元格反映的是将这两个变量代入公式后得到的计算结 果。双变量模拟运算表中的两组输入数值使用同一个公式。这 个公式必须引用两个不同的输入单元格。在输入单元格中，源 于数据表的输入值将被替换。工作表中的任何单元格都可作 为输入单元格，尽管输入单元格不必是数据表的一部分，但是 数据表中的公式必须引用输入单元格。

二、单变量模拟运算表的应用 已知某企业所生产产品的单价、销量、固定成本、单位变

动成本，如图 1 所示。假设产品单价分别为 250 元、252 元、 单变量模拟运算表还可以同时对多个公式进行计算。如 254 元、256 元、258 元，试求其他条件不变时的利润变动额。 这图 1 所示，在 F2 单元格可以设置公式“=D2*B4”，以下的操 作是已知一个变量的多个不同值，求对应的不同计算结果的 问题，方法基本相同，这里不再详细说明。 可以用单变量模拟运算表来解决。操作步骤如下： 三、双变量模拟运算表的应用 双变量模拟运算表在计算公式固定的前提下，可快速求

第一步，在 D2 单元格中输入公式“=D2*B4-B3*B4- B2”在 D3：，D7 单元格区域输入单变量可能的取值。

应注意的是，如果可能的取值在同一列中，则含有公式的 单

出两个变量连续变动的计算结果，下面举例说明其具体用

法。

1. 双变量模拟运算表在本量利分析中的应用。单变量模

元格必须在第一个变量取值的右上角（ 此处为 D2 单元 格）；拟运算表仅考虑了单价的变化，没有考虑销售量的变化。若销 如果可能的取值在同一行中，则含有公式的单元格必须 在第售量和单价都发生变化，其他条件不变，可以用双变量模拟运 一个变量取值的左下角。

算表来预测利润。操作步骤如下：

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

（1）先设计好模拟运算表的模板，如图 2 所示，两个变量 的变化值分别填在一行与一列中，而两个变量所在的行与列 交叉的那个单元格反映的是将这两个变量代入公式后得到的 计算结果。

（2）在 B8：B13 和 C8：G8 单元格区域输入两个变量的取 值。

（3）在 B7 单元格中输入公式“=B7*A8-B2-B3*A8”， 要注意的是，公式必须放在两组变量交叉的单元格 B7 中。

（4）选定包含两组变量和公式的单元格区域 B8：G13。 （5）单击“数据”菜单中的“模拟运算表”选项，出现“模拟 运算表”对话框。

（6）在“输入引用行的单元格”编辑框中引用行系列数据 变量的位置，这里是 A8；在“输入引用列的单元格”编辑框中 引用列系列数据变量的位置，这里是 B7。

全国中文核心期刊·财会月刊□

款法下年限、利率两因素决定的每年还款额，图 3 中显示负 数，表示还款资金流出。当然，我们也可以求出每月的还款额， 只需要将还款期改为月，年利率改为月利率就可以了。

同样道理，我们可以用 PPMT（）函数预测每期偿还的本 金，用 IPMT（）函数预测每期偿还的利息，这里不再详述。

4. 双变量模拟运算表在债券风险综合分析中的应用。对 于具有相同息票利率的债券，若期限长短以及投资者的必要 报酬率各不相同，与之相对应的债券价格也必然不同。无论对 于债券的发行方还是投资方，在债券价格的计算中考虑期限 因素对必要收益率变动的敏感程度，都具有十分重要的意义。 假设两种

债券的当前息票年利率都为 10% ，面值都为

1 000 元，第一种债券的到期年限为 5 年，第二种为 20 年，要 求比较两种债券的利率风险。操作步骤如下：

（1）建立如图 4 所示的双变量模拟运算表，将期限和必要

（7）单击“确定”选项即得到结果。

2. 双变量模拟运算表在编制各种现值、终值系数表中的 应用。下面以用双变量模拟运算表编制年金现值系数表为例， 介绍一下在 Excel 中如何生成这些系数表。

（1）单击“文件/新建”“新建”或选项，建立一张新工作簿， 并选（B3：B12）（、C2：G2）单元格区域。

收益率设为变化量。

（2）在 A5 单元格中输入公式：“=PV（E2，D2，-B2，-C2）”， 在模拟运算表的“输入引用行的单元格”对话框中输入$D$2， 在“输入引用列的单元格”对话框中输入$E$2，再单击“确定” 按钮，就可

（3） 根据图 4 中的数据作出具有不同到期日的债券价格

择一张工作表，将期数、利率等基本数据输入该工作表， 可设为得到不同情况下的债券价格。

（2）并在行与列交叉的 B2 单元格中输入目标函数 PV 与利率变动的关系图，该关系图由 5 条陡峭程度不同的曲线

构成。债券的到期期限越长，其曲线越陡峭，其价格对利率波 动（rate，nper，pmt，fv，type），即在该单元 格 中 输 入 公 式“=PV

的敏感程度越高，即风险越大。 （A2，B1，-1）”。同样，如果求复利现值系数，在 B2 单元格中 输入

入公式“=FV（A2，B1，0，-1）”。 （3）选择目标单元区域 B2：G12，使用“数据/模拟运算表”

命令，出现如图 3 所示的对话框。在该对话框的“输入引用行 的

单元格”中输入$A$2，在“输入引用列的单元格”中输入$B

$1，再单击“确定”按钮，各年金现值系数就会自动显示在年金 现

值系数表中。

3. 双变量模拟运算表在筹资决策中的应用。

例：某企业向银行贷款 1 000 000 元，双方约定贷款利率

随之变动。单位财务人员想要了解不同利率以及不同还款期

公式“=PV（A2，B1，0，-1）”；如果求复利终值系数，在 B2 单元格中输对应的还款额，可以建立如图 3 所示的双因素分析表。在 B3 单元格中输入公式：“=PMT（A3，B2，B1）”，在模拟运算表对 话框的“输入引用行的单元格”中输入$A$3，在“输入引用列 的单元格”中输入$B$2，再单击“确定”按钮，就可得到等额还

主要参考文献

刘根霞.Excel 模拟运算表在财务决策中的运用.中国管理

信息化，2008；19

·□

本文来源：https://www.bwwdw.com/article/afzj.html