Excelで損益分岐点分析をマスター!実証済みの3つの計算方法

損益分岐点(Break-Even Point)分析は、あらゆるビジネスにとって非常に重要なツールです。本記事では、Excelで損益分岐点を計算する3つの効果的な方法を、手順を追ってわかりやすく解説します。

損益分岐点とは?

損益分岐点とは、特定の販売単価のもとで利益も損失も発生しない(利益がゼロになる)ために必要な販売数量、または特定の販売数量に対して必要な販売単価を求める計算のことです。この分析の主な目的は、ビジネスの採算ライン(限界条件)を把握することにあります。

損益分岐点を計算するには、以下の4つの要素が必要です。

• 固定費
• 変動費
• 1個あたりの販売価格(単価)
• 売上高

損益分岐点が成立する条件は次のとおりです。

売上高 = 総固定費(TFC)+ 総変動費(TVC)

ここからは、Excelで損益分岐点を計算する3つの効果的な方法を紹介します。それぞれ異なるExcelの機能を使って損益分岐価格を求めるため、用途に応じて使い分けられます。同じ考え方で、損益分岐売上高も計算可能です。

方法1:「ゴールシーク」機能で損益分岐点を求める

最初の方法では、「ゴールシーク(Goal Seek)」機能を使って損益分岐点を計算します。ここでは、単価・販売数・売上高・1個あたりのコスト・変動費・固定費・利益を含むデータセットを使用します。以下の手順に従ってください。

• まず、下の画像のようにデータを入力します。数値はご自身の状況に合わせて変更しても構いません。

• 次に、セルC6に以下の数式を入力して売上高を計算します。

• 続いて、セルC8に以下の数式を入力して変動費を求めます。

• さらに、以下の数式で利益を計算します。

• 次に、リボンの「データ」タブを開き、「What-If 分析」→「ゴールシーク」を選択します。

• 表示されたゴールシークのダイアログボックスで、「設定するセル」に利益のセル参照($C$10)を指定し、「目標値」に0を入力、「変化させるセル」に単価のセル($C$4)を設定します。

• 「OK」を押すと、次のウィンドウに解答が表示されます。
• 再度「OK」をクリックします。

• 最終的に、損益分岐点の結果として更新された単価が確認できます。

関連記事:Excelのゴールシークで損益分岐分析を行う方法

方法2:シンプルな数式で損益分岐点を求める

数式を使って損益分岐点を計算したい方には、この方法がおすすめです。ここでは方法1と同じデータセットを使用します。以下の手順に従って進めてください。

• まず、下の画像のようにデータを入力します。

• 次に、セルC8に以下の数式を入力して変動費を計算します。

• 続いて、セルC4に以下の数式を入力して単価を求めます。

• さらに、セルC6に以下の数式を入力すれば売上高も計算できます。

• 最後に販売数を変更すると、それに応じて損益分岐点となる単価が自動的に調整されるのがわかります。

関連記事:Excelの数式で損益分岐売上高を計算する方法

方法3:Excelのグラフ(チャート)で損益分岐点を求める

グラフ機能を使っても、Excelで損益分岐点を特定できます。視覚的に損益分岐点を確認したい場合に便利な方法です。以下の手順に従ってください。

• まず、下の画像のようなデータセットを作成します。内容は必要に応じて変更してください。

• 次に、セルF5に以下の数式を入力して売上高を計算します。

• 続いて、セルG5に以下の数式を入力してコストを求めます。

• さらに、セルH5に以下の数式を入力して利益を計算します。

• オートフィル(フィルハンドル)を使って、各単価の行に数式をコピーします。
• 次に、表全体を選択します。

• 「挿入」タブを開き、「折れ線グラフまたは面グラフの挿入」→「折れ線」を選択します。

• すると、下のような折れ線グラフが作成されます。

• グラフを選択し、右クリックから「データの選択」を選びます。

• 「データソースの選択」ウィンドウで「単価」を選択し、「横(項目)軸ラベル」の「編集」をクリックします。

• 「軸ラベル」ウィンドウで単価の列全体を選択し、「OK」を押します。

• 最終的に、表では販売数50のときに利益が0になっていること、また折れ線グラフでも販売数50の地点で売上高の線とコストの線が交差していることが確認できます。この交点こそが損益分岐点です。

練習用ワークブックのダウンロード

練習用ワークブックはこちらからダウンロードできます。

まとめ

損益分岐点の計算は、ビジネス分析や事業開発において非常に役立ちます。本記事では、Excelで損益分岐点を計算する3つのシンプルな方法(ゴールシーク機能、数式、グラフ)を紹介しました。ご質問やご提案があれば、ぜひコメントでお知らせください。Excelに関する類似記事については、当サイトの他の記事もぜひご覧ください。

関連記事

• Excelで複数製品の損益分岐分析を行う方法
• ExcelでNPV損益分岐分析を行う方法
• Excelで損益分岐チャートを作成する方法
• Excelの数式で損益分岐分析を計算する方法

<< 損益分岐分析(Excel)に戻る | 財務のためのExcel活用 | Excel学習

無料の高度なExcel演習問題(解答付き)を受け取ろう!