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円)となります。

 

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

 

 


 

ライフ

大型二輪教習 2段階5回目

  大型二輪教習、2段階5回目の教習を受けてきました。   2段階の教習は全7回ですので、残すところあと2回となりました。   うまくいけば、今月中の免許取得も可能かもしれません。   今回は、前回最後にちょろっと確認した2コースをメインに走りました。   1コースが慣れているので、2コースだと勝手が違います。コースも若干迷いながらな感じです。。   同じクランクでも入り口が違うだけで、少し戸惑ってしまったり、、   コーンに当たるまで ...

ReadMore

税金

ふるさと納税のよくある(聞く)勘違い3選

お客様や友人とふるさと納税についての話をする機会に、質問があったり、中には少し勘違いされているなというケースもあります。   ふるさと納税に限ったことではありませんが、税金に関することは少しややこしいところもあるので、ちょっとした認識違いはあって当然です。   今回は、ふるさと納税について、見聞きしたり、実際に質問があったことの中から、よくある勘違いを3つ確認してみたいと思います。   目次1 節税になる2 確定申告でふるさと納税した分税金が戻ってくる3 限度額を超えたら絶対 ...

ReadMore

長崎

長崎県産品・特産品の総合情報・販売サイト 「e-ながさきどっとこむ」利用してみました

  長崎県産品の総合情報・販売サイト 「e-nagasaki.com」を利用してみました。   ときどき、お客様の経費に出てくるので気になっていました。   特産品のカテゴリー毎にわかれており、充実の品揃えです。   水産品・魚介類。   ちゃんぽん・麺類。   カステラ・スイーツなどなど。   会員登録することで、すぐに利用できます。 しかも、2021年10月31日までであれば、「長崎よかもんキャンペーン 第3弾」の特典により、2,0 ...

ReadMore

ライフ

大型二輪教習 2段階4回目

  大型二輪教習、2段階4回目の教習を受けてきました。   今回も前回からの流れのまま、検定コース1の確認です。   最初は教官の後をついて行くので復習ができるわけですが、前回乗車したときと違い、今回は1回走っただけで、その後、すぐ通しでやる形となりました。   今回はしっかり目にコースを覚えてきたので、「合図→確認」に重点をおいて走ろうと思ったのですが、力が入りすぎたのか、一本橋で落ちてしまいました、、 どうも、苦手意識があるようです。 検定では一本橋脱輪は即終了 ...

ReadMore

税金

海外移住時に必要な税務手続について

海外に移住する場合に、必要となる税務手続について確認する機会がありましたので、本日の記事にしたいと思います。   目次1 納税管理人の届出2 海外移住前の確定申告3 納税管理人になれるのは?   納税管理人の届出 海外に1年以上住んでいる人、若しくは1年以上住む予定の人については、出国した日の翌日から税法上、「非居住者」となります。   サラリーマンの方が海外赴任等で「非居住者」となった場合、日本での課税関係は源泉徴収のみで完結することも多いかもしれませんが、例えば、日本国内 ...

ReadMore

ライフ

禁煙成功のきっかけとなったあんでるせんのマスターから聞いた話

今は喫煙者ではありませんが、若い頃にタバコを吸っている時期がありました。   25歳になる年に禁煙したのですが、それまで何度もやめようとチャレンジするも失敗の連続でした。   小学生ぐらいの頃、父親の腰踏み(マッサージ?)というミッションがあったのですが、父親はヘビースモーカーで、腰を踏んでいるときもタバコを吸っているものですから、副流煙をまともに受けるのが嫌で嫌でたまりませんでした。それを双子の弟と交代交代やっていました。   そんなこともあり、どちらかというとタバコは嫌い ...

ReadMore

ブログ

ブログ250記事投稿(248時間52分、240,730字)から思うこと

2021年2月1日から始めたこのブログ、毎日1記事更新しており昨日で250記事目となりました。   250記事目と中途半端なタイミングとなってしまった理由は、単純に200記事目を取り上げるのを失念していたためです。。   100記事目は、気づいて記事にしていたのですが、、   このときの記事にも書いておりますが、とにかく毎日書くと決め、「今」できる範囲で、完璧を求めず書いているわけですが、その時と違うことといえば、当時日付をまたいでも当日扱いにしておりましたが、今はその日中の ...

ReadMore

ライフ

大型二輪教習 2段階3回目(今日は乗ってません)

  大型二輪教習、2段階3回目の教習を受けてきました。   最近、大型二輪の教習の記事ばかりになっておりますが、自身の記録としての意味合いもあり、ログを残そうと思っておりますので、温かい目でみていただければと思います。   今回の教習は、バイクには乗車せずシミュレーターを使った教習ということで、いつもと違ったものでした(実際はシミュレーターも然程使ってはいません)。 汗もかかないので、帰ってからが楽でした。     まず、二人乗りについての動画を視聴し、単 ...

ReadMore

  • この記事を書いた人

平川吉輝

税理士、AFP、医業経営コンサルタント
1979年8月13日生。
長崎県在住。
2021年2月1日から日毎日更新中。

-Excel, 会計・経理