Excelで損益分岐点シミュレーションを作る方法

損益分岐点とは？まず押さえたい基本の考え方
Excelで使う項目を整理しよう：売上・固定費・変動費の分け方
損益分岐点を自動計算するExcelシートの作り方
売上やコストを変えてシミュレーションする方法
グラフで見える化！損益分岐点をわかりやすく分析するコツ

損益分岐点とは？まず押さえたい基本の考え方

損益分岐点とは、ひと言でいうと「利益がプラスでもマイナスでもなく、ちょうどゼロになる売上高」のことです。ビジネスでは英語で「Break Even Point」と呼ばれ、略してBEPとも表現されます。

たとえば、あなたが副業で月額サービスを始めたとします。毎月のサーバー代やツール利用料、人件費などのコストがかかる一方で、ユーザーからの売上が発生します。このとき、売上がコストを上回れば黒字、下回れば赤字です。そして、売上とコストがぴったり同じになるラインが「損益分岐点」です。

損益分岐点を理解するうえで重要なのが、コストを固定費と変動費に分けて考えることです。

固定費：売上の増減に関係なく発生する費用
変動費：売上や販売数量に応じて増減する費用

固定費の例としては、オフィス家賃、社員の基本給、システム利用料、広告の月額契約費などがあります。売上がゼロでも基本的に発生する費用です。一方、変動費には、商品の仕入れ費、販売手数料、配送費、決済手数料などがあります。売れた分だけ増える費用と考えるとわかりやすいでしょう。

損益分岐点の基本式は、次のように表せます。

損益分岐点売上高＝固定費 ÷ 限界利益率

ここで出てくる限界利益率とは、売上のうち固定費の回収と利益に使える割合のことです。計算式は以下の通りです。

限界利益率＝限界利益 ÷ 売上高

さらに、限界利益は「売上高 − 変動費」で求められます。つまり、商品やサービスを売ったときに、変動費を差し引いて手元に残る金額が限界利益です。

たとえば、売上高が100万円、変動費が40万円であれば、限界利益は60万円です。この場合、限界利益率は60％になります。もし固定費が30万円なら、損益分岐点売上高は「30万円 ÷ 60％ = 50万円」です。つまり、月の売上が50万円を超えれば黒字化できる、ということになります。

この考え方を知っておくと、「あといくら売上を伸ばせば黒字になるのか」「広告費を増やしても採算が合うのか」「価格を下げた場合、どれくらい販売数が必要なのか」といった判断がしやすくなります。

特にExcelで損益分岐点をシミュレーションできるようになると、数字を変えるだけで複数パターンをすぐに比較できます。感覚ではなく、データをもとに判断できるため、企画書作成や事業計画、上司への説明にも役立ちます。

まずは「売上」「固定費」「変動費」「限界利益率」の関係を押さえることが、Excelで損益分岐点シミュレーションを作る第一歩です。

Excelで使う項目を整理しよう：売上・固定費・変動費の分け方

損益分岐点をExcelで計算する前に、まずやるべきことは必要な項目を整理することです。ここがあいまいなままシートを作ると、「計算式は合っているのに、結果が実態とズレる」という状態になりがちです。

Excelで損益分岐点シミュレーションを作るときは、大きく分けて次の3つの項目を用意します。

売上：商品やサービスを販売して得られる金額
固定費：売上に関係なく毎月ほぼ一定で発生する費用
変動費：売上や販売数量に応じて増減する費用

まず売上は、「販売単価 × 販売数量」で考えるとExcelに落とし込みやすくなります。たとえば、1件あたり5,000円のサービスを月に200件販売する場合、売上は「5,000円 × 200件 = 100万円」です。Excelでは、販売単価と販売数量を別々のセルに入力しておくと、あとから単価や数量を変えてシミュレーションしやすくなります。

次に固定費です。固定費は、売上が増えても減っても基本的に変わらない費用を指します。具体的には、以下のような項目です。

オフィス家賃
社員の基本給
サーバー代やクラウドサービス利用料
会計ソフト、チャットツールなどの月額費用
広告の固定契約費

ポイントは、「売上がゼロでも発生するか？」と考えることです。たとえば、月額3万円のSaaS利用料は、売上がなくても支払う必要があるため固定費に分類できます。一方で、成果報酬型の広告費や販売手数料は、売上に応じて変わるため固定費ではなく変動費として扱うのが自然です。

変動費は、商品やサービスが売れるほど増える費用です。代表的なものには、次のような項目があります。

商品の仕入れ費用
外注制作費
配送費
決済手数料
販売手数料

たとえば、1個売れるたびに仕入れ費が2,000円、決済手数料が売上の3％かかる場合、これらは変動費として入力します。Excelでは「1個あたり変動費」または「変動費率」として整理しておくと、計算がしやすくなります。

実務では、固定費と変動費の判断に迷う費用もあります。たとえば広告費は、毎月10万円を固定で支払っているなら固定費、売上の10％を成果報酬で支払っているなら変動費です。また、人件費も、正社員の基本給は固定費、販売件数に応じた歩合給は変動費として分けて考えます。

Excelに入力する項目例としては、以下のように整理するとわかりやすいです。

分類	項目例	入力方法
売上	販売単価、販売数量	単価 × 数量
固定費	家賃、人件費、サーバー代	月額費用を入力
変動費	仕入れ費、手数料、配送費	1個あたり費用、または売上に対する割合

まずは細かく完璧に分類しようとしすぎず、「売上に連動するかどうか」を基準に分けることが大切です。Excelでは後から項目を追加・修正できるため、最初はシンプルな形で作り、必要に応じて精度を高めていきましょう。

この項目整理ができていれば、次のステップで損益分岐点を自動計算するExcelシートをスムーズに作れます。

損益分岐点を自動計算するExcelシートの作り方

項目の整理ができたら、いよいよExcelで損益分岐点を自動計算するシートを作っていきます。ポイントは、入力するセルと計算するセルを分けることです。あとから数値を変更しても、計算結果が自動で更新される形にしておくと便利です。

まずは、次のような構成でシートを作りましょう。

セル	項目	入力・計算内容
B2	販売単価	例：5,000
B3	販売数量	例：200
B4	1個あたり変動費	例：2,000
B5	固定費	例：300,000

ここまでが、手入力する部分です。入力セルは背景色を薄い黄色などにしておくと、「ここだけ変更すればいい」とひと目でわかります。実務で使う場合も、入力ミスを防ぎやすくなります。

次に、計算用の項目を作ります。たとえば、A7以降に次のような項目を並べてください。

セル	項目	Excelの数式例
B7	売上高	=B2*B3
B8	変動費合計	=B4*B3
B9	限界利益	=B7-B8
B10	限界利益率	=B9/B7
B11	損益分岐点売上高	=B5/B10
B12	損益分岐点販売数量	=B11/B2

この形にしておけば、販売単価や販売数量、変動費、固定費を変更するだけで、売上高や損益分岐点が自動的に再計算されます。

特に重要なのは、損益分岐点売上高と損益分岐点販売数量です。損益分岐点売上高は「いくら売れば赤字を脱出できるか」、損益分岐点販売数量は「何個売れば黒字ラインに届くか」を示します。

たとえば、販売単価が5,000円、1個あたり変動費が2,000円、固定費が30万円の場合、1個売るごとに3,000円の限界利益が出ます。この場合、固定費30万円を回収するには「300,000円 ÷ 3,000円 = 100個」の販売が必要です。Excelで自動計算しておけば、このような計算を毎回手作業で行う必要がありません。

見やすいシートにするコツは、計算結果のセルに太字や罫線を使うことです。特にB11とB12は重要な指標なので、背景色を変えて目立たせるとよいでしょう。また、金額のセルには「通貨」や「桁区切り」の表示形式を設定しておくと、上司やチームメンバーにも伝わりやすくなります。

まずはこのシンプルな形で作っておけば十分です。次のステップでは、売上やコストを変えながら、複数パターンのシミュレーションを行えるようにしていきます。

売上やコストを変えてシミュレーションする方法

損益分岐点を自動計算できるシートが作れたら、次は数値を変えてシミュレーションしてみましょう。Excelの強みは、販売単価・販売数量・変動費・固定費を変更するだけで、利益や損益分岐点がすぐに再計算されることです。

まず試したいのは、販売数量を変えるシミュレーションです。たとえば、現在の販売数量が200個の場合、150個・250個・300個に変えると利益がどう変わるかを確認します。これにより、「最低何個売れば赤字を避けられるか」「目標利益を出すには何個必要か」が見えてきます。

次に、販売単価を変えるシミュレーションも重要です。単価を5,000円から4,500円に下げると販売数は増えそうですが、1個あたりの利益は減ります。一方で、5,500円に上げると少ない販売数でも利益を出しやすくなりますが、売れ行きが落ちる可能性もあります。Excel上で複数パターンを比較すると、感覚ではなく数字で判断できます。

おすすめは、シートの横に次のようなシミュレーション表を作る方法です。

パターン	販売単価	販売数量	固定費	利益
通常ケース	5,000円	200個	300,000円	計算結果
値下げケース	4,500円	260個	300,000円	計算結果
広告強化ケース	5,000円	280個	400,000円	計算結果

このようにパターンを並べると、「値下げして販売数を増やすべきか」「広告費を増やしても採算が合うか」といった判断がしやすくなります。利益の計算式は、基本的に利益＝売上高 − 変動費合計 − 固定費です。

また、Excelのゴールシーク機能を使うと、目標から逆算することもできます。たとえば「利益を10万円にしたい場合、販売数量はいくつ必要か」を調べたいときに便利です。メニューの「データ」から「What-If分析」→「ゴールシーク」を選び、目標値と変化させるセルを指定します。

シミュレーションを行うときのコツは、現実的な数字で比較することです。販売数量をいきなり2倍にする、固定費を極端に下げるといった前提では、実務で使える判断材料になりません。「広告費を5万円増やす」「単価を10％下げる」など、実際に起こりそうな範囲で試すのがおすすめです。

複数の条件を比べることで、損益分岐点シートは単なる計算表ではなく、意思決定に使えるシミュレーションツールになります。

グラフで見える化！損益分岐点をわかりやすく分析するコツ

損益分岐点シミュレーションは、数字の表だけでも確認できますが、より直感的に理解したいならグラフ化するのがおすすめです。特に上司への説明やチームでの共有では、「どこから黒字になるのか」がひと目で伝わります。

損益分岐点をグラフで表すときは、横軸に販売数量、縦軸に金額を設定します。そして、以下の3つの線を表示するとわかりやすくなります。

売上高：販売数量が増えるほど右肩上がりになる線
総費用：固定費＋変動費を表す線
固定費：販売数量に関係なく一定の線

グラフ上では、売上高の線と総費用の線が交わるポイントが損益分岐点です。この交点より左側は費用が売上を上回るため赤字、右側は売上が費用を上回るため黒字になります。

Excelで作る場合は、まず販売数量を0個、50個、100個、150個、200個のように段階的に並べます。その横に、各数量ごとの売上高、変動費、総費用を計算する列を作りましょう。たとえば、販売単価が5,000円、1個あたり変動費が2,000円、固定費が300,000円なら、総費用は「固定費＋変動費」で計算できます。