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

相談者

• 損益分岐点を簡単に算出したい
• 損益分岐点をグラフで表示したい

HAYATO

このようなニーズにお答えします。

損益分岐点はエクセルの機能を使って簡単にグラフにすることができます。この記事を読みながら進めるだけで下図のようなグラフを作成できますよ。

損益分岐点とは

損益分岐点について簡単におさらいします。

損益分岐点とは、収益と費用が等しくなり、利益がゼロとなる売上高の水準です。営業量・原価・利益の関係を分析し、どれぐらいの売上高で利益が出るかを把握する時（短期利益計画の作成時）に使われます。

この分析は、一般的に「損益分岐点分析 (break-even-analysis) 」または「CVP分析 (cost-volume-profit analysis」と言われています。

損益分岐点売上高は下記の計算式で算出します。

損益分岐点をグラフで表示する方法（売上高と総費用）

損益分岐点をグラフで表示する方法は、下記の４つの手順で行います。各手順についてエクセルの画面を見ながら説明していきます。

１．損益分岐点売上高を算出する

損益分岐点売上高をエクセルで算出する方法を説明します。下図の通り、黄色箇所のセルに前提条件である「販売単価」、「品同費単価」、「固定費」を入力すれば、エクセル数式を活用して自動で算出することができます。

２．グラフの元データを作成する

損益分岐点のグラフを作成するためには、下図の通り「販売量」、「売上高」、「総費用（変動費＋固定費）」、「固定費」の４つについて最小値、最大値を表形式で作成する必要があります。

今後の分析のことも考えて、１．のデータを活用して損益分岐点分析に必要な基礎データを作成した上で、グラフ作成に必要な表を作成します。下図の通り、「販売量」の最小値と最大値を追加で設定すればエクセルの数式を活用して簡単に作成できます。

「販売量」、「売上高」、「総費用」、「固定費」の４つの最小値と最大値が表示できれば、損益分岐点のグラフの準備は完了です。

３．グラフを作成する

グラフの元データを使用して損益分岐点をグラフ化する手順を説明します。

先ほど作成した表データの全てを選択した状態にします（①）。そして、エクセルのツールバーの「挿入」をクリックして（②）、グラフの右下の矢印をクリックします（③）。

グラフの挿入画面が拡大で表示されるので、上部のタブから「すべてのグラフ」をクリック（④）、左のメニューから「面」を選択して（⑤）、右側のグラフをクリックします（⑥）。

グラフの作成をクリックするとエクセルにグラフが挿入されます。

４．グラフを調整する

このままではグラフが見にくいので書式を調整していきます。

グラフの塗りつぶし状態になっている部分をクリックすると、右側に「データ系列の書式設定」が表示されるので、塗りつぶしのところを「塗りつぶしなし」（⑦）、枠線のところを「単色」（⑧）に変更します。線の太さや色は自分の好みに合わせて変更しましょう。

あとはニーズに合わせて適宜調整して完了です。例えば、下記のような調整をしてみるとより見やすくなりますよ。

損益分岐点をグラフで表示する方法（限界利益と固定費）

限界利益と固定費の関係を示した損益分岐点グラフを表示したい場合は、グラフの元データを下表に変更します。

あとは売上高と総費用の関係を示した損益分岐点グラフと同様の手順でグラフを作成すれば完成です。

まとめ

損益分岐点とは、収益と費用が等しくなり、利益がゼロとなる売上高の水準です。営業量・原価・利益の関係を分析し、どれぐらいの売上高で利益が出るかを把握する時（短期利益計画の作成時）に使われます。

損益分岐点をグラフで表示する方法として、下記の４つの手順をエクセルの画面を見ながら説明しました。

損益分岐点分析は利益計画を作成する際によく使用される分析方法です。損益分岐点をグラフ化して上司やクライアント等に説明する機会も多いと思いますので、本記事の内容を是非活用してください。