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

 

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

 

 


 

投資・節約・お金

マイナポータル ねんきんネットと連携

  以前ねんきんネットについて記事にしました。 ねんきんネットで出来ること|長崎の税理士 平川吉輝のblog   ねんきんネットでは年金記録や将来の年金見込額など確認することができます。 私はユーザIDを取得して登録しましたが、マイナポータルから登録することも可能です。 マイナポータルから登録する場合は、ユーザIDを取得する必要はありません。 IDを取得している場合でも、マイナポータルと連携することでマイナポータル経由でねんきんネットを利用することができます。   手続きも簡 ...

ReadMore

投資・節約・お金

三井住友カード Vpassアプリのスタンプカードの対象とならないもの

  三井住友カードを利用している方であれば、Vpassアプリも利用している方も多いかと思います。 以前、Vpassアプリの「利用通知スタンプカード」について記事にしました。 三井住友カード Vpassアプリのスタンプカードについて|長崎の税理士 平川吉輝のblog   カードの利用通知サービスを設定し、500円以上の決済のたびにスタンプが1つ付き、それが20個貯まるといずれかひとつ抽選でプレゼントが当たるというサービスです。 ハズレはありません。 例えば、2022年7月の特典はこちら。 ...

ReadMore

ライフ

サンクコスト効果に惑わされないようにしたい

  サンクコスト効果とは、すでに発生していて取り消すことができないコスト等に気を取られ、合理的な判断ができなくなる心理傾向のことを指します。 埋没費用効果とかコンコルド効果(コンコルドの誤謬)ともいいます。 継続することがさらなる損失拡大をもたらすことになっても、それまでに費やした費用や労力がもったいないと感じて、ついつい続けてしまう状態です。 誰しも1度は経験があるかもしれませんね。   取り巻く状況も変化していきますから、以前の状態であれば意味があることも、今の自分にとっては効果が ...

ReadMore

ライフ

BBQ初心者向け 火起こしアイテム

  頻繁にというわけではないですが、自宅の庭でBBQをすることもあります。 家族でお出かけするのも減ったこともあり、以前よりその機会は増えたように思います。 とはいえ、そこまで慣れているというわけではありませんので、火起こしに苦労することもありました。 火がつかないとスタートできないので、そうもたつくわけにはいきません。   なので、便利なアイテムに頼るようにしています。 現在火起こしの際に利用しているのはこちらです。   いくつかの着火剤を試してみましたが、なんとなく今はこ ...

ReadMore

投資・節約・お金

マイナポイント第2弾 子供の公金受取口座について

    第1弾の申請で娘分についても手続きしておりました。 未成年の子供については、自分で手続きするのは難しいことから、15歳以下の子供については親(法定代理人)が代理で申請可能です。 その際、決済サービスは親名義のものを指定することができます(親自身のものとは別のサービスを選ぶ必要があります)。   第2弾の公金受取口座の登録の場合はどうかというと、親名義の口座を登録することはできません。 よくあるご質問コーナーにも記載がありますが、公金受取口座は本人名義の口座を登録するこ ...

ReadMore

投資・節約・お金

マイナポイント第2弾 受け付け開始

  2022年6月30日からマイナポイント第2弾の受け取り申請の受付が開始されました。 マイナンバーカードを健康保険証として登録すると7,500円相当、公金受け取り口座情報を登録した場合に7,500円相当のポイントがそれぞれ付与されます(マイナンバーカードを新たに取得する場合はプラス最大5,000円、計20,000円相当)。 早速手続きしてみることにしました。 私はマイナポイントアプリから申込みしてみました。   開始当日だからか、アクセスが集中しているようで少し待ち時間がありました。 ...

ReadMore

長崎

第2弾 ふるさとで"心呼吸"の旅キャンペーン【2022年7月14日まで期間延長】

    「第2弾 ふるさとで"心呼吸"の旅キャンペーン」の利用期間が2022年7月14日(木)まで延長となりました。 対象者は期間延長前と同様です(長崎県、福岡県、佐賀県、熊本県、大分県、宮崎県、鹿児島県在住者)。   県内市町のキャンペーンは3市(佐世保市、平戸市、五島市)あるようです。 第2弾 佐世保市内でゆったり宿泊キャンペーン|佐世保市 R4年度 平戸市 特別宿泊割キャンペーン|平戸市 平戸満喫ドライブキャンペーン|平戸市 "今だ!!五島へ行こう!!"旅キャンペーン| ...

ReadMore

税理士試験

小幅な伸びでいい、続けることが大事

  税理士試験まであと1ヶ月ちょっとですね。 受験生の方には、残り期間なんとか踏ん張ってもらいたいです。   私も受験生時代を思い出すと、直前期に入った頃でも、なんでこんな簡単なところ間違うの?とか、似たようなところでつまづいたりすることもありました。 毎日やっているのに、成長どころか後退してない?と思う年もあったような。。   ただ、それでもコツコツやり続けていると、ある時点でぐんと伸びるような感覚を持つときもありました(全然ないときもありましたが)。 税理士試験に限ったこ ...

ReadMore

  • この記事を書いた人

平川吉輝

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

-Excel, 会計・経理