モンテカルロシュミレーションのSimVoiでエクセルに追加される関数について

おはようございます。
今日はモンテカルロシミュレーションのお話です。

はじめに

FP＆Aスペシャリストの鷲巣さんのTwitterで紹介されていたツール「SimVoi」を購入し、使い方を調べようと思ったが、英語でも日本語でもあまりテキストでの紹介記事が見当たらなかった。

外国のYouTuberで紹介されている方がいたが、結果、鷲巣さんのYouTubeに戻って拝聴したが、説明が数段わかりやすかった。

今回は、私がつまずいたところをnoteにメモしておこうと思う。

ちなみに僕が使っているのはこのSimVoiという$79買い切りのアドインです。不確実因子を感度分析して、トルネードチャートで視覚化するSensitというアドインを合わせても$129で買い切りなので、割とお買い得かも。https://t.co/7p2utLhKvm

4/4

— 鷲巣大輔｜FP&Aスペシャリスト (@WashizuD) April 12, 2023

モンテカルロシミュレーションとは

モンテカルロシミュレーションって何ですか？という方もいると思いますのでまずはこちらの説明をググっておきました。

モンテカルロシミュレーションは、不確実な事象の起こり得る結果を予測する数学的手法です。コンピュータープログラムはこの方法を使用して、過去のデータを分析し、選択したアクションに基づいて将来のさまざまな結果を予測します。例えば、新製品の最初の月の売上を見積もる場合、モンテカルロシミュレーションプログラムに過去の売上データを与えることができます。このプログラムは、一般的な市況、製品価格、広告予算などの要因に基づいて、さまざまな売上高を見積もります。

出所：https://aws.amazon.com/jp/what-is/monte-carlo-simulation

みたいな感じで、僕らがエクセルでよくつくるビジネスケースやモデルに対して様々な分布に基づいてたくさんの乱数をデータに突っ込んだ結果の期待値を返してくれる方法です。

分布の代表例は正規分布で、製品の品質管理や、従業員の評価、標準テストのスコア分析などで使われます。正規分布を使うことで、品質のばらつきやスコアの平均値と標準偏差を考慮して、適切な基準を設定することができます。また対数正規分布も使われることが多く、経済データや所得分布、株価の変動、製品の売上や需要予測などで使われています。対数正規分布は、正の値を持つデータで非対称性がある場合に適しており、大きな値が発生する可能性を考慮した分析ができます。

こういった確率の考え方を考慮して計算を行ってくれる優れた数学的なアプローチです。

カジノが好きな人は、モンテカルロという言葉はおなじみで、モナコの美しい景色があたまに浮かびます（笑）

SimVoiをつかってみよう

さて、このアドインソフトのインストールはとっても簡単です。購入後アドインの設定手順に従えばとても簡単にエクセル画面にしたのようになります。

できましたか？

次に行うのは、SimVoi-311-Exampleの実行です。購入時に取得できるエクセルファイルです。（ダウンロードで３つのファイルの取得できます。）

専用関数は１７個

アドインをインストールすると、使えるようになる特殊な１７個の関数があります。私もこれに戸惑ったので今日はその話を書きます。なおマニュアル「10.2 USING SIMVOI FUNCTIONS」に以下の通り示されています。

• RandBeta(alpha,beta,,[MinValue],[MaxValue])

• RandBinomial(trials,probability_s)

• RandBiVarNormal(mean1,stdev1,mean2,stdev2,correl12)

• RandCumulative(value_cumulative_table)

• RandDiscrete(value_discrete_table)

• RandExponential(lambda)

• RandInteger(bottom,top)

• RandLogNormal(Mean,StDev)

• RandNormal(mean,standard_dev)

• RandPoisson(mean)

• RandSample(population)

• RandTriangular(minimum,most_likely,maximum)

• RandTriBeta(minimum,most_likely,maximum,[shape])

• RandTruncBiVarNormal(mean1,stdev1,mean2,stdev2,correl12,[min1],[max1],[min2],[max2])

• RandTruncLogNormal(Mean,StDev,[MinValue],[MaxValue])

• RandTruncNormal(Mean,StDev,[MinValue],[MaxValue])

• RandUniform(minimum,maximum)

基本的に、これらの関数はこの括弧の中で示されるものを別セルで用意しておいてリンクをはっていくことで変化する要素にこの範囲で乱数を発生させてくれるものです。

それぞれ、マニュアルからの簡単な翻訳を載せておきます。

1. RandBeta: アルファとベータパラメーターを指定してベータ分布の乱数を生成します。アルファとベータは、分布の形状を制御するパラメーターであり、0より大きい任意の値を取ることができます。オプションで最小値と最大値を指定することができます。

2. RandBinomial: 試行回数と成功確率を指定して二項分布の乱数を生成します。試行回数は正の整数であり、成功確率は0から1までの実数です。

3. RandBiVarNormal: 平均、標準偏差、および相関係数を指定して二変量正規分布からランダムなペアの変量（x,y）を返します。

4. RandCumulative: 累積度数表（累積度数関数）からランダムな変量（x）を返します。

5. RandDiscrete: 離散的な確率分布表からランダムな変量（x）を返します。

6. RandExponential: パラメータλ（レート）で指定された指数分布からランダムな変量（時間または距離）を返します。

7. RandInteger: 指定された範囲内の整数をランダムに生成します。

8. RandLogNormal: 平均と標準偏差を指定して対数正規分布の乱数を生成します。最小値と最大値をオプションで指定することができます。

9. RandNormal: 平均と標準偏差を指定して正規分布の乱数を生成します。最小値と最大値をオプションで指定することができます。

10. RandPoisson: 平均値を指定してポアソン分布の乱数を生成します。

11. RandSample: 指定された集合からランダムにサンプリングします。

12. RandTriangular: 最小値、最大値、および最頻値を指定して三角分布の乱数を生成します。

13. RandTriBeta: 最小値、最大値、および最頻値を指定して三角ベータ分布の乱数を生成します。オプションで形状パラメーターも指定することができます。

14. RandTruncBiVarNormal: 平均、標準偏差、相関係数、および各変量の下限と上限を指定して切り捨て二変量正規分布からランダムなペアの変量（x,y）を返します。

15. RandTruncLogNormal: 平均と標準偏差、および下限と上限を指定して切り捨て対数正規分布からランダムな変量（x）を返します。

16. RandTruncNormal: 平均と標準偏差、および下限と上限を指定して切り捨て正規分布からランダムな変量（x）を返します。

17. RandUniform: 最小値と最大値の間で一様分布の乱数を生成します。

鷲巣さんのYoutubeでは、下の１番目のみが使われています。同梱のエクセルサンプルでは、加えて二つが紹介されています。きっとこの３つが基本関数だろうから、今回はこの３つに絞って紹介しておきます。（利用が進めば、リライトしていきます）

１，RandTruncNormal(Mean,StDev,[MinValue[MaxValue])
２，RandTriangular(minimum,most_likely,maximum)
３，RandDiscrete(value_discrete_table)

代表的？な３つの関数紹介

１，RandTruncNormal(Mean,StDev,[MinValue],[MaxValue])

関数は、基本的には乱数を発生されるのですが利用するエクセルモデルに応じて関数を使い分けましょう。この１については順に、Mean/平均、StDev/標準偏差、minValue/最小値、Max/最大値と指定されています。このSimVoiにおいては変数となる箇所はこの関数が入っていることが必要になるようです。直接値を直接打ち込むことも構いません、なるべく別セルから参照するようにした方が応用が効いてよいでしょう。最小と最大についてはオプションなので指定しなくても構いません。平均と偏差の範囲で乱数をお任せしてしまうことができます。

用途例：需要数、売上数などで用いることができます。

２，RandTriangular(minimum,most_likely,maximum)

次にこの関数は、サンプルで利用されています。順にminimum/最小値、most_likely/最頻値、maximum/最大値が指定できます。この中央にくる値についてはmode/最頻値を用いてサンプルで示されています。表現が面白いですよね。

用途例：サンプルでは変動費単価をこちらを用いてつかっていました。少ないSKUであれば頻度の高い単価の商品があればこの関数でよいかもしれません。

３，RandDiscrete(value_discrete_table)

この関数は下のようなテーブル構造のようなデータを持っているときに有効です。

説明： 離散的な確率分布からランダムな値を返してくれます。この関数は、可能な各離散値xとそれに対応する確率P(X=x)で指定される。そのため2列の範囲（左列に値、右列に対応する確率）を表すテーブルである必要があります。

用途例：サンプルでは固定費をパターン分けするような使い方がされています。

この３つの関数つかってみましょ。とりあえず今回はこれまで。