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

 

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

 

 


 

ライフ

自宅での仕事、子どもたちの声が騒がしいなと感じたときの対応について

自宅で仕事をしているので、仕事中子どもたちが家で友達と遊んでいるときの声も聞こえてくるときもあります。 今日も遊びに来てくれた友達と楽しそうに遊んでいました。 もっと小さくても聞こえるよ、というぐらいボリュームが大きいこともあります。 そんなときの対応と、考えたことについて書いてみます。   目次1 「声小さめにお願いね」と言う2 ノイズキャンセリングイヤホンを使う3 諦める   「声小さめにお願いね」と言う   とりあえず、ダメ元でお願いしてみます。 効果は5分と持たないで ...

ReadMore

効率化

機密データもDropboxで一元管理

パソコンの端末ではなく、クラウド上にデータを保管しています。 私が利用しているのは、Dropboxというストレージサービスです。 お客様とのデータ共有にも利用しています。 最近では、機密性の高い内容のものについてもDropboxのツールを利用し、管理・保管するようにしています。   目次1 ログイン情報等も管理2 機密性の高いデータも保管3 Dropbox Vaultの使い方   ログイン情報等も管理   以前はパスワード管理アプリを利用していましたが、最近はDropbox ...

ReadMore

長崎

出島メッセ長崎|会議室の利用について③(予約確定から利用当日まで)

本日利用してみました。   目次1 予約確定から当日までのやり取り2 利用当日について3 初回利用の感想など   予約確定から当日までのやり取り   2021年12月19日に利用許可申請を受付けたとのご連絡があり、その後12月23日に支払い方法についてのメールが届きました。 支払い方法は振込のみのようで、クレジットカード決済等には対応してないようですね。 その2日後、催事前の最終確認のメールがあり、いくつか確認があります。 備品の追加がないか プロジェクターや看板等の持ち込み ...

ReadMore

ライフ

iPhoneでアプリ内課金を制限する方法

  目次1 子ども用スマホ2 ゲームも一定のルールで3 アプリ内課金を制限する方法   子ども用スマホ   娘用に妻が以前使っていたiPhoneを利用しています。 Appleのファミリー共有で「お子様用アカウント」を作る方法もあるようですが、その辺りをよくわかっていなかったので、私のアカウントのまま使っています。 聞くところによると、13歳未満のファミリーアカウントを作成するのは多少めんどくさいとか。 知らなかったので、そのまま自分のアカウントを使った次第です。   ...

ReadMore

長崎

ながさきプレスの年間購読は今がお得【初回限定キャンペーン開催中】

できるだけ年間購読はしないようにしていますが、、   目次1 地域情報の収集2 美容室では「ながさきプレス」から3 ながさきプレスの初回限定キャンペーン   地域情報の収集   地域のイベントや飲食店など、調べたいものがあったときでも、ある程度ネットで検索して調べることができる時代です。 例えば、「長崎 おでかけ」とか「長崎 イベント」などのキーワードで検索することである程度探すことはできます。観光ポータルサイトもいくつかあります。 飲食店等も同様ですね。 または、ブログやS ...

ReadMore

ライフ

個人的に感じているランニングにはないウォーキングのメリット

2022年からはウォーキングからランニングに切り替える予定でしたが、ランニング自体が久しぶりなので、走ると筋肉痛になります。 何かの動画で超回復について知り、筋肉痛があるときはさらなる負荷はかけないほうがいいのかな?と思い、痛みがあるときは負荷が軽めのウォーキングにするようにしています。 ランニング、ウォーキングどちらかを毎日実施しているわけですが、(超)個人的に思うランニングにはないウォーキングのメリットについて、本日の記事にしたいと思います。   目次1 考える時間になる2 着替えなくてもい ...

ReadMore

独立

独立前後で変えたこと・変わったこと

独立してから170日ほど。 色々な変化がありますが、最近思ったことや出来事から、変わったなと思うことをいくつか挙げてみたいと思います。   目次1 寝る前にアラームをかけなくなった2 仕事の合間に運動するようになった3 仕事で外出しようとすると「なんで?」と聞かれるようになった   寝る前にアラームをかけなくなった   寝る前にアラームを設定しなくなりました。 この時間に起きないといけないということもありませんし、保育園に送っていく時間までには必ず起きているので、アラームをか ...

ReadMore

会計・経理 効率化

弥生会計のショートカットキー

  クラウド会計を利用している方も増えていますが、シェアから考えると弥生会計を使っている方も多いかと。 弥生会計の入力については、スマート取引取り込みなど便利なツールや、Excelからのインポートを利用している方もいらっしゃると思いますが、様々な事情で力技で入力する必要があるケースもあると思います。 入力だけでなく、帳票表示などの画面操作についてもショートカットキーを使うと効率的です。 本日は弥生会計のショートカットキーについて記事にしたいと思います。   目次1 基本はAltキーから ...

ReadMore

  • この記事を書いた人

平川吉輝

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

-Excel, 会計・経理