今回からは、Excelを使った計画数値のシミュレーションについて見ていきます。
Excelは「表計算ソフト」というその名の通り、ある条件に基づいて計算した結果を生成するのが得意なツールです。
たとえば、「前期比～％増」とか「粗利益率～％」の場合など様々な条件設定をして、数値がどうなるかを簡単にシミュレーションすることができます。
四則演算の単純な計算式を設定する程度なので、専門知識も必要なく、手軽に数値の検証を行うことができます。
今回は損益（PL）のシミュレーションについて見ていきます。
＜PLの変数を押さえる＞
我々の業務上、損益の計画数値自体を作成するというよりも、どちらかというと、事業部門などで作成された計画値に対して何らかの意見や検証を求められる、という場面の方が多いと思います。
前期比何％増とか、PLの計画値のつくり方はいくつかありますが、検証やシミュレーションを行う、という前提に立つならば、管理会計でよく使う変動費と固定費（損益分岐点売上高）の考え方がわかりやすく、数値の検証もしやすい形になります。
管理会計の本で必ず紹介されている下記の式です。
損益分岐点売上高 ＝ 固定費／（１－変動費率）
「売上高 － 変動費 ＝ 粗利益」とすれば、この式は
損益分岐点売上高 ＝ 固定費／粗利益率
と書き換えることができます（「売上高 － 変動費 ＝ 粗利益」の両辺を「売上高」で割れば、１－変動費率 ＝ 粗利益率 となる）。
さらにこれに目標利益分を加味すると、次の式になります。
目標売上高 ＝ （目標利益＋固定費）／粗利益率
この式からわかるように、「利益」、「固定費」、「粗利益率」が定まれば、売上高は自ずと決まるという方程式に単純化しています。
つまり、「利益」、「固定費」、「粗利益率」の３つを変数として、これらの設定をどう変えていくかで、ＰＬの形（売上高）が決まってくることになります。
例えば、提示されてきた計画値に対して、目標とする利益率に達していない場合に、必要となる利益額を設定した上で、前期並みの固定費かつ粗利益率といった仮定を置いた場合に、どれくらいの売上高が必要かを逆算します。
その売上高が実現可能なものなのか、難しい場合は、変数の設定を動かしてみて、売上高が目標として現実的かどうかをシミュレーションします。
＜目標利益と固定費を決めて、粗利益率（前期実績等）から必要な売上高を逆算＞
言葉だけで見ているよりも、実際にExcel上での数値の動きで見た方がわかりやすいので、Excelで見ていきましょう。
使うのは、単純に次のような表だけです。
上記の箱図を表にしただけのものです。損益分岐点分析の考え方に基づいて、黄色くした「粗利益率」、「固定費」、「（営業）利益」の３つを変数として、あとの「粗利益」、「売上高」、「変動費」は自動計算されるように次のような計算式を設定しておきます。
【設定する計算式】
● 粗利益 ＝ SUM(C5:C8) ← 営業利益と固定費の合計額
● 売上高 ＝ IF(F1=0,””,C4/F1) ← 粗利益÷粗利益率（粗利益率の値がゼロの時は計算しないようにしつつ）
● 変動費 ＝ IF(C2=””,””,C2-C4) ← 売上高－粗利益の値
＜PLのシミュレーションイメージ＞
たとえば、この例のように利益「２００,０００」が会社目標として必要だったとします。
前期実績の粗利益率「２５％」と今期の固定費見込「６００,０００」をもとにすると必要となる売上高は「３,２００,０００」と決まってきます。
ただし、事業部から上がってきた今期の見込売上高としては、どう頑張っても「３,０００,０００」が上限で、「３,２００,０００」という数字は現場の意見としては実現不可能だと言われたとします。
（こういう話がよくあると思います）
このときに、実際にExcel上で、黄色くした数値（変数）を少し動かしてみて、売上高がどう（どれくらい）変わるかをシミュレーションしてみます。
このうち、利益「２００,０００」はマストで動かさないと仮定すると、あと動かせる変数は「固定費」のいずれかと「粗利益率」になってきます。
たとえば、固定費の中の「その他経費」を「２００,０００ → １５０,０００」へ減らしてみたら売上高がどうなるかを、実際にExcel上でいじって、その変化を現場の人に見せます。
そうするとこの例の場合だと、固定費を「５０」減らしただけで、売上高は「３,２００,０００ → ３,０００,０００」へ変化します。
現場の人の感覚としては、経費を少し減らしたくらいで売上高がそんなに大きく動くというイメージがないことが多いので、言葉だけで「経費を少し減らせませんか」とかやるよりも、実際にExcelを使った簡単な数字の動きを見せながら説明した方が、話は早いです。
特にこの例のように、「粗利益率」が「２５％」とあまり高くないケースでは、少し固定費を削っただけでも売上高が割と大きく影響を受けます。
会計データを扱っていて、損益分岐点の考え方が分かっている人であれば、当たり前の数字の動きかもしれませんが、会計にあまり慣れていない人にとっては、この数字の動きが感覚的にわかりません。
なので、実際にExcel上で数字がどう動くかを見せてしまった方が理解してもらえます。
もう１つの変数である「粗利益率」の方をも同様に簡単にシミュレーションできます。
たとえば、「平均単価を少し上げる」、あるいは「外注費原価をもう少し下げる」ということで、「２５％→２７％」に改善できた場合のシミュレーション結果が下記です。（「その他経費」は従前の「２００,０００」に戻したとして）
これでも売上高「３,０００,０００」以内で、目標利益を達成できる計画になります。
提出されてきたPL計画値に対して、現場部門の人と目標値のすり合わせを行う場合には、このような簡単なExcel表を作ってしまって、実際の数字の動きを見せながら、実現可能なラインを確認していった方が、机上であれこれ話すよりも手っ取り早いです。
単純なExcelの計算式だけで済みますので、気軽に試してみてください。
次回も引き続き、PL計画のところを見ていきます。
表だけのシミュレーションでもいいのですが、ここにさらに図があると、特に現場の人は理解が進みます。
Excelですと、図もすぐに簡単に挿入できるところが利点です。
次回はここに図解をプラスする方法を見ていきます。
