在 Excel 中 Monte Carlo 模擬簡介

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

本文是採用Microsoft Excel 資料分析和商務模型來 Wayne L 本。

  • 誰會使用 Monte Carlo 模擬?

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

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

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

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

我們想要精確地估計確定事件的機率。 例如,為何會有正淨現值 (NPV) 的新產品的現金流量的機率? 什麼是我們投資公事包風險因素? Monte Carlo 模擬可讓我們模型情況呈現不確定性,然後播放它們電腦千分位的次數。

附註: Monte Carlo 模擬來自 1930年和來估計的所需的至爆炸 atom 轟炸鏈結反應能成功的機率 1940年期間執行的電腦模擬名稱。 Physicists 參與這項工作所大愛用者賭博,讓他們提供模擬Monte Carlo程式碼名稱。

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

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

  • 一般馬達、 Proctor 場賭博、 Pfizer、 Bristol Myers Squibb 和賴 Lilly 使用模擬來估計平均傳回和風險因素的新產品。 在 GM,這項資訊是由 CEO 用來判斷哪些產品來到市場。

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

  • 若要判斷最佳的植物容量每種藥品 lilly 使用模擬。

  • Proctor 場賭博用模擬來和模型和最佳化赫奇外部 exchange 風險。

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

  • 石油和藥品公司使用模擬值 「 真實選項 」 等選項,即可展開、 合約或延後專案的值。

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

當您在儲存格中輸入公式= rand ()時,您會收到平均可能要採用任何值 0 和 1 之間的數字。 因此,大約 25%的時間,您應該會收到一個數字小於或等於 0.25。大約 10%,您應該會收到一個數字,是時間的至少 0.90,依此類推。 為了示範 RAND 函數的運作方式,看看檔案 Randdemo.xlsx,顯示圖 60-1。

書籍圖像

附註:  當您開啟檔案 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 and 小於 0.45 的隨機數字會產生 20000; 的要求大於或等於 0.45 and 小於 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 重新計算的隨機數字時,模擬的機率是接近我們假設的 demand 機率。

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

書籍圖像

假設我們想要模擬 400 的試用版或用一般的隨機變數 40000 平均值和標準差為 10000 反覆項目。 (您可以在儲存格 E1 和 E2,鍵入這些值與名稱這些儲存格的意思標準差,分別。) 將公式c4 複製到 C5:C403 產生 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 每張卡片。 應該列印多少卡片?

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

書籍圖像

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

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

如果我們產生更多卡片比需求,單位數量留下等於進入生產階段前減去要求;否則無單位會留下。 我們計算與公式unit_disp_cost*IF(produced>demand,produced–demand,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年會在資料行中輸入 A16 儲存格中開始。

書籍圖像

接下來我們會在儲存格 B15:E15 中輸入 10000、 20000、 40000 (60000) 我們可能生產數量。 我們想要計算的每個試用版的數字 (1 到 1000年) 獲利和每個實際執行數量。 我們藉由輸入= C11參考利潤 (計算的儲存格 C11) 在我們的運算列表 (A15) 的左上角儲存格中的公式。

我們已準備好要訣竅 Excel 將模擬的每個實際執行數量需求 1000年反覆項目。 選取表格範圍 (A15:E1014),然後在 [資料] 索引標籤上的 [資料工具] 群組中,按一下 [苦頭 If 分析,然後選取 [運算列表。 若要設定雙向運算列表,選擇 [列變數儲存格為我們生產數量 (儲存格 C1) 並選取任何空白的儲存格 (我們之所以選擇這個儲存格 I14) 欄輸入儲存格。 後按一下 [確定],Excel 會模擬每個訂購數量的 1000 demand 的值。

若要了解運作方式為何,請考慮放在 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,mean 利潤會變更。 這是因為的每當您按下 F9,不同的 1000年隨機數字序列會用來產生每個訂購數量的需求。

利潤平均值的信賴區間     在此情況下要求自然問題是,到哪些間隔我們 95%確定 true 平均數利潤落嗎? 這個間隔會呼叫95%的利潤平均值的信賴區間。 任何模擬輸出的平均值 95%信賴區間是由下列公式計算:

書籍圖像

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

書籍圖像

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

  1. GMC 經銷商認為 2005 Envoys 需求將會以正常方式散發 200 的平均值和標準差為 30。 接收位使者他成本為 $25000,與他的 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 支援專員連絡以深入了解您的意見。

×