使用场景管理器进行假设分析

在灵敏度分析中有两个以上变量的情况下,场景管理器非常有用.场景管理器为所考虑的变量的每组输入值创建场景.场景可以帮助您探索一组可能的结果,支持以下 :

  • 改变多达32个输入集.

  • 合并来自多个不同工作表或工作簿的方案.

如果要分析超过32个输入集,并且值只表示一个或两个变量,可以使用数据表.虽然它仅限于一个或两个变量,但数据表可以包含任意数量的不同输入值.请参阅本教程中的带有数据表的假设分析.

场景

场景是Excel保存的一组值,可以在工作表上自动替换.您可以在工作表上创建和保存不同的值组作为方案,然后在这些方案之间切换以查看不同的结果.

例如,您可以有几种不同的预算方案来比较各种可能的收入水平和费用.您还可以使用不同来源的不同贷款方案来比较各种可能的利率和贷款期限.

如果您要在方案中使用的信息来自不同的来源,您可以收集单独工作簿中的信息,然后将不同工作簿中的方案合并为一个.

在获得所需的所有方案后,可以创建方案摘要报告 :

  • 包含所有方案的信息.

  • 这可以让您并排比较各种情景.

场景管理器

场景管理器是Excel中的假设分析工具之一.

要使用Scenario Manager创建分析报告,您必须遵循以下步骤 :

步骤1 : 定义一组初始值并识别您想要改变的输入单元格,称为更改单元格.

步骤2 : 创建每个方案,命名方案并为该方案的每个更改输入单元格输入值.

步骤3 : 选择输出单元格,称为要跟踪的结果单元格.这些单元格包含初始值集合中的公式.公式使用更改的输入单元格.

方案管理器创建一个报告,其中包含每个方案的输入值和输出值.

初始值场景

在创建多个不同场景之前,您需要定义场景所基于的一组初始值.

设置场景的初始值为 :

  • 定义包含输入值的单元格.

  • 正确命名输入单元格.

  • 标识具有常量值的输入单元格.

  • 指定常量输入的值.

  • 识别具有更改值的输入单元格.

  • 指定更改输入的初始值.

  • 定义单元格包含结果.结果单元格包含公式.

  • 正确命名结果单元格.

  • 将公式放在结果单元格中.

考虑上一个贷款示例.现在,继续如下 :

  • 为贷款金额定义一个单元格.

    • 此输入值对于所有方案都是常量.

    • 将单元命名为Loan_Amount.

    • 将值指定为5,000,000.

  • 定义利率,付款数量和类型(月初或月末的付款)的单元格.

    • 这些输入值将在各种方案中发生变化.

    • 将单元格命名为Interest_Rate,NPER和Type.

    • 将这些单元格中的分析初始值分别指定为12%,360和0.

  • 定义EMI的单元格.

    • 这是结果值.

    • 将单元格命名为EMI.

    • 将公式放入这个单元格为 :

      = PMT(Interest_Rate/12,NPER,Loan_Amount,0,Type)

您的工作表如下所示 :

定义单元格

正如您所看到的那样,输入单元格和结果单元格位于列C中,其名称在D列中给出.

正在创建场景

在设置场景的初始值之后,您可以使用场景管理器创建场景,如下所示;

  • 单击功能区上的DATA选项卡.

  • 单击数据工具组中的假设分析.

  • 选择方案下拉列表中的经理.

创建场景

出现"方案管理器"对话框.您可以观察到它包含消息 :

"没有定义的方案.选择添加到."

添加场景

你需要在方案管理器中为每组更改值创建方案.最好使用初始值定义第一个场景,因为它允许您在显示不同场景时随时切换回初始值.

使用初始值创建第一个场景如下 :

  • 单击"方案管理器"对话框中的添加按钮.

出现添加场景对话框.

  • 在Scenario Name下,键入Scenario 1.

  • 在更改单元格下,按住Ctrl键输入单元格的引用,即C3,C4和C5.

对话框的名称更改为"编辑场景".

  • 编辑注释为 - 初始值框中的文本.

  • 选择"防止保护下的更改"选项,然后单击"确定".

Prevent Chang es

出现场景值对话框.您定义的初始值显示在每个更改单元格框中.

场景值

场景1 创建初始值.

在更改单元格中创建三个具有不同值的场景,如下所示;

  • 单击"场景值"对话框中的添加按钮.

出现"添加场景"对话框.请注意,C3,C4,C5出现在更改单元格框中.

  • 在"方案名称"框中,键入方案2.

  • 评论中的文字编辑为 - 不同的利率.

  • 选择阻止保护下的更改,然后单击确定.

选择阻止更改

将出现方案值对话框.初始值出现在更改的单元格中.将 Interest_Rate 的值更改为 0.13 ,然后点击添加.

方案值对话框

将出现添加方案对话框.请注意,C3,C4,C5出现在更改单元格下的框中.

  • 在"方案名称"框中,键入方案3 .

  • 评论框中的文字编辑为 - 不同的编号.付款.

  • 选择防止保护下的更改并单击确定.

场景名称框

将出现"场景值"对话框.初始值出现在更改的单元格中.将NPER的值更改为300并单击添加.

更改值

将出现添加方案对话框.请注意,C3,C4,C5出现在更改单元格框中.

  • 在"方案名称"框中,键入方案4.

  • 评论框中的文字编辑为 - 不同类型的付款.

  • 选择"防止保护下的更改",然后单击"确定".

添加场景对话框

将出现场景值对话框.初始值出现在更改的单元格中.将Type的值更改为1.单击确定,因为已添加了要添加的所有方案.

更改的值

将出现方案管理器对话框.在场景下的框中,您将找到已创建的所有场景的名称.

  • 单击场景1.如您所示意识到,方案1包含初始值.

  • Scenario Manager
  • 现在,单击摘要.将出现"方案摘要"对话框.

方案摘要报告

Excel提供两种类型的方案摘要报告 :

  • 场景摘要.

  • 场景数据透视表报告.

在"方案摘要"对话框中,您可以找到这两种报告类型.

在"报告类型"下选择"方案摘要".

选择场景摘要

场景摘要

结果单元格框,选择单元格 C6 (这里,我们已经设置了 PMT 功能).单击"确定".

"方案摘要"报告显示在新工作表中.工作表被命名为Scenario Summary.

Scenario Summary

你可以在Scenario Summary报告中观察以下内容 :

  • 更改单元格 : 列入用作更改细胞的所有细胞.正如您已命名单元格,Interest_Rate,NPER和Type,这些似乎使报告有意义.否则,将仅列出单元格引用.

  • 结果单元格 : 显示指定的结果单元格,即EMI.

  • 当前值 : 它是第一列,并在创建摘要报告之前登记在"方案管理器"对话框中选择的该方案的值.

  • 对于所有方案,您已创建,更改的单元格将以灰色突出显示.

  • 在EMI行中,将显示每个方案的结果值.

您可以通过显示在创建方案时添加的注释来使报告更有意义.

  • 点击 + 包含方案名称的行左侧的按钮.方案的注释显示在方案名称下面的行中.

单击加号按钮

来自不同来源的方案

假设您从三个不同的来源获取方案,并且您需要准备方案摘要报告在主工作簿中.您可以通过将不同工作簿中的方案合并到主工作簿中来完成此操作.按照下面给出的步骤 : 去;

  • 假设这些方案位于工作簿,Bank1_Scenarios,Bank2_Scenarios和Bank3_Scenarios中.打开三个工作簿.

  • 打开主工作簿,其中包含初始值.

  • 点击DATA>假设分析>主工作簿中的场景管理器.

场景管理器对话框出现.

来自不同来源的场景

正如您所看到的,没有任何情况,因为您还没有添加了任何.单击合并.

将出现"合并方案"对话框.

合并场景

如您所见,在Merge场景下,您有两个框 :

  • 预订

  • 工作表

您可以从包含的特定工作簿中选择特定工作表要添加到结果中的方案.单击 Book 的下拉箭头以查看工作簿.

注意 : 相应的工作簿应该打开以显示在此列表中.

Merge Scenarios对话框

选择图书 -   Bank1_Scenarios .

显示Bank1工作表.在对话框的底部,显示源表上找到的方案数.单击确定.

Select Book

将出现"方案管理器"对话框.合并到主工作簿的两个方案将列在方案下.

Master Workbook

单击合并按钮.将出现合并方案对话框.现在,从"预订"框的下拉列表中选择 Bank2_Scenarios .

显示Bank2工作表.在对话框的底部,显示源表上找到的方案数.单击确定.

显示的源表

场景管理器出现对话框.合并到主工作簿中的四个方案列在方案下.

Scenarios

单击合并按钮.将出现合并方案对话框.现在,从"预订"框的下拉列表中选择 Bank3_Scenarios .

显示Bank3表.在对话框的底部,将显示源表上找到的方案数.单击确定.

Scenario Found

将显示"方案管理器"对话框.合并到主工作簿中的五个方案将列在方案下.

方案合并

现在,您已拥有生成场景摘要报告的所有必需方案.

单击"摘要"按钮.将出现场景摘要对话框.

  • 选择场景摘要.

  • 在"结果单元格"框中,键入C6并单击"确定".

结果单元格框

"方案摘要"报告显示在主工作簿中的新工作表上.

新工作表摘要

显示方案

假设您正在展示您的方案,并且您希望动态地从一个方案切换到另一个方案,显示相应方案的输入值集和结果值.

  • 单击DATA>假设分析>数据工具组中的方案管理器.将出现"方案管理器"对话框.将显示方案列表.

  • 选择要显示的方案.点击显示.

Displaying Scenarios

工作表上的值将更新为所选方案的值.重新计算结果值.

Selected Scenario

场景数据透视表报告

您还可以以数据透视表的形式查看Scenario报告.

  • 单击方案管理器对话框中的"摘要"按钮.将出现"方案摘要"对话框.

  • 选择"报告类型"下的方案数据透视表.

  • 结果单元格框中的C6类型.

场景数据透视表

场景数据透视表显示在新工作表上.

工作表上的场景数据透视表报告