感度分析とは？エクセルでプロジェクトの安定性や危険度を分析する

 

感度分析とは「ある要素」が変わったときに、
それに伴う結果がどのように変化するか？を分析する方法です。

未来を予測することは不可能であり、
何かを予想する際に絶対的な解を算出するのも困難です。
そこで、複数の解から一番フィットしそうな解を探し出すのです。

 

 

 

【感度分析とは】

▶︎感度分析の概要

感度分析は、結果にどの要因がどれくらい影響するか？
を数値で見る方法です。

トルネードグラフやスパイダーグラフでわかりやすく見せることができます。
この分析を使うと、事業の収益に大きく影響する要素を数値で知ることができ、
プロジェクトの管理に役立てることができます。

 

 

▶︎通常の感度分析

.

▶︎リスクの感度分析

 

 

 

 

＼ちょっと難しそう…と思ったら/

まずは専門家に相談してみる

 

 

 

【感度分析のやり方】

（単位：万円）

	基準値	売上変動		人件費変動		他コスト変動
変動率		＋10%	-10%	＋10%	-10%	＋10%	-10%
売上	100	110	90	100	100	100	100
人件費	30	33	27	40	20	30	30
他コスト	5	5	5	5	5	5.5	4.5
利益	65	72	58	55	75	64.5	65.5
差分		+7	-7	-10	+10	-0.5	-0.5

 

上記のように各要因が利益にどう影響するか？
を計算していきます。

この表から分かるように
利益への影響は「人件費」が最も大きく、「他コスト」が最も小さいことがわかります。
各要因の変動による影響を見ることで、どの要因を改善するべきか？？を把握することができるのです。

 

 

▶︎黒字の場合

• 「売上」がどれくらい下がると利益０か？
• 「人件費」がどれくらい増えると利益０か？
• 「他コスト」がどれくらい増えると利益０か？

 

 

▶︎赤字の場合

• 「売上」がどれくらい上がると赤字脱出か？
• 「人件費」がどれくらい減ると赤字脱出か？
• 「他コスト」がどれくらい減ると赤字脱出か？

 

 

▶︎Excelで感度分析！

 

 

（01）サンプルシミュレーションを作る

 

 

（02）「売上成長率」「人件費」を行・列に入力する

シミュレーションしたい数字をそれぞれ行・列に入力します。

• 成長率：列に入力⇨80〜120％
• 人件費：行に入力⇨20〜100万円

 

 

（03）行・列の交わるセルに利益を入れる

行・列が交わるセルに
シミュレーションで出したい結果の現在値が入ったセルと「＝」で結ぶ

※例だと利益の「70」と＝で結ぶ

 

 

（04）計算したい範囲を選択する

 

 

（05）「What-If分析」

「データ」→「What-If分析」

 

 

「データテーブル」選択

 

 

「行の代入セル」に行側に入力した要因の現在値のセルを入れる

※例だと人件費の「20」セルを選択する

 

 

「列の代入セル」に列側に入力した要因の現在値のセルを入れる

※例だと成長率の「100%」セルを選択する

 

 

OKを押すとシミュレーション結果が出ます。

 

 

（06）体裁を整え、分析する

分析しやすいように体裁を整えます。
ここは個人の好みですが、私の場合は

• 罫線を引く
• 色をつける
• マイナスは赤がつくようにする

程度です！

 

分析は、たとえば

「売上成長率」が80％になってしまった場合でも
「人件費」を20万円に抑えれば
成長率120％の場合と同じくらいの利益を出すこともできる

というようなシミュレーションが可能になります。

 

 

 

と思ったら

 

 

 

【感度分析の活用分野】

• 製造
• 研究開発
• 資源エネルギー

など

たとえば、品質に最も影響を及ぼす工程を特定して
改善策を考えることができるようになります。

 

ただ、実際のビジネスとなると不確実な要因が多いため
モンテカルロシミュレーションと併せて
採用されることが多いです。

 

 

 

【感度分析まとめ】

 

 

ここまで感度分析を
説明してきましたがいかがでしたか？？