Excelでモンテカルロ・シミュレーションを構築する方法：データ駆動型の意思決定のための完全ガイド

モンテカルロ・シミュレーションは、財務予測、プロジェクト管理、在庫分析などのシナリオにおいて、不確実性をモデル化しリスクの影響を評価するための統計手法です。数千件のシナリオを自動生成することで結果のばらつきを把握でき、不確実性のある状況でもデータに基づいた意思決定が可能になります。この記事では、Excelを使ってモンテカルロ・シミュレーションを実装する手順を詳しく解説します。

まずは実装する課題を定義しましょう。ここでは小規模なECサイトの売上データを例に、成果に影響を与える主要指標をもとに利益をシミュレーションします。

ステップ1：Excelでデータをセットアップする

各列に以下の主要指標を入力します。

• A列： シミュレーション番号（1、2、3、…、1000）
• B列： 販売価格（RAND関数で単価をシミュレーション）
• C列： 販売数量（販売個数をシミュレーション）
• D列： 単位あたり原価（原価の乱数）
• E列： 売上高（販売価格×販売数量で計算）
• F列： 総コスト（固定費と変動費に基づいて計算）
• G列： 利益（売上高から総コストを差し引いて計算）

ステップ2：Excel関数でランダムな入力値を生成する

各変数の乱数を生成するには、ExcelのRAND、RANDBETWEEN、NORM.INV関数を使用します。

シミュレーション番号

「フィル」の連続データ機能を使うと番号を簡単に入力できます。まずA2セルに「1」を入力しておくと、列をドラッグした際に自動的に連番が生成されます。

• セルA2を選択し、「1」を入力します。

販売価格

• 平均$30、標準偏差$4.5の正規分布を使用します。
• セルB2に以下の数式を入力します。

数式：

=NORM.INV(RAND(), 30, 4.5)

この数式は、平均30を中心に標準偏差4.5で正規分布する値を生成します。

販売数量

• 特定商品の販売数量として、100〜550の範囲内でランダムな値を生成します。
• セルC2に以下の数式を入力します。

数式：

=RANDBETWEEN(100, 550)

単位あたり原価

• $10〜$18の一様分布から乱数を生成します。
• セルD2に以下の数式を入力します。

数式：

=RAND()*(18-10)+10

売上高

• 売上高は「販売数量×販売価格」で計算します。
• セルE2に以下の数式を入力します。

数式：

=C2*B2

総コスト

総コストは固定費・変動費・生産コストの合計として計算します。セルF2に以下の数式を入力してください。

数式：

=($D2 * $C2) + 1000 + (E2 * 0.1)

この数式は、固定費・変動費・生産コストを組み合わせて総コストを算出します。

• ($D2 * $C2)： 変動費です。生産数量（C2）と単位原価（D2）に基づいてコストを計算します。
• 1000： 固定費として加算される一定額で、生産量に関係なく発生します。
• (E2 * 0.1)： 売上高に10%の率を掛けて生産コストを計算します。

利益

• 利益を計算するには、以下の数式をセルG2に入力します。

数式：

=E2-F2

この数式は、売上高から総コストを差し引きます。

出力結果：

すべての数式と各変数の計算結果を確認できます。後ほどこれらの数式をコピーして、全シミュレーション分を生成します。

ステップ3：数式をコピーして全シミュレーションを作成する

• B列〜G列の数式を下方向へドラッグして、各シミュレーション用のランダムデータを生成します。

• 各行が1回のシミュレーションに対応します。

ステップ4：結果を分析する

1000件のシミュレーションを生成したら、その結果を分析しましょう。以下の統計指標を使うことで、ビジネスの収益ポテンシャルを評価できます。

平均利益

以下の数式で平均利益を計算します。期待値を把握するのに役立ちます。

数式：

=AVERAGE(G2:G1001)

最小利益

以下の数式で最小利益を特定します。最悪のシナリオを示します。

数式：

=MIN(G2:G1001)

最大利益

以下の数式で最大利益を特定します。最良の結果を示します。

数式：

=MAX(G2:G1001)

損失発生確率

利益がマイナスとなったシミュレーションの割合を計算します。

数式：

=COUNTIF(G2:G1001,"<0")/COUNTA(G2:G1001)

これにより、どの程度の損失が発生しうるかを把握できます。

利益の95%信頼区間（CI）

95%信頼区間の下限と上限を計算します。

下限：

=PERCENTILE(G2:G1001, 0.025)

この数式は2.5パーセンタイルを計算します。つまり、利益データの2.5%がこの値を下回ることを意味します。

上限：

=PERCENTILE(G2:G1001, 0.975)

この数式は97.5パーセンタイルを計算します。つまり、利益データの97.5%がこの値を下回ることを意味します。

出力結果：

利益のヒストグラムを作成する

• 利益の列を選択します。
• 挿入タブ >> グラフグループ >> ヒストグラムを選択します。

利益列のヒストグラムが表示されます。

利益の分布を見ることで、利益がどのように散らばっているかを視覚的に把握できます。

データテーブル機能を使ったモンテカルロ・シミュレーション

データテーブル機能を利用してモンテカルロ・シミュレーションを実行することもできます。シミュレーション番号の入力には「連続データ」機能を使いましょう。

• 列見出し付きのデータテーブルを作成します。
• シミュレーション番号列のセルA2に「1」を入力します。
• ホームタブ >> フィル >> 連続データを選択します。

• 連続データダイアログボックスで以下を設定します。
  • 範囲： 列
  • 種類： 加算
  • 増分値： 1
  • 停止値： 1000
  • OKをクリックします。

• A2:G1001の表全体を選択します。
• データタブ >> What-If 分析 >> データテーブルを選択します。

• データテーブルダイアログボックスで以下を設定します。
  • 行の代入セル： 空白のままにします。
  • 列の代入セル： セルA2を選択します。
  • OKをクリックします。

データテーブルによって1000件のシミュレーション値が一括生成されます。

ヒントとベストプラクティス

• 変数の上限・下限にはセル参照を使うと、後から簡単に更新できます。
• 計算方法を手動に設定しましょう。そうしないとEnterキーを押すたびにデータが再計算されます。セルを再計算したい場合はF9キー（再計算）を使用します。
• より正確な予測を行うには、最低でも1,000〜10,000回のシミュレーションを実行しましょう。
• 数万回規模のシミュレーションが必要な場合は、VBAの方が効率的に処理できます。

まとめ

以上の手順に従えば、Excelでモンテカルロ・シミュレーションを実装できます。これは不確実性のもとで起こりうる結果を予測するための非常に汎用性の高い手法です。ExcelのRANDBETWEEN、RAND、NORM.INV関数を活用すれば、リスクを分析し意思決定の質を向上させる堅牢なモデルを構築できます。モンテカルロ・シミュレーションは、財務リスクの分析やプロジェクト日程の最適化など、幅広い場面で活用できる強力なツールです。