产品销售利润评估：单变量求解

在实际工作中，我们经常会遇到很多类似这样的问题：要实现3万元的利润，需要达到多少销售额？

如何分析这类问题呢？一般而言，如果我们在一个工作表中建立了销售额与利润之间的某种模型，则可以通过改变销售额等变量，来查看利润是多少。然而，上面的问题恰恰是反过来的，即如果希望获得某种结果，需要怎样的条件呢？

这样的问题还有很多，例如：能支付的月供为5300元，能购买多少房价的房子呢？即一般是在知道房价的情况下，通过改变首付和贷款期限，来查看月供多少。而现在我们需要直接按一定的首付和贷款期限，从能够承担的月供，来思考能购买的房价为多少。

这些问题与上文讨论的由因到果的模型恰好相反，这里是一种“需要某种结果，可变因素应该是怎样的”思路。当然，我们可以通过模拟运算表或者方案管理器尝试各种各样的可变因素，找到符合某一结果数据要求的接近的可变因素的值。但是，能否直接由从结果反推出可变因素的值呢？单变量求解正是为解决这样的问题设计的。

单变量求解（Goal Seek）：指在确定了单元格之间的关系后，如果想获得某一个结果数据，则需要确定在另一个被直接或间接引用的可变单元格（引用单元格）中输入的值。这里，我们以产品销售的利润评估问题为例，讲解单变量求解是如何工作的。

一个简化的产品销售成本、利润分析数据关系表格如图17-61所示，其中：

图17-61　单变量求解模型

产品平均进货价（元/套）：需要手工录入。

产品平均售价（元/套）：需要手工录入。

固定成本（元）：需要手工录入。

产品销售费用率：一般是指产品销售过程产生的费用与销售额之比。通常，随着销量的增加，费用率会降低。因此，C7单元格是一个公式“=IF(C8<=20, 35%, IF(C8<=100, 32%, IF(C8<=200, 28%, 25%)))”，表示产品销量与费用率之间的关系，如表17-2所示。

表17-2　产品销量与费用率

产品销量（套）：需要手工录入。

销售额（元）= 平均售价×销量，因此C11单元格为公式“=C5*C8”。

进货成本（元）= 进货价×销量，因此C12单元格为公式“=C4*C8”。

销售费用（元）= 销售额×销售费率，因此C13单元格为公式“=C11*C7”。(www.xing528.com)

销售成本（元）= 进货成本＋固定成本＋销售费用，因此C14单元格为公式“=C12＋C6＋C13”。

利润（元）= 销售额－销售成本，因此C15单元格为公式“= C11－C14”。现在的问题是：如需达到保本，如利润为100元，需要实现多少销量？

操作步骤

【Step 1】 建立模型。在工作表的适当位置建立输入数据与结果数据之间的关系。按照图17-61以及上述说明录入数据。在建模过程中，关键的“产品销量”数据可以录入一个理想值，如200或者100。

【Step 2】 单变量求解。单击“数据”选项卡—“预测”组—“模拟分析”模块—“单变量求解”选项，弹出如图17-62左图所示的“单变量求解”对话窗。

按上述问题的描述，“目标单元格”输入选择框可以通过单击问题模型工作表中的“利润”$C$15单元格录入（或输入目标单元格引用）；“目标值”输入选择框直接输入100；“可变单元格”输入选择框也可以通过单击问题模型工作表中的“产品销量”$C$8单元格录入。然后，单击“确定”按钮，弹出“单变量求解状态”窗口，同时开始进行迭代递归求解，生成了如图17-62右图所示的反向推导出来的结果。

图17-62　单变量求解过程与结果

最后，单击“单变量求解状态”窗口中的“确定”按钮，模型数据即变为按照新的“结果要求”（即利润为“¥100”）所产生的全套数据。注意：

单变量求解表面上是一个由可变单元格（自变量）和结果数据（因变量）相互转换关系的过程，但实际上这是Excel通过迭代递归过程完成的求解。因为并不是任何数据关系都可以反向求出解析解。例如本例中，就几乎不可能用解析式来找到“利润”对应“销售额”的公式，而“利润” 与“销量”之间的关系，看似直接，实则间接，因为“销售成本”也是“利润”的决定因素，而“销售成本”与“销量”也密切关联。Excel的有趣之处在于，单元格之间的数据关系可以是直接的，也可以是间接的。可见，单变量求解的过程必须超越简单的基于函数关系求逆向解的方法，而通过迭代的方法获得逆向解。

利用单变量求解解决逆向问题的时候，Excel并不总能找到一个值来产生你想要的结果，因为有时根本不存在解决方案。例如，模型是“目标单元格为可变单元格的平方”，却需要对“-100”进行单变量求解，这一问题就超出了迭代求解的能力。在这种情况下，“单变量求解状态”窗口可能会给出一个错误值，或者直接报错。

如果利用单变量求解不能获得正确解，可以通过下面三个方法解决：

● 调整Excel选项的“最多迭代次数” 和“最大误差”参数设置。操作方法：单击“文件”选项卡—“选项”—“公式”，可在其中增加迭代次数或增大最大误差。如图17-63所示。

● 检查变量之间的逻辑关系。例如，在本例中我们在输入数据时增加了一个“销售人员数”的变量，却没有为这个变量与“利润”建立任何关系。因此，当我们仍然以“利润”为模板单元格，又以“销售人员数”作为可变单元格来求解时，Excel会反复进行迭代求解，最后会报告“对单元格C15进行单变量求解仍不能获得满足条件的解”。只有当我们将“销售人员数”和“销售费用” 关联起来后，Excel才能获得正确解。

● 检查问题本身。例如，不可能用迭代方法找到对“-100开平方”的结果。

图17-63　Excel选项—迭代计算选项