Excelを使って逆算で利益をシミュレーション ゴールシーク・データテーブル・利益感度を計算するマクロ

Excelマクロ入門

どうやって利益を出すか?それを考えるにあたって、ヒントになるようなことができないか?

Excelを使ってシミュレーションをしてみるという手があります。

どうやって利益をだすか?

「これからどうするか?」を考えるために、これまでの実績の数字をうまく使いたいところです。

ここで、「利益をどうやって出そうか?」を考える場合には、必要な利益を決めて、逆算してみるのがおすすめです。

通常、損益計算書は売上→原価→粗利益→固定費(人件費など)→利益の順番で表示され、上から見ていくわけです。

それとは逆に、先に必要な利益を出しておき、一番下から上に逆算して必要な売上がいくらなのか?を求めるわけです。

具体的には、利益と固定費を足して、粗利益を計算し、粗利益を粗利率(粗利益/売上)で割って売上をけいさんするというように。

ただ、それで必要な売上が分かったとしても…いったいどうすればいいの?

たとえば売上が120万円だとすると、

  • 5,000円×240人=1,200,000円
  • 6,000円×200人=1,200,000円

どちらも同じ120万円になるわけですが、決算書にあるのは120万円の表示だけ。

数量の情報というのは、決算書には載っていないのです。

もし、単価や数量の情報があれば、もう少し具体的に考えることができそうです。

そこで数の情報を入れて、計算してみてはどうだろう?というのが今回のお話です。

数量のデータは、仕事をしていれば、販売情報などで手元に持っているはずですから。

 

補足
今回の事例では「売上=単価×数量(それ以外は考慮外)」としています。

ゴールシークで逆算して計算する

前述した逆算をExcelを使ってやってみます。今回のモデルはこういったデータです。(税金や在庫は考慮外としています。)

C13セルにある利益について、ゴールシークという関数を使います。

利益は現在12万円。

「今後、銀行に返済しないといけないから、利益は20万円くらいは欲しいよなぁ。」という場合、いくらの単価で売ればいいか?というのを計算してみます。

値を変化させることができるのは、数式が設定されていないセルだけ。

逆にいえば、直接入力されている値は変化させることができるわけです。売上単価に限らず、販売数量、仕入単価、固定費も。

ということで、ゴールシークを使ってやってみましょう。

リボンのデータから

→What-If分析→ゴールシークを選びます。もしくはAlt→A→W→Gと順番にタッチします。

数式入力セルは利益のセルC13を、目標値は20万円とし、変化させるセルは、売上単価のセル、C6としてO.Kをクリックすると、

カウンターが動き出し、単価が5333.33…円のときに利益が20万円。

ということで。他の条件が変わらなければ、利益を20万円にするためには340円ほど売上単価が上がるような手を打つ必要があるといえそうです。

売上だけが注目されがちですが、実際には利益に影響するものには、

  • 仕入単価がいくら下がったら?
  • 数量がいくつ増えたら?
  • 固定費がいくら減ったら?

という視点もあるわけで、今回の事例の場合、売上単価以外にも3つの要素があるといえます。

じゃあ、どこから手をつけたらいいのか?そのあたりをつけたいものです。

そこで、利益感度というのを計算してみます。感度が小さいほど、ちょっとした動きで利益へのアタリが大きいということになります。

利益感度の計算式

利益感度(%)=想定要素/現状要素×100

逆に利益への影響が小さなところを見直しても、それほど利益への影響はないということになります。

今回の事例に戻ると、売上単価は、現在5,000円の売値が5,333円になると利益が20万円になるということでしたので、

この場合の利益感度は、107%となり、7%の動きで目標利益の20万円になるということです。

他の要素も同じようにゴールシークをつかって計算してみました。

100に近いほど、影響が高いということになります。

というわけで、いちばん影響が大きいのは7%の売上単価、逆にいちばん影響がなさそうなのは、17%下げてようやく利益20万円になるとされた仕入単価という結果。

まずは売上単価を中心に考えると、目標利益に到達できそうです。それぞれをちょっとずつ触るというのもいいかもしれません。

どれか1つだけしか触れないということでもないですしね。

データテーブル

データテーブルという機能があり、単価×数量についてどうやって利益を出そうかを考えることもできます。

列と行に要素になる数量を並べておき、

クロスする位置にこっそり利益をセットしておきます。(今回は12万円)そのまま範囲指定して、

データ→What-If分析→データテーブルとクリック(Alt→A→W→T)し、

変化させる行と列を指定すると、

どの単価、どの個数で利益がいくらになるかを一覧で確認することができます。

さらに条件付き書式を設定すると、どの組み合わせなら20万円以上の利益(緑のセル)がでて、どの手を打つとキケン(赤のセル)かもひと目でわかります。

単価が増やせれば利益がそれ以上に増えることということを改めて実感できます。

いろいろと数字を利用することで、考えるきっかけにすることができますが、そもそもの利用するデータの精度が大事です。

となるとやはり毎月経理をすることはかかせません。

今回の記事が「これからどうする」を考えるひとつのきっかけになればうれしいです。

損益分岐点は4つある 利益感度分析でどこから手をつけるかを考えてみよう | GO for IT 〜 税理士 植村 豪 Official Blog

数をこなせないフリーランスが安売りできない理由もよくわかりますので、手を動かして試してみていただければ。

利益感度分析をするマクロ

ということで、利益感度をゴールシークを使って計算してみました。

今回の処理を4パターンやろうとすると、何度も同じキーをタッチしたり、値を貼り付けたりする必要があり、けっこう疲れます。(わたしは)

そこで、今回の流れをマクロで書いてみました。手を動かしてやったことを順番に書いています。

セルC2の利益を目標の利益にしておき、ボタンをクリックすると、G列の「想定」に結果が反映されるというしくみです。F列の現状の欄には、現在の実績数値を入力しておきます。

何度も使う場合は、こういったボタンをつくっておき、ボタン1つで計算してくれます。同じ処理を繰り返すことを減らす工夫は、小さくても考えておきたいものです。

マクロのボタンの設定方法はこちらで、

Excelマクロ超入門 マクロを使ってもらうときにやっておきたいこと | GO for IT 〜 税理士 植村 豪 Official Blog

マクロを書くための設定はこちらの記事にまとめています。

Excel VBA超入門 マクロを書いてみたい人集合! 仕事ときどきアウトプットがおすすめ | GO for IT 〜 税理士 植村 豪 Official Blog

「これからどうする?」を考える上での参考になればうれしいです。


【編集後記】
昨日はカットと法人のお客様と決算関係と今後についての打合せでした。

【昨日の1日1新】
※「1日1新」→詳細はコチラ
亀屋芳広 おはぎ
ワンタイムPW