データテーブルで感応度分析 利益シミュレーションをひと目で把握しよう

  • URLをコピーしました!

Excelでできることはいろいろあります。

995dd54b-1853-4100-96f0-e84cfc7e60aa

わたしもExcelはまだまだわからないことが多いです。日々勉強ですね。

目次

単価がいくらだと何人に来てもらえば利益が出るかを一瞬で知りたい

以前の記事でExcelでゴールシーク機能を使って、分析する方法をご紹介しました。

[blogcard url=”https://www.gou-blog.com/profit/”]

[blogcard url=”https://www.gou-blog.com/goalseek/”]

ただ、これを1つ1つやるのは変化させたい数字が複数あれば大変です。

この作業を何回もやる必要があります。

そういった場合にはExcelの「データテーブル」という機能が便利です。

例えば、単価と客数の2つの数字が変わると、利益がいくら出るのか?といったことを知りたい場合に使います。

感応度分析というものです。

キャプチャ

この「月間の損益」を使って実際にやってみます。

Excelで感応度分析に挑戦

実際にやってみます。今回の変数を「単価」と「数量」にします。

まずは分析用の表を作成

売上単価を横軸、1日当たりの客数を縦軸として分析用の表をつくります。

金額の幅はモデル値を最低の数字としないで、モデル値より低いものも、高いものも両方設定すると幅広い検証ができます。

私は単価と人数の変数は、わかりやすいようにフォントの色を変えています。

キャプチャ3

単価はとりあえず500円~1,000円まで、1日当たりの客数は50人~100人で設定してみました。後で検証しやすいように、モデルと同じシートに作成するのをオススメします。

利益のセルを分析用の表の左上にコピー

次に答えが知りたい値(今回の場合は利益)を分析用の表の左上(L3のセル)に利益のセル(G17)を参照させます。(式:=G17)

キャプチャ3

利益の238(千円)がL3のセルに表示されました。

キャプチャ4

これで準備は整いました。

データテーブルで感応度分析

ここで、データテーブルを使います。感応度分析の対象になる表を対象範囲として選択し、

キャプチャ5

「データ」タブ→「What-if分析」から「データテーブル」を選択します。Alt→A→W→Tでもできます。

キャプチャ6

データテーブルの画面が表示され、「行の代入セル」と「列の代入セル」の指定を聞いてきます。

行は横軸なので「単価」、列は縦軸なので「客数」を指定します。

キャプチャ6

データテーブルが完成。更に見やすくするために工夫を

すると、分析の表に数字が一瞬で埋まりました。モデル値は単価700円の客数100人で、利益が238千円でした。

キャプチャ6

客数が10人増えて110人になれば、単価は同じでも352千円の利益になるということがわかります。

一方で単価が100円下がったら20人客数が増えても、165(千円)の利益しか出ないというわけです。

単価の幅を50円ずつにしたかったと思ったら青い部分を変更すればいいだけです。こちらの方が現実的でしょう。

変数を変えても利益の部分は自動的に再計算されます。

キャプチャ7

更に見やすくするために、もうひと工夫しましょう。

条件付き書式設定を使います。「セルの強調表示ルール」→「指定の値より小さい」を選択します。

Alt→H→L→H→Lでも大丈夫です。

キャプチャ8

ゼロより小さいものは赤になるようにします。するとOKを押す前でもプレビュ-表示されます。

マイナス、つまり利益でなく赤字になった部分はすべてセルが赤色になったのが確認できます。

キャプチャ9

更に条件付き書式設定を使います。もう一度「セルの強調表示ルール」を選択し、今度は「指定の値より大きい」を選択します。

Alt→H→L→H→Gでも大丈夫です。

今度はゼロより大きなものは緑になるようにします。

キャプチャ10

これで黒字と利益の分岐がハッキリしました。見る人にとってみやすい資料を作成することを意識しましょう。

[blogcard url=”https://www.gou-blog.com/form/”]

キャプチャ11

後は黒の238(千円)は見栄えよくするために白フォントにして見えなくしましょう。

キャプチャ12

こうすれば、利益の出るところと赤字になるところが一目瞭然になりました。

例えば、750円に単価を上げれば、10人減少してもほぼ同額の利益が出るとわかります。

750円で客数100人であれば、363(千円)の利益となり、125(千円)も利益が増えます。

こうすることで、より今後の方針を検討しやすくなるのではないでしょうか。

ゴールシーク機能を使って検算してみよう。

最後に以前記事にしたゴールシーク機能を使って簡単な検算をしてみます。

[blogcard url=”https://www.gou-blog.com/goalseek/”]

「データ」タブ→「What-if分析」から「ゴールシーク」を選択します。Alt→A→W→Gでもできます。

キャプチャ13

先ほど触れた利益が363になるような単価は?というゴールを目指します。

キャプチャ14

利益は363(千円)になるには、単価が750円である必要があるとの答えが出ました。

キャプチャ15

分析の表にある数字で確認する場合にはこれで検証ができますが、表にないような50円単位でない単価や10人単位でない数字を答えとして欲しい場合にはゴールシークで計算する、もしくは分析表の価格や人数の単位を変更することで確認しましょう。トライ・アンド・エラーで色々やってみましょう。

 

【編集後記】
昨日は、法人2件の決算業務で1日事務所。6月決算のお客様を2件ほぼ完成させてからお盆休みに入りたいので、頑張ります。

この記事が気に入ったら
いいね または フォローしてね!

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!
目次