事業の年間の損益分岐点を把握して経営に活かすことについて、こちらの記事に書きました↓
損益分岐点（break even point）とは、売上高が費用と等しくなるポイントのことです。
費用は変動費と固定費に分けられます。
事業の1年間の業績をベースにすれば、
1年間の売上高が年間の費用（変動費＋固定費）と等しくなるポイントが損益分岐点
ということになります。
そして、損益分岐点の時の売上高を損益分岐点売上高といいます。
1年間の売上高が損益分岐点売上高よりも大きい場合、利益が出ており黒字となっている状態です。
逆に、1年間の売上高が損益分岐点売上高よりも小さい場合、損失が生じており赤字となっている状態です。
さて、事業の業績から経営分析をするためにはこの損益分岐点売上高を計算する必要があるわけですが、Excel を使って計算することができます。
今日は、Excel を使って損益分岐点売上高を計算する方法を紹介します。
ゴールシークを使う方法、数式を使う方法の2つの方法についてお話しします。
1、損益分岐点の例
会社の年間の成績を元に損益分岐点を出す前提とします。
次のような決算内容の場合を考えてみます。
【収益】
・売上高 19,500,000
【費用】
・仕入 10,050,000
・役員報酬 6,000,000
・給料 7,969,000
・交通費 123,000
・通信費 167,000
・交際費 214,000
・減価償却費 342,000
・賃借料 100,000
・保険料 123,000
・修繕費 232,000
・光熱費 215,000
・消耗品費 219,000
・雑費 99,000
・支払利息 222,000
費用については、仕入のみが変動費、他は固定費とします。
●変動費 10,050,000円
●固定費 16,025,000円
さて、年間の売上高を横軸にして「当期の売上高」と「年間の費用」をプロットしてざっくりと損益分岐点を求めるためのグラフを描くと次のようになります。
損益分岐点は、年間の売上高が年間の費用（固定費 + 変動費）と等しくなるポイントです。
損益分岐点での売上高を「損益分岐点売上高」といいます。
この例では、当期の売上高は損益分岐点売上高よりも低く赤字となっています。
さて、当期の成績を元に次年度は黒字を出すべく売上高の目標を立てたいわけですが、そのためにも損益分岐点売上高がいくらか把握する必要があるわけです。そして損益分岐点売上高を超える売上高で目標設定をすべきでしょう。
以下で損益分岐点売上高の額がいくらになるか計算したいと思います（グラフを見ればだいたいは分かりますけどね）。
2、ゴールシークを使って損益分岐点売上高を計算する方法
まずゴールシークを使う方法について説明します。
次のような表を作っておきます。
黄色で色付けしたセルに数値（売上高、固定費、変動費）を入力し、青で色付けしたセルに目的とする「損益分岐点売上高」を表示させる仕様です。
「売上高−変動費」を計算するセル「C12」「D12」には引き算の数式を入力してあります。
C12
D12
セル「C6」で変動費の傾き（変動費 / 売上高）を計算しています。
「変動費の傾き」をaとして、変動費の線を数学的に y = a・x とすると次の図のようになります。上記 Excel シートではこれを数式にしているということですね。
セル「D11」には、売上高が変化した際の変動費を求める式を入力してあります。上の図でいう変動費の数式 y = a・x と同じ計算をしているということですね（aは変動費の傾き、xは年間の売上高）。
ゴールシークの使い方を説明します。
まず、「データ」タブの「What-if 分析」から「ゴールシーク」を選択します。
すると以下のボックスが出てくるので、入力が必要な3箇所を埋めます。
「数式入力セル」「変化させるセル」にはセルを選択して入力し、「目標値」には数値を直接入力します。
アウトプットとしてほしい数値は、「変化させるセル」の数値です。
今回で言えば、損益分岐点は「売上高－変動費＝固定費」となるポイントなので､「売上高－変動費」の「D12」を「数式入力セル」に入れて、その値が「目標値」である固定費 16,025,000と一致するように「変化させるセル」である「売上高」の「D10」を変化させます。
これらに入力して「OK」を押すと、「数式入力セル」の値が「目標値」と一致するように「変化させるセル」の値が変化して行くのです。
そして、「数式入力セル」である「D12」が目標値 16,025,000 に達したら以下のようなボックスのメッセージがでます。
この時の「変化させるセル」である「D10」の値が､損益分岐点売上高となるのです。
3、数式を使って損益分岐点売上高を計算する方法
もう1つ､数式を使って損益分岐点を計算する方法もあります。
横軸の「年間の売上高」を x
縦軸を y
とすると
・売上高は y = x
・固定費+変動費は y = a・x + b
というように数式で表すことができます。
傾きa は「変動費の傾き」
切片b は「固定費」
となります。
そして、損益分岐点は a・x + b = x となるポイントです。
a・x + b = x をいう数式をいじると最終的に、xは
傾きa ：「変動費の傾き」
切片b ：「固定費」
の2つで表せます。
ここで求めた x の数式をセル「D10」に埋め込んであげるのです。
具体的には上の数式の aをセル「C6」､bをセル「C5」として以下のように入力します。
こうしておくだけで、後は黄色のセルにそれぞれ当期の成績である「固定費」「売上高」「変動費」を入力すれば損益分岐点売上高が計算されます。
4、まとめ
数式を使うやり方の方が手間がかからないですね。
ただ、ゴールシークを使う方法は今回のケースのみならず､他でも使えるので紹介しました。
これらのやり方を是非活用して頂きたいです。