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

 

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

 

 


 

税金ほか

相続税申告のあれこれ

  相続税とは、亡くなられた親などから、お金や土地などの財産を受け継いだ(相続した)財産にかかる税金です。   すべての相続について申告が必要というわけではありませんが、一定の金額を上回ると相続税の申告(納付)が必要となります。 本日は相続税申告の概要について簡単に確認してみます。   相続税の申告納付期限 相続税の申告は、相続の開始があったことを知った日の翌日から10ヶ月以内に行うことになっています(期限の日が土日祝のときは、その翌日が期限とみなされます)。   ...

ReadMore

税金ほか

措置法26条 専従者給与の検討

  以前、措置法26条について記事にしました。   その記事でも少し触れましたが、適用事業者において、措置法が有利となった場合、専従者給与の支給について検討が必要となります。   有利不利の判定 ①(専従者給与×自費診療割合)×事業主本人の税率=事業主本人の節税額 ②(専従者給与ー専従者の所得控除)×専従者の税率=専従者負担税 ①>②なら、支給したほうが有利、①<②なら支給しないほうが有利となります。     支給するメリット 専従者給与の支給 ...

ReadMore

会計・経理

給与計算時の端数処理あれこれ

    給与計算をする際、労働時間や円未満の賃金額の端数が出たりしますが、それぞれ取扱いがどのようになるか確認してみたいと思います。   労働時間 給与計算では、まず労働時間を集計することになるのですが、労働時間は1分単位で集計することが大原則となっています。 1ヶ月における時間外労働、深夜労働、休日労働の各々の合計については、端数処理をすることが認められており、1時間未満の端数がある場合には、30分未満切り捨て、それ以上を1時間に切り上げても差し支えないとされています。 & ...

ReadMore

税金ほか

小規模企業共済は配偶者も加入できるか

  個人事業主の配偶者も小規模企業共済に加入できるか、お尋ねいただくこともあります。   小規模企業共済は一定の判断基準(業種や常時使用する従業員の数など)を満たしていないと加入できません。   個人事業主の配偶者も原則は加入することができないのですが、共同経営者の要件をすべて満たせば、「個人事業主の共同経営者」という立場で、小規模企業共済に加入することができます。   個人事業主の共同経営者の要件は次のとおり。 申込者が経営に携わっている事業を営む個人が、小規模事 ...

ReadMore

医療機関等 税金ほか

措置法26条 検討する際の注意点

    措置法26条とは 租税特別措置法(措置法)26条は、「医師優遇税制」とも呼ばれ、適用条件を満たしていれば、社会保険診療に係る実際の経費の額にかかわらず、一定の計算式により経費の金額を算出することが可能となります。事前の届出等は必要がなく、確定申告時に選択することができます。 医師又は歯科医業を営む個人である 年間の事業所得の総収入の合計額が7,000万円以下、かつ、社会保険診療報酬が5,000万円以下である 上記1、2に該当する事業者が適用事業者となります。   検討 ...

ReadMore

ライフ 効率化

SESAME タッチProの設定方法など

  スマートロックのセサミ3を利用しています。     今回SESAME タッチプロを導入することにしました。 アプリでの操作で事足りるかなと思っていましたが、弟が使っているのをみて便利そうだなと感じたもので。     設定も簡単です。 まず、カバーを開けて電池のところにあるフィルムを外します。     タッチプロを近くに置いた状態でセサミアプリの右上の「+」をタップし、「新規デバイス」を選択します。     これで ...

ReadMore

ライフ

Amazonプライム会員なら「business plime」も1ユーザーは無料

  事業者向けのAmazonビジネス。 個人向けのAmazonプライムのようにAmazonビジネスにも「Businessプライム」という限定特典のついたプランがあります。 以前は有料プランしかありませんでしたが、Amazonプライム会員であれば追加料金なしで利用できる「BusinessプライムDuo」が2023年より提供されています。   利用できるの1ユーザーのみで、上位プランの一部機能は利用できませんが、通常会員では送料がかかる「お急ぎ便・当日お急ぎ便」が無料となるので、それだけで ...

ReadMore

会計・経理 税金ほか

新設法人における事前確定届出給与に関する届出書の提出期限について

  事前確定届出給与とは、役員賞与など、法人の役員に所定の時期に確定額を支給する旨の定め(事前確定届出給与に関する定め)に基づいて支給する給与のことをいいます。   事前確定届出給与は、法定の提出期限までに所轄税務署長にその定めの内容に関する届出書を提出しなければならないことになっており、届出書の提出がなされず支給された事前確定届出給与は経費にならないこととされています。 この届出書の提出期限は、原則、次のうち1か2いずれか早い日と定められています。 株主総会等の決議によりその事前確定 ...

ReadMore

  • この記事を書いた人

平川吉輝

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

-Excel, 会計・経理