モンテカルロ シミュレーションの概要 (Excel

• Whoモンテカルロ シミュレーションを使用する場合

• セルに 「=RAND()」と入力すると 、どうなるでしょうか。

• 個別のランダム変数の値をシミュレートする方法

• 通常のランダム変数の値をシミュレートする方法

• グリーティング カード会社は、作成するカードの数をどのように決定できますか。

不確実なイベントの確率を正確に推定します。 たとえば、新しい製品のキャッシュ フローに正の正味現在価値 (NPV) が与える確率は何ですか。 投資ポートフォリオのリスク要因は何ですか? モンテカルロ シミュレーションを使用すると、不確定な状況をモデル化し、コンピューター上で何千回も再生できます。

次の 5 つの章では、モンテカルロ シミュレーションの実行にExcel例を示します。

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

• General Motors、Proctor and Lile、Pfizer、Bristol-Myers Squibb、Eli Lilly はシミュレーションを使用して、新製品の平均リターンとリスク要因の両方を推定します。 GM では、この情報を CEO が使用して、どの製品が市場に出てくるのか判断します。

• GM は、企業の純利益の予測、構造コストと購入コストの予測、さまざまな種類のリスク (利率の変化や為替変動など) に対する影響の判断などのアクティビティにシミュレーションを使用します。

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

• Proctor と Optimale は、シミュレーションを使用して、為替リスクをモデル化し、最適に生み出します。

• Sears はシミュレーションを使用して、サプライヤーから注文する必要がある各製品ラインのユニット数を決定します。たとえば、今年注文する必要がある Dockers エンジンのペアの数などです。

• 石油およびドラッグ会社は、シミュレーションを使用して、プロジェクトを拡張、契約、延期するオプションの値など、"実際のオプション" を評価します。

• ファイナンシャル プランナーは、モンテカルロ シミュレーションを使用して、顧客の退職に最適な投資戦略を決定します。

数式 =RAND() をセルに入力すると、0 ~ 1 の値を想定する可能性が高い数値が返されます。 したがって、時間の約 25% は、0.25 以下の数値を取得する必要があります。0.90 以上の数値を取得する必要があります。 RAND 関数のしくみを示す場合は、図 60-1 に示Randdemo.xlsxファイルを参照してください。

最初に、セル 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
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 以上

需要のシミュレーションを示す場合は、次のページDiscretesim.xlsx図 60-2 に示すように、ファイルの種類を確認します。

シミュレーションの鍵は、ランダムな数値を使用して、テーブル範囲 F2:G5 (ルックアップ という名前) から参照を開始 します。 0 以上 0.10 未満の乱数では、10,000 の需要が生成されます。ランダムな数値が 0.10 以上 0.45 未満の場合、需要は 20,000 です。ランダムな数値が 0.45 以上 0.75 未満の場合、需要は 40,000 です。0.75 以上の乱数は 60,000 の需要を生み出します。 数式 RAND() を C3 から C4:C402 にコピーして、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 と標準偏差 σ を持つ通常のランダム変数のシミュレートされた値が生成されます。 この手順は、図 60-3 にNormalsim.xlsxファイルの一部に示されています。

平均が 40,000 で標準偏差が 10,000 の通常のランダム変数に対して、400 試行 (反復) をシミュレートするとします。 (セル E1 と E2 にこれらの値を入力し、これらのセルに平均とσという名前を付けできます)。 数式 =RAND() を C4 から C5:C403 にコピーすると、400 種類の乱数が生成されます。 数式 NORMINV(C4,mean,σ) を B4 から B5:B403 にコピーすると、平均が 40,000 で標準偏差が 10,000 である通常のランダム変数から 400 の異なる試行値が生成されます。 F9 キーを押して乱数を再計算すると、平均は 40,000 に近く、標準偏差は 10,000 に近いままです。

基本的に、乱数xの場合、数式 NORMINV(p,mu,σ)は、平均 mu と標準偏差 σを持つ通常のランダム変数のp番目の第 1 位を生成します。 たとえば、セル C4 の乱数 0.77 (図 60-3 を参照) は、セル B4 で、平均が 40,000、標準偏差が 10,000 である通常のランダム変数の約 77 パーセントを生成します。

このセクションでは、モンテカルロ シミュレーションを意思決定ツールとして使用する方法について説明します。 バレンタイン デー カードの需要が、次の個別のランダム変数によって管理されている場合を仮定します。

需要	確率
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 反復)。 次に、1,000 回の反復で最大平均利益を得られる注文数量を決定します。 このセクションのデータは、図 60-4 にValentine.xlsxファイル内で確認できます。 セル B1:B11 の範囲名をセル C1:C11 に割り当てる。 セル範囲 G3:H6 には、名前参照 が割り当 てられます。 販売価格とコスト パラメーターはセル C4:C6 に入力されます。

セル C1 に評価版の生産数量 (この例では 40,000) を入力できます。 次に、数式 =RAND()を使用してセル C2 に乱数を作成します。 前に説明したように、 数式 VLOOKUP(rand,lookup,2)を使用して、セル C3 のカードの需要をシミュレートします。 (VLOOKUP 数式では 、rand は RAND 関数ではなくセル C3 に割り当てられたセル名です)。

販売ユニット数は、生産数量と需要の少ない数です。 セル C8 では 、MIN(生成,需要)*を使用して収益unit_price。 セル C9 では、生成された数式を使用して合計生産コストを計算します*unit_prod_cost。

需要を超える数のカードを生成した場合、残っているユニット数は生産から需要を差し引いた数になります。それ以外の場合、単位は残りはありません。 セル C10 の廃棄コストは、数式 unit_disp_cost*IF(生成>需要,生成-需要,0) で計算されます。 最後に、セル C11 で利益を収益として計算します(total_var_cost total_disposing_cost)。

生産数量ごとに F9 キーを何度も押し (1000 など) 効率的に押し、各数量の予想利益を集計する効率的な方法が必要です。 このような状況は、2 つのデータ テーブルが私たちの助けとなる状況です。 (データ テーブルの詳細については、第 15 章「データ テーブルによる感度分析」を参照してください)。 この例で使用するデータ テーブルを図 60-5 に示します。

セル範囲 A16:A1015 に、1 ~ 1000 の数値を入力します (1,000 件の試行に対応します)。 これらの値を作成する簡単な方法の 1 つは、セル A16 に「1」 を入力する方法です。 セルを選択し、[編集] グループの [ホーム] タブで [塗りつぶし] をクリックし、[系列] を選択して [系列]ダイアログ ボックスを表示します。 図60-6 に示す [系列] ダイアログ ボックスに、ステップ値 1 と停止値 1000 を入力します。 [系列 ] 領域で 、[列] オプション を選択 し 、[OK] をクリックします。 セル A16 から始まる列 A に 1 ~ 1000 の数値が入力されます。

次に、セル B15:E15 に可能な生産数量 (10,000、20,000、40,000、60,000) を入力します。 各試用番号 (1 ~ 1000) と各生産数量の利益を計算します。 データ テーブル (A15) の左上のセルに 「=C11」と入力して、利益の数式 (セル C11 で計算) を参照します。

これで、生産数量ごとに 1,000 回の需要をExcelをシミュレートする準備が整いました。 テーブル範囲 (A15:E1014) を選択し、[データ] タブの [データ ツール] グループで [What If Analysis] をクリックし、[データ テーブル] を選択します。 2 ウェイ データ テーブルを設定するには、[行の入力セル] として生産数量 (セル C1) を選択し、空白セル (セル I14 を選択) を [列入力セル] として選択します。 [OK] をクリックするとExcel注文数量ごとに 1,000 件の需要値がシミュレートされます。

これが機能する理由を理解するには、セル範囲 C16:C1015 のデータ テーブルによって配置される値を検討します。 これらの各セルについて、Excelセル C1 に 20,000 の値を使用します。 C16 では、列の入力セル値 1 が空白セルに配置され、セル C2 の乱数が再計算されます。 その後、対応する利益がセル C16 に記録されます。 次に、列セルの入力値 2 が空白セルに配置され、C2 の乱数が再び再計算されます。 対応する利益がセル C17 に入力されます。

セル B13 から C13:E13 に数式 AVERAGE(B16:B1015)をコピーすることで、各生産数量のシミュレートされた平均利益を計算します。 セル B14 から C14:E14 に STDEV(B16:B1015)という数式をコピーすることで、注文数量ごとにシミュレートされた利益の標準偏差を計算します。 F9 キーを押すごとに、注文数ごとに 1,000 回の需要がシミュレートされます。 40,000 枚のカードを生成すると、常に最大の予想利益が得られる。 したがって、40,000 枚のカードを生成するのが適切な決定である可能性があります。

リスクが決定に及ぼす影響      40,000 枚ではなく 20,000 枚のカードを生成した場合、予想利益は約 22% 減少しますが、(利益の標準偏差によって測定される) リスクはほぼ 73% 減少します。 したがって、リスクを極めて危険にさらす場合は、20,000 枚のカードを生成する方が適切な決定になる可能性があります。 ついでに、10,000 枚のカードを作成すると、常に 10,000 枚のカードを作成する場合、残り物なしですべてのカードを販売する、という理由で、常に 0 枚の標準偏差があります。

平均利益の信頼区間      この状況で質問する自然な質問は、真の平均利益が下がる期間は 95% ですか? この間隔は、平均利益の95% 信頼区間と呼ばれる。 シミュレーション出力の平均に対する 95% の信頼区間は、次の数式で計算されます。

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

40,000 のカレンダーが注文された場合の平均利益が $56,687 ~ $62,589 であるのは 95% です。

1. GMC の販売店は、2005 年の環境に対する需要は、通常、平均 200、標準偏差 30 で分散すると考える。 Envoy を受け取るコストは 25,000 ドルで、Envoy を 40,000 ドルで販売しています。 フル価格で販売されていないすべての Envoy の半分は、30,000 ドルで販売できます。 200、220、240、260、280、または 300 Envoys の注文を検討しています。 彼は何人注文する必要がありますか?

2. 小さなスーパーマーケットは、毎週注文する必要がある People の雑誌の部数を決定しようとしている。 ユーザーに対する需要 は、 次の個別のランダム変数によって管理されていると考える。

   需要	確率
   15	0.10
   20	0.20
   25	0.30
   30	0.25
   35	0.15

3. このスーパーマーケットでは、People のコピーごとに 1.00 ドルを支払い、1.95 ドルで販売しています。 未売の各コピーは 0.50 ドルで返されます。 注文する必要がある People の部数は何部ですか?