在 Excel 中 Monte Carlo 模擬簡介

附註:  我們想要以您的語言,用最快的速度為您提供最新的說明內容。 本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。讓這些內容對您有所幫助是我們的目的。希望您能在本頁底部告訴我們這項資訊是否有幫助。 此為 英文文章 出處,以供參考。

本文是 Wayne L 著重調整從Microsoft Excel 資料分析和商務模型

  • 誰會使用 Monte Carlo 模擬?

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

  • 您可以模擬不連續的隨機變數的值?

  • 您可以模擬標準的隨機變數的值?

  • 賀卡公司要如何判斷多少卡片以產生?

我們想要精確地估計不確定事件的機率。例如,新產品的現金流量必須正數淨現值 (NPV) 的機率是什麼?什麼是我們投資產品組合的風險因素?Monte Carlo 模擬可讓我們模型呈現不確定性並播放其的次數千分位電腦上的情況下。

附註: Monte Carlo 模擬來自 1930年及 1940 來估計的所需的至爆炸 atom 炸彈鏈反應能成功的機率時所執行的電腦模擬名稱。這項工作所需的 physicists 已的賭博、,讓所提供的模擬Monte Carlo代碼名稱。

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

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

  • 一般馬達、 Proctor 與場賭博、 Pfizer、 Bristol Myers Squibb 和賴 Lilly 使用模擬來估計新產品的風險因素和平均 return 鍵。在 GM,這項資訊可供 CEO 來決定哪些產品了上市。

  • GM 使用模擬的活動,例如預測的公司的淨收入、 預測結構化及購買的成本,以及決定其它不同類型的 (例如利率變更和匯率變動) 的風險。

  • Lilly 使用模擬,來判斷每項最佳的植物容量。

  • Proctor 和場賭博使用模擬模型和最佳樹叢外部 exchange 風險。

  • Sears 使用模擬判斷每個產品線單位數量應該為由供應商,例如數目的排序今年的 Dockers 長褲。

  • 石油及製藥公司使用模擬值 [真實選項],例如其中一個選項以展開、 合約或延後專案的值。

  • 財務規劃會使用用戶端的退休決定最佳的投資策略 Monte Carlo 模擬。

當您在儲存格中輸入公式= rand ()時,您會收到假設 0 和 1 之間的任何值都可能相等的數字。因此,大約 25%的時間,您應該取得數字小於或等於 0.25。處理 10%的時間,您應該會取得數字會至少 0.90,依此類推。若要示範 RAND 函數的運作方式,查看檔案 Randdemo.xlsx,顯示圖 60-1。

書籍圖像
圖 60-1 示範 RAND 函數

附註: 當您開啟檔案 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 函數的每個可能的值。下列作業可確保會發生的情況下,10%的 10000 的要求,並等。

視需要

指派的隨機數字

10,000

小於 0.10

20,000

大於或等於 0.10,而且小於 0.45

40000

大於或等於 0.45,而且小於 0.75

60,000

大於或等於 0.75

若要示範模擬的需求,查看檔案 Discretesim.xlsx 下, 一個頁面上顯示圖 60 為 2。

書籍圖像
圖 60 2 模擬不連續的隨機變數

我們的模擬的關鍵是進行查閱表格範圍 F2:G5 (命名為 [查閱]) 中的使用的隨機數字。大於或等於 0 且小於 0.10 的隨機數字會產生 10000; 的要求大於或等於 0.10,而且小於 0.45 的隨機數字會產生 20000; 的要求大於或等於 0.45,而且小於 0.75 的隨機數字會產生 40000; 的要求隨機數字大於或等於 0.75 會產生 60000 的要求。您從複製 C3 到 C4:C402 公式RAND產生 400 隨機數字。您然後產生 400 試用版或重複的行事曆需求從複製 B3 到 B4:B402 VLOOKUP(C3,lookup,2)的公式。此公式可確保的任何隨機數字小於 0.10 產生的 10000 要求時,任何 0.10 和 0.45 之間的隨機數字會產生的要求 20000,依此類推。在儲存格範圍 F8:F11 中,使用 COUNTIF 函數來判斷產生每個指定我們 400 反覆運算的。我們會按 f9 鍵重新計算的隨機數字,模擬的機率就靠近我們假定的需求的機率。

如果您輸入的任何儲存格公式NORMINV(rand(),mu,sigma),將會產生模擬的值的標準的隨機變數有平均數記憶和標準差標準差。此程序說明檔案 Normalsim.xlsx,顯示圖 60-3。

書籍圖像
圖 60 3 模擬標準的隨機變數

現在就讓我們假設我們想要模擬 400 試用版或重複的 40000 平均值和標準差為 10000 標準的隨機變數。(您可以在儲存格 E1 和 E2,鍵入這些值與名稱這些儲存格的意義標準差,分別。)公式複製到 C5:C403 C4 產生 400 不同的隨機數字。複製 b4 到 B5:B403 公式NORMINV(C4,mean,sigma)從標準的隨機變數 40000 的平均值和標準差為 10000 產生 400 不同的試用值。當我們按 F9 鍵重新計算的隨機數字時,是指會保持靠近 40000 和靠近 10000 標準差。

基本上,隨機數字的x,公式NORMINV(p,mu,sigma)產生的p第百分位數的標準差sigma平均數的記憶與標準的隨機變數。例如,隨機數字在儲存格 C4 0.77 (請參閱圖 60-3) 會產生儲存格 B4 中大約 77th 的百分位數標準的隨機變數 40000 的平均值和標準差為 10000。

在此區段中,您會看到蒙地卡羅模擬如何作為決策工具。假設的情人節賀卡由下列不連續的隨機變數:

視需要

機率

10,000

0.10

20,000

0.35

40000

0.3

60,000

0.25

賀卡銷售的 $4.00,且製作每張卡片的變數成本為 $1.50。剩餘的卡片必須被丟棄成本為 $0.20 每張卡片。列印多少卡片?

基本上,我們的次數 (例如 1000年反覆運算) 模擬每個可能的生產數量 (10000、 20000、 40000 或 60000)。然後我們判斷哪些訂購數量 1000年反覆運算產生最大值的平均收益。您可以找到資料檔 Valentine.xlsx,顯示圖 60-4 中的此區段。您可以指定範圍名稱,在儲存格 B1:B11 至儲存格 C1:C11。儲存格範圍 G3:H6 會被指派名稱對應。在儲存格 C4:C6 中輸入我們的銷售價格和成本參數。

書籍圖像
圖 60 4 情人節卡片模擬

您可以在儲存格 C1 中輸入試用生產數量 (在此範例中 40000)。接下來,與公式的儲存格 C2 中建立的隨機數字。如上所述,您可以模擬需求與VLOOKUP(rand,lookup,2)公式的儲存格 C3 中的卡片。(在 VLOOKUP 公式中, rand是指派給儲存格 C3,RAND 函數的儲存格名稱)。

賣出單位的數字是較小的我們生產數量和要求。您可以在儲存格 C8,計算與公式我們營收最小值 (產生,視需要) * unit_price。您可以在儲存格 C9,計算公式的總生產成本產生 * unit_prod_cost

如果我們產生比視需要更多的卡片,單位數量留下等於生產需求; 減號否則沒有單位是左。我們在計算中使用公式的儲存格 C10 我們種方式成本unit_disp_cost * IF (產生 > 需求,產生 – 需求,0)。最後,在儲存格 C11,我們來計算我們利潤為營收 – total_var_cost-total_disposing_cost

我們想要的每個產品數量按 F9 多次 (例如 1000年),以及計算的每個數量我們預期的獲利有效方法。這種情況下是雙向運算列表名稱我們救星或的號碼。(的運算列表的詳細資料,請參閱章節 15,「 敏感性分析與資料表格,」)。此範例中的 [資料] 資料表會顯示圖 60-5。

書籍圖像
圖 60-5 的賀卡模擬雙向運算列表

在儲存格範圍 A16:A1015 中,輸入的數字 1-1000 (相對應我們 1000年試用版)。若要建立這些值的其中一個簡單方法是 A16 儲存格中輸入1 。選取的儲存格,然後在 [常用] 索引標籤的 [編輯] 群組中,按一下 [填滿],並選取要顯示 [數列] 對話方塊的數列。在 [數列] 對話方塊,顯示在圖 60-6,輸入 1 的間距值] 和 [停止 1000年的值。在 [數列] 區域中,選取 [] 選項中,,然後按一下[確定]。數字 1-1000年會在資料行中輸入 A16 儲存格中開始。

書籍圖像
圖 60-6 填入試用數字 1 到 1000年使用 [數列] 對話方塊

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

我們已準備好要技巧 Excel 將模擬 1000年重複的每個產品數量。選取表格範圍 (A15:E1014),然後在 [資料] 索引標籤上的 [資料工具] 群組中,按一下 [苦頭分析,並再選取 [運算列表。若要設定雙向運算列表,選擇 [列變數儲存格為我們生產數量 (儲存格 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%確定,則為 true 的平均數 profit 落嗎?這段稱為95%的利潤平均值的信賴區間。任何模擬輸出的平均數 95%的信賴區間是由下列公式計算:

書籍圖像

在儲存格 J11,您會計算 95%的信賴區間上平均數的利潤低的限制,當公式D13–1.96*D14/SQRT(1000)以產生 40000 行事曆。在儲存格 J12,您會計算與公式D13+1.96*D14/SQRT(1000)我們 95%信賴區間的上限。這些計算會顯示圖 60-7。

書籍圖像
排序 40000 行事曆時的平均數 profit 圖 60 7 95%信賴區間

我們已確認我們平均數的利潤時的排序 40000 行事曆 $56,687 介於 $62,589 95%。

  1. GMC 經銷商認為需求 2005 Envoys 將會以正常方式散發 200 的平均值和標準差為 30。接收 Envoy 他成本為 $25000,並他銷售 Envoy 40000 的。不賣出完整的價格的所有 Envoys 的一半,可以賣出 $30000 的。他考慮順序 200、 220、 240、 260、 280 或 300 Envoys。幾應該他順序?

  2. 小型商場嘗試決定的人員雜誌他們應該訂單每週的份數。相信其指定的人員由下列不連續的隨機變數:

    視需要

    機率

    15

    0.10

    20

    0.20

    25

    0.30

    30

    0.25

    35

    0.15

  3. 商場支付 $1.00 每份人員,並銷售它為 $1.95。可傳回 $0.50 每個 unsold 的複本。人員的份數應該存放區順序?

需要更多協助嗎?

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

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×