Excel 会計・経理

Excel(エクセル)で損益分岐点グラフを作成する方法

先日、お客様のところで損益分岐点について知りたいとのお話がありました。

言葉は聞いたことがあるし、何となくはわかるけど、損益分岐点売上高の計算となると少し難しいという方もいらっしゃると思います。

今回は、そのような方に向けて、損益分岐点についてのご案内と、簡単なものになりますが、Excelグラフの作成についてもご紹介したいと思います。

 

 

 

損益分岐点とは

管理会計上の損益分岐点とは、一般的に売上高を指すもので、簡単にいうと営業利益(本業の利益)が0(トントン)になる売上高のことをいいます。

売上高 ー 経費 = 0

になる売上高のことです。

つまり利益がマイナスにならないデッドラインですね。

 

経費の分類について

そして、管理会計では経費を「変動費」と「固定費」の2種類に分類します。

変動費

変動費とは、売上に比例するように増加したり減少したりするような経費のことをいいます。

例えば、商品の原材料や、仕入れにかかる費用、外注費などです。

業種によっては、人件費などが入る場合もあると思います。

 

固定費

固定費とは、売上の増減に関係なく一定に発生する費用のことをいいます。

例えば、家賃、人件費、固定資産税、保険料など文字通り固定的に発生する費用です。

 

損益分岐点の計算式

損益分岐点を最も簡単な計算式で表すと、以下のようになります。

損益分岐点売上高 = 固定費 ÷ 限界利益率

「限界利益」とは、売上高から変動費を差し引いた利益のことをいいます。

そして、上記の「限界利益率」とは、売上高に対する限界利益の比率のことをいいます。

限界利益率 = 限界利益 ÷ 売上高

という式で計算されます。

例えば、

売上高 5,000円  変動費 3,000円  固定費 1,000円

の場合、限界利益は

5,000円 ー 3,000円 = 2,000円

限界利益率は、

2,000円 ÷ 5,000円 = 0.4

損益分岐点売上高は、

1,000円 ÷ 0.4 = 2,500円

となります。

損益分岐点グラフをExcelで作成する方法

上記の数字を元データとして、簡単なグラフを作成してみたいと思います。

 

データをこのように準備します。

売上高の最小値を0、最大値を5,000円。

変動費のセルには、売上高が0のとき、5,000円のときの変動費と固定費の合計額を入力しています。

 

このようにセルを選んだ状態で、Alt + F1(ファンクションキーの1)を押します(Windowsの場合)。

 

すると、このようにグラフが出ると思います。

 

グラフのデザインを変更します(Windowsの場合、グラフの種類を面に変更。画像はMacのものです。。)

 

 

そして、グラフデータ内の図形の塗りつぶしをなしに変更して加工していきます。

 

これで、損益分岐点のグラフを簡単に作成することができます。

ちなみに、グラフの青(売上高の線)とオレンジ(変動費+固定費の線)が交わるところが、損益分岐点売上高(今回の場合2,500円)となります。

 

セルに計算式など入れると、さらに簡単に作成できると思いますので、興味がある方はお試しいただければと思います。

 

 


 

ライフ

娘の運動会2025

  長崎市内の小学校は先週と今日、運動会が行われるところが多いようです。 娘の小学校は今日が運動会でした。   ちょっと前の予報では雨マークもあり、昨日もそれなりに雨が降っていたので、開催されるのか心配でしたが、ちょうどいい曇天で運動会日和でしたね。 今年は祖母、それからいとこのお姉ちゃんも来てくれました。 きっと祖父も近くで観てくれていたことと思います。     去年は場所取りでやや出遅れてしまったので、今年は例年より早めに出発し、ほぼ希望通りの場所を確保すること ...

ReadMore

ライフ

「カロミル」を1ヶ月使ってみた所感

きっちりではなく、ざっくりつけています。。 以前の記事で体重の記録及び体重が増加していることを書きました。 そのときに少し触れましたが、「カロミル(https://www.calomeal.com/about-calomeal/)」というアプリを試しています。 利用し始めて1ヶ月程ですが、使ってみての感想など書いてみます。     記録が簡単 これまで摂取カロリーについては、捉えようとしてなかったので、試すことにしました。 他にも同じようなアプリがあると思うのですが、たまたま観た動画で ...

ReadMore

税金ほか

最低賃金の対象となる賃金、割増賃金の基礎となる賃金の違い

  賃金支払いについては、法令によって色々なルールが定められており、その内容に従って支払う必要があります。 色々なルールがありますが、本日は最低賃金の対象となるもの、割増賃金の基礎となる賃金の範囲等について確認したいと思います。     最低賃金 最低賃金の対象となる賃金は、毎月支払われる基本的な賃金となります。 具体的には、実際に支払う賃金から「対象とならない賃金」を差し引いて求めます。 対象とならない賃金は次のとおり。 結婚手当など、臨時に支払われる賃金 賞与など、1ヶ月 ...

ReadMore

  • この記事を書いた人

平川吉輝

税理士、AFP
1979年8月13日生、45歳。
長崎県長崎市在住。
2021年2月1日から 1575日毎日更新中。

-Excel, 会計・経理

S