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

スポンサードリンク




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

995dd54b-1853-4100-96f0-e84cfc7e60aa

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

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

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

損益分岐点を把握する第一歩 損益計算書では見えない「数量」を使って考えよう
商品やサービスが売れなくても値引き競争に巻き込まれるのは避けるべきです。特に中小企業は値引き競争に巻き込まれるべきではありません。値引き競争に巻き込まれると...
損益分岐点をExcel(エクセル)でつかんでみる ゴールシーク機能を使ってみよう
事業を継続する上で損益分岐点を知っておくことは重要です。損益分岐点を見る代表的なものに損益分岐点売上高があります。損益分岐点売上高とは利益がゼロになる売上を...

ただ、これを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

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

見やすい資料を提供することを心がける
税理士業をやっていると、「このアクションをした場合の効果はどのくらいか?」「決算説明資料」などといった資料を作成する場面がよくあります。 5/23 品川駅です...
キャプチャ11

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

キャプチャ12

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

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

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

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

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

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

損益分岐点をExcel(エクセル)でつかんでみる ゴールシーク機能を使ってみよう
事業を継続する上で損益分岐点を知っておくことは重要です。損益分岐点を見る代表的なものに損益分岐点売上高があります。損益分岐点売上高とは利益がゼロになる売上を...

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

キャプチャ13

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

キャプチャ14

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

キャプチャ15

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

 

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


スポンサードリンク




相続税申告・ひとりしごとをサポートします 植村豪税理士事務所

相続や贈与のことでお悩みの方、「決算書の数字が読めない」、「資金繰りを改善したい」、「クラウド会計を使ってみたい」というひとり社長やフリーランスの方のサポートに力を入れています。