◆◇◆◇◆◇◆◇◆◇◆◇◆◇◆◇◆◇◆◇◆◇◆◇◆◇◆◇◆◇

統計のエクセル計算

発行：エスオーエル株式会社
https://www.sol-j.co.jp/

連載「高鍋鮎美の三次元測定機って何なの？？」
2021年10月6日号　VOL.102

平素は格別のお引き立てを賜り、厚く御礼申し上げます。
X線CTスキャンによる精密測定やアプリケーション開発情報などをテーマに、
無料にてメールマガジンを配信いたしております。

◆◇◆◇◆◇◆◇◆◇◆◇◆◇◆◇◆◇◆◇◆◇◆◇◆◇◆◇◆◇



こんにちは。営業の高鍋鮎美です。


今月のメルマガも（前月の英語に続き）少し脱線しますが、
現在私が嵌まっている統計のエクセル計算について、
ご紹介したいと思います。


私は去年からオンラインのMBAコースを取っていて、
現在Corporate Financeという
会計や、株式、社債などに関する授業を履修しています。


会計学は営業職という事もあり多少やっていたのですが、
投資関連はMBAで初めて体系的に学び、
結構面白いなぁと思って自分でも少しずつ投資を始めたりしています。


その中で統計の計算もよく出てきます。

統計と言うと、弊社の場合、測定機の繰り返し精度を求める時に、
1σや3σを出す事が多いのですが、

いつも技術担当者が算出したのを見ているだけだったので、

金融の分野でも使われる事を知り、自分も関わるようになると、
やはり数学は応用範囲が広いなぁと、前より身近に感じるようになってきました。



私が習ったところだと、
例えば投資（株式投資でも、新事業への投資でも）をする時に、
リスクやリターンを計算して、どこにどの位投資するのかを判断する材料に
統計が使えるそうです。


一例を挙げると、
新事業への投資を計画していてマーケティングチームのリサーチ結果から、
以下のような予想が立てられたとします。


シナリオ　　　 可能性　　  リターン
Worst case    10%         -14%
Poor case      20%         -4%
Most case      40%         +6%
Good case     20%         +16%
Best case      10%         +26%



この情報から可能性の重み付けをして標準偏差を求め、
正規分布のグラフを作り、
他へ投資した場合と比較するという流れです。



Step 1：
まずは、Weighted averageという重みづけした平均を求めます。
これがExpected valueと言って、可能性を加味したリターンの平均値になります。

計算としては、
それぞれのcaseで可能性 x リターンをして、それを合計します。

10%*(-14%)+20%*(-4%)+40%*6%+20%*16%+10%*26%=6%

エクセルだとSUMPRODUCT関数を使えば一発です！



Step 2：
各リターンから先程出したWeighted averageを引きます。

Worst case:(-14%)-6%)=-20%
Poor case:(-4%)-6%=-10%
Most case:6%-6%=0%
Good case:16%-6%=10%
Best case:26%-6%=20%

これが偏差（Deviation）になります。



Step 3：
各偏差を二乗します。

Worst case:(-20%)^2=0.04
Poor case:(-10%)^2=0.01
Most case:(0%)^2=0
Good case:(10%)^2=0.01
Best case:(20%)^2=0.04



Step 4：
二乗した結果にそれぞれの可能性を掛けます。


Worst case:0.04*10%=0.004
Poor case:0.01*20%=0.002
Most case:0*40%=0
Good case:0.01*20%=0.002
Best case:0.04*10%=0.004


これを合計した0.012が分散（Variance）です。



Step 5：
分散をルートして標準偏差（Standard Deviation）を求めます。

(0.012)^(0.5)=10.95%

エクセルだとSQRT関数が使えます。



Step 6：
これで6%に対して1σが10.95%と求まったので、

6%-10.95%=-4.95%
6%+10.95%=16.95%


上記の範囲が1σ（発生確率約68%の範囲）だと分かります。



Step 7：
他に投資を検討している案件でも同じように計算して、
どこに投資するのが良いのか、
または全額一か所ではなく、投資自体もポートフォリオを組んで
複数の事業に分けて投資した方が良いのかを判断する材料になります。



因みに上記ではステップずつやったので大変になってしまいましたが、
エクセル関数を組み合わせればもっと効率よく計算出来ます。

今のところ私が使えているところでは、
Step 3まで頑張って、

=SQRT(SUMPRODUCT("可能性","Step 3の結果")で
標準偏差10.95%が求まりました。



もっと他にどのようなところで統計が使われているのか、
興味を持ってきた今日この頃です。



今週も最後までお読み頂きありがとうございました。


--
A.T