Excelで損益分岐点分析を作成する方法：完全ガイド
損益分岐点分析とは、総売上高と総費用が等しくなり、ビジネスが利益も損失も出さない売上水準を特定するものです。Excelでこれを作成するには、固定費、単位当たりの価格、および単位当たりの変動費を入力します。固定費を価格と変動費の差で割り、売上高と総費用をグラフにします。
このガイドでは、計算式、必要な入力項目、Excelでの3つのステップ、および具体的な計算例について解説します。また、価格や費用が変動した場合に何が変わるかを検証するために、Goal Seekやデータテーブルを使用する方法も紹介します。
損益分岐点分析とは何か
US Small Business Administrationは、損益分岐点を「総費用と総売上高が等しくなる点」と定義しています。その点を下回ると、売上があっても費用の回収には至りません。その点を上回ると、売上ごとに利益が積み上がります。
SBAは、利益の予測や融資の確保と並んで、起業コストを計算する理由の一つに損益分岐点分析を挙げています。融資元や投資家は、ビジネスが赤字を脱するまでにどれだけの売上が必要なのかを示すこの分析を求めることがよくあります。
損益分岐点分析は、3つの実用的な問いに答えてくれます。何単位を販売する必要があるか？それはいくらの売上高に相当するか？そして、その結果は価格や費用の変化に対してどの程度敏感（センシティブ）か？
また、新しいアイデアの簡単なテストとしても機能します。新製品や新しい拠点に投資する前に、3つの入力項目を予測してみてください。そして、必要となる販売数量が、対象とする市場において現実的かどうかを検討します。
損益分岐点の計算式
SBAは、損益分岐点の計算式として2つのパターンを提示しています。
販売数量（単位）ベース：
損益分岐点（単位）＝ 固定費 ／（単位当たり販売価格 － 単位当たり変動費）
売上高ベース：
損益分岐点（売上高）＝ 固定費 ／ 限界利益率
SBAは、2つ目の項目を「製品の価格と、その製品を作るためにかかる費用の差」と説明しています。売上高ベースの計算式では、このマージンを比率（価格から変動費を引いたものを、価格で割ったもの）として計算します。
スプレッドシートでは、この区別が重要になります。単位当たりの限界利益は金額（5ドルの製品に対して3ドルなど）です。限界利益率はパーセンテージ（60%など）になります。数量ベースの計算式には金額を、売上高ベースの計算式には比率を使用します。
また、SBAは「この損益分岐点分析は、単一の製品またはサービスを前提としている」という前提条件を設けています。複数の製品を扱う場合の対処法については、後半のセクションで説明します。
始める前に必要なもの
すべての損益分岐点分析は、3つの入力項目によって決まります。Excelでの作業よりも、費用を正しく分類することの方が重要です。
| 入力項目 | 意味 | 例 |
|---|---|---|
| 固定費 | 売上数量に関わらず常に一定の費用 | 家賃、給与、保険料、ソフトウェアのサブスクリプション料金 |
| 単位当たり変動費 | 販売数量（単位）に比例して増加する費用 | 原材料費、梱包費、決済手数料、配送料 |
| 単位当たり価格 | 顧客が1単位に対して支払う金額 | 定価、または割引適用後の平均販売価格 |
すべての入力項目で同じ期間を使用してください。家賃が月払いであれば、算出される結果は月間の損益分岐点になります。年俸と月々の家賃を混在させると、意味のない数値になってしまいます。
単位当たりの変動費は、最も推測に頼りがちな入力項目です。代わりに、過去の実績から見積もりましょう。前四半期の総変動費を、同四半期の販売数量で割ります。四半期ごとに結果が大きく変動する場合は、複数期間の平均値を使用します。
一部の費用は混在しています。基本料金と従量料金がある電話プランには、固定部分と変動部分があります。どちらの分類に属するかを推測するのではなく、これらを分割してください。
Excelで損益分岐点分析を作成する方法
以下の3つのステップで、実際に動作する損益分岐点モデルとグラフを作成します。これらは、Google Sheetsでも同様に機能する標準的な数式を使用しています。
ステップ 1：入力項目を設定する
空白のシートを開き、セルA1からA3に「固定費」、「単位当たり価格」、「単位当たり変動費」とラベルを付けます。B1からB3にそれぞれの値を入力します。
入力項目は必ず独立したセルに入力し、数式内に直接数値を打ち込まないようにしてください。そうすれば、入力項目が変更されたときに後続のすべての計算が自動的に更新され、1つのセルを編集するだけでシナリオをテストできます。
入力セルを薄い塗りつぶし色でフォーマットします。これにより、ファイルを開いた人に対して、どの数値を変更してよいかが一目で伝わります。
ステップ 2：損益分岐点を計算する
5行目から8行目に計算式を追加します。
- A5に「単位当たり限界利益」と入力し、B5に
=B2-B3と入力します。 - A6に「損益分岐点（数量）」と入力し、B6に
=ROUNDUP(B1/B5,0)と入力します。 - A7に「限界利益率」と入力し、B7に
=B5/B2と入力します。 - A8に「損益分岐点売上高」と入力し、B8に
=B1/B7と入力します。
B6ではROUNDUP（切り上げ）が重要です。製品を端数で販売することはできず、切り捨ててしまうと損益分岐点にわずかに届かなくなってしまいます。
B8の値が、B6に価格を掛けた数値とおおむね一致しているか確認してください。一致しない場合は、入力項目のいずれかが間違ったセルに入力されています。
ステップ 3：損益分岐点グラフを作成する
計算式の下に、小さなテーブルを作成します。D列に、0から始めて0、500、1,000のように等間隔で販売数量をリストアップします。損益分岐点を超えるまで数値を増やしていきます。E列で、=D11*$B$2を使用して売上高を計算します。F列で、=$B$1+D11*$B$3を使用して総費用を計算します。
D列からF列を選択し、グラフを挿入します。数量の列を真の数値軸として処理できるため、直線付きの散布図が最も適しています。
売上高のラインはゼロから始まり、急角度で上昇します。総費用のラインは固定費の水準から始まり、より緩やかに上昇します。これらが交わる点が損益分岐点です。読者が目分量で推測しなくて済むように、そこにデータラベルを追加します。交点より右側の領域を薄い色で網掛けし、利益ゾーンを示します。
具体的な計算例
ある移動式コーヒーショップの固定費が月額6,000ドルだとします。コーヒーを1杯5.00ドルで販売しており、1杯あたりコーヒー豆、牛乳、カップ、カード決済手数料などで2.00ドルの費用がかかります。
| 計算内容 | 結果 |
|---|---|
| 単位当たり限界利益 | $5.00 - $2.00 = $3.00 |
| 損益分岐点（数量） | $6,000 / $3.00 = 2,000杯 |
| 限界利益率 | $3.00 / $5.00 = 60% |
| 損益分岐点売上高 | $6,000 / 0.60 = $10,000 |
このコーヒーショップは、費用を回収するために月に2,000杯（売上高にして10,000ドル）を販売する必要があります。それ以降は、1杯売れるごとに3.00ドルの利益が積み上がります。
目標利益を設定する場合も同じ論理を使用します。月に3,000ドルの利益を得たい場合は、それを固定費に加えます。9,000ドルを3.00ドルで割ると、3,000杯になります。
安全余裕度は、どれだけの余裕があるかを示します。このコーヒーショップが2,600杯の販売を見込んでいる場合、赤字に転落するまでに600杯の減少まで耐えることができます。これは予想売上高の約23%に相当します。
Goal Seekを使用して損益分岐点価格を見つける
問いが逆になることもあります。販売数量は分かっていて、損益分岐点となる価格を知りたい場合です。
Microsoftのサポートページでは、このユースケースをシンプルに説明しています。数式から得たい結果は分かっているものの、それを導く入力値が分からない場合、Goal Seekは1つのセルを調整することでその入力値を見つけ出します。
C1に予想販売数量を入力した上で、B9などの利益計算セルに=B2*C1-B1-B3*C1と入力します。その後、Microsoftの手順に従います。「データ」タブの「予測」グループで「What-If 分析」を選択し、次に「Goal Seek」を選択します。数式入力セルに「B9」、目標値に「0」、変化させるセルに「B2」を設定します。
Excelは利益がちょうどゼロになるまで価格を調整します。月に1,500杯の場合、このコーヒーショップが損益分岐点に達するには6.00ドルの価格設定が必要になります。
Microsoftは、「Goal Seekは1つの変数入力値に対してのみ機能する」という制限事項を指摘しています。複数の入力項目を同時に解決するには、Solverアドインを使用するよう案内しています。
感度分析：価格や費用が変化したらどうなるか？
単一の損益分岐点の数値だけでは、そのビジネスモデルがどれほど脆弱であるかが見えにくくなります。感度分析テーブル（感度表）は、さまざまな入力範囲にわたる結果を示してくれます。
コーヒーショップの例を使うと、価格や費用のわずかな変化が損益分岐点を大きく動かすことが分かります。
| シナリオ | 限界利益 | 損益分岐点（数量） |
|---|---|---|
| 基本ケース：価格5.00ドル、変動費2.00ドル | $3.00 | 2,000 |
| 価格が5.50ドルに上昇 | $3.50 | 1,715 |
| 変動費が2.50ドルに上昇 | $2.50 | 2,400 |
| 固定費が7,500ドルに上昇 | $3.00 | 2,500 |
「What-If 分析」の下にあるExcelの「データ テーブル」機能を使用すると、このようなグリッドを自動的に作成できます。1つの列に価格の範囲を並べ、1つの行に変動費の範囲を並べます。そして、データテーブルの参照先を損益分岐点（数量）のセルに指定します。
同じ規模の変化を比較して、どの入力項目が最も重要であるかを確認します。この例では、50セントの変化が変動費に影響すると損益分岐点が400杯増加しますが、価格に上乗せされた場合は285杯減少するだけです。変動費が、交渉する価値のある最初のコストとして挙げられることが多いのはこのためです。
複数製品における損益分岐点分析
SBAの計算式は単一の製品を想定しています。しかし、ほとんどのビジネスでは複数の製品を販売しており、それぞれ異なるマージンを持っています。
一般的なアプローチは、加重平均限界利益を使用することです。各製品が占める売上シェアを予測し、各製品の限界利益にそのシェアを掛け合わせて足し合わせます。固定費をその加重平均値で割ることで、製品ミックス全体の損益分岐点（数量）を算出します。
簡単な例を挙げます。製品Aは限界利益が3ドルで、販売数量の60%を占めています。製品Bは限界利益が5ドルで、40%を占めています。加重平均限界利益は、3ドル×0.6 ＋ 5ドル×0.4 ＝ 3.80ドルとなります。固定費が7,600ドルの場合、損益分岐点は2,000単位（製品Aが1,200単位、製品Bが800単位）になります。
SBAは、月ごとに売上が変動する場合は、製品ごとに個別に計算を行うことも推奨しています。また、心に留めておくべき注意点も付け加えています。損益分岐点は計画策定や融資の実現可能性を評価するための予測値であり、詳細な会計処理に代わるものではありません。
製品ミックスが変われば、損益分岐点もそれに伴って変化します。総売上高が一定であっても、低マージンの製品へのシフトが起きると、損益分岐点は上昇します。
損益分岐点分析のプレゼンテーション方法
ほとんどの読者が必要とするのは、3つの数値と1つのグラフです。まず、損益分岐点（数量）、損益分岐点売上高、そして安全余裕度を提示します。そのすぐ下に、交点にラベルを付けたグラフを配置します。
次に、感度分析テーブルを示します。これは、融資元やマネージャーが次に必ず尋ねる「費用が上昇したり、売上が届かなかったりしたらどうなるか？」という問いに答えるものです。
入力項目は見える状態にしておきます。固定費の簡単なリスト、価格、および単位当たりの変動費があれば、読者は1分でロジックを確認できます。SBAは、損益分岐点がビジネスプランにおける重要な計算項目であると指摘しているため、入念にチェックされることを想定しておきましょう。
よくある間違い
- 異なる期間の混在。 月々の家賃と年俸を混在させると、意味のない結果になります。すべて1つの期間に換算してください。
- 顧客の支払額が少ないのに定価を使用する。 割引が一般的である場合は、実際に受け取った平均価格を使用してください。
- 細かな変動費の失念。 カード手数料、梱包費、配送料などは単位ごとに積み重なり、結果を左右します。
- キャパシティの無視。 2交代制の導入やより広いスペースが必要になるなど、特定の販売数量に達すると固定費が跳ね上がることがよくあります。各段階で再計算を行ってください。
- 切り捨て。 端数が出た場合、損益分岐点に達するには前の単位ではなく、次の整数単位が必要になります。
AIを活用して迅速に行う
費用の分類さえ明確になれば、スプレッドシートによる方法はうまく機能します。通常、時間がかかるのはそこに至るまでのプロセスです。なぜなら、費用は総勘定元帳のエクスポートデータや山のような請求書の中に埋もれているからです。
AIワークスペースを活用すれば、その仕分け作業を自動化できます。費用のエクスポートデータをPowerdrill Bloomにアップロードし、各行を固定費か変動費に分類するよう指示した上で、損益分岐点（数量）と売上高を計算させます。同じリクエストで、感度分析テーブルとグラフの作成も依頼できます。
結果を信頼する前に、分類内容を確認してください。光熱費などの混在費用は、あなた自身でしか判断できない場合がよくあります。
より広範な計画策定については、sensitivity analysis generatorやAI financial modelingのページで関連するモデルをカバーしています。当社のbudget vs. actual report（予算対実績レポート）の作成ガイドでは、実際の業績が計画を上回っているか下回っているかを追跡する方法を紹介しています。総額ではなくタイミングを把握するには、cash flow report（キャッシュフローレポート）が、実際にいつ現金が入ってくるかを示してくれます。
費用のエクスポートデータが用意できているなら、Powerdrill Bloomをお試しいただき、その損益分岐点の結果をご自身のスプレッドシートと比較してみてください。
よくある質問
損益分岐点分析とは何ですか？
損益分岐点分析とは、総売上高と総費用が等しくなる売上水準を計算するものです。その時点では、ビジネスは利益も損失も出しません。一般的に、ビジネスプラン（事業計画書）や融資の申請書などで使用されます。
数量ベースの損益分岐点はどのように計算しますか？
固定費を単位当たりの限界利益（販売価格から単位当たりの変動費を引いたもの）で割ります。固定費が6,000ドルで、単位当たりの限界利益が3ドルの場合、損益分岐点は2,000単位となります。
Excelで損益分岐点分析を行うするにはどうすればよいですか？
固定費、価格、変動費をそれぞれ別のセルに入力します。価格から変動費を引いて限界利益を計算し、固定費をその値で割ります。売上高と総費用を数量に対してグラフ化し、交点を読み取ります。
限界利益と限界利益率の違いは何ですか？
単位当たり限界利益は金額（価格から変動費を引いたもの）です。限界利益率はその金額を価格で割ったもので、パーセンテージで表示されます。数量ベースの損益分岐点には金額を使用し、売上高ベースの損益分岐点には比率を使用します。
なぜ損益分岐点分析が重要なのですか？
ビジネスが赤字を脱するまでにどれだけ販売しなければならないかを示すからです。また、価格、変動費、家賃など、どの入力項目がその基準値を最も大きく動かすかも明らかになります。SBAは、起業コストを計算する理由の一つにこれを挙げています。
情報源： US Small Business Administration「起業コストの計算」 · Microsoft サポート「Goal Seekの使用」。具体的な計算例では、説明用の数値を使用しています。