经济软件分析第八章What-If分析

1、what-if分析”，是在财务、会计、管理、统计等经济应用 领域不可缺少的工具。例如在财务分析中，许多指标的计算都 要涉及到若干个参数。像长期投资项目，其偿还额与利率、付 款期数、每期付款额度等参数密切相关。又如固定资产的折 旧，与固定资产原值、估计残值、固定资产的生命周期、折旧 计算的期次以及余额递减速率等密切相关。而作为决策者往往 需要定量地了解，当这些参数变动时对有关指标的影响。这些 分析可以利用excel的模拟运算表工具实现。以下通过投资效 益的分析说明有关工具的使用。,第八章 what-if分析,8.1 模拟运算表 所谓模拟运算表实际上是工作表中的一个单元格区域，它 可以显示一个计算

2、公式中某些参数的值的变化对计算结果的影 响。由于它可以将所有不同的计算结果以列表方式同时显示出 来，因而便于查看、比较和分析。根据分析计算公式中的参数 的个数，模拟运算表又分为单变量模拟运算表和双变量模拟运 算表。 8.1.1 单变量模拟运算表 单变量模拟运算主要用来分析当其它因素不变时，一个参 数的变化对目标值的影响。例如，要计算一笔贷款的分期偿还 额，可以使用excel提供的财务函数之一pmt。而如果要分析 不同的利率对贷款的偿还额产生的影响，则可以使用单变量模 拟运算表。,8.1.2 双变量模拟运算表 当需要其它因素不变时，两个参数的变化对目标值的影响 时，需要使用双变量模拟运算表。例如

3、上例，如果不仅要考虑 利率的变化，还可以选择贷款年限，这时需要分析不同的利率 和不同的贷款期限对贷款的偿还额的影响，这时需要使用双变 量模拟运算表。 双变量模拟运算表的操作步骤与单变量模拟运算表类似： 选择某个单元格区域作为模拟运算表存放区域，在该区域的最 左列输入假设的利率变化范围数据；在该区域的第一行输入可 能的贷款年限数据。 在模拟运算表区域的左上角单元格输入计 算月偿还额的计算公式。选定整个模拟运算表区域。,8.1.3 敏感分析 利用模拟运算表还可以进一步进行其他方面的敏感分析。 下面通过购买某个险种的保险时如何选择缴款方式，来说明有 关敏感分析的操作。 设准备购买某保险10万元，可以

4、有两种缴款方式供选择： 一种是趸交，即一次付清105,490元；另一种是分30年付款， 每年付6,350元。如果单从付款额来说，后一种付款方式累计 缴款190,500元，大大多于趸交的款额。但是对于这种长期投 资问题，还必须要考虑利息的收益和利率变动的影响。为此， 可以利用excel提供的现值函数pv或未来值函数fv，计算和比 较在特定利率情况下两者的收益。再进一步应用模拟运算表分 析利率变动的影响。,在模拟运算表的基础上，还可以进一步进行敏感分析。可 以通过改变除行变量和列变量以外其他参数的值，分析其对模 拟运算表计算结果的影响；而改变函数名称，则可以方便地得 到其他相关指标的的模拟运算表。

5、,8.2 方案分析 模拟运算表主要用来考查一个或两个决策变量的变动对于 分析结果的影响，但对于一些更复杂的问题，常常需要考查更 多的因素。例如为了达到公司的预算目标，可以从多种途径入 手。可以通过增加广告促销，可以提高价格增收，可以降低包 装费、材料费，可以减少非生产开支等等。利用excel提供的 方案管理器，可以模拟为达到目标而选择的不同方式。对于每 个变量改变的结果都被称之为一个方案，根据多个方案的对比 分析，可以考查不同方案的优劣，从中选择最合适公司目标的 方案。,8.2.1 创建方案 创建方案是方案分析的关键，应根据实际问题的需要和可 行性来创建一组方案。在创建方案之前，为了使创建的方

6、案能 够明确地显示有关变量，以及为了将来进行方案总结时便于阅 读方案总结报告，需要先给有关变量所在的单元格命名。 在存放有关变量数据的单元格右侧单元格中输入相应指标 的名称。选定要命名的单元格区域和单元格名称区域。 单击插入菜单中的名称命令，然后单击指定子命令。在名 称在框中选定最右列复选框，单击确定按钮。 单击工具菜单中的方案命令，将弹出方案管理器对话框。 由于现在还没有任何方案，所以方案管理器对话框中间显示“未 定义方案”的信息。,8.2.2 浏览、编辑方案 方案创建好以后，可以根据需要查看每个方案对利润总额 数据的影响。其具体操作步骤是： 在方案管理器对话框的方案列表中，选定要查看的方案

7、。 单击方案管理器对话框的显示按钮，再单击确定。这时工作表 中将显示该模拟方案的计算结果。 如果需要修改某个方案，其具体操作步骤是：在方案管理 器对话框的方案列表中，选定要修改的方案。单击方案管理器 对话框的编辑按钮。这时会弹出与添加方案一样的编辑方案对 话框。可以根据需要修改方案名称，改变可变单元格以及重新 输入可变单元格的变量值。,8.2.3 方案总结 上述浏览方式只能一个方案一个方案地查看，如果将所有 方案汇总到一个工作表中，然后再对不同方案的影响比较分 析，这对于帮助决策人员综合考查各种方案效果更好。excel 的方案工具可以根据需要对多个方案创建方案总结，以便决策 人员做出更明智的决

8、策。具体操作步骤如下： 单击工具菜单中的方案命令，将弹出方案管理器对话框。 单击方案管理器对话框中的总结按钮，将弹出方案总结对话 框，根据需要在方案总结对话框中选择适当的结果类型，一般 情况下可选择方案总结，如果需要对报告进一步分析，可选方 案数据透视表。在结果单元格框中指定利润总额所在的单元 格。单击确定按钮。,在方案总结中，“当前值”列显示的是在建立方案汇总时， 可变单元格原来的数值。每组方案的可变单元格均以灰色底纹 突出显示。根据各方案的模拟数据计算出的目标值也同时显 示在总结中（单元格区域d13:g13），便于管理人员比较分 析。比较三个方案的结果单元格“利润总额”的数值，可以看出 “

9、降低成本”方案效果最好，“减少费用”方案次之，“增加收入”方 案对目标值的影响最小。,8.3 目标搜索 “what-if”分析方法主要采用模拟计算的方法解决不同因素 或不同方案对目标的影响。这对于计划人员、决策人员都是常 用的工具。但是对于生产的组织和实施人员来说，经常遇到的 是相反的问题。例如，根据上级有关部门制定的某个目标，分 析要实现该目标，需要实现的具体指标，再逐一落实。当然也 可以根据每个具体指标，进一步分析要达到的更详细的指标。 在进行这样的分析时，往往由于计算方法较为复杂或是许多因 素交织在一起而很难进行。这可以利用excel的目标搜索技术 实现。,8.3.1 单变量求解 仍以上

10、一节的蒙龙公司损益表为例。假设该公司下个月的 利润总额指标定为145000，要考查当其他条件基本保持不变的 情况下，销售收入需要增加到多少。由于利润总额与销售收入 的关系不是简单的同量增加的关系（即不是销售收入增加1 元，利润总额也增加1元），也不是简单的同比例增长关系 （即不是销售收入增加1元，利润总额按70%比例增加0.7 元），而可能要涉及到其它多方面因素。比如说，销售收入增 加，可能需要增加销售人员的奖金、差旅费、运输费和装卸费 等开支等等。所以手工计算是比较复杂的，需要根据工作表中 的计算公式一项一项的倒推计算。而excel提供的目标搜索技 术，即单变量求解命令可以方便计算出来。,注

11、意， 使用单变量求解命令的关键是在工作表上建立正确的数学模 型，即通过有关的公式和函数描述清楚相应数据之间的关系。 例如该表中产品销售利润、营业利润和利润总额分别是按下述 公式计算的： 产品销售利润产品销售收入产品销售成本产品销售费用产品销售税金 营业利润产品销售利润其它业务利润管理费用财务费用 利润总额营业利润投资收益营业外收入营业外支出 而产品销售成本、产品销售费用等数据也是根据产品销售收入 按一定公式计算的。这是保证分析结果有效和正确的前提。,8.3.2 图上求解 目标搜索技术还可以利用图形直观地进行。例如上例，如 果要分析使利润总额增加到146000元，相应的销售收入需增加 到多少元，

12、可按下述步骤操作： 选定销售收入和利润总额等数据所在的单元格，这里选定 销售收入、销售利润、营业利润和利润总额等数据。单击图表 向导按钮，按提示制作一个柱形图。为了便于查看，在步骤之 2中，选定系列选项卡，在分类（x）轴标志选项中选定销售收 入、销售利润、营业利润和利润总额标志所在的单元格。,8.3.3 其他应用 利用目标搜索技术可以求解许多类似的问题。例如，利用 pmt函数，可以根据贷款额、利率和周期方便地计算出每期的 付款额。但是反过来，已知某企业近5年每月偿还贷款的能力 为100000元，要计算其可以承受的贷款额度，就需要掌握更多 的函数和计算方法。但是使用目标搜索技术则可以直接求解。

13、再如在宏观经济分析中要求控制投资规模，在固定资产投资总 额降低5%的目标下，相应的自筹投资应控制在多少。这可 能需要涉及到诸多因素，例如预算内投资、贷款投资、利用外 资投资、国民生产总值、物价指数等等，而且这些因素之间还 存在着相互制约的关系，用手工计算是相当复杂的。利用目标 搜索技术，只要在工作表中建立了上述方程也可以直接求解。,上述这些问题归纳起来都是数学上的求解反函数问题，即 对已有的函数 ，给定y 的值，反过来求解x 。一般情 况下可以按照 与 的依赖关系，构造一个反函数 。但是当变量 之间的依赖关系较为复杂，特别是对于非线性函数，构造反函 数的工作也是较为复杂繁琐的。而利用目标搜索技术，则可以 利用直接函数方便地完成反函数的计算。 利用目标搜索技术还可以直接求各种方程，特别是非