【Excel】売上や利益に対する感応度分析を行い、議論を円滑に進める

2023年7月23日

会社の事業計画を作成していると、複数の変数を調整して、売上や利益の妥当性を確認する必要があります。

例えば、以下のような売上の計画で、青字の価格と販売台数を変えて利益がどのように変わるか見てみたいときがあります。”価格だけ”あるいは”販売台数だけ”を変えて、利益を見ることは、数字を少し変えればできます。一方で、価格と販売台数の両方を変えて利益をいくつかのパターンで見るのは、少し数字を変えただけでは難しいです。

計画や予想を立てる際に、ある変数が変化したとき、売上や利益にどの程度の影響があるかを見る分析を感応度分析といいます。Excelでは、上記のような複数の変数の変化に対してシミュレーションを行うことが簡単にできるので、その方法を紹介したいと思います。

シミュレーションパターンの考え方

では、まず現在の売上計画からシミュレーションパターンを考えていきます。

シミュレーションパターンの考え方に明確に決まったものはありませんが、変化させたい変数の上下で一定の幅を取ります。感応度分析の目的は、変化の影響を見ることなので、厳密な数値を設定する必要はありません。

ここでは、売上計画に対して価格と販売台数を次のように変化させ、利益に対する影響を見たいと思います。

価格：現在の価格2,000千円に対して、100千円ずつ減らす＆増やす
販売台数：現在の販売台数1,000台に対して、100台ずつ減らす＆増やす

上記の内容を右側の表に整理すると次の右側のようになります。横軸に価格のパターン、縦軸に販売台数のパターンが入っています。パターンはシミュレーションをしながら、必要に応じて増やしていきます。

この表を作成して、感応度分析の準備は完了です。

利益のシミュレーション

では、実際に感応度分析を行っていきます。

今回は利益に対する影響をシミュレーションしたいので、利益のセルをI5に参照します。

ちなみに、利益についてはきちんと計算で算出していないと、正しくシミュレーションができません。左側の表については次のような計算式となっています。

項目	計算式
売上	価格×販売台数（=F6*F7）
価格	（ベタ打ち）
販売台数	（ベタ打ち）
材料費	1台当たり材料費×販売台数（=F9*F7）
1台当たり材料費	（ベタ打ち）
固定費	（ベタ打ち）
利益	売上-材料費-固定費（=F5-F8-F10）

感応度分析を行う表を選択し、「データ」タブ⇒「What-If分析」⇒「データテーブル」を選択します。

行の代入セルに横軸の「価格（F6）」、列の代入セルに縦軸の「販売台数（F7）」を選択し、「OK」を押します。

すると、次のようにそれぞれの変数のパターンでの利益が算出されます。オレンジでハイライトしたセルL8が元々の価格2,000千円、販売台数1,000台のパターンです。

活用方法

感応度分析により、様々なパターンがシミュレーションできれば、色々な仮説を立てることができます。

例えば、価格を上げる方が良いか、価格を下げて販売台数を増やす方が良いか、という論点に対して、下表で比較すると価格2,200千円で800台売れるよりも、価格1,800千円で1,200台売れる方が利益は大きいことが分かります。では、価格の上昇・低下に対して販売台数はどのように影響するか、過去の売上データなどから検証すれば、最適な価格設定ができます。