損益分岐点をExcelで計算する方法｜セル・数式・グラフの作り方

1. 固定費・単価・変動費・販売数量を入力する

新しいシートのA列に項目名、B列に数値を入力します。金額は円、期間は1か月に統一。数値に「円」「個」は含めません。

固定費・変動費・数量は0以上、販売単価は0より大きい数を使います。単価と単位変動費は販売数量によらず一定とします。実務の固定費・変動費の区分は、会計上の売上原価・販管費の区分と必ずしも一致しません。

2. 数式を入力して損益分岐点と営業利益を求める

次の数式を指定セルに入力します。B8とB14は表示形式を「パーセンテージ・小数点以下2桁」にしてください。数式に×100を足す必要はありません。

B7の400円は、1個販売するたびに固定費の回収と利益に充てられる金額です。10万円÷400円＝250個。率を使うなら10万円÷0.4＝25万円です。限界利益・限界利益率の違いも確認できます。

IFで条件に応じて結果を分け、単位限界利益が0以下なら損益分岐点を空欄にします。空欄は「0円で黒字になる」という意味ではありません。固定費が正なら、この前提では販売を増やしても固定費を回収できません。売上0では安全余裕率も算出しません。

B15は「営業利益が0以上」の最小整数数量です。「営業利益が0を上回る」数量は、損益分岐点が整数ちょうどなら、さらに1個必要です。途中の値を丸めず、表示桁数だけを調整して検算してください。

3. 散布図で売上と総費用の交点を描く

1. D1〜G1に順番に「販売数量」「売上高」「総費用」「固定費」と入力します。
2. D2〜D9に、0、100、200、250、300、400、500、600を上から入力します。
3. E2に=$B$3*D2、F2に=$B$2+$B$4*D2、G2に=$B$2を入力し、E2:G2を9行目までコピーします。$付きの参照は固定し、D2はD3、D4…へ変化させます。
4. D1:G9を選択し、挿入メニューから「散布図」の直線付きの種類を選びます。各系列のX値をD2:D9、Y値をE2:E9、F2:F9、G2:G9にします。
5. 横軸名を「販売数量（個）」、縦軸名を「金額（円）」にし、凡例を表示。売上高と総費用が250個・25万円で交わることを確認します。

250個の前後は数量の間隔が違うため、横軸を数値として扱う散布図を使います。数量を等間隔に並べる通常の折れ線グラフと区別してください。メニュー名はExcelの版や端末で異なる場合があります。

4. 数字を変えて検算する

• B5を250にすると、売上25万円−変動費15万円−固定費10万円＝利益0円です。
• B5を200にすると、利益は−2万円、安全余裕率は−25%です。負の値をエラーとして消さないようにします。
• B5を300に戻し、B3を1,200にすると、単位限界利益600円、限界利益率50%、損益分岐点売上高20万円、営業利益8万円になります。必要最小整数数量は167個です。
• B3を600にすると、単位限界利益は0円になり、B9・B10・B14・B15が空欄になります。売れた数量にかかわらず固定費10万円が残ります。

初期値はB2＝100000、B3＝1000、B4＝600、B5＝300です。複数商品、追加設備費、値下げによる需要変動はこの単純な表の範囲外です。試験対策では、条件から使える式を選ぶ練習につなげましょう。