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

 

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

 

 


 

ライフ

TOHOシネマズ映画鑑賞券の利用方法

シネマイレージカードセゾン限定特典の1つに、永久不滅ポイント200ポイントでTOHOシネマズ映画鑑賞券1枚に交換できるという特典があります。   使い方も簡単です。 TOHOシネマズのサイトかアプリでチケットを購入するタイミングでチケットの下の部分をめくったところにある番号を入力するだけです。   下の画面の「ムビチケまたはTCチケットをお持ちの方」の方を選択します(「TCクーポンをお持ちの方」の方ではありません)。   TCチケット番号、TCチケット暗証番号をそれぞれ入力し ...

ReadMore

ライフ

イベント割 ムビチケ作品共通券について

  全国の映画館で利用できる「イベント割 ムビチケ作品共通券」が2022年12月2日(金)から販売されます(一部の映画館及び一部の作品では利用不可)。 土日、祝日も一般1,200円、小人(高校生まで)800円と通常料金よりも得な特別料金で購入できます。 販売期間:2022年12月2日(金)12:00~2023年1月29日(日)23:59 (イベント割予算の消化状況によっては早期終了することもあり) 利用期間:チケット発行から2023年1月31日(火) 対象者:購入時にワクチン接種証明書を提出(ア ...

ReadMore

投資・節約・お金 長崎

長崎和牛パスポートの無料配布について

長崎和牛のPR・消費拡大を図るため、ながさきプレスと長崎和牛銘柄推進協議会との共同企画で、県内指定店で利用できる「長崎和牛パスポート」が配布されます。 2021年は有料販売(1,000円)されていましたが、今回はフリーペーパーです。   配布開始 2022年12月1日(木) 配布場所 長崎空港、新地バスターミナル、県内コンビニ、量販店等 利用可能期間 2022年12月1日から2023年3月31日 配布部数 20,000部(なくなり次第、配布終了)   ランチ、ディナー、テイクアウトなど ...

ReadMore

投資・節約・お金 長崎

team NAGASAKI SAFETY|第2弾認証店利用拡大キャンペーン実施中

  長崎県で対象の認証店で利用できる「クーポンキャンペーン」が実施されています。 会計時に2,000円以上の飲食を行うと、クーポン券(1,000円分)が配布されます。 しかも、配布されたクーポンは配布当日でも利用可能です。   サイトでキャンペーン参加店を検索できるようになっているので、気なるお店がないかチェックしてみてもいいかもしれません。   クーポンは店舗ごとに管理されているようで、キャンペーン期間内でも配布終了となっていることもあるようです。 少し検索した感じでは、商 ...

ReadMore

ライフ 長崎

エコパーク論所原に行ってきました②

前回からの続き、 2日目です。   朝食、コーヒー、片付けなどを済ませ、論所原のケビンをあとにしました。 子どもたちはちょっと前にヤギさんやウサギさんと触れあえる「ふれあい広場」へ。 思えば、前回と同じ流れですね(少しフライング気味ですが)。   前回は餌やり体験がメインでしたが、子どもたちの強い要望により、乗馬(引き馬)体験を行うことになりました。 10時半からということなので、時間まで近くを散策することに。   最初はやらないといっていた下の娘も、みんながやっているのを見 ...

ReadMore

ライフ 長崎

エコパーク論所原に行ってきました

この週末を利用してエコパーク論所原に行ってきました。 今回2回目です。   基本的には前回と同じような体制です。 前回と同じメンバーで、前回同様ケビンを利用させてもらいました。   キャンプ気分を味わいたいけど、まだテントを使った本格的なものはハードルが高いという私たちにはちょうどよいです(やっぱりお風呂には入りたいという要望もあるもので。。)。 だいたい予定したとおりの時刻に到着し、チェックインを済ませます。   私は今回も火起こし担当です(私がやりたいからやらせてもらって ...

ReadMore

会計・経理

領収書(経費)に関する勘違い

領収書は支払いを証明する大事な書類です(本日は受け取る方について)。 本日は、ときどきいただくこともある領収書に関するちょっとした勘違いについて、確認してみたいと思います。   目次1 レシートではダメ2 ないと経費にできない3 領収書さえあればOK   レシートではダメ 領収書は支払いを証明する大事な書類です(再掲)。 仕事のために支払ったとか、どこにいくら払ったなどの証拠となるものです。 基本的には、 ・日付 ・宛名 ・金額 ・店名 ・内容 が記載されています。 レシートには宛名が ...

ReadMore

会計・経理 税金

個人が外貨預金を所有している場合の注意点

  以前、法人について記事にしました。   本日は個人が外貨預金を所有している場合、特に為替相場が大きく変動している場合の課税上の注意点について確認してみたいと思います。   個人が外貨預金を所有している際の課税関係は、主に利息と為替差益の発生によるものです。 外貨建資産の販売や購入などの取引を「外貨建取引」といいますが、個人で行った外貨建取引に係る円換算額は、各種所得の金額として把握することになります。 為替差益とは、この外貨建取引を行った際に、為替相場の差によって生じる利 ...

ReadMore

  • この記事を書いた人

平川吉輝

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

-Excel, 会計・経理