【Excel】エクセルで損益分岐点を計算する方法（CVP分析・グラフ作成・テンプレート活用）

ビジネスにおいて「どれだけ売れれば赤字にならないのか」という問いに答えるのが損益分岐点分析（BEP分析）です。

エクセルを使えば、損益分岐点の計算はもちろん、CVP分析（費用・売上・利益の関係分析）やグラフによる視覚化まで、手軽に実現することができます。

特に固定費・変動費・売上高の関係を正確に把握することが、損益分岐点分析の基本となります。

本記事では、エクセルで損益分岐点を計算する方法を、CVP分析の基礎からグラフ作成・テンプレート活用まで体系的に解説していきます。

経営管理や事業計画に携わる方はもちろん、財務の基礎を学びたい方にもぜひ参考にしていただければ幸いです。

エクセルで損益分岐点を計算するための基本的な考え方

それではまず、エクセルで損益分岐点を計算するための基本的な考え方とCVP分析の概要について解説していきます。

損益分岐点とは、売上高と総費用が等しくなる点、つまり利益も損失もゼロになる売上高または販売数量のことです。

この概念を正しく理解することが、エクセルでの計算を正確に行うための出発点となります。

固定費・変動費・限界利益の基本概念を理解する

損益分岐点を計算するには、まず費用を「固定費」と「変動費」に分類することが必要です。

固定費とは、売上高や生産量に関係なく一定額が発生する費用のことで、家賃・人件費（固定給）・減価償却費などが代表的な例です。

変動費とは、売上高や生産量に比例して増減する費用のことで、原材料費・仕入原価・外注費などが該当します。

限界利益とは「売上高－変動費」で計算される値で、固定費を回収し利益を生み出すための原資となります。

この計算式がエクセルでの損益分岐点計算の基本となるため、しっかり理解しておきましょう。

CVP分析とは何かを理解する

CVP分析とは、Cost（費用）・Volume（販売量）・Profit（利益）の3要素の関係を分析する手法で、損益分岐点分析をさらに発展させたものです。

CVP分析では「売上がxx%下がったら利益がどう変わるか」「固定費をxx万円削減すると損益分岐点はどこになるか」といった多角的なシミュレーションが可能になります。

エクセルの計算機能とグラフ機能を組み合わせることで、CVP分析を視覚的かつ動的に行えるダッシュボードを構築できるでしょう。

エクセルで損益分岐点を計算するための基本的なシート構成

損益分岐点をエクセルで計算するための基本的なシート構成を整理すると、以下のような入力項目と計算結果の区分けが有効です。

項目	種別	セルへの入力例
売上高（1単位あたり単価）	入力値	B2に単価を入力
変動費（1単位あたり）	入力値	B3に変動費単価を入力
固定費（月額または年額）	入力値	B4に固定費合計を入力
限界利益率	計算値	=(B2-B3)/B2
損益分岐点売上高	計算値	=B4/(B2-B3)*B2 または =B4/限界利益率セル
損益分岐点販売数量	計算値	=B4/(B2-B3)

このようなシンプルな構成でも、入力値を変えるだけで損益分岐点がリアルタイムに計算されるため、非常に実用的なツールとして活用できます。

エクセルで損益分岐点のグラフを作成する方法

続いては、エクセルで損益分岐点をグラフとして視覚化する方法を確認していきます。

数値だけでなくグラフで表示することで、損益分岐点の位置と利益・損失の関係が直感的に理解できるようになります。

特に経営会議や報告資料での説明に、グラフは非常に大きな説得力を持ちます。

損益分岐点グラフのデータテーブルを準備する方法

損益分岐点グラフを作成するには、まず販売数量に対する売上高・変動費・固定費・総費用の値を一覧にしたデータテーブルを準備します。

販売数量を0から最大想定数量まで一定間隔（例：100個刻み）で設定し、それぞれの数量に対して以下の値を計算してください。

エクセルでは販売数量の列を作り、単価・変動費・固定費をそれぞれ別のセルに入力しておき、数式で参照する形で作成するのが効率的です。

このようにデータテーブルを自動計算で作ることで、入力値を変えるたびにテーブルとグラフが自動的に更新される仕組みを構築できるでしょう。

折れ線グラフで損益分岐点を可視化する手順

データテーブルが完成したら、グラフを作成していきます。

販売数量・売上高・総費用の3列を選択し、「挿入」タブから「折れ線グラフ」を選択してください。

挿入されたグラフでは、売上高の折れ線と総費用の折れ線が交差する点が損益分岐点を示します。

グラフタイトルを「損益分岐点グラフ」などに変更し、横軸に「販売数量（個）」・縦軸に「金額（円）」といったラベルを追加することで見やすくなります。

交差点に注釈を加えたい場合は、テキストボックスや縦の補助線（図形の直線）を追加して視覚的に強調するとよいでしょう。

損益分岐点グラフをさらに見やすくするデザインのポイント

グラフの色や線の太さを工夫することで、損益分岐点グラフの視認性が大きく向上します。

売上高の線は青色・総費用の線は赤色など、色を明確に分けることで一目で区別できるようになるでしょう。

損益分岐点の位置には縦の補助線（X軸から伸びる点線）と横の補助線（Y軸から伸びる点線）を追加することで、具体的な販売数量と売上高の目標値が読み取りやすくなります。

また、利益ゾーン（売上高が総費用を上回る領域）と損失ゾーン（売上高が総費用を下回る領域）を異なる色で塗り分けると、より説得力の高いグラフになります。

CVP分析をエクセルで行う応用テクニック

続いては、エクセルを使ったCVP分析の応用テクニックを確認していきます。

基本的な損益分岐点計算をマスターしたら、目標利益の達成に必要な売上高の計算や感度分析へと発展させることで、より実践的な経営分析ツールが作れます。

CVP分析をエクセルで行う際の応用的な使い方を順に見ていきましょう。

目標利益を達成するための必要売上高を計算する方法

損益分岐点の概念を発展させることで、特定の目標利益を達成するための必要売上高も計算できます。

エクセルではこの計算式をセルに入力し、目標利益のセルを変動させることでリアルタイムに必要売上高がシミュレーションできます。

複数の目標利益シナリオ（例：50万円・100万円・150万円）を縦に並べて一覧表にすることで、目標設定の参考資料として活用できるでしょう。

感度分析を使って変動要因の影響を把握する方法

CVP分析において、各変動要因（単価・変動費・固定費）が1%変化したときに損益分岐点がどう変わるかを分析する感度分析は非常に有用です。

エクセルの「データテーブル（What-If分析）」機能を使うことで、変数を変化させたときの結果を一覧表形式で自動計算できます。

「データ」タブの「What-If分析」→「データテーブル」を使って、固定費や変動費を一定範囲で変化させた場合の損益分岐点の変化をマトリクス形式で確認することが可能です。

複数製品・複数事業の損益分岐点を分析する方法

複数の製品ラインや事業部門を持つ場合は、製品ごとの損益分岐点を計算してから全社合計を集計する方法が有効です。

各製品の単価・変動費・固定費（直接固定費）を別シートに入力し、SUMIFやVLOOKUP関数で集計シートに引き込む構成にすることで管理しやすくなります。

加重平均限界利益率の概念を使えば、製品ミックスを考慮した全社損益分岐点も計算でき、より実態に近い分析が可能になるでしょう。

エクセルの損益分岐点テンプレートの活用と作成方法

続いては、エクセルの損益分岐点テンプレートの活用と作成方法を確認していきます。

毎回ゼロから計算シートを作成するのは手間がかかるため、再利用可能なテンプレートとして整備しておくことが業務効率化の大きな鍵となります。

既存のテンプレートを活用する方法と、自分でカスタムテンプレートを作成する方法の両方を見ていきましょう。

エクセルの標準テンプレートから損益分岐点シートを探す方法

マイクロソフトはエクセルの標準テンプレートとして、損益分岐点分析用のシートを提供しています。

エクセルを開いて「ファイル」→「新規」を選択し、検索ボックスに「損益分岐点」または「BEP」と入力することで関連テンプレートが表示される場合があります。

英語版では「Break-Even Analysis」で検索すると豊富なテンプレートが見つかるため、英語が問題なければ活用するのもよいでしょう。

自分でカスタム損益分岐点テンプレートを作成するポイント

業種や業務内容に合わせたカスタムテンプレートを作成することで、より実務に即した分析が可能になります。

テンプレートとして整備する際のポイントとして、入力セルと計算セルを色分けして一目でわかるようにすること・入力規則でデータ型を制限すること・数式を誤って上書きしないようシート保護をかけることが挙げられます。

また、グラフを同一シートに埋め込んでおくことで、数値を更新するだけでグラフも自動更新される仕組みを構築できます。

損益分岐点テンプレートに追加すると便利な機能

基本的な損益分岐点計算に加えて、以下のような機能をテンプレートに組み込むことで、分析の幅が大きく広がります。

追加機能	実装方法	メリット
シナリオ比較（楽観・標準・悲観）	3列で並べて比較表を作成	リスク評価が容易になる
月次損益分岐点の推移グラフ	月別データを折れ線グラフで表示	季節変動の把握ができる
安全余裕率の計算	=(売上高-損益分岐点売上高)/売上高	損益分岐点からの余裕度がわかる
損益分岐点比率の計算	=損益分岐点売上高/売上高	経営の健全性指標として活用できる

安全余裕率や損益分岐点比率は、自社の経営体力を客観的に評価するための重要な指標です。

これらを合わせてテンプレートに組み込むことで、経営分析ツールとしての完成度が大幅に高まるでしょう。

まとめ

本記事では、エクセルで損益分岐点を計算する方法として、CVP分析の基礎概念・エクセルでの計算式の設定・グラフの作成・応用的なシミュレーション・テンプレートの活用まで、体系的に解説しました。

損益分岐点分析の核心は、固定費・変動費・限界利益率の正確な把握にあり、この3つを正しく設定することで信頼性の高い分析が実現します。

エクセルのグラフ機能やWhat-If分析を活用することで、数値の変化が視覚的に確認できる動的なダッシュボードを構築することも可能です。

再利用可能なテンプレートとして整備しておくことで、毎月・毎期の定例分析も効率的に行えるようになるでしょう。

本記事の内容を参考に、エクセルを活用した損益分岐点分析を経営管理や事業計画立案に積極的に取り入れてみてください。