Excelでできることはいろいろあります。
わたしもExcelはまだまだわからないことが多いです。日々勉強ですね。
単価がいくらだと何人に来てもらえば利益が出るかを一瞬で知りたい
以前の記事でExcelでゴールシーク機能を使って、分析する方法をご紹介しました。
[blogcard url=”https://www.gou-blog.com/profit/”]
[blogcard url=”https://www.gou-blog.com/goalseek/”]
ただ、これを１つ１つやるのは変化させたい数字が複数あれば大変です。
この作業を何回もやる必要があります。
そういった場合にはExcelの「データテーブル」という機能が便利です。
例えば、単価と客数の２つの数字が変わると、利益がいくら出るのか？といったことを知りたい場合に使います。
感応度分析というものです。
この「月間の損益」を使って実際にやってみます。
Excelで感応度分析に挑戦
実際にやってみます。今回の変数を「単価」と「数量」にします。
まずは分析用の表を作成
売上単価を横軸、１日当たりの客数を縦軸として分析用の表をつくります。
金額の幅はモデル値を最低の数字としないで、モデル値より低いものも、高いものも両方設定すると幅広い検証ができます。
私は単価と人数の変数は、わかりやすいようにフォントの色を変えています。
単価はとりあえず500円～1,000円まで、１日当たりの客数は50人～100人で設定してみました。後で検証しやすいように、モデルと同じシートに作成するのをオススメします。
利益のセルを分析用の表の左上にコピー
次に答えが知りたい値（今回の場合は利益）を分析用の表の左上（L3のセル）に利益のセル（G17）を参照させます。（式：＝G17）
利益の238（千円）がL3のセルに表示されました。
これで準備は整いました。
データテーブルで感応度分析
ここで、データテーブルを使います。感応度分析の対象になる表を対象範囲として選択し、
「データ」タブ→「What-if分析」から「データテーブル」を選択します。Alt→A→W→Tでもできます。
データテーブルの画面が表示され、「行の代入セル」と「列の代入セル」の指定を聞いてきます。
行は横軸なので「単価」、列は縦軸なので「客数」を指定します。
データテーブルが完成。更に見やすくするために工夫を
すると、分析の表に数字が一瞬で埋まりました。モデル値は単価700円の客数100人で、利益が238千円でした。
客数が10人増えて110人になれば、単価は同じでも352千円の利益になるということがわかります。
一方で単価が100円下がったら20人客数が増えても、165（千円）の利益しか出ないというわけです。
単価の幅を50円ずつにしたかったと思ったら青い部分を変更すればいいだけです。こちらの方が現実的でしょう。
変数を変えても利益の部分は自動的に再計算されます。
更に見やすくするために、もうひと工夫しましょう。
条件付き書式設定を使います。「セルの強調表示ルール」→「指定の値より小さい」を選択します。
Alt→H→L→H→Lでも大丈夫です。
ゼロより小さいものは赤になるようにします。するとOKを押す前でもプレビュ－表示されます。
マイナス、つまり利益でなく赤字になった部分はすべてセルが赤色になったのが確認できます。
更に条件付き書式設定を使います。もう一度「セルの強調表示ルール」を選択し、今度は「指定の値より大きい」を選択します。
Alt→H→L→H→Gでも大丈夫です。
今度はゼロより大きなものは緑になるようにします。
これで黒字と利益の分岐がハッキリしました。見る人にとってみやすい資料を作成することを意識しましょう。
[blogcard url=”https://www.gou-blog.com/form/”]
後は黒の238（千円）は見栄えよくするために白フォントにして見えなくしましょう。
こうすれば、利益の出るところと赤字になるところが一目瞭然になりました。
例えば、750円に単価を上げれば、10人減少してもほぼ同額の利益が出るとわかります。
750円で客数100人であれば、363（千円）の利益となり、125（千円）も利益が増えます。
こうすることで、より今後の方針を検討しやすくなるのではないでしょうか。
ゴールシーク機能を使って検算してみよう。
最後に以前記事にしたゴールシーク機能を使って簡単な検算をしてみます。
[blogcard url=”https://www.gou-blog.com/goalseek/”]
「データ」タブ→「What-if分析」から「ゴールシーク」を選択します。Alt→A→W→Gでもできます。
先ほど触れた利益が363になるような単価は？というゴールを目指します。
利益は363（千円）になるには、単価が750円である必要があるとの答えが出ました。
分析の表にある数字で確認する場合にはこれで検証ができますが、表にないような50円単位でない単価や10人単位でない数字を答えとして欲しい場合にはゴールシークで計算する、もしくは分析表の価格や人数の単位を変更することで確認しましょう。トライ・アンド・エラーで色々やってみましょう。
【編集後記】
昨日は、法人２件の決算業務で１日事務所。６月決算のお客様を２件ほぼ完成させてからお盆休みに入りたいので、頑張ります。
