Excel の Monte Carlo のシミュレーションの概要

この記事は、も Winston によって Microsoft Excel のデータ分析とビジネスモデリング を通じて採用されました。

  • Monte Carlo シミュレーションを使用するのは誰ですか?

  • セルに「 = RAND () 」と入力するとどうなりますか?

  • 個別のランダム変数の値をシミュレートするにはどうすればよいですか?

  • 通常のランダム変数の値をシミュレートするにはどうすればよいですか?

  • グリーティングカード会社は、作成するカードの数を決定する方法を教えてください。

不確実なイベントの確率を正確に見積もる必要があります。 たとえば、新しい製品のキャッシュフローが正の正味現在価値 (NPV) を持つ可能性は何ですか。 投資ポートフォリオのリスク要因について教えてください。 Monte Carlo シミュレーションでは、不確定性を示す状況をモデル化し、コンピューターで何度も再生することができます。

注:  Name Monte Carlo のシミュレーション は、1930s と1940s の間に実行されたコンピューターのシミュレーションから取得され、detonate への atom 爆弾に必要なチェーンの反力が正常に動作する確率を推定します。 この作業に関係する physicists は、ギャンブルの大きなファンだったため、シミュレーションにはコード名 Monte Carloが与えられました。

次の5つの章では、Excel を使用して Monte Carlo のシミュレーションを実行する方法の例を示します。

多くの会社では、Monte Carlo のシミュレーションを意思決定プロセスの重要な部分として使用しています。 いくつかの例を次に示します。

  • 一般的なモーター、未処理の Tor と Gamble、Pfizer、Bristol-Myers Squibb、Eli Lilly のシミュレーションを使って、新製品の平均利回りとリスク要因の両方を見積もっています。 GM では、この情報を使用して、市場に投入される製品を特定することができます。

  • GM は、企業の純利益の予測、構造と購買のコストの予測、さまざまな種類のリスク (金利の変更、為替レートの変動など) に対する弱点の特定などのアクティビティにシミュレーションを使用します。

  • Lilly はシミュレーションを使用して、各薬品に最適なプラントの容量を決定します。

  • Gamble はシミュレーションを使って、外国為替のリスクをモデル化し、最適な生垣をします。

  • Sears は、シミュレーションを使って、製造業者から注文する必要がある各製品ラインの単位数を決定します。たとえば、この年には、Dockers ズボンのペアの数を指定します。

  • オイルおよび薬品会社では、プロジェクトの展開、契約、延期のオプションの値など、"実際のオプション" という値にシミュレーションを使用します。

  • 財務プランナーは、Monte Carlo のシミュレーションを使って、顧客の退職に対する最適な投資方針を決定します。

セルに「 = RAND () 」という数式を入力すると、0 ~ 1 の範囲の値を取得するのと同じような数値が得られます。 そのため、約25% の値を取得するには、0.25 以下である必要があります。時間の約10% は、0.90 以上の番号を取得する必要があります。 RAND 関数の動作を確認するには、図60-1 に示すファイル Randdemo.xlsx を参照してください。

本の画像

注:  ファイル Randdemo.xlsx を開くと、図60-1 に示すランダムな番号が表示されません。 RAND 関数は、ワークシートが開かれたとき、またはワークシートに新しい情報が入力されたときに、自動的に生成される数値を自動的に再計算します。

まず、セル C3 から C4 にコピーします。数式 = RAND ()を C402 します。 次に、範囲 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

4万

0.3

60,000

0.25

このような予定表を何度も再生する必要がある場合、またはシミュレートする場合は、どうすればよいですか。 この方法では、RAND 関数の各値を予定表に対する需要と関連付けることができます。 次の割り当てを行うことで、1万の要求がいつでも10% 発生することが保証されます。

オン

ランダム番号の割り当て

10,000

0.10 より小さい

20,000

0.10 以上で、0.45 より小さい

4万

0.45 以上で、0.75 より小さい

60,000

0.75 以上

需要のシミュレーションを示すには、次のページの図60-2 に示されているファイル Discretesim.xlsx を参照してください。

本の画像

シミュレーションのキーとして、ランダムな数値を使用して、テーブル範囲 F2: G5 (名前付き 検索) からの参照を開始することができます。 0以上で0.10 より小さい乱数は、1万の需要を生み出します。0.10 以上で0.45 より小さい乱数は、2万の要求を生み出します。0.45 以上で0.75 より小さい乱数は、4万の要求を生み出します。また、0.75 以上の乱数を指定すると、6万の需要を生み出すことになります。 400の乱数を生成するには、C3 から C4: C402 数式 ()をコピーします。 次に、B3 から B4 へコピーすることによって、400試用またはイテレーションを生成します。 B402 数式 (C3、lookup、2)。 この数式は、0.10 より小さいランダムな数値が1万の要求を生成することを保証します。0.10 と0.45 の間の任意の乱数は、2万の要求を生成します。 セル範囲 F8: F11 で、COUNTIF 関数を使用して、各要求を生成する400のイテレーションの割合を判断します。 F9 キーを押してランダムな数値を再計算すると、シミュレートされた確率が需要の確率に近いものになります。

数式 NORMINV (rand (), mu, σ)にセルを入力した場合、平均 mu と標準偏差 シグマを持つ通常のランダム変数のシミュレート値が生成されます。 この手順については、図60-3 に示す「ファイルの Normalsim.xlsx」を参照してください。

本の画像

たとえば、4万と1万の標準偏差を使って、400の試用 (反復) をシミュレートするとします。 (これらの値をセル E1 と E2 に入力して、それぞれのセルに "平均" と "シグマ" という名前を指定することができます)。 数式 = RAND () を C4 から C5: C403 にコピーすると、400の異なるランダム番号が生成されます。 B4 から B5 へのコピー: B403 数式 NORMINV (C4、平均、シグマ) では、400の異なる試用値が、4万の平均と1万の標準偏差で生成されます。 F9 キーを押して乱数を再計算すると、平均値は4万に近く、標準偏差は1万に近くなります。

基本的に、乱数xの場合、数式NORMINV (p、mu、sigma)は、平均muと標準偏差シグマを使って、標準のランダム変数のp番目の百分位を生成します。 たとえば、セル C4 の乱数 0.77 (図60-3 を参照) は、標準のランダム変数の77th 百分位の値であり、標準偏差は4万、標準偏差は1万で生成されます。

このセクションでは、Monte Carlo のシミュレーションを意思決定ツールとして使用する方法について説明します。 バレンタインの日カードの需要は、次の個別のランダム変数によって制御されているとします。

オン

確率

10,000

0.10

20,000

0.35

4万

0.3

60,000

0.25

グリーティングカードは $4.00 で販売されており、各カードを作成するための変動コストは $1.50 です。 未使用のカードは、1枚あたり $0.20 のコストで処分する必要があります。 印刷するカードの数

基本的には、可能な生産量 (1万、2万、4万、または 6万) を何度もシミュレートします (たとえば、1000イテレーション)。 次に、1000のイテレーションの平均収益の最大値を求める注文数量を決定します。 このセクションのデータは、図60-4 に示す [ファイル Valentine.xlsx で確認できます。 セル B1: B11 からセル C1: C11 に範囲の名前を割り当てます。 セル範囲 G3: H6 には、名前の 参照が割り当てられています。 販売価格とコストのパラメーターはセル C4: C6 に入力します。

本の画像

セル C1 に、試用版の生産量 (この例では 4万) を入力することができます。 次に、数式 = RAND ()を使用して、セル C2 に乱数を作成します。 前に説明したように、 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 試用版に対応します)。 これらの値を簡単に作成する方法の1つとして、セル A16 に 1 を入力する方法があります。 セルを選び、[ ホーム ] タブの [ 編集 ] グループで [ 塗りつぶし] をクリックし、[連続データの挿入 ] をクリック して [ 系列 ] ダイアログボックスを表示します。 図60-6 に示されている [ 系列 ] ダイアログボックスで、ステップ値1と1000の停止値を入力します。 [ 系列 の範囲] 領域で、[ 段組み ] オプションを選び、[ OK]をクリックします。 数値 1 ~ 1000 は、セル A16 の最初の列に入力されます。

本の画像

次に、B15 など: E15 に、可能な生産量 (1万、2万、4万、6万) を入力します。 各トライアル番号 (1 ~ 1000) および各生産数量の利益を計算する必要があります。 ここでは、 = C11を入力して、データテーブルの左上のセルの (セル C11 で計算される) 利益の計算式について説明します。

これで、Excel を使用して、各生産量の需要の1000イテレーションをシミュレートする準備が整いました。 テーブル範囲 (A15: E1014) を選択し、[データ] タブの [データツール] グループで、[分析] をクリックし、[データテーブル] をクリックします。 双方向のデータテーブルを設定するには、行の代入セルとして microsoft の生産量 (セル C1) を選び、空白セル (セル I14) を列の代入セルとして選択します。 [OK] をクリックした後、Excel では、各注文量の1000需要値がシミュレートされます。

この問題の原因を理解するには、セル範囲 C16: C1015 のデータテーブルによって指定された値を考慮してください。 各セルについて、Excel ではセル C1 で2万の値が使用されます。 C16 では、列の代入セルの値1が空白セルに配置され、セル C2 の乱数が再計算されます。 その後、対応する利益がセル C16 に記録されます。 列セルの入力値2が空白セルに配置され、C2 の乱数が再計算されます。 対応する利益は、セル C17 に入力します。

セル B13 から C13 にコピーすることによって、数式の 平均 (B16: B1015)を計算します。これは、各生産数量の平均シミュレーション利益を計算することです。 セル B14 から C14 にコピーします。 E14 式 STDEV (B16: B1015)では、各注文数量のシミュレートされた利益の標準偏差を計算します。 F9 キーを押すたびに、1000の需要の繰り返しが各注文の数量に対してシミュレートされます。 4万カードを生成すると、常に予想される最大の利益が得られます。 したがって、4万カードを作成することが適切な決断であるということが表示されます。

意思決定のリスクへの影響      4万カードの代わりに2万を作成した場合、期待される利益は約22% になりますが、このようなリスク (標準偏差によって測定された) は、73% の低下をします。 したがって、averse が非常に危険にさらされている場合は、2万カードを作成することで適切な決定を行うことができます。 1万カードの作成には、常に0カードの標準偏差が含まれています。1万カードを作成すると、その中に残されているものがすべて含まれていないことになります。

注:  このブックでは、[テーブル以外自動] オプションが [自動] に設定されています。 [数式] タブの [計算方法] で [計算方法] を使用します。 この設定では、F9 キーを押さない限り、データテーブルは再計算されません。これは、ワークシートに何か入力するたびに再計算されるため、大きなデータテーブルでは作業が遅くなるためです。 この例では、F9 キーを押すたびに、平均の利益が変化します。 これは、F9 キーを押すたびに、異なる1000ランダム番号のシーケンスが使用され、注文の各数量に対する需要が発生します。

平均利益の信頼区間      このような状況において、自然な質問は、95% の間隔で、実際の利益がどのようになるのでしょうか。 この間隔は、 平均利益の95パーセントの信頼区間と呼ばれます。 シミュレーション出力の平均値の95パーセントの信頼区間は、次の数式で計算されます。

本の画像

セル J11 では、4万の予定表が D13 – 1.96 * D14/SQRT (1000)で生成された場合の、平均利益に対する95パーセントの信頼区間の下限を計算します。 セル J12 では、 D13 + 1.96 * D14/SQRT (1000)という数式を使用して、95パーセントの信頼区間の上限を計算します。 これらの計算は、図60-7 で示されています。

本の画像

4万の予定表が注文された時点での $56687 と $62589 の間の平均の利益は、95% になっています。

  1. GMC の親は、2005 Envoys の需要が200と標準偏差30の平均値で分散されていることを意味します。 エンボイの料金は $25000 で、$4万のエンボイが販売されています。 $3万では、全額価格で販売されていないすべての Envoys の半分が販売されます。 200、220、240、260、280、300 Envoys を注文することを検討しています。 注文数はいくつですか?

  2. 小規模の supermarket では、各週に注文する の雑誌の部数を特定しようとしています。 ユーザーに対する需要は、次の個別のランダム変数によって制御されます。

    オン

    確率

    15

    0.10

    20

    0.20

    50

    0.30

    求める

    0.25

    35

    0.15

  3. Supermarket は、各 ユーザー のコピーに対して $1.00 を支払い、$1.95 で販売します。 販売されていない各コピーは、$0.50 に対して返すことができます。 ストアの注文には、 ユーザー のコピーはいくつありますか?

補足説明

Excel Tech Community では、いつでも専門家に質問できます。Microsoft コミュニティでは、サポートを受けられます。また、Excel User Voice では、新機能についての提案や改善案を送信することができます。

ヘルプを表示

Office のスキルを磨く
トレーニングの探索
新機能を最初に入手
Office Insider に参加する

この情報は役に立ちましたか?

ご意見をいただきありがとうございます。

フィードバックをお寄せいただき、ありがとうございます。Office サポートの担当者におつなぎいたします。

×