如何在您的财务模型中建立下拉式方案 - 最常用的虚拟方法

构建场景的最常用方法是使用公式和下拉框的组合。在财务模型中，您可以创建一个可能的方案及其输入的表格，并将方案名称链接到输入单元格下拉框。模型的输入链接到方案表。如果模型已经正确地建立，所有的输入流经输出端，模型的结果将随着用户从下拉框中选择不同的选项而改变。

<！ --1 - >

数据验证下拉框用于财务建模（包括情景分析）中的多个不同目的。

使用数据验证来模拟盈利情况

下载文件0801. xlsx。打开它并选择标签为8-1-start的选项卡。

按照模拟的方式，将输入排列在B列中。您可以通过简单地更改其中一个输入来执行灵敏度分析 - 例如，将单元格B3中的每位呼叫运营商的客户从40更改为45，你会看到所有的依赖号码的变化。这将是一个敏感性分析，因为你只改变一个变量。相反，在这个完整的场景分析练习中，您将一次更改多个变量，因此您需要手动更改一些数字。

<！要使用数据验证下拉框执行情景分析，请执行以下步骤：

取下载的模型，剪切并粘贴从C列到F列的描述。您可以执行此操作通过突出显示单元格C6：C8，按Ctrl + X，选择单元格F6，然后按Enter键。

1. 单元格B3到B8中的输入是驱动模型的有效范围，并保持如此。但是，他们需要变成公式，这取决于您要创建的下拉框。

   将列B中的范围复制到列C，D和E.

2. 您可以通过高亮显示B3：B8，按下Ctrl + C，选择单元格C3：E3并按下Enter来完成此操作。这些数额将是每个情况下相同，直到你改变它们。

   在第2行中，输入标题“最好情况”

3. ， “基本情况” ， 和最差情况。 设置情景分析模型。

   

   请注意，公式仍然链接到列B中的输入，如通过选择单元格C12并按F2快捷键所看到的。

   编辑每个方案下的输入。

4. 你可以放任何你认为可能的东西，但为了匹配这个例子中的数字，输入数值。现在忽略B列。
   情景分析的输入。

   

   现在您需要在顶部添加下拉框，这将推动您的场景。放置下拉框的位置并不重要，但应放在易于找到的位置，通常位于页面的顶部。在单元格E1中，输入标题方案

   。

5. 选择单元格F1，将格式更改为输入，以便用户可以看到该单元格是可编辑的。 执行此操作的最简单方法是按照下列步骤操作：
6. 单击已格式化为单元格的单元格之一（如单元格E3）。

   按下“主页”选项卡左侧的“剪贴板”部分中的“格式刷”图标。您的光标将变成画笔。

   1. 选择单元格F1粘贴格式。格式画家通常是一次性使用。选择单元格后，画笔将从光标消失。如果您希望“格式刷”变得“粘滞”并应用于多个单元格，请从“主页”选项卡中双击该图标。
   2. 现在，在单元格F1中，从数据选项卡的数据工具部分选择数据验证。数据验证对话框出现。
   3. 在设置选项卡上，将允许下拉列表更改为列表，使用鼠标选择范围= $ C $ 2：$ E $ 2，然后单击确定。

      创建数据验证下拉方案。

7. 单击现在出现在单元格F1旁边的下拉框，然后选择其中一个方案（例如，基本案例）。

   将公式应用于场景

8. B列中的单元格仍在驱动模型，并且这些需要由公式替换。但是，在添加公式之前，应该更改范围中单元格的格式，以显示它们包含公式，而不是硬编码的数字。请执行以下步骤：

   

   选择单元格B3：B8，然后从“主页”选项卡的“字体”组中选择“填充颜色”。
9. 将填充颜色更改为白色背景。

区分模型中的公式和输入单元非常重要。您需要向任何打开该范围内的单元格的用户清楚地说明该范围中的单元格包含公式，并且不应该被覆盖。

现在，您需要将列B中的硬编码值替换为将随下拉框更改而更改的公式。你可以使用一些不同的功能来做到这一点。一个HLOOKUP，一个嵌套的IF语句，一个IFS和一个SUMIF都可以做到这一点。按照以下步骤添加公式：

1. 选择单元格B3，并添加一个公式，该公式将根据单元格F1中的内容更改值。
2. 以下是不同选项下的公式：

   = HLOOKUP（$ F $ 1，$ C $ 2：$ E $ 8,2,0）

请注意，使用此解决方案，您需要更改从2到3的行索引号等等。相反，您可以在第三个字段中使用ROW函数，如下所示：= HLOOKUP（$ F $ 1，$ C $ 2：$ E $ 8，ROW（A3）-1,0）

1. = IF（$ F $ 1 = $ C $ 2，C3，IF（$ F $ 1 = $ D $ 2，D3，E3））

   = IFS（$ F $ 1 = $ C $ 2，C3，$ F $ 1 = $ D $ 2，D3，$ F $ 1 = $ E $ 2，E3）

   • = SUMIF（$ C $ 2：$ E $ 2，$ F $ 1，C3：E3）

     与往常一样，有几种不同的选择可供选择，最好的解决方案是是最简单和最容易理解的。这些函数中的任何一个都会产生完全相同的结果，但是必须更改HLOOKUP中的行索引号并不健壮，并且添加ROW可能会让用户感到困惑。嵌套的IF语句是非常棘手的构建和跟随，尽管新的IFS函数旨在使嵌套的IF函数更简单，但仍然相当笨拙。 SUMIF构建和跟踪相当简单，如果将来需要添加额外的场景，则很容易扩展。

   • 请注意，IFS是仅在Office 365和Excel 2016或更高版本中安装的新功能。如果您使用此功能，并且有人在以前版本的Excel中打开此模型，则可以查看该公式，但是她将无法对其进行编辑。
   • 将单元格B3中的公式向下复制到列中。
   • 完成的情景分析。

     通过使用普通的复制和粘贴，你将失去所有的格式。保留模型的格式非常重要，以便您一眼就能看到哪些输入是以美元值，百分比或客户编号表示的。使用粘贴公式保留格式。您可以通过将单元格复制到剪贴板上，突出显示目标范围，右键单击并选择“粘贴公式”图标来仅粘贴公式，并保持格式不变。

     现在有趣的部分！现在是在模型中测试场景功能的时候了。

2. 单击单元格F1，更改下拉框，并在不同情况下切换时观察模型输出的变化。