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

• モンテカルロ シミュレーションを使用するユーザー

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

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

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

• 会社カードグリーティングによって、生成するカードの数はどのように決まりますか?

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

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

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

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

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

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

• Proctor と Gamble は、シミュレーションを使用して外国為替リスクをモデル化し、最適にヘッジします。

• Sears はシミュレーションを使用して、サプライヤーから注文する各製品ラインの単位数 (たとえば、今年注文する必要がある Dockers パンツのペアの数) を決定します。

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

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

セルに数式 =RAND() を 入力すると、0 ~ 1 の値を想定する可能性が高い数値が取得されます。 したがって、時間の約 25% は、0.25 以下の数値を取得する必要があります。時間の約 10% は、少なくとも 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、および 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 以上

需要のシミュレーションを示すために、次のページの図 60-2 に示すファイル Discretesim.xlsx を確認します。

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

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

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

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

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

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

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

需要のあるカードよりも多くのカードを生成する場合、残りのユニット数は生産から需要を差し引いた数に等しくなります。それ以外の場合、ユニットは残されません。 セル C10 の廃棄コストは、 式 unit_disp_cost*IF (生産>需要、生産需要、0) で計算します。 最後に、セル 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] をクリックします。 セル A16 から始まる列 A には、1 から 1000 の数値が入力されます。

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

これで、Excel をだまして、生産数量ごとに 1,000 回の需要のイテレーションをシミュレートする準備ができました。 テーブル範囲 (A15:E1014) を選択し、[データ] タブの [データ ツール] グループで [What If Analysis] をクリックし、[データ テーブル] を選択します。 双方向データ テーブルを設定するには、行入力セルとして生産数量 (セル 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で分配されると考えています。 エンボイを受け取るコストは25,000ドルで、彼は40,000ドルでエンボイを販売しています。 完全な価格で販売されていないすべてのエンボイの半分は$30,000で販売することができます。 彼は200、220、240、260、280、または300エンボイを注文することを検討しています。 彼はいくつ注文する必要がありますか?

2. 小さなスーパーマーケットは、週に何枚の雑誌People注文するかを決定しようとしています。 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のコピーはいくつですか?