【Excel】損益分岐点分析を散布図でわかりやすく視覚化する方法

損益分岐点分析は、企業の売上と費用の関係を整理して、「利益がゼロになる売上高」を把握する重要な分析手法です。今回は、Excelを使って散布図で損益分岐点を視覚化する方法を紹介します。

グラフを使えば、固定費と変動費、売上高の関係が一目でわかり、経営判断に役立ちます。

内容：

なお、確認のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン2509）です。

Excelのアウトプットイメージは、次のとおりです。

このように散布図を利用すると、数量と金額の関係を正確に視覚化できます。

折れ線グラフや面グラフはデータの傾向を示すには適していますが、売上線と総費用線の交点である損益分岐点を正確に視覚化するには不向きです。特に、数量と金額の両方を軸に設定する場合、散布図の方が適切です。

上記のアウトプットイメージの上側にある表（グラフ用座標）が、グラフを作成するために必要な座標データです。

この表は、以下の前提条件をもとに作成しています。

座標データを示す表ではなく、空白のセルを選択した状態で、[挿入] → [グラフ] → [散布図」→「散布図（直線とマーカー）] を選択する。

※ 座標データを示す表などを選択してグラフを作成する方法もありますが、意図しないデータ範囲となり、手順が複雑になることもあるため、ここでは一旦空白のグラフエリアを作成する手順としています。

上記の結果、次のように空白のグラフエリアが作成される。

上記の結果、「データソースの選択」画面が表示される。

「凡例項目（系列）」として、グラフに表示する線を次の内容で設定する。

具体的には、売上線の場合は次のように、系列名、系列Xの値、系列Yの値を設定している。それぞれ、冒頭のアウトプットイメージにあるグラフ用座標のセル位置を指定している。

同様に、他の項目の系列を追加する。設定後のイメージは次のとおり。

以上の設定を行い「OK」をクリックすると、下図のように必要な線が描画されたグラフが作成される。

最後に、各線の書式設定、グラフタイトル、軸ラベルなどを追加し、冒頭のアウトプットイメージのような完成系に仕上げる。

以上、Excelを使って、損益分岐点分析を散布図でわかりやすく視覚化する方法の紹介でした。

shikumika’s diary