Excel2000应用案例之十六

4.3 目标搜索

“What-If”分析方法主要采用模拟计算的方法解决不同因素或不同方案对目标的影响。这对于计划人员、决策人员都是常用的工具。但是对于生产的组织和实施人员来说，经常遇到的是相反的问题。例如，根据上机有关部门制定的某个目标，分析要实现该目标，需要实现的具体指标，再逐一落实。当然也可以根据每个具体指标，进一步分析要达到的更详细的指标。在进行这样的分析时，往往由于计算方法较为复杂或是许多因素交织在一起而很难进行。这可以利用Excel

2000的目标搜索技术实现。

4.3.1 单变量求解

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

2000提供的目标搜索技术，即单变量求解命令可以方便计算出来。

首先将有关数据和公式输入到工作表中，如上例的图4－12所示。请注意，使用单变量求解命令的关键是在工作表上建立正确的数学模型，即通过有关的公式和函数描述清楚相应数据之间的关系。例如该表中产品销售利润、营业利润和利润总额分别是按下述公式计算的：

产品销售利润＝产品销售收入－产品销售成本－产品销售费用－产品销售税金

营业利润＝产品销售利润＋其它业务利润－管理费用－财务费用

利润总额＝营业利润＋投资收益＋营业外收入－营业外支出

而产品销售成本、产品销售费用等数据也是根据产品销售收入按一定公式计算的。这是保证分析结果有效和正确的前提。应用单变量求解命令的具体操作步骤如下：

选定目标单元格C15，单击工具菜单中的单变量求解命令。

这时弹出单变量求解对话框，如图4－20所示。

图4－20

Excel 2000自动将当前单元格的地址“C15”填入到目标单元格框中；在目标值框中输入预定的目标“145000”；在可变单元格框中输入产品销售收入所在的单元格地址“C3”，也可指定可变单元格后，直接单击该C3单元格。单击确定。

这时弹出单变量求解状态对话框，说明已找到一个解，并与所要求的解一致。

单击确定按钮，可以看到求解的结果如图4－21所示。

图4－21

从图中可以看出，在其他条件基本保持不变的情况下，要使利润总额增加到145000元，即增加3545元，其产品销售收入需增加到1441020元，即增加38320元。

4.3.2 图上求解

目标搜索技术还可以利用图形直观地进行。例如上例，如果要分析使利润总额增加到146000元，相应的销售收入需增加到多少元，可按下述步骤操作：

选定销售收入和利润总额等数据所在的单元格，这里选定销售收入、销售利润、营业利润和利润总额等数据。单击图表向导按钮，按提示制作一个柱形图。为了便于查看，在步骤之2中，选定系列选项卡，在分类（X）轴标志选项中选定销售收入、销售利润、营业利润和利润总额标志所在的单元格。

慢双击利润总额数据系列，如图4－22所示。

图4－22

将鼠标指向利润总额数据系列的上沿，并向上拖拽，直到其显示数据为所需的146000为止，注意这时在Y坐标轴相应的数据位置会出现一个横线，指示当前数据的大小。如图4－23所示。

图4－23

这时将弹出单变量求解对话框，Excel 2000自动将利润总额单元格的地址“C15”填入到目标单元格框，将“146000”填入到目标值框中。

在可变单元格框中输入销售收入所在的单元格地址“C3”，也可指定可变单元格后，直接单击C3单元格。单击确定。计算结果如图4－24所示。

图4－24

从图中可以看到销售收入需增加到1451831元，即增加49131元，才能使利润总额增加4545元，达到利润总额增加到146000元的目标。

4.3.3 其他应用

利用目标搜索技术可以求解许多类似的问题。例如，利用PMT函数，可以根据贷款额、利率和周期方便地计算出每期的付款额。但是反过来，已知某企业近5年每月偿还贷款的能力为100000元，要计算其可以承受的贷款额度，就需要掌握更多的函数和计算方法。但是使用目标搜索技术则可以直接求解。再如在宏观经济分析中要求控制投资规模，在固定资产投资总额降低5%的目标下，相应的自筹投资应控制在多少。这可能需要涉及到诸多因素，例如预算内投资、贷款投资、利用外资投资、国民生产总值、物价指数等等，而且这些因素之间还存在着相互制约的关系，用手工计算是相当复杂的。利用目标搜索技术，只要在工作表中建立了上述方程也可以直接求解。

上述这些问题归纳起来都是数学上的求解反函数问题，即对已有的函数 ，给定 的值，反过来求解 。一般情况下可以按照 与 的依赖关系，构造一个反函数

。但是当变量之间的依赖关系较为复杂，特别是对于非线性函数，构造反函数的工作也是较为复杂繁琐的。而利用目标搜索技术，则可以利用直接函数方便地完成反函数的计算。

利用目标搜索技术还可以直接求各种方程，特别是非线性方程的根。在数值分析中解任意方程通常有叠代法、割线法、半间距法等多种算法求解，但大多较为复杂。而利用Excel

2000的单变量求解命令则是求解方程的方便工具。例如要求解下述非线性方程的根：

其具体操作步骤如下：

设用A1单元格存放 的解。选定A1单元格，并将其命名为X。

选定B1单元格，并在其中输入公式：“=2*X^3-5*X^2+7*X-10”。此时因为A1单元格的值为空，故X的值按0计算，所以B1单元格的值为“-10”。

单击工具菜单中的单变量求解命令。在单变量求解对话框中，Excel 2000自动将当前单元格B1填入目标单元格框；在目标值框中输入“0”；指定可变单元格为A1。如图4－25所示。单击确定。

图4－25

这时出现单变量求解状态对话框，显示已求得一个解。这时A1单元格显示的是求得的方程的一个根，近似为2。如图4－26所示。

图4－26

通过本章内容的学习，应掌握模拟运算表、方案和单变量求解等辅助决策分析工具的应用。通过上述工具的应用使得管理决策数量化、科学化，提高管理水平。