Excel で損益分岐点グラフを作成する方法

損益分岐点は、固定費、ユニットあたりのコスト、およびユニットあたりの収益を考慮して、利益を上げるために販売しなければならないユニット数を表します。たとえば、マイラー バルーンを販売する場合の損益分岐点を知りたい場合があります。損益分岐点がわかれば、利益を得るために何個の風船を売らなければならないかがわかります。 Excel 2007 を使用して損益分岐点をグラフ化するには、固定費 (建物、設備の保守など) と変動費 (電気、賃金、その他の変動費) を知る必要があります。グラフでは、損益分岐点は収益と総費用の交点で示されます。

ステップ 1

セル A1 に「固定費」と入力し、B1 に固定費の金額を入力します。たとえば、マイラー バルーンのサプライヤは、バイヤーになるために 100 ドルの会費を支払う必要があり、バルーンをいくつ購入しても、その金額が請求されます。その場合、B1 に「100」と入力します。

ステップ 2

セル A2 に「単価」と入力し、B2 に単価を入力します。たとえば、各バルーンの費用は 1 ドルです。 B2 に「1」を入力します。

ステップ 3

セル A3 に「単位あたりの収益」と入力し、B3 に単位あたりの収益の金額を入力します。郡のフェアでバルーンを販売する予定で、バルーン 1 個あたり 6 ドルを請求できることがわかっている場合は、B3 に「6」と入力します。

ステップ 4

セル A5 に「単位」と入力します。セル A6 に数字の 1 を入力します。数字の 1 の下 (セル A7) に数字の 2 を入力し、25 になるまで数字の入力を続けます。

ステップ 5

セル B5 に「コスト」と入力します。 B6 では、引用符なしで「=A6*$B$2+$B$1」と入力します。この式は、「ユニット数にユニットあたりのコストを掛けてから、固定コストを加算する」ことを意味します。

ステップ 6

B6 をコピーして、Cost 列のすべてのセルに貼り付けます。この例では、最初のセルは「101」と表示され、最終的な値が「125」になるまで、各セルの値が 1 ずつ増加します。

ステップ 7

セル C5 に「収益」と入力します。 C6 では、「=A6*$B$3」と入力します (引用符なし)。この式は、「ユニット数にユニットあたりの収益を掛ける」ことを意味します。

ステップ 8

C6 をコピーして、収益列のすべてのセルに貼り付けます。この例では、最初のセルの値は「6」で、各セルの値は「150」になるまで 6 ずつ増加します。

ステップ 9

セル D5 に「利益」と入力します。利益は収益-費用なので、数式「=C6-B6」をセル D6 に入力します。

ステップ 10

そのセルをコピーして、Profit 列のすべてのセルに貼り付けます。この例では、最初のセルは "-95" または "(95)" (マイナス 95 を意味します) と表示されます。最後の列は「25」となるはずです。

ステップ 11

A5 から D30 までの領域を、マウスの左キーを押したままその領域にマウスを合わせて強調表示します。

ステップ 12

Excel インターフェイスの上部にあるリボンの [挿入] タブをクリックします。 [挿入] タブの [グラフ] 領域内に、[折れ線] ボタンが表示されます。

ステップ 13

そのボタンをクリックし、サブ メニューから [積み上げ線] を選択してグラフを挿入します。損益分岐点は、チャート上で利益線がコスト線と交差する点です。

ヒント

グラフに各単位のコストを表示したくない場合は、折れ線グラフをクリックして強調表示し、delete キーを押します。