損益分岐点分析はエクセルの数式では固定費変動費の混在費用と感度分析で限界が来ますが、分解の型を押さえれば実務レベルまで机上で計算し切れます。
無料相談受付中いきなり作らない。
AIで何がどう変わるかを、先に見極める。
- ノーコードの卒業先、AIネイティブ受託。事業の文脈で要件から実装まで伴走
- 45分・Web。検討段階のご相談・資料だけでも歓迎。しつこい追客はしません
目次
損益分岐点(BEP)分析をエクセルで行う限界 固定費変動費分解と感度分析の実務
損益分岐点分析は固定費÷限界利益率というシンプルな数式で計算できますが、費用の性質分解と感度分析をエクセルで回そうとすると、シートが増殖し検算が効かなくなる限界が早い段階で訪れます。
エクセルで損益分岐点分析に取り組む経営者・経理担当者のイメージ
売上がいくらあれば赤字にならないか――多くの経営者・経理責任者が最初に手を伸ばすツールはエクセルです。関数を組めば損益分岐点(BEP)自体はすぐに計算できますが、費用の分解や感度分析まで手を広げると、途端にシートが複雑になり検算が効かなくなります。本記事では、エクセルでBEP分析を組む基本手順と、実務でぶつかる限界、そして限界を超える現実的な進め方を解説します。
損益分岐点(BEP)とは何か エクセルで計算する基本の考え方
損益分岐点売上高は「固定費 ÷ 限界利益率」で求まり、限界利益率は「(売上高 − 変動費) ÷ 売上高」で計算します。
たとえば月商1,000万円・変動費600万円・固定費300万円の会社であれば、限界利益率は(1,000万円−600万円)÷1,000万円=40%です。損益分岐点売上高は固定費300万円÷限界利益率40%=750万円となり、月商が750万円を下回ると赤字に転じる計算になります。逆に言えば、現状の月商1,000万円は損益分岐点を250万円上回っており、利益率を維持できれば安全余裕率は25%(250万円÷1,000万円)ある、という読み方ができます。
エクセルでこの計算式自体を組むのは難しくありません。SUM関数で固定費・変動費を集計し、割り算のセルを1つ作れば済みます。問題は次のステップ、つまり「どの費用が固定費で、どの費用が変動費か」を仕分ける工程から始まります。ここでつまずく会社が実務では圧倒的に多いというのが実感です。
固定費300万円・限界利益率40%から損益分岐点売上高750万円を導く計算構造
エクセルでBEP分析を組む具体的な手順 固定費と変動費の分解が最初の壁
BEP分析の精度は計算式ではなく、費用を固定費と変動費に仕分ける「費用分解」の工程で決まります。
固定費は売上の増減に関わらず一定にかかる費用（地代家賃・正社員の人件費・減価償却費など）、変動費は売上に比例して増減する費用（材料費・仕入原価・外注加工費など）です。実際に損益分岐点分析の相談を受けていると、水道光熱費・通信費・パート人件費のような「半分固定・半分変動」の性質を持つ費用を、どちらかに全額寄せてしまっているケースに非常によく出会います。全額を固定費に寄せると限界利益率が過大に出て損益分岐点を低く見誤り、逆に全額を変動費に寄せると損益分岐点を高く見積もりすぎる、という具合に、寄せ方ひとつで経営判断に使う数字がぶれてしまいます。
実務でよく使われる分解方法は、直近6〜12か月の実績データを費目ごとに並べ、売上高との相関を目視またはグラフで確認したうえで、相関が弱い費目は固定費、強い費目は変動費に仕分ける「勘定科目法」です。水道光熱費のように性質が混在する費目は、過去の請求書から基本料金部分（固定）と使用量に応じた従量部分（変動）を按分して分けると精度が上がります。この仕分け表をエクセルの別シートに一覧化し、BEP計算シートから参照させる構成にしておくと、翌月以降の更新が楽になります。
固定費・変動費の仕分け表とBEP計算シートを連動させる構成イメージ
感度分析をエクセルでやろうとすると起きる限界
感度分析（変動費率や固定費が変わったときにBEPがどう動くかの試算）をエクセル1枚でやろうとすると、条件の組み合わせごとにシートが増殖し、どれが最新かわからなくなる限界が早期に訪れます。
先ほどの例で、仕入価格の上昇により変動費率が60%から65%に上がったケースを試算してみます。限界利益率は35%(100%−65%)に下がるため、損益分岐点売上高は固定費300万円÷35%=約857万円まで上昇します。変動費率がわずか5ポイント動いただけで、損益分岐点は750万円から857万円へ約107万円(約14%)も切り上がる計算です。この程度の感度分析であれば1つのシートで足りますが、実務では「原材料費が10%上がったら」「人件費を1人増やしたら」「賃料が改定されたら」といった条件を複数組み合わせて試算したくなります。
こうなると、条件ごとにシートをコピーして数値だけ書き換える運用になりがちで、気づけば「BEP試算_v2」「BEP試算_人件費増パターン」のようにファイルが乱立します。どのシートの数式が最新の費用分解ロジックを反映しているか誰も把握できなくなり、経営会議で使う数字がシートごとに微妙に食い違う、という事態も珍しくありません。単一事業ならまだしも、事業部・店舗が複数ある会社では、事業ごとのBEPを串刺しで比較する集計作業自体が大きな負荷になります。
変動費率が5ポイント上昇するだけで損益分岐点売上高が約107万円切り上がる例
限界のサイン いつ仕組み化に切り替えるべきか
BEP分析をエクセルで回す限界は、「感度分析の条件分岐が3パターンを超える」「事業・店舗単位の比較が必要になる」「更新のたびに数式を組み直している」の3つのサインで判断できます。
下の表は、実務でよく観測される限界のサインと、仕組み化を検討すべき目安をまとめたものです。
| 限界サイン | 目安となる状態 |
|---|---|
| 感度分析の条件分岐 | 3パターン以上を並行して試算したい |
| 事業・店舗の比較単位 | 2つ以上の事業部・店舗のBEPを串刺しで見たい |
| 更新頻度 | 月次で数式・参照を都度組み直している |
| 関与人数 | 経理担当以外（経営者・現場責任者）も数字を確認したい |
これらのうち2つ以上に当てはまる場合、エクセル単体での運用は工数対効果が見合わなくなってきているサインです。逆に、単一事業・年に数回の試算で足りるうちは、費用分解表さえ整理されていればエクセルで十分に実務が回ります。
限界サインのチェックリストを確認する実務担当者のイメージ
限界を超える進め方 シートを捨てず段階的に精度を上げる
エクセルを一気に捨てて高機能なBIツールへ移行する必要はなく、費用分解の精度を上げてから感度分析の型を固定するという2段階で進めるのが現実的です。
STEP1では、まず固定費・変動費の仕分け表を独立したシートとして整備し、按分ロジックを数式ではなく注釈で明文化します。これだけで、担当者が変わっても仕分けの根拠を追える状態になり、BEP計算の土台が安定します。STEP2では、感度分析用のシートを「条件を入力すれば自動で再計算される」テンプレート形式に固定し、条件ごとにシートを増殖させる運用をやめます。データテーブル機能を使えば、変動費率や固定費を1か所変えるだけで複数パターンを一覧比較できます。
事業部・店舗が増えてきて串刺し比較そのものが負荷になっている場合は、集計の仕組み化を検討するタイミングです。自社の費用構造のどこから手をつけるべきか判断に迷う場合は、初月無料の経営AI診断（通常30万円相当）で、固定費・変動費の分解状況と感度分析の運用負荷を一緒に可視化し、優先順位を整理することもできます。
費用分解の整備と感度分析テンプレート化の2ステップで精度を上げる進め方
まとめ：損益分岐点分析は数式より費用分解と感度分析の型で決まる
損益分岐点(BEP)分析の計算式自体はエクセルで十分に組めますが、実務の限界は「固定費・変動費の分解精度」と「感度分析の条件管理」で訪れます。まずは費用分解表を独立させて根拠を明文化し、感度分析はテンプレート化してシートの増殖を止める。この2段階を押さえれば、規模が大きくなるまではエクセルのままで実務レベルの採算判断ができます。自社の分解ロジックや感度分析の運用に不安がある場合は、初月無料の経営AI診断（通常30万円相当）で現状を一緒に確認してみてください。
よくある質問
損益分岐点(BEP)をエクセルで計算する基本の数式は何ですか？
損益分岐点売上高は「固定費 ÷ 限界利益率」で計算し、限界利益率は「(売上高 − 変動費) ÷ 売上高」で求めます。たとえば月商1,000万円・変動費600万円・固定費300万円であれば、限界利益率は40%、損益分岐点売上高は300万円÷40%=750万円です。エクセルではSUM関数で固定費・変動費を集計したうえで、この割り算を1つのセルに組めば計算自体は完結します。ただし数式が正しくても、費用の仕分けが誤っていれば結果も誤りますので、次の費用分解の精度が実務上はより重要になります。
固定費と変動費の分解でよくある失敗は何ですか？
最も多いのは、水道光熱費・通信費・パート人件費のように売上と部分的にしか連動しない「半分固定・半分変動」の費用を、どちらか一方に全額寄せてしまう失敗です。全額を固定費に寄せると限界利益率が実態より高く出て損益分岐点を低く見誤り、逆に全額を変動費に寄せると損益分岐点を高く見積もりすぎます。過去の請求書から基本料金部分と従量部分を按分して分ける、あるいは直近6〜12か月の実績と売上高の相関を確認して仕分ける「勘定科目法」を使うと、この誤差を小さくできます。
感度分析をエクセルでやるとなぜ限界が来るのですか？
感度分析は「変動費率が上がったら」「固定費が増えたら」といった条件ごとに損益分岐点を試算する作業ですが、条件を1シートに1パターンずつ作っていくと、条件を組み合わせるほどシートが増殖します。どのシートが最新の費用分解ロジックを反映しているか分からなくなり、経営会議で使う数字がシートごとに食い違う、という事態が起きやすくなります。条件分岐が3パターンを超えたあたりから、テンプレート化やデータテーブル機能への切り替えを検討すべきサインです。
どのタイミングで損益分岐点分析をエクセルから仕組み化すべきですか？
「感度分析の条件分岐が3パターンを超える」「事業・店舗単位でBEPを串刺し比較したい」「更新のたびに数式を組み直している」の3つのうち2つ以上が当てはまるタイミングが目安です。単一事業で年数回の試算に留まるうちは、費用分解表さえ整理されていればエクセルで十分に実務を回せます。逆にこれらのサインが重なってきたら、費用分解表の明文化と感度分析のテンプレート化という2段階で、無理なく精度を引き上げていくのが現実的です。
関連記事
- 管理会計をエクセルで回す限界のサインと脱却タイミングの見極め方 — 関連: 管理会計全体の限界サインを整理
- 商品別・案件別の粗利管理をエクセルで行う限界と原価紐付けの実務知 — 関連: 原価紐付けと採算判断の実務
- プロジェクト別原価と損益をエクセルで管理する限界と採算把握の実務 — 関連: プロジェクト単位の採算把握
- 取引先別・得意先別の採算管理をエクセルで行う実務と、儲かる取引先の見極め方 — 関連: 取引先単位の採算分析
- 会計事務所のAI活用 記帳と問い合わせ対応を半自動化する実務手順 — 関連: 会計業務のAI活用（異クラスタ）
「まず費用感だけ知りたい」という方へ。
1分で概算費用がわかるシミュレーターをご用意しています。
よくある質問
- Q. 損益分岐点(BEP)をエクセルで計算する基本の数式は何ですか？
- A. 損益分岐点売上高は「固定費 ÷ 限界利益率」で計算し、限界利益率は「(売上高 − 変動費) ÷ 売上高」で求めます。たとえば月商1,000万円・変動費600万円・固定費300万円であれば、限界利益率は40%、損益分岐点売上高は300万円÷40%=750万円です。エクセルではSUM関数で固定費・変動費を集計したうえで、この割り算を1つのセルに組めば計算自体は完結します。ただし数式が正しくても、費用の仕分けが誤っていれば結果も誤りますので、次の費用分解の精度が実務上はより重要になります。
- Q. 固定費と変動費の分解でよくある失敗は何ですか？
- A. 最も多いのは、水道光熱費・通信費・パート人件費のように売上と部分的にしか連動しない「半分固定・半分変動」の費用を、どちらか一方に全額寄せてしまう失敗です。全額を固定費に寄せると限界利益率が実態より高く出て損益分岐点を低く見誤り、逆に全額を変動費に寄せると損益分岐点を高く見積もりすぎます。過去の請求書から基本料金部分と従量部分を按分して分ける、あるいは直近6〜12か月の実績と売上高の相関を確認して仕分ける「勘定科目法」を使うと、この誤差を小さくできます。
- Q. 感度分析をエクセルでやるとなぜ限界が来るのですか？
- A. 感度分析は「変動費率が上がったら」「固定費が増えたら」といった条件ごとに損益分岐点を試算する作業ですが、条件を1シートに1パターンずつ作っていくと、条件を組み合わせるほどシートが増殖します。どのシートが最新の費用分解ロジックを反映しているか分からなくなり、経営会議で使う数字がシートごとに食い違う、という事態が起きやすくなります。条件分岐が3パターンを超えたあたりから、テンプレート化やデータテーブル機能への切り替えを検討すべきサインです。
- Q. どのタイミングで損益分岐点分析をエクセルから仕組み化すべきですか？
- A. 「感度分析の条件分岐が3パターンを超える」「事業・店舗単位でBEPを串刺し比較したい」「更新のたびに数式を組み直している」の3つのうち2つ以上が当てはまるタイミングが目安です。単一事業で年数回の試算に留まるうちは、費用分解表さえ整理されていればエクセルで十分に実務を回せます。逆にこれらのサインが重なってきたら、費用分解表の明文化と感度分析のテンプレート化という2段階で、無理なく精度を引き上げていくのが現実的です。
あわせて読みたい
