Excel 中的 Monte Carlo 模擬簡介

這篇文章是從Microsoft Excel 資料分析和商業模型(Wayne Winston)改編而來。

  • 誰使用 Monte Carlo 模擬?

  • 當您在儲存格中輸入= RAND ()時,會發生什麼情況?

  • 如何模擬離散隨機變數的值?

  • 如何模擬標準隨機變數的值?

  • 問候卡公司如何決定要產生多少張卡片?

我們想要準確估計不確定事件的可能性。 例如,新產品的現金流程將是積極的淨現值(NPV)的可能性為何? 我們投資陣容的風險因素為何? Monte Carlo 模擬讓我們能夠針對表現不確定性的情況建模,然後在電腦上進行上千次播放。

附註:  Name Monte Carlo 模擬是來自1930s 和1940s 期間執行的電腦模擬,以估計 atom 炸彈所需的鏈反應必須在成功運作的可能性。 此工作所涉及的 physicists 是很大的賭博風扇,因此它們將模擬程式碼名稱Monte Carlo

在接下來的五個章節中,您將會看到一些範例,瞭解如何使用 Excel 來執行 Monte Carlo 模擬。

許多公司都使用 Monte Carlo 模擬做為其決策流程的重要部分。 以下是一些範例。

  • 一般汽車、Proctor 和 Gamble、Pfizer、Bristol-Myers Squibb 和黎國 Lilly 使用模擬來估計新產品的平均回報和風險因素。 在 GM,CEO 會使用此資訊來判斷哪些產品上市。

  • GM 會針對活動(例如,利率變更與匯率波動)等活動進行類比,例如,預測公司的淨收益、預測結構及購買成本,以及判斷其對不同類型風險的敏感程度。

  • Lilly 使用模擬來判斷每個藥品的最佳植物容量。

  • Proctor 和 Gamble 使用模擬來模型和最佳的籬外國 exchange 風險。

  • Sears 會使用 [模擬] 來判斷每個產品行的單位數量應從供應商訂購,例如,此年應訂購的 Dockers trousers 數。

  • 石油與藥品公司使用模擬來價值「實際選項」,例如,將專案擴大、合同制或延期的選項值。

  • 財務規劃人員使用 Monte Carlo 模擬來判斷用戶端退休的最佳投資策略。

當您在儲存格中輸入公式= 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資料命名。 然後,在 F 欄中,您可以追蹤400亂數字(儲存格 F2)的平均值,並使用 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),它會告知我們沒有產生其他亂數字的值。

假設行事曆的需求是由下列離散隨機變數所管轄:

[可能性]

10,000

0.10

20,000

0.35

40000

0.3

60,000

0.25

我們如何讓 Excel 為您的行事曆進行多次播放,或模仿這種需求? 秘訣是將 RAND 函數的每個可能值與可能的行事曆需求建立關聯。 下列作業可確保10000的需求將會發生10% 的時間,依此類推。

已指派隨機編號

10,000

低於0.10

20,000

大於或等於0.10,且小於0.45

40000

大於或等於0.45,且小於0.75

60,000

大於或等於0.75

若要示範需求的類比,請查看檔案 Discretesim .xlsx,如下頁圖60-2 所示。

書籍圖像

我們的模擬的關鍵是使用隨機號碼,從資料表範圍 F2: G5 (命名查閱)開始查閱。 大於或等於0且小於0.10 的亂數字將產生10000的要求;大於或等於0.10 且小於0.45 的亂數字會產生20000的要求;大於或等於0.45 且小於0.75 的亂數字會產生40000的要求;而大於或等於0.75 的亂數字將產生60000的需求。 您可以從 C3 複製到 C4 來產生400亂數: C402 公式RAND ()。 然後,您可以從 B3 複製到 B4,以產生行事曆需求的400實驗或小程式: B402 公式VLOOKUP (C3,lookup,2)。 此公式可確保任何小於0.10 的亂數字都會產生10000的需求,在0.10 和0.45 之間的任何亂數字都會產生20000的需求,依此類推。 在儲存格範圍 F8: F11 中,使用 COUNTIF 函數來判斷我們400反覆運算產生每個需求的分數。 當我們按下 F9 重新計算亂數字時,模擬的可能性會接近我們的假設需求可能性。

如果您在任何儲存格中輸入公式NORMINV (rand ()、mu、sigma),您將會產生一個模擬值,其中的標準隨機變數具有平均值mu和標準差西格瑪。 此程式會顯示在 [檔案 Normalsim. .xlsx] 中,如圖60-3 所示。

書籍圖像

假設我們想要針對平均值為40000的一般隨機變數,以及10000的標準差來類比400實驗或小部分。 (您可以在儲存格 E1 和 E2 中輸入這些值,並分別為這些儲存格意思西格瑪命名。) 將公式= RAND ()從 C4 複製到 C5: C403 會產生400不同的亂數字。 從 B4 複製到 B5: B403 公式NORMINV (C4、mean、sigma)從標準隨機變數產生400不同的試用值,而平均值為40000,標準差為10000。 當我們按下 F9 鍵來重新計算亂數時,平均值會保持接近40000,標準差接近10000。

實質上,對於亂數x,公式NORMINV (p,mu,sigma)會產生具有平均值mu和標準差西格瑪之標準隨機變數的p百分位數。 例如,儲存格 C4 中的亂數0.77 (請參閱圖60-3)會在儲存格 B4 中產生,大約是平均值為40000且標準差為10000的標準隨機變數的77th 百分位數。

在本節中,您將瞭解如何使用 Monte Carlo 模擬來做為決策工具。 假設情人節卡片的需求是由下列離散隨機變數所管轄:

[可能性]

10,000

0.10

20,000

0.35

40000

0.3

60,000

0.25

$4.00 的信用卡銷售賀卡,且每個卡片的產生成本可變為 $1.50。 剩餘的卡必須以 $0.20 每張卡片的成本來處置。 應該列印多少張卡片?

實質上,我們會多次模擬每個可能的生產數量(10000、20000、40000或60000)(例如,1000小程式)。 接著,我們會決定哪一種訂單數量會產生1000反覆運算的最大平均利潤。 您可以在 [情人. .xlsx] 中找到此區段的資料,如圖60-4 所示。 您可以將儲存格 B1: B11 中的範圍名稱指派給儲存格 C1: C11。 儲存格範圍 G3: H6 會指派名稱查閱。 我們的銷售價格和成本參數是在儲存格 C4: C6 中輸入。

書籍圖像

您可以在儲存格 C1 中輸入試用版生產數量(在此範例中為40000)。 接著,在儲存格 C2 中使用公式= RAND ()來建立亂數字。 如先前所述,您可以使用公式VLOOKUP (rand、lookup、2),在儲存格 C3 中模擬卡片的需求。 (在 VLOOKUP 公式中, rand是指派給儲存格 C3 的儲存格名稱,而不是 rand 函數。)

銷售的單位數量是生產數量與需求的較小者。 在儲存格 C8 中,您會使用公式MIN (已產生、需求) * unit_price來計算我們的收入。 在 C9 儲存格中,您會計算出已產生 * unit_prod_cost的總生產成本。

如果我們所產生的卡數量超過需求,剩餘的單位數等於生產減去需求;否則將不會留下任何單位。 我們會使用unit_disp_cost * IF (產生>需求、產生的要求、0)等公式,在儲存格 C10 中計算我們的處置成本。 最後,在儲存格 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將在 A 欄中以儲存格 A16 開始輸入。

書籍圖像

接下來,我們在儲存格 B15: E15 中輸入可能的生產數量(10000、20000、40000、60000)。 我們想要計算每個試用期(1到1000)和每個生產數量的利潤。 我們會在資料表的左上角儲存格(A15)中,輸入= C11,以參照利潤的公式(在儲存格 C11 中計算)。

現在,我們已準備好誘騙 Excel 來模擬每個生產數量的需求1000。 選取表格範圍(A15: E1014),然後在 [資料] 索引標籤上的 [資料工具] 群組中,按一下 [If 分析],然後選取 [資料表格]。 若要設定雙向的資料表格,請選擇我們的生產數量(儲存格 C1)作為資料列的儲存格,然後選取任何空白儲存格(我們選取儲存格 I14)作為資料行的輸入儲存格。 按一下 [確定] 後,Excel 會針對每個訂單數量類比1000需求值。

若要瞭解為什麼要這樣做,請考慮由 C16: C1015 儲存格範圍內的資料表格所放置的值。 針對這些儲存格,Excel 會在儲存格 C1 中使用值20000。 在 C16 中,會將欄輸入儲存格值1放在空白儲存格中,而儲存格 C2 中的亂數字會重新計算。 對應的利潤接著會記錄在儲存格 C16 中。 然後,將2欄儲存格輸入值放在空白儲存格中,C2 中的亂數字會再次重新計算。 相對應的利潤是在儲存格 C17 中輸入。

透過從儲存格 B13 複製到 C13: E13 公式AVERAGE (B16: B1015),我們會計算每個生產數量的平均模擬利潤。 透過從儲存格 B14 複製到 C14: E14 公式STDEV (B16: B1015),我們會為每個訂單數量計算我們模擬利潤的標準差。 每次按下 F9 時,1000會針對每個訂單數量類比需求的每個訂單數次。 產生40000卡時,通常會產生最大的預期利潤。 因此,會顯示產生40000卡片的正確決策。

風險對決策的影響     如果我們產生20000,而不是40000卡片,預期利潤大約會下降22%,但我們的風險(由利潤的標準差所衡量)會下降幾乎73%。 因此,如果我們極大地 averse 風險,可能會產生20000卡片是正確的決策。 順便說一下,產生10000卡片的標準差總為0個卡,因為如果我們產生10000卡,我們將永遠出售所有這些卡,而不需要任何 leftovers。

附註:  在此活頁簿中,[計算] 選項會設定為 [除] 以外的 [自動]。 (使用 [公式] 索引標籤上 [計算] 群組中的 [計算] 命令。) 這項設定可確保我們不會重新計算我們的資料表格,除非我們按下 F9,這是一個很好的做法,因為大型資料表格會在您每次在工作表中輸入內容時,都會減緩您的工作。 請注意,在這個範例中,每按 F9,平均利潤都會變更。 發生這種情況是因為每當您按下 F9 時,會使用不同的1000隨機編號序列來產生每個訂單數量的需求。

平均利潤的置信區間     在這種情況下要問的一個自然問題是,我們的95% (確定)真正的平均利潤會下降? 此間隔稱為平均利潤的95% 置信度間隔。 任何模擬輸出平均數的95置信間隔是由下列公式計算得出:

書籍圖像

在儲存格 J11 中,您會在使用公式D13 – 1.96 * D14/SQRT (1000)產生40000行事歷時,計算平均利潤的95% 置信區間的低限制。 在儲存格 J12 中,您會使用公式D13 + 1.96 * D14/SQRT (1000)來計算95% 置信區間的上限。 這些計算如圖60-7 所示。

書籍圖像

我們的95% 可確保當您的40000行事曆定購時,我們的平均利潤為 $56687 和 $62589。

  1. GMC 莊家認為 2005 Envoys 的需求將會以200和標準差為30的平均值來散佈。 其收到 Envoy 的費用是 $25000,他銷售 $40000 的 Envoy。 所有未以全價出售的 Envoys 的一半,都可以出售至 $30000。 他正在考慮定購200、220、240、260、280或 300 Envoys。 他應該訂貨多少?

  2. 小型超市正在嘗試判斷每週要訂購多少人的人員雜誌份數。 他們認為自己對使用者的需求是由下列離散隨機變數所管轄:

    [可能性]

    15

    0.10

    20

    0.20

    0.30

    為期

    0.25

    35

    0.15

  3. 超市為每位人員支付 $1.00,並銷售 $1.95。 每個 unsold 複本都可以傳回 $0.50。 商店訂單應該有多少

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

附註:  本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 告訴我們這項資訊是否有幫助? 這裡是供您參考的英文文章

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×