如何在您的財務模型中構建下拉場景

構建場景最常用的方法是使用公式和下拉框的組合。在財務模型中，您創建一個可能的場景及其輸入的表格，並將場景名稱鏈接到輸入單元格下拉框。模型的輸入鏈接到場景表。如果模型已經正確構建，所有輸入都流經輸出，那麼模型的結果將隨著用戶從下拉框中選擇不同的選項而改變。

數據驗證下拉框用於金融建模中的多種不同目的，包括場景分析。

使用數據驗證來模擬盈利場景

下載文件 0801.xlsx。打開它並選擇標記為 8-1-start 的選項卡。

按照這種建模方式，輸入列在 B 列中。您可以通過更改其中一個輸入來執行敏感性分析 - 例如，將單元格 B3 中的每個呼叫接線員的客戶從 40 更改為 45，您將查看所有相關數字的變化。這將是一種敏感性分析，因為您只更改了一個變量。相反，您將在這個完整的場景分析練習中一次更改多個變量，因此您需要做的不僅僅是手動調整幾個數字。

要使用數據驗證下拉框執行場景分析，請執行以下步驟：

獲取下載的模型並將描述從 C 列剪切並粘貼到 F 列。您可以通過突出顯示單元格 C6:C8、按 Ctrl+X、選擇單元格 F6 並按 Enter 來完成此操作。

單元格 B3 到 B8 中的輸入是驅動模型的活動範圍，並將保持如此。但是，它們需要成為根據您將創建的下拉框而變化的公式。

將 B 列中的範圍複製到 C、D 和 E 列。

您可以通過突出顯示 B3:B8，按 Ctrl+C，選擇單元格 C3:E3，然後按 Enter 來完成此操作。這些金額對於每個場景都是相同的，直到您更改它們。

在第2行中輸入標題最佳案例，基本案例，和最壞的情況。

建立場景分析模型。

請注意，公式仍然鏈接到 B 列中的輸入，正如您可以通過選擇單元格 C12 並按 F2 快捷鍵看到的。

編輯每個場景下的輸入。

您可以輸入您認為可能的任何內容，但為了使數字與本示例中的數字相匹配，請輸入值。暫時忽略 B 列。

情景分析的輸入。

現在您需要在頂部添加下拉框，這將驅動您的場景。下拉框的確切位置並不重要，但它應該位於易於找到的位置，通常位於頁面頂部。

在單元格 E1 中，輸入標題 Scenario 。

選擇單元格 F1，將格式更改為 input，以便用戶可以看到該單元格是可編輯的。

最簡單的方法是按照以下步驟操作：

單擊已格式化為輸入的單元格之一，例如單元格 E3。

按“主頁”選項卡左側“剪貼板”部分中的“格式刷”圖標。您的光標將變為畫筆。

選擇單元格 F1 以粘貼格式。

格式刷通常是一次性使用的。選擇單元格後，畫筆將從光標處消失。如果您希望格式刷變得“粘性”並應用於多個單元格，請在從“主頁”選項卡中選擇該圖標時雙擊該圖標。

現在，在單元格 F1 中，從數據選項卡的數據工具部分選擇數據驗證。

出現數據驗證對話框。

在設置選項卡上，將允許下拉列表更改為列表，使用鼠標選擇範圍 =$C$2:$E$2，然後單擊確定。

創建數據驗證下拉方案。

單擊現在出現在單元格 F1 旁邊的下拉框，然後選擇一種方案（例如，基本案例）。

將公式應用於場景

B 列中的單元格仍在驅動模型，這些單元格需要用公式替換。但是，在添加公式之前，您應該更改區域中單元格的格式以顯示它們包含公式，而不是硬編碼的數字。按著這些次序：

選擇單元格 B3:B8，然後從主頁選項卡上的字體組中選擇填充顏色。

將填充顏色更改為白色背景。

區分模型中的公式和輸入單元格非常重要。您需要向打開模型的任何用戶明確說明，此範圍內的單元格包含公式，不應被覆蓋。

現在，您需要將 B 列中的硬編碼值替換為隨著下拉框更改而更改的公式。您可以使用許多不同的功能來做到這一點；一個 HLOOKUP、一個嵌套的 IF 語句、一個 IFS 和一個 SUMIF 都可以解決問題。按照以下步驟添加公式：

選擇單元格 B3，然後添加一個公式，該公式將根據單元格 F1 中的內容更改值。

以下是不同選項下的公式：

=HLOOKUP($F$1,$C$2:$E$8,2,0)
請注意，使用此解決方案，您需要在向下複製公式時將行索引號從 2 更改為 3，依此類推。相反，您可以在第三個字段中使用 ROW 函數，如下所示： =HLOOKUP($F$1,$C$2:$E$8,ROW(A3)-1,0)
=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 中的公式複製到列中。