Excel 蒙地卡羅模擬簡介

套用到
Microsoft 365 Excel Excel 2024 Excel 2021 Excel 2019 Excel 2016

本文改編自 Wayne L. Winston 的《Microsoft Excel 資料分析與商業建模 》。

概觀

  • 誰會使用蒙地卡羅模擬？
  • 當你在儲存格中輸入 =RAND () 會發生什麼事？
  • 你要怎麼模擬離散隨機變數的值？
  • 你要如何模擬一個常態隨機變數的值？
  • 賀卡公司如何決定要製作多少張卡片？

我們希望能準確估算不確定事件的機率。 例如，新產品的現金流在淨現值 (淨現值) 的機率是多少？ 我們的投資組合風險因子是什麼？ 蒙地卡羅模擬讓我們能夠模擬存在不確定性的情境，並在電腦上演繹數千次。

注意

蒙 地卡羅模擬 這個名稱來自1930至1940年代進行的電腦模擬，用以估算原子彈引爆所需的連鎖反應成功成功的機率。 參與這項工作的物理學家非常熱衷於賭博，因此他們給模擬取代號為 蒙地卡羅（Monte Carlo）。

接下來的五章將展示如何使用 Excel 進行蒙地卡羅模擬的範例。

誰會使用蒙地卡羅模擬？

許多公司將蒙地卡羅模擬作為決策過程中的重要一環。 以下是一些例子。

  • 通用汽車、寶潔、輝瑞、Bristol-Myers Squibb 與禮來利用模擬來估算新產品的平均報酬率與風險因子。 在通用汽車，執行長會利用這些資訊來決定哪些產品上市。
  • 通用汽車會用模擬來預測企業的淨利、預測結構與採購成本，以及判斷其對利率變動和匯率波動等不同風險 (的敏感性) 。
  • 禮來利用模擬來判斷每種藥物的最佳植株產能。
  • 寶潔公司利用模擬來建模並最佳化對沖外匯風險。
  • Sears 利用模擬來決定應向供應商訂購每條產品線的數量——例如今年應訂購的 Dockers 褲子數量。
  • 石油與製藥公司利用模擬來評估「實體選項」，例如擴大、收縮或延後專案的選項價值。
  • 理財規劃師利用蒙地卡羅模擬來為客戶的退休規劃制定最佳投資策略。

當你在儲存格中輸入 =RAND () 會發生什麼事？

當你在一個儲存格中輸入公式 =RAND () ，你會得到一個同樣可能出現介於 0 和 1 之間的任何值的數字。 因此，大約有 25% 的機率，你會得到小於或等於 0.25 的數值;大約有 10% 的機率，你會得到至少 0.90 的數字，依此類推。 為了示範 RAND 函數的運作方式，請參考圖 60-1 所示的檔案 Randdemo.xlsx。

書籍圖片

注意

當你打開檔案 Randdemo.xlsx 時，你會看到圖 60-1 中顯示的隨機數字。 RAND 函數在打開工作表或輸入新資訊時，總是自動重新計算產生的數字。

首先，從 C3 格複製到 C4：C402，公式 =RAND () 。 接著你將範圍命名為 C3：C402 Data。 接著在 F 欄，你可以追蹤 F2) (400 個隨機數的平均值，並使用 COUNTIF 函數來判斷介於 0 到 0.25、0.25 到 0.50、0.50 和 0.75 以及 0.75 和 1 之間的分數。 按下 F9 鍵時，隨機數會被重新計算。 注意 400 個數字的平均值總是約 0.5，且約有 25% 的結果出現在 0.25 的間隔中。 這些結果與隨機數的定義一致。 另外要注意，RAND 在不同格子中產生的數值是獨立的。 例如，若在 C3 格子產生的隨機數很大， (例如 0.99) ，則無法告訴我們其他隨機數的值。

你要怎麼模擬離散隨機變數的值？

假設對曆法的需求受以下離散隨機變數控制：

需求 Probability
10,000 0.10
20,000 0.35
40,000 0.3
60,000 0.25

我們要怎麼讓 Excel 多次模擬或模擬這種對行事曆的需求？ 關鍵在於將 RAND 函數的每個可能值與可能的日曆需求關聯起來。 接下來的任務確保10,000個需求有10%的機率出現，依此類推。

需求 隨機分配的數字
10,000 低於0.10
20,000 大於或等於0.10且小於0.45
40,000 大於或等於0.45且小於0.75
60,000 大於或等於0.75

為了展示需求模擬，請參考下一頁圖60-2所示的檔案 Discretesim.xlsx。

書籍圖片 我們模擬的關鍵是使用隨機數從表範圍 F2：G5 (命名的 查找) 發起查詢。 大於或等於0且小於0.10的隨機數會產生10,000的需求;大於或等於0.10且小於0.45的隨機數將產生20,000的需求;大於或等於0.45且小於0.75的隨機數將產生40,000個需求;而大於或等於0.75的隨機數則會產生60,000的需求。 你透過從 C3 複製到 C4：C402 的公式 RAND () 來產生 400 個隨機數。 接著你透過從 B3 複製到 B4：B402 的公式 VLOOKUP， (C3，lookup，2) ，產生 400 次試行或迭代。 此公式確保任意小於0.10的隨機數產生10,000需求，介於0.10至0.45之間的任意隨機數產生20,000需求，依此類推。 在 F8：F11 的單元範圍內，使用 COUNTIF 函數來決定我們 400 次迭代中產生每個需求的比例。 當我們按 F9 重新計算隨機數時，模擬的機率接近我們假設的需求機率。

你要如何模擬一個常態隨機變數的值？

如果你在任何儲存格輸入公式 NORMINV (rand () ，mu，sigma) ，你會產生一個模擬的常態隨機變數值，平均值為 mu ，標準差為 sigma。 此程序如圖60-3所示的檔案 Normalsim.xlsx 所示。

書籍圖片 假設我們想模擬一個平均值為40,000、標準差為10,000的常態隨機變數，進行400次試行或迭代。 (你可以將這些值輸入到 E1 和 E2 儲存格，分別命名為 平均 值和 sigma。) 將 =RAND () 從 C4 複製到 C5：C403 會產生 400 個不同的隨機數。 從 B4 複製到 B5：B403，公式 NORMINV (C4，平均，西格瑪) 從一個平均值 40,000、標準差 10,000 的常態隨機變數產生 400 個不同的試驗值。 當我們按 F9 鍵重新計算隨機數時，平均值仍接近 40,000，標準差接近 10,000。

基本上，對於隨機數 x，公式 NORMINV (p、mu、sigma) 產生一個常態隨機變數的 第 p百分位，平均值為 mu ，標準差為 sigma。 例如， (圖60-3中C4的隨機數0.77) 在B4格子中產生約77百分位的常態隨機變數，平均值為40,000，標準差為10,000。

賀卡公司如何決定要製作多少張卡片？

在本節中，您將看到蒙地卡羅模擬如何作為決策工具。 假設情人節卡片的需求受以下離散隨機變數控制：

需求 Probability
10,000 0.10
20,000 0.35
40,000 0.3
60,000 0.25

賀卡售價為4.00美元，製作成本為1.50美元。 剩餘的卡必須以每張卡0.20美元的費用處理掉。 應該印多少張卡片？

基本上，我們會模擬每個可能的生產量 (10,000、20,000、40,000 或 60,000 次) (例如 1000 次迭代) 。 接著我們判斷哪個訂單數量在1000次迭代中產生最大平均利潤。 您可以在圖60-4所示的檔案 Valentine.xlsx 中找到此區段的資料。 你可以將 B1：B11 格子裡的範圍名稱指派給 C1：C11。 儲存區範圍 G3：H6 被分配為名稱查詢。 我們的銷售價格與成本參數輸入於 C4：C6 格中。

書籍圖片 在這個範例) C1 中，你可以輸入試生產數量 40,000 (。 接著，在 C2 格子中創造一個隨機數，公式為 =RAND () 。 如前所述，你用公式 VLOOKUP (rand、lookup、2) 來模擬 C3 格的卡片需求。 (在 VLOOKUP 公式中， rand 是分配給 C3 的格子名稱，而非 RAND 函數 )

銷售數量是我們生產量和需求中較少的單位。 在C8格中，你用公式MIN計算我們的收入， (產生需求) *unit_price。 在 C9 格中，你用 公式 produced*unit_prod_cost 計算總生產成本。

如果我們生產的卡牌數量超過需求量，剩餘的單位數等於生產減去需求;否則不會剩餘單位。 我們在 C10 格中用公式 unit_disp_cost*如果 (產生>需求，生產–需求，0) 計算處置成本。 最後，在 C11 格子中，我們將利潤計算為 營收——total_var_cost-total_disposing_cost。

我們希望有一種高效的方式，可以多次按 F9 (例如，每個生產數量按 1000) ，並計算每個產量的預期利潤。 這種情況下，雙向資料表就救了我們一命。 (詳見第15章「含資料表的敏感度分析」。) 本範例中使用的資料表如圖60-5所示。

書籍圖片 在 A16：A1015 的儲存格範圍內，輸入 1–1000 (對應我們的 1000 次試驗) 。 一個簡單的方法就是先在 A16 格子輸入 1 。 選擇儲存格，然後在編輯群組的首頁標籤中點選填充，然後選擇系列以顯示系列對話框。 在圖 60-6 所示的 系列 對話框中，輸入步進值為 1，停止值為 1000。 在 「系列入站 」區域，選擇 欄位選項， 然後點擊 確定。 數字 1 至 1000 會從 A16 格開始輸入 A 欄。

書籍圖片 接著輸入可能的生產量 (10,000、20,000、40,000、60,000) ，在 B15：E15 電池中。 我們想計算每個試驗編號 (1到1000) 及每個生產數量的利潤。 我們參考 A15) 資料表左上格 C11 () 格中計算的利潤公式， (輸入 =C11。

我們現在準備欺騙 Excel 模擬每個生產量 1000 次的需求迭代。 選擇 A15：E1014) (表格範圍，然後在資料工具群組中的資料標籤中，點選「假設分析」，然後選擇「資料表」。 要建立雙向資料表，請選擇產生量 (C1) 作為列輸入格，並選擇任意空白格 (選擇 I14) 作為欄位輸入格。 點擊確定後，Excel 會模擬每個訂單數量的 1000 個需求值。

為了理解其原理，請考慮資料表中格子範圍 C16：C1015 的值。 對於這些儲存格，Excel 會在 C1 儲存格中使用 20,000 的值。 在 C16 中，欄位輸入格值 1 被放入空白格子，C2 格子中的隨機數重新計算。 相應的利潤會記錄在 C16 格子中。 接著將欄位格子輸入值 2 放入空白格子，C2 中的隨機數再次重新計算。 相應的利潤輸入於C17格。

透過從 B13 格複製公式 AVERAGE (B16：B1015) ，我們計算出每個生產量的平均模擬利潤。 透過從 B14 格複製公式 STDEV (B16：B1015) ，我們計算出每個訂單數量模擬利潤的標準差。 每次按下 F9，每個訂單數量都會模擬 1000 次需求迭代。 生產40,000張卡片總是帶來最高的預期利潤。 因此，製作40,000張卡牌似乎是正確的決定。

風險對我們決策的影響 如果我們生產了20,000張卡片而不是40,000張卡片，預期利潤會下降約22%，但以利潤) 標準差衡量的風險 (下降了近73%。 因此，如果我們極度排斥風險，製作20,000張卡可能是正確的決定。 順帶一提，生產10,000張卡的標準差總是0，因為如果我們生產10,000張卡，我們會把所有卡都賣光，且不會有任何剩餘。

注意

在這個工作簿中， 計算 選項被設定為 「除表格外自動」。 (在公式分頁的計算群組中使用計算指令。) 此設定確保資料表不會重新計算，除非按 F9，這是個好主意，因為大型資料表若每次輸入工作表都會重新計算，會拖慢工作進度。 請注意，在這個例子中，每當你按下 F9，平均利潤就會改變。 這是因為每次按下 F9 時，會用一串不同的 1000 個隨機數字來產生每個訂單數量的需求。

平均利潤信賴區間 在這種情況下，一個自然的問題是，我們有95%的把握，真正的平均利潤會下降到哪個區間？ 此區間稱為 平均利潤的95%信賴區間。 任何模擬輸出的平均值95%信賴區間可由以下公式計算：

書籍圖片

在 J11 儲存格中，計算 95% 信賴區間下限，計算產生 40,000 個日曆時平均利潤的下限，公式為 D13–1.96*D14/SQRT (1000) 。 在 J12 格子中，你用公式 D13+1.96*D14/SQRT (1000) 計算我們 95% 信賴區間的上限。 這些計算如圖60-7所示。

書籍圖片 我們有95%的把握，當訂購40,000本月曆時，平均利潤介於56,687至62,589美元之間。

問題

  1. 一位GMC經銷商認為2005年款Envoy的需求將以平均200輛、標準差30的常態分布呈現。 他獲得使者的成本是25,000美元，而他以40,000美元賣出使者。 所有未以原價出售的使節中，有一半可售出3萬美元。 他正在考慮訂購200、220、240、260、280或300名使節。 他應該點多少？

  2. 一家小型超市正在嘗試決定每週應該訂購多少份《People》雜誌。 他們認為對 People 的需求受以下離散隨機變數所控制：

    需求 Probability
    15 0.10
    20 0.20
    25 0.30
    30 0.25
    35 0.15

  3. 超市每本《People》售價1.00美元，售價為1.95美元。 每本未售出的書可以 0.50 美元退回。 店裡應該訂購多少本《People》？

需要更多協助嗎？

你隨時可以向 Excel 技術社群 的專家詢問，或在 社群中獲得支援。