Excel ガイド:データ駆動型の意思決定のためのモンテカルロ シミュレーションの構築

モンテカルロ シミュレーションは、財務予測、プロジェクト管理、在庫分析などのシナリオにおける不確実性をモデル化し、リスクの影響を評価するために使用される統計手法です。数千のシナリオを生成するモンテカルロ シミュレーションは、不確実性の下でデータに基づいた意思決定を行うのに役立つ変動性を評価する方法を提供します。この記事では、Excel を使用してモンテカルロ シミュレーションを実装します。

モンテカルロ シミュレーションを実装するための問題を定義しましょう。結果に影響を与える主要な指標に基づいて利益をシミュレーションする小規模な e コマース データセットを考えてみましょう。

ステップ 1:Excel でデータを設定する

主要なメトリクスを列に挿入します。主要な指標は次のとおりです。

• 列 A: シミュレーション番号 (1、2、3、…、1000)。
• 列 B: 販売価格 (RAND 関数を使用して単位あたりの価格をシミュレートします)。
• 列 C: Units Sold (販売されたユニット数をシミュレートします)。
• 列 D: ユニットあたりのコスト（原価計算の乱数）
• 列 E: 収益を計算します (単位と費用を掛け合わせます)。
• 列 F: 総コスト（固定費と変動費に基づく）
• 列 E :利益の計算 (収益と総コストを差し引く)。

ステップ 2:Excel 関数を使用してランダムな入力を生成する

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

シミュレーション番号

[シリーズ] オプションを使用して数値を挿入できます。最初はシミュレーションNo.欄に1を入れます。列をドラッグすると、シミュレーション番号が自動生成されます。

• セル A2 を選択し、1 を挿入します。

販売価格:

• 平均 $30 の正規分布を使用する 標準偏差は $4.5 です。 .
• セル B2 に次の数式を挿入します。

式:

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

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

販売単位:

• ポアソン分布を使用してランダムなポアソン値を生成する
• セル C2 に次の数式を挿入します。

式:

この式は、特定の商品の販売単位として 100 ～ 550 の範囲のランダムな値を生成します。

ユニットあたりのコスト:

• $10 から均一な配分を生成します 18 ドルまで .
• セル D2 を選択し、次の数式を挿入します。

式:

収益:

• 収益を計算するには、販売個数と販売価格を掛けます。
• セル E2 に次の数式を挿入します。

式:

総費用:

固定費、変動費、生産費の合計として総コストを計算します。

• セル F2 を選択し、次の数式を挿入します。

式:

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

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

• ($D2 * $C2): それは変動費です。この部分では、生産されたユニット数 (D2) と単価 (C2) に基づいて総コストを計算します。
• 1000: 固定費として追加され、生産レベルに関係なく一定の金額が追加されます。
• (E2 * 0.1): 収益に 10% の率を適用して制作コストを計算します。

利益:

• 利益を計算するには、次の式を挿入します。

式:

この式は、総コストから収益を差し引きます。

出力:

すべての式と変数の結果を確認できます。後ですべての数式をコピーしてシミュレーションを生成できます。

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

• 列 B、C、D、E、F の数式を下にドラッグします。 そしてG 各シミュレーションのランダム データを生成する

• 各行は 1 つのシミュレーションを表します。

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

1000 のシミュレーションが生成されると。シミュレーションの結果を分析してみましょう。次の統計的尺度を使用して、ビジネスの潜在的な利益を評価できます。

平均利益 :

次の式を挿入して平均利益を計算します。

式:

平均利益は、期待される結果を得るのに役立ちます。

最低利益:

次の式を挿入して、最小利益を特定します。

式:

最小利益は最悪のシナリオを示します。

最大利益:

最大利益を特定するには、次の式を使用します。

式:

最大の利益が得られると、可能な限り最良の結果が得られます。

損失の確率 :

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

式:

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

これは、どのくらいの損失が発生する可能性があるかを把握するのに役立ちます。

利益の 95% 信頼区間 (CI) :

95% CI の下限と上限を計算します。

下限:

=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_Anaization から>> データテーブルを選択します .

• データテーブル内 ダイアログボックス;
  • 行入力セル: 空白のままにしておきます。
  • 列入力セル: A2 セルを選択します。
  • [OK] をクリックします。 .

データ テーブルは 1000 個のシミュレーション値を生成します。

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

• 変数制限のセル参照を使用すると、簡単に更新できます。
• 計算モードを設定します。 マニュアルへ 。それ以外の場合、Enter を押すたびにデータが更新されます。 。セルを計算するには、今すぐ計算 (F9) を使用します。 オプション。
• より正確な予測を得るには、少なくとも 1,000 ～ 10,000 回のシミュレーションを実行します。
• 何万ものシミュレーションを実行する必要がある場合、VBA を使用するとより効率的に処理できます。

結論

すべての手順に従うことで、Excel でモンテカルロ シミュレーションを実装できます。これは、不確実性の下で起こり得る結果を予測する多用途の方法です。 Excel の RANDBETWEEN、RAND、NORM.INV 関数を使用すると、リスクを分析し、意思決定を改善するための堅牢なモデルを構築できます。モンテカルロ シミュレーションは、財務リスクの分析、プロジェクトのタイムラインの最適化などに役立ちます。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!