読者の未来に、少しでも役立つネタを配信するブログ。

エクセルで簡単に数値のシミレーション!感度分析のやり方

      2018/01/28


エクセル感度分析_記事導入画像

エクセルで数値のシミレーションをしたい方、感度分析をやりたい方に、読んでほしいブログです。

どうも、みらしん(@miraisyndrome)です。

エクセルで利益予測などをシミレーションするとき、いくつかのシナリオに分けて予測するのって、大変ですよね?

そんなときに、エクセルで感度分析をおこなうと、いくつもの数値シミレーションを簡単に出すことができます。

私はこの感度分析のやり方を「外資系投資銀行のエクセル仕事術」という本で覚えたので、今回はエクセルでできる感度分析のやり方について、紹介していきたいと思います。

スポンサーリンク

感度分析とは?

感度分析とは、ある要素が変わったときに、最終的な結果がどう変わるのか、分析する方法になります。

「感度分析」でググると1番上に出てくるグロービス大学院の定義では、以下のように書いてありました。

感度分析とは、計画や予想を立てる際に、ある要素(変数・パラメータ)が現状あるいは予測値から変動したとき、最終的な利益やキャッシュフローなどにどの程度の影響を与えるかを見る分析。

文字にするとこのような感じですが、実際のケースでは、以下のような場面で使われます。

感度分析を使用するケース

以下の収支計画を元に、感度分析を使用するケースを見ていきます。

エクセル感度分析_画像1

この収支計画では、2017年度から2020年度までの「売上」「費用」「利益」の計画がまとまっています。

そして、「売上」は「販売数」×「単価」で構成されており、「販売数」の「成長率」によって、販売数が変化しています。

エクセル感度分析_画像2

「費用」については、「人件費」+「賃料」で構成されており、さらに「人件費」は「従業員数」×「1人あたりの人件費」で構成されています。

こちらも、「従業員数」の変化によって、「人件費」が変化しています。

エクセル感度分析_画像3

※この収支計画の見方については、以下のブログに詳しく書いております。
【参考】プロから学ぶ!エクセルで見やすい表を作成する方法
見方がわからない場合、参考にしてくだい。

現時点の収支計画では、2020年度の「利益」が450万になっていますが、2020年度の「(販売数の)成長率」と「従業員数」が変わると、「利益」はどのように変化するのでしょうか?

エクセル感度分析_画像4

このようなシミレーションをしたいときに、感度分析を利用します。

エクセルで感度分析のやり方を知らないと、「(販売数の)成長率」や「従業員数」の数値をポチポチ変えて、「利益」の数値を一つ一つ見ていくことになります。

エクセルで感度分析のやり方を知っていると、「(販売数の)成長率」「従業員数」の変化が「利益」にどのように影響するのか、以下のように一目でわかるようになります。

エクセル感度分析_画像5

エクセルを使った感度分析のやり方

前振りが長くなりましたが、エクセルを使った感度分析のやり方を説明しています。

最初に、感度分析の表を作るための準備をします。

感度分析の目的がわかるようにタイトルをつけておきます。(今回は「2020年度の収益シミレーション」)

エクセル感度分析_画像6

シミレーションしたい1つ目の項目と数値を、以下のように「列」側に入力します。(今回は「(販売数の)成長率」)

エクセル感度分析_画像7

現在の収支計画では、2020年度の「(販売数の)成長率」は「100%」と入力されていますが、シミレーションしたい数値「70%〜130%」を列に入力します。

エクセル感度分析_画像8

今度は、シミレーションしたい2つ目の項目と数値を、以下のように「行」側に入力します。(今回は「従業員数」)

エクセル感度分析_画像9

現在の収支計画では、2020年度の「従業員数」は「70」と入力されていますが、シミレーションしたい数値「50〜100」を列に入力します。

エクセル感度分析_画像10

そして、以下のように、「列」と「行」の交わるセルに「=”シミレーション結果で出力したい項目の現在値が入ったセル”」(今回は2020年度の「利益」のセル「I13」)という計算式を入れます。

エクセル感度分析_画像11

エクセル感度分析_画像12

いよいよ、ここから感度分析をおこなっています。

以下のように、今まで入力した「列」「行」の数値部分から、表を作るようにセルを選択します。

エクセル感度分析_画像13

セルを選択したまま、メニューの「データ」タブにある「What-If分析」を選択します。

エクセル感度分析_画像14

「データテーブル」を選択します。

エクセル感度分析_画像15

ポップアップが出てきますので、「行の代入セル」の項目には、行側に入力した項目の現在値が入ったセルを選択して入力します。(今回は従業員数のセル「$I$10」)

エクセル感度分析_画像16

また、「例の代入セル」の項目には、列側に入力した項目の現在値が入ったセルを選択して入力します。(今回は成長率のセル「$I$6」)

エクセル感度分析_画像17

「OK」を押します。

エクセル感度分析_画像18

シミレーション結果の数値が出力された表ができあがりました。

エクセル感度分析_画像19

このままだと見にくいので、見やすくなるように仕上げていきます。

シミレーション結果の数値に、メニューのホームタブにある「桁区切りのスタイル」を適用します。

エクセル感度分析_画像20

エクセル感度分析_画像21

「列」と「行」の交わるセルにある数値は、見えなくても良い数値なので、文字色を白にしておきます。

エクセル感度分析_画像22

エクセル感度分析_画像23

エクセル感度分析_画像24

最後に、罫線を引いて感度分析の完成です。

エクセル感度分析_画像1

今回の感度分析では、2020年度の「(販売数の)成長率」が「70%〜130%」、また、「従業員数」が「50〜100」になった場合、2020年度の収益がどのように変わっていくのか、一目でわかるようになりました。

この感度分析があれば、例えば「(販売数の)成長率」が「80%」になってしまう場合でも、「従業員数」を「60」で抑えられば、同じくらいの利益を出すことができる、みたいなシミレーションができるようになります。

エクセル感度分析_画像25

以上、エクセルを使った感度分析のやり方でした。

今後、エクセルを使って数値のシミレーションする機会があれば、ぜひ感度分析を使ってみてください。

また、私が感度分析を覚えるきっかけになった「外資系投資銀行のエクセル仕事術」の本には、他にも「エクセルの表を見やすく作る方法」や「使えるショートカットコマンドの一覧」などが載っています。

エクセルをよく使う人には、すごく役立つ本だと思うので、ぜひ読んでみてくだい!

何かあれば、ツイッターメールまで。

#こちらのブログもオススメです。
初心者でも簡単!エクセルの作業を自動化するマクロの作り方
プロから学ぶ!エクセルで見やすい表を作成する方法

 - IT