Hatena::ブログ(Diary)

エクセル&グラフのティップス倉庫

2013-12-02

回帰分析を活用して損益分岐点売上高を計算する

今回はエクセルの回帰分析の機能を用いて、実際の企業の損益分岐点売上高を計算してみましょう。題材としては日産自動車の財務諸表を使います。(損益分岐点の考え方に自信がない方は、こちらをご参照ください)

f:id:dwashizu:20131202145211j:image:w640

損益分岐点売上高を計算するための手順は、下記の3ステップになります。

  1. 変動比率、固定費額を算出する
  2. 算出された変動比率、固定費額をもとに、事業の収支モデルを作る
  3. 収支モデルにおいて損益トントンになるような売上高をゴールシークで算出する

※ゴールシークの使い方についてはこちらをご参照ください。


f:id:dwashizu:20131202150358j:image:w360:rightプロセスとして文字に書くと簡単そうなのですが、一体変動費率はいくらなのか、固定費はいくらなのかはなかなか分かりにくいものです。

一般的に使われるのは、損益計算書上の勘定科目を見ながら、変動費要素、固定費要素を分けてみるというアプローチ。右のチャートは、平成24年度および25年度の日産自動車の連結損益計算書ですが、たとえばこれを参考にして、売上原価、サービス保証料、販売諸費などのコスト項目は売上成績によって連動することが想像されるので「変動費」とする、というやり方ですね。ただし、製造原価と言っても、製造機械などは固定費的要素も少なからず含まれていますから、なかなか正確に把握することができません。そこでもう一つのアプローチとして、回帰分析を使って損益分岐点売上高を算出する方法をご紹介します。

回帰分析とは、簡単に言えば過去の事象の中から法則性を見つけ数式化すること。つまり過去の業績から変動比率、固定費を算出するアプローチです。
つまり【コスト=売上高x変動比率+固定費】という式に過去の売上高、コストを当てはめて、帰納的に変動比率、固定費を算出するやり方ですね。具体的には平成17年から平成25年にかけての9年間にわたっての売上高・コストを二つの変数として取り上げ、散布図を描き、そこに近似直線をひいてあげると、それが売上とコストとの関係性になってきます。その方程式(Y=aX+b)にあてはまるaの部分が変動比率、bの部分が固定費になります(上記赤字で書いた式の通り)。

ここまでできれば後はステップ2と3を踏んであげれば損益分岐点売上高は計算できます。具体的な操作方法については下記の動画でご紹介してますので、ご参照ください。
D

質問質問 2013/12/20 15:45 回帰式が、y=1.2X-AAAのようになるケースはどう考えれば良いのでしょうか。普通に近似曲線を取ると、変動比率が1を超えて、固定費がマイナスになってしまうパターンです。

dwashizudwashizu 2013/12/20 16:02 補正R2が信頼たるレベルであると仮定して、原理的には変動比率が1を超えることはなく、固定費=切片がマイナスになることはないですので、おそらくそのケースは回帰分析的には正しいけど実際にはリアリティのない数字ということで済ますことになりそうです。少なくとも説明に耐えられるものではないということでしょうね。
そういうことが起こりうる理由としては、標本の精度が低い、すなわち途中でビジネスモデルが大きく変わり、連続性がないなどの事が考えられるのではと思います。まあ、詳しいデータを見ていないので何とも言えませんけども・・・

質問質問 2013/12/22 23:16 ありがとうございました。データ数の問題かもしれません。

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

トラックバック - http://d.hatena.ne.jp/dwashizu+tips/20131202/1386023434