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

 

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

 

 


 

税金

更正の請求・還付申告の期限5年間の違いについて

  以前、所得税の確定申告を間違えたときの手続きについて記事にしました。   更正の請求とは、確定申告をした後に、納めた税金が多すぎた、純損失の金額が少なかった、還付された税金が少なかったという場合に行う手続きです。 これとは別に還付申告という手続きもあります。 還付申告とは、申告義務がない人が確定申告をすることで、納め過ぎとなった税金の還付を受けることができる制度です。 例えば、年末調整ではできない医療費控除などがある場合の手続きとなりますね。 いずれも納め過ぎとなった税金を取り戻す ...

ReadMore

ライフ

移住マッチングサービス ピタマチを試してみました

今どこかに移住したいというわけではないですが、時折夫婦で〇〇とかに住めたらいいよね~などと話すこともあります。   何かの動画を見ていて、気になるサービスがあったので試してみました。 今回試したのはこちら。 移住マッチングサービス ピタマチ     画像を直感で選ぶだけで、自分にピッタリな移住先をおすすめしてくれ、自分が重視しているコトがわかるというもの。 ユーザー登録をすることで無料で診断することができるようです。 早速試してみました。     &nbs ...

ReadMore

税金

住民税が非課税となる基準について

住民税の計算、税率については、基本的にどこに住んでいても住民税の金額に差があるわけではありません。     住民税は、所得割額と均等割額という2つの合計したものを納付することになります。 所得割が所得に応じて課税されるのと違い、均等割は所得の金額に関係なく、課税される条件の方であれば、均等な額が課税されることになります。 前述のとおり住民税の金額については、住んでいる地域によって差はないのですが、非課税となる基準については少し異なる部分があります。 生活保護法を根拠に規定された「級地区 ...

ReadMore

ライフ

リターンライダーになって感じたことなど

まだ、それほど乗れておりませんが、感じたことなど書いてみます。   目次1 基本の大切さ2 チャレンジすることの重要性3 体力は衰えていく   基本の大切さ 一昨年に自動車学校で大型二輪免許を取得して、1年ちょっと経ってから、初めて大型バイクに乗って公道を走ったわけですが、改めて感じたことは、やはり基本が大事ということでしょうか。 自動車学校での課題は、結構大事な要素がつまっていたんだなと感じます。 教習中は、クランクや一本橋などこんな状況実際にはないか、あったとしても避けるだろうし、 ...

ReadMore

ライフ

外で仕事をするために持っていくもの

外で仕事をすることもあります。 そのときに持っていくものなどについて書いてみます。 ウォーキングとかランニングとか、目的が決まっているときはその用途にあったモノだけを持っていっております。 外出するときに常に持ち歩いているというわけではないですが、遠出するときや、外出先で空き時間がありそうとか、仕事ができそうなシーンに持っていくものがあります(スマホや財布、AirPods Proなど、普段から持ち歩くものは省きます)。 特別なものはなく、かなりシンプルですけど。。     MacBoo ...

ReadMore

税金

確定申告が終わったら今年の納税スケジュールも確認しておこう

所得税の確定申告は、所得税を計算する手続きですが、フリーランス・個人事業主の方の場合は、それ以外の税金も関わってきます。 個人事業主の場合は、毎月何かしらの税金を納める形になりますね。。 消費税の課税事業者の方であれば、消費税の納付もありますし、所得税及び消費税の納付税額によっては予定納税等もあります。 個人事業税などは、開業してから知るという方もいるかもしれませんね。 税理士事務所に依頼されている方であれば、今年納税する各種税金の税額についても案内してもらっているケースもあると思いますが、ご自身で申告を ...

ReadMore

税金

所得税の確定申告を間違えたときの手続きについて

昨日3月15日は確定申告の提出期限でした。 提出したあとで、内容に間違いがあったことに気付いたら、もちろん修正することも可能です。 間違いに気付いたタイミングによって、取るべき手続きが少し変わります。 ケース別に確認してみたいと思います。   目次1 法定申告期限内2 法定申告期限後3 まとめ   法定申告期限内   申告期限内なら再度申告書を提出することになります(訂正申告)。 期限内に複数提出された場合、最後に提出されたものを正として扱うことになっています。 今は電子申告 ...

ReadMore

独立 税理士

独立後2回目の確定申告を終えて

独立してから2回目の確定申告を終えて、感じたことなど書いてみます。   目次1 劇的には変化しない2 予定外は想定した(そして起きた)3 夜仕事する時間は減った   劇的には変化しない 全体的な印象としては、前回と比較して劇的に何かが良くなったということはないように感じます。残念ながら。 去年の今頃は、来年はもう少し効率的に、早期に終了するよう取り組むと意気込んでいたと思うのですが、思い描いたようにうまくはいかないものです。 まったく同じ状況ではないので(良くも悪くも)、単純に比較する ...

ReadMore

  • この記事を書いた人

平川吉輝

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

-Excel, 会計・経理