会計人のためのExcel活用術(36)

会計データの編集で押さえておきたい計画シミュレーション(PL②)

Share on twitter
Share on facebook
Spreadsheet,Document,Information,Financial,Start,Up,Concept

前回に引き続き、Excelを使った損益計画のシミュレーションについて見ていきます。
今回はグラフを使った図解の仕方です。

<利益シミュレーション>
 前回、変動費と固定費(損益分岐点売上高)の考え方を使って、Excel表での利益シミュレーションについて見ました。

 目標売上高 = (目標利益+固定費)/粗利益率

の算式をExcel表に組み込んで、実際にExcel上で数字(利益、固定費、粗利益率)を動かしたときに、売上高がどのくらい変化するかを具体的に見ていきました。

 今回は、この表を図解について見ていきます。
 表だけでシミュレーションするのでもよいのですが、特に現場部門の人と目標値のすり合わせを行う場合には、図があった方が理解してもらいやすくなります。
 Excelであれば、グラフ機能を使って簡単に図解することが出来ますので、表とあわせて図も横に付けてしまいましょう。

 表の横に、図を作成するための次のようなフィールド(セル範囲)を用意します。
 各セルの値は必ず横の数字を参照するようにします。
 そうすると表の数字が変化するのに合わせて、図の形も変化するようになります。

<損益図の図解>

これを「積み上げ縦棒」でグラフ化します。
範囲を選択して「挿入」→グラフの中の「積み上げ縦棒」を選びます
そうすると単純な棒グラフが出てくるので、向きを変えてあげます。
グラフのどこでもいいので右クリック→「データの選択」。
「行と列の切り替え」ボタンを押して、グラフの向きを変更します。

 表の項目が上から下に積みあがったグラフ(変動費が下、粗利益が上)になっているので、項目の表示位置を変更して、グラフ間のスペースをなくします。
 項目の表示位置を変更するのは先ほどと同じく「データの選択」の左側の枠で↓↑ボタンを使います。

 グラフ間のスペースをなくすにはどの棒グラフでもいいので、右クリック→「データ系列の書式設定」で、「要素の間隔」を「0%」に変更します。


 そうすると、損益のハコ図が出来ますので、あとは好きに色を変更したり、形を整えたり、ラベル表示を変更すれば完成です。

<図をつけたシミュレーションイメージ>
 たとえば、前回使った例と同じく、利益「200,000」が必要なケースで、この粗利益率と固定費から逆算すると必要な売上高は「3,200,000」と計算されます。
 このとき、粗利益率や固定費の値を動かして、売上高「3,000,000」以下で利益「200,000」を実現できないかのシミュレーションを行います。
 前回は表だけで数字の動きを見ましたが、さらに図があると、分かりやすくなります。

 たとえば、固定費の中の「その他経費」を「200,000 → 150,000」へ減らしてみたときに売上高は「3,200,000 → 3,000,000」へ変化します。
  図も表の値を参照しているので、数値の変化に応じて形が変わります。

 「粗利益率」でも同様に「25%→27%」に改善できた場合に、売上高が「3,000,000」以内で、目標利益を達成できますが、このときの図の変化も一緒に提示します。

 もちろん表だけでシミュレーションしてあげるのもよいですが、Excelであれば、ついでに図を横につけることも簡単にできます。
 特に会計数値にあまり慣れていない人にとっては、横に図があるだけで、わかりやすさや印象が全然違ってきます。
 表を使って数字を動かしながら、それに応じた変化を図でも見せるようにすれば、事業部門の人もイメージがわきやすくなります。
 手軽にできるシミュレーションなので、実現可能な計画値に落としこめるように事業部門の人をサポートできるようにしましょう。

 次回は、BS計画のところを見ていきます。
 BSは会社の中では経理の人くらいしか見ていないところになります。
 特に会計事務所の立場として、計画上でどのようなサポートが出来るかをExcelを使いながら見ていきましょう。

関連記事

この投稿者のその他の記事