Excel 中的靈敏度分析（樣本數據表）

金融領域的流程總是相互關聯的——一個因素依賴於另一個因素並隨之變化。 跟踪這些變化並了解未來的預期，可能使用 Excel 函數和電子表格方法。

使用數據表獲取多個結果

數據表功能是假設分析的要素——通常通過 Microsoft Excel 完成。 這是敏感性分析的第二個名稱。

概述

數據表是一種單元格範圍，可以通過更改某些單元格中的值來解決問題。 它是在需要跟踪公式組件的更改並根據這些更改接收結果更新時創建的。 讓我們了解如何在研究中使用數據表，以及它們是什麼類型。

數據表基礎知識

有兩種類型的數據表，它們的組件數量不同。 您需要編譯一個表格，重點是您需要檢查的值的數量 uXNUMXbuXNUMXb。

當一個或多個表達式中只有一個變量可以改變其結果時，統計學家使用單個變量表。 例如，它經常與 PMT 功能結合使用。 該公式旨在計算定期付款的金額，並考慮到協議中規定的利率。 在這樣的計算中，變量寫在一列中，計算結果寫在另一列中。 帶有 1 個變量的銘牌示例：

Excel 中的靈敏度分析（樣本數據表）
1

接下來，考慮具有 2 個變量的板。 它們用於兩個因素影響任何指標變化的情況。 這兩個變量可能最終出現在與貸款相關的另一個表中，該表可用於確定最佳還款期和每月還款金額。 在此計算中，您還需要使用 PMT 功能。 具有 2 個變量的表的示例：

Excel 中的靈敏度分析（樣本數據表）
2

創建具有一個變量的數據表

考慮使用只有 100 本書庫存的小書店示例的分析方法。 其中一些可以賣得更貴（50美元），其餘的將減少買家的成本（20美元）。 計算出售所有商品的總收入——所有者決定以高價出售60%的書籍。 你需要弄清楚如果你提高大量商品的價格——70%，等等，收入將如何增加。

請注意！ 總收入必須使用公式計算，否則無法編制數據表。

  1. 選擇一個遠離工作表邊緣的空閒單元格並在其中寫入公式： =總收入的單元格。 例如，如果在單元格 C14 中寫入收入（指示隨機指定），您需要這樣寫： =S14。
  2. 我們在該單元格左側的列中寫下商品數量的百分比——而不是在其下方，這非常重要。
  3. 我們選擇百分比列和總收入鏈接所在的單元格範圍。
Excel 中的靈敏度分析（樣本數據表）
3
  1. 我們在“數據”選項卡上找到“假設分析”項並單擊它——在打開的菜單中，選擇“數據表”選項。
Excel 中的靈敏度分析（樣本數據表）
4
  1. 將打開一個小窗口，您需要在“按...中的行替換值”列中指定一個單元格，其中包含最初以高價出售的書籍的百分比。 完成此步驟是為了重新計算總收入，同時考慮到增加的百分比。
Excel 中的靈敏度分析（樣本數據表）
5

在輸入數據用於編譯表格的窗口中單擊“確定”按鈕後，計算結果將顯示在行中。

將公式添加到單個變量數據表

從幫助計算只有一個變量的動作的表格中，您可以通過添加額外的公式來製作複雜的分析工具。 它必須在已經存在的公式旁邊輸入——例如，如果表格是面向行的，我們在現有公式右側的單元格中輸入表達式。 設置列方向後，我們將新公式寫在舊公式下面。 接下來，按照算法：

  1. 再次選擇單元格區域，但現在它應該包含新公式。
  2. 打開“what if”分析菜單並選擇“Datasheet”。
  3. 根據板的方向，我們將新公式添加到行或列中的相應字段中。

創建包含兩個變量的數據表

此類表格的開頭略有不同——您需要在百分比值上方放置一個指向總收入的鏈接。 接下來，我們執行以下步驟：

  1. 將價格選項寫在與收入鏈接的一行中——每個價格對應一個單元格。
  2. 選擇一系列單元格。
Excel 中的靈敏度分析（樣本數據表）
6
  1. 通過工具欄上的“數據”選項卡打開數據表窗口，就像編譯帶有一個變量的表一樣。
  2. 在“Substitute values by columns in ...”列中替換一個初始價格較高的單元格。
  3. 在“Substitute values by rows in ...”列中添加一個包含昂貴書籍銷售初始百分比的單元格，然後單擊“OK”。

結果，整個表格充滿了不同商品銷售條件下可能的收入金額。

Excel 中的靈敏度分析（樣本數據表）
7

加快包含數據表的工作表的計算

如果您需要在不觸發重新計算整個工作簿的數據表中進行快速計算，您可以採取一些措施來加快該過程。

  1. 打開選項窗口，在右側菜單中選擇“公式”項。
  2. 在“工作簿中的計算”部分中選擇“自動，數據表除外”項。
Excel 中的靈敏度分析（樣本數據表）
8
  1. 讓我們手動重新計算表中的結果。 為此，請選擇公式並按 F 鍵。

執行敏感性分析的其他工具

該程序中還有其他工具可幫助您執行敏感性分析。 它們將一些原本必須手動完成的操作自動化。

  1. “參數選擇”功能適用於已知所需結果的情況，並且您需要知道變量的輸入值才能獲得這樣的結果.
  2. “尋找解決方案”是解決問題的插件。 有必要設置限制並指向它們，然後系統會找到答案。 解決方案是通過更改值來確定的。
  3. 可以使用場景管理器進行敏感性分析。 此工具位於數據選項卡下的假設分析菜單中。 它替換了多個單元格中的值——數量可以達到 32。調度程序比較這些值，以便用戶不必手動更改它們。 使用腳本管理器的示例：
Excel 中的靈敏度分析（樣本數據表）
9

Excel中某投資項目的敏感性分析

假設分析在需要預測的情況下特別有用，例如投資。 分析師使用這種方法來了解公司股票的價值將如何因某些因素的變化而變化。

投資敏感性分析法

在分析“假設”時，使用枚舉——手動或自動。 取值範圍已知，一一代入公式。 結果是一組值。 從它們中選擇適當的數字。 讓我們考慮在金融領域對其進行敏感性分析的四個指標：

  1. 淨現值——通過從收入金額中減去投資金額計算得出。
  2. 內部收益率/利潤——表示一年內需要從投資中獲得多少利潤。
  3. 回收率是所有利潤與初始投資的比率。
  4. 貼現利潤指數——表明投資的有效性。

公式

嵌入靈敏度可以使用以下公式計算： 以 % 為單位的輸出參數變化 / 以 % 為單位的輸入參數變化。

輸出和輸入參數可以是前面描述的值。

  1. 您需要知道標準條件下的結果。
  2. 我們替換其中一個變量並監控結果的變化。
  3. 我們計算了兩個參數相對於既定條件的百分比變化。
  4. 我們將獲得的百分比插入公式並確定靈敏度。

Excel中投資項目敏感性分析示例

為了更好地理解分析方法，需要一個示例。 讓我們用以下已知數據分析項目：

Excel 中的靈敏度分析（樣本數據表）
10
  1. 填表分析一下項目就可以了。
Excel 中的靈敏度分析（樣本數據表）
11
  1. 我們使用 OFFSET 函數計算現金流。 在初始階段，流量等於投資。 接下來，我們應用公式： =IF(OFFSET(Number,1;)=2;SUM(流入 1:流出 1);SUM(流入 1:流出 1)+$B$ 5）

    公式中的單元格名稱可能會有所不同，具體取決於表格的佈局。 最後，添加來自初始數據的值——殘值。
Excel 中的靈敏度分析（樣本數據表）
12
  1. 我們確定項目將獲得回報的期限。 對於初始階段，我們使用以下公式： =總結（G7：G17;»<0″)。 單元格範圍是現金流列。 對於進一步的時期，我們應用這個公式： =初始週期+IF（第一個e.stream>0；第一個e.stream；0）。 該項目在 4 年內處於盈虧平衡點。
Excel 中的靈敏度分析（樣本數據表）
13
  1. 我們為項目獲得回報的那些時期的數量創建一個列。
Excel 中的靈敏度分析（樣本數據表）
14
  1. 我們計算投資回報率。 有必要用一個特定時期的利潤除以初始投資來表示。
Excel 中的靈敏度分析（樣本數據表）
15
  1. 我們使用以下公式確定折扣因子： =1/(1+光盤.%) ^號碼.
Excel 中的靈敏度分析（樣本數據表）
16
  1. 我們使用乘法計算現值——現金流乘以貼現因子。
Excel 中的靈敏度分析（樣本數據表）
17
  1. 讓我們計算 PI（盈利指數）。 一段時間內的現值除以項目開始時的投資。
Excel 中的靈敏度分析（樣本數據表）
18
  1. 讓我們使用 IRR 函數定義內部收益率： =IRR（現金流範圍）。

使用數據表進行投資敏感性分析

對於投資領域的項目分析，其他方法比數據表更適合。 許多用戶在編譯公式時會感到困惑。 要找出一個因素對其他因素變化的依賴性，您需要選擇正確的單元格來輸入計算和讀取數據。

Excel 中的因子和離散度分析以及計算自動化

敏感性分析的另一種類型是因子分析和方差分析。 第一種類型定義了數字之間的關係，第二種揭示了一個變量對其他變量的依賴性。

Excel 中的方差分析

這種分析的目的是將價值的可變性分為三個部分：

  1. 由於其他值的影響而導致的可變性。
  2. 由於影響它的值的關係而發生的變化。
  3. 隨機變化。

讓我們通過 Excel 插件“數據分析”進行方差分析。 如果沒有啟用，可以在設置中啟用。

初始表必須遵循兩個規則：每個值有一列，其中的數據按升序或降序排列。 有必要檢查教育水平對沖突行為的影響。

Excel 中的靈敏度分析（樣本數據表）
19
  1. 在數據選項卡中找到數據分析工具並打開其窗口。 在列表中，您需要選擇單向方差分析。
Excel 中的靈敏度分析（樣本數據表）
20
  1. 填寫對話框的行。 輸入間隔是所有單元格，不包括標題和數字。 按列分組。 我們在新工作表上顯示結果。
Excel 中的靈敏度分析（樣本數據表）
21

由於黃色單元格中的值大於 XNUMX，因此可以認為該假設不正確——教育與衝突行為之間沒有關係。

Excel中的因子分析：一個例子

讓我們分析一下銷售領域的數據關係——需要識別流行和冷門的產品。 初始信息：

Excel 中的靈敏度分析（樣本數據表）
22
  1. 我們需要找出第二個月哪些商品的需求增長最多。 我們正在編制一個新表來確定需求的增長和下降。 使用以下公式計算增長： =IF((需求2-需求1)>0；需求2-需求1；0)。 減少公式： =IF（增長=0；需求1-需求2；0）。
Excel 中的靈敏度分析（樣本數據表）
23
  1. 計算商品需求的增長百分比： =IF（增長/結果 2 =0；減少/結果 2；增長/結果 2）。
Excel 中的靈敏度分析（樣本數據表）
24
  1. 為了清楚起見，讓我們製作一個圖表——選擇一系列單元格並通過“插入”選項卡創建一個直方圖。 在設置中，您需要刪除填充，這可以通過格式化數據系列工具來完成。
Excel 中的靈敏度分析（樣本數據表）
25

Excel中方差的雙向分析

對幾個變量進行方差分析。 舉個例子：你需要找出男人和女人對不同音量的聲音的反應有多快。

Excel 中的靈敏度分析（樣本數據表）
26
  1. 我們打開“數據分析”，在列表中你需要找到一個沒有重複的雙向方差分析。
  2. 輸入間隔 - 包含數據的單元格（沒有標題）。 我們在新工作表上顯示結果，然後單擊“確定”。
Excel 中的靈敏度分析（樣本數據表）
27

F值大於F-critical，這意味著地板會影響對聲音的反應速度。

Excel 中的靈敏度分析（樣本數據表）
28

結論

本文詳細討論了Excel電子表格中的敏感性分析，以便每個用戶都能了解其應用方法。

發表評論