使用方案管理器评估产品生产方案

利用上述数据表格来分析条件变动所带来的结果变化非常有用，但是仍然存在一些局限性：

仅可构造一个或两个变动单元格的情况。

数据表的设定不够直观、快捷。

一个双变量模拟运算表只能显示一个公式单元格的结果，要查看其他结果值的变化就需要建立多个模拟运算表。

在许多应用中，我们可能对一些精选组合更感兴趣，而不是随着两个变动单元格的变化所形成的、所有的、整个的变动表。

图17-53　产品生产方案

方案管理器正是可以解决这些问题的有力工具。

方案管理器（Scenario Manager）：是一种能反映用户设定的多个可变单元格的变动组合情况，同时获得其他结果数据随之发生的动态变化，并生成摘要报告，显示各种值组合对任意数量的结果单元格的影响的数据工具。其中，这些摘要报告可以是以大纲或数据透视表形式展现的。

我们以一个简化的产品生产方案评估为例来介绍方案管理器的使用。

图17-53所示的工作表中包含了三个输入单元格，即资源成本变量的小时人工成本（C2）、单位物料成本（C3）和电费（C4）。在实际应用中，为了使用的方便性，可以利用名称管理器将这三个成本变量单元格分别定义为变量“小时人工成本”“单位物料成本”和“电费”。

该公司生产三种产品，每一种产品各自需要不同的工时、不同数量的物料和电量来生产。

由此，即可获得产品成本（C10:E10），其中产品A的成本（C10）公式为“= C2*C7 + C3* C8+C4*C9”。使用名称管理器定义了成本变量之后，在编制产品A的成本公式时，Excel即会自动改写为“=（小时人工成本*C7）+（单位物料成本*C8）+（电费*C9）”。

在C10建立了产品A的成本公式并由Excel算出其结果后，拉动C10单元格的填充柄向右填充到E10单元格，则算出了另两个产品的成本。

各产品的销售价格是企业根据市场和产品情况制定的，可以直接输入。

单位利润即每单位产品的销售价格减去产品成本的差，例如C12单元格的公式为“=C11-C10”，由Excel算出其结果后，向右填充到E12。

生产数量为单位时间内某个订单的产品生产数量，可以直接输入。

利润则为单位利润乘以生产数量所得的积。例如，C14单元格的公式为“=C12*C13”，由Excel算出其结果后，向右填充到E14。总利润则为三个产品的利润之和，即“C15=SUM(C14:E14)”。

决策者在经营中所要考虑的因素当然有很多，但如果只考虑资源成本的变动，以及总利润的结果情况如何，就需要评估在各种成本要素发生变化时的总利润变动情况。当然可以利用前面介绍的模拟运算表，通过分析两两组合资源成本的变动情况而形成多张表的方式来进行评估。但是，这种方式显然既不直观，操作步骤也过于累赘。实际上，这种情况往往只需考虑几种资源成本的组合方案即可。例如，经营决策者通常将资源成本的组合方案分为三类，如表17-1所示：

表17-1　资源成本的三类方案

在分析的过程中当然可以将三类方案的成本代入上述模型中，从而获得不同的评估表。但如果需要进行动态管理并自动生成摘要报告或者数据透视表，方案管理器即是一个很好的选择。利用方案管理器的操作方法如下：

操作步骤

【Step 1】 建模。如图17-53所示，建立问题模型。其中，关键任务是区分出可变单元格和结果数据。可变单元格的选择是建模过程的核心步骤。因此，对于实际分析需求的准确把握非常重要。例如，在上述例子中就是要把握资源成本对利润的影响情况。因此，应将资源成本作为可变单元格。Excel的方案管理器允许用户建立多达32个可变单元格的分析方案，在实际工作中可以对各种可变因素进行复杂的组合。

图17-54　方案管理器(www.xing528.com)

【Step 2】 建立方案。单击“数据”选项卡—“预测”组—“模拟分析”模块下的“方案管理器”选项，弹出如图17-54左图所示的“方案管理器”对话框。

第一次打开方案管理器时，方案列表中为空。可以通过以下子步骤建立一个方案：（1）单击“添加”按钮，弹出“编辑方案”窗口；（2）在“编辑方案” 窗口中首先编辑“方案名”；（3）然后，选择或编辑“可变单元格”，如图17-54右图所示；（4）最后，单击“确定”按钮，弹出“方案变量值”录入对话窗口，如图17-55所示；（5）这时，可以看到“方案变量值”录入对话窗口中已经根据可变单元格的设置生成了对应的录入框，在录入框中录入这一方案的变量值；（6）单击“确定”按钮，返回到“方案管理器”对话框。

图17-55　“方案变量值”录入对话窗口

【Step 3】 添加方案。根据方案的数量，重复【Step 2】中设置方案的6个子步骤，直至将各个方案全部建立完成。例如，在产品生产方案评估中，即根据前面分析建立了“最佳方案”“最差方案”和“最可能方案”。

在实际工作中，对同一个问题，不同的管理人员可能会有不同的解决方案。因此，决策者可以让管理人员分别做出自己的方案，然后，通过合并的方式，将不同的方案合并到一个工作表中。Excel还提供了将多个工作表中的方案合并到当前工作簿的当前工作表中的途径，即如果当前工作簿的其他工作表或者在打开的其他工作簿中已存在有一定的方案，我们则可以通过单击“方案管理器”对话框中的“合并”按钮，将这些方案合并到当前工作表中。如图17-56所示。

图17-56　合并方案

图17-57　显示方案

【Step 4】 修改方案。在“方案管理器”对话框中选中“方案”列表框中的一个方案，单击的“编辑”按钮，Excel即会打开“编辑方案”对话框，即可按照【Step 2】中的子步骤进行方案编辑。

【Step 5】 显示方案。方案制定好后，可以在方案管理器中，双击各个方案，或者在选中方案之后单击“显示”按钮，将本方案所确定的可变单元格的值代入模型之中，从而获得最终的结果。如图17-57所示，就是在选择“最差方案”后单击“显示”按钮所得到的效果。我们可以看到，在这一方案下，各产品的利润和总利润都明显减少，产品A的“单位利润”和“利润”甚至出现了负值（使用红色的字体颜色即代表负值），由此可见企业生产管理中成本控制的重要性。

图17-58　方案摘要生成配置

【Step 6】 生成方案摘要。动态显示虽然可以获得各个方案所设定的可变单元格对模型中的结果带来的影响。但是，毕竟这些方案是单独显示的，存在一定的不便。而方案的摘要报告则可提供各个方案汇总在一起的完整对比情况。

单击“方案管理器”对话框中的“摘要”按钮，弹出如图17-58所示的“方案摘要”对话窗，在“报表类型”选项组中选择“方案摘要”选项。然后，在“结果单元格”录入选择框中录入或者选择结果单元格，Excel即会在本工作表之前自动新建一个工作表，并在其中生成如图17-59所示的方案摘要报表。在选择结果单元格时可以用半角逗号隔开不同单元格或区域。

图17-59　方案摘要报表

注意：在本例中，已经预先对单元格C14、D14、E14以及C16分别定义名称为“产品A利润”“产品B利润”“产品C利润”和“总利润”。否则，上面报表的结果单元格下显示的即为“$C$14”“$D$14”“$E$14” 和“$C$16”。

温馨提示

强烈推荐在生成方案摘要之前，先对结果单元格进行命名，即只有通过“公式”选项卡—“定义的名称”组，对模型中的结果单元格进行命名，这样在方案摘要报表中才能获得有意义的结果说明。

在“方案摘要”对话框中，如果在“报表类型”选项组中选择“方案数据透视表”选项，在选择同样的结果单元格后，也会自动新建一个工作表，并生成如图17-60所示的数据透视表。

图17-60　方案数据透视表报告