エクセルで数値のシミレーションをしたい方、感度分析をやりたい方に、読んでほしいブログです。
エクセルで利益予測などをシミレーションするとき、いくつかのシナリオに分けて予測するのって、大変ですよね？
そんなときに、エクセルで感度分析をおこなうと、いくつもの数値シミレーションを簡単に出すことができます。
私はこの感度分析のやり方を「外資系投資銀行のエクセル仕事術」という本で覚えたので、今回はエクセルでできる感度分析のやり方について、紹介していきたいと思います。
スポンサーリンク
感度分析とは？
感度分析とは、ある要素が変わったときに、最終的な結果がどう変わるのか、分析する方法になります。
「感度分析」でググると1番上に出てくるグロービス大学院の定義では、以下のように書いてありました。
感度分析とは、計画や予想を立てる際に、ある要素（変数・パラメータ）が現状あるいは予測値から変動したとき、最終的な利益やキャッシュフローなどにどの程度の影響を与えるかを見る分析。
文字にするとこのような感じですが、実際のケースでは、以下のような場面で使われます。
感度分析を使用するケース
以下の収支計画を元に、感度分析を使用するケースを見ていきます。
この収支計画では、2017年度から2020年度までの「売上」「費用」「利益」の計画がまとまっています。
そして、「売上」は「販売数」×「単価」で構成されており、「販売数」の「成長率」によって、販売数が変化しています。
「費用」については、「人件費」＋「賃料」で構成されており、さらに「人件費」は「従業員数」×「1人あたりの人件費」で構成されています。
こちらも、「従業員数」の変化によって、「人件費」が変化しています。
※この収支計画の見方については、以下のブログに詳しく書いております。
【参考】プロから学ぶ！エクセルで見やすい表を作成する方法
見方がわからない場合、参考にしてくだい。
現時点の収支計画では、2020年度の「利益」が450万になっていますが、2020年度の「（販売数の）成長率」と「従業員数」が変わると、「利益」はどのように変化するのでしょうか？
このようなシミレーションをしたいときに、感度分析を利用します。
エクセルで感度分析のやり方を知らないと、「（販売数の）成長率」や「従業員数」の数値をポチポチ変えて、「利益」の数値を一つ一つ見ていくことになります。
エクセルで感度分析のやり方を知っていると、「（販売数の）成長率」「従業員数」の変化が「利益」にどのように影響するのか、以下のように一目でわかるようになります。
エクセルを使った感度分析のやり方
前振りが長くなりましたが、エクセルを使った感度分析のやり方を説明しています。
最初に、感度分析の表を作るための準備をします。
感度分析の目的がわかるようにタイトルをつけておきます。（今回は「2020年度の収益シミレーション」）
シミレーションしたい1つ目の項目と数値を、以下のように「列」側に入力します。（今回は「（販売数の）成長率」）
現在の収支計画では、2020年度の「（販売数の）成長率」は「100%」と入力されていますが、シミレーションしたい数値「70%〜130%」を列に入力します。
今度は、シミレーションしたい2つ目の項目と数値を、以下のように「行」側に入力します。（今回は「従業員数」）
現在の収支計画では、2020年度の「従業員数」は「70」と入力されていますが、シミレーションしたい数値「50〜100」を列に入力します。
そして、以下のように、「列」と「行」の交わるセルに「=”シミレーション結果で出力したい項目の現在値が入ったセル”」（今回は2020年度の「利益」のセル「I13」）という計算式を入れます。
いよいよ、ここから感度分析をおこなっています。
以下のように、今まで入力した「列」「行」の数値部分から、表を作るようにセルを選択します。
セルを選択したまま、メニューの「データ」タブにある「What-If分析」を選択します。
「データテーブル」を選択します。
ポップアップが出てきますので、「行の代入セル」の項目には、行側に入力した項目の現在値が入ったセルを選択して入力します。（今回は従業員数のセル「$I$10」）
また、「例の代入セル」の項目には、列側に入力した項目の現在値が入ったセルを選択して入力します。（今回は成長率のセル「$I$6」）
「OK」を押します。
シミレーション結果の数値が出力された表ができあがりました。
このままだと見にくいので、見やすくなるように仕上げていきます。
シミレーション結果の数値に、メニューのホームタブにある「桁区切りのスタイル」を適用します。
「列」と「行」の交わるセルにある数値は、見えなくても良い数値なので、文字色を白にしておきます。
最後に、罫線を引いて感度分析の完成です。
今回の感度分析では、2020年度の「（販売数の）成長率」が「70%〜130%」、また、「従業員数」が「50〜100」になった場合、2020年度の収益がどのように変わっていくのか、一目でわかるようになりました。
この感度分析があれば、例えば「（販売数の）成長率」が「80%」になってしまう場合でも、「従業員数」を「60」で抑えられば、同じくらいの利益を出すことができる、みたいなシミレーションができるようになります。
以上、エクセルを使った感度分析のやり方でした。
今後、エクセルを使って数値のシミレーションする機会があれば、ぜひ感度分析を使ってみてください。
また、私が感度分析を覚えるきっかけになった「外資系投資銀行のエクセル仕事術」の本には、他にも「エクセルの表を見やすく作る方法」や「使えるショートカットコマンドの一覧」などが載っています。
エクセルをよく使う人には、すごく役立つ本だと思うので、ぜひ読んでみてくだい！
#こちらのブログもオススメです。
初心者でも簡単！エクセルの作業を自動化するマクロの作り方
プロから学ぶ！エクセルで見やすい表を作成する方法