ExcelでNPV損益分岐分析をマスターする方法：ステップバイステップ完全ガイド

NPV損益分岐分析は、ビジネスプロセスを理解するために最も広く活用されているツールの一つであり、将来の投資判断を形作る重要な指標でもあります。本記事では、Excelを使ってNPV損益分岐点に必要な製品生産数を求めるための、シンプルなステップバイステップの手順をわかりやすく解説します。

NPV損益分岐分析とは

NPV損益分岐分析を用いることで、企業が利益を出すために何単位の製品やサービスを販売する必要があるのかを把握できます。言い換えれば、この分析は営業費用をカバーするために必要な売上数量を示すものです。損益分岐分析は、企業のすべての固定費を賄うために必要な販売数量を確定します。また、需要側からの分析によって、販売者の販売力についても洞察が得られます。損益分岐点（数量）の数学的な表現は以下の通りです。

BEQ ＝ 損益分岐数量（単位数）

FC ＝ 固定費

P ＝ 単価

VC ＝ 単位あたり変動費

損益分岐分析の前提条件

• 総コストは固定費と変動費に分類でき、準変動費は無視できる。
• 収益関数とコスト関数は線形であると仮定する。
• 製品の価格は変更されないと仮定する。
• 販売数量と製造数量は等しいと仮定する。
• 対象となる生産量の範囲内では、固定費は一定である。
• 変動費の増加率は一定であると仮定する。
• 技術は変化せず、労働生産性も向上しないと仮定する。
• 要素価格には影響がないものとする。
• 投入物の価格変動はゼロとして扱う。
• 複数製品を扱う企業の場合、製品ミックスは一定であると仮定する。

Excelで行うNPV損益分岐分析：ステップバイステップ手順

ここでは、ある企業の以下のデータを使用して損益分岐分析を実施します。互換性の問題を避けるため、Microsoft Excel 365の使用をおすすめします。

STEP 1：総キャッシュインフローを計算する

NPV損益分岐点の計算に入る前に、まず計算の基本構造を整える必要があります。最初に、製品からのキャッシュインフローを計算します。

• まず、製品の収益ストリームを計算することで、事業への総キャッシュフローを求めます。
• そのために、製品の単価を入力します。
• 初期の生産単位数は「1」に設定します。

• 次に、「単価」に「生産単位数」を掛け合わせることで、総売上高を求めます。
• セルC7を選択し、以下の数式を入力します。

STEP 2：総変動費を求める

総キャッシュインフローを計算した後は、製品の製造にかかる変動費を計算します。

• 変動費を計算するには、まずセルC10に単位あたりの変動費を入力します。

• 総変動費を求めるには、セルC11を選択して以下の数式を入力します。
• Enterキーを押すと、総変動費が表示されます。

STEP 3：利益を計算する

売上高とすべてのコストの見積もりが完了したので、利益の計算に進みます。

• 総固定費をセルC14に入力します。

• 次に、セルC15を選択して以下の数式を入力し、総コストを計算します。

• 続いて、セルC16を選択して以下の数式を入力すると、総利益を求められます。
• Enterキーを押すと、総利益の結果が表示されます。

STEP 4：ゴールシーク機能でNPV損益分岐分析を行う

利益の計算が完了したので、ゴールシークツールを実行してNPV損益分岐分析の推定を始めましょう。

• キャッシュフローを最適化するために、ゴールシークツールを起動します。
• まず、データタブ → 予測 → What-If分析 → ゴールシークの順にクリックします。

• ゴールシークをクリックすると、新しいウィンドウが開きます。
• そのウィンドウで、設定するセルボックスにセルC16を指定します。

• 次に、変化させるセルボックスにセルC6を指定します。
• 目标値ボックスには「0」を設定します。

• 処理が完了すると、最適な生産単位数がセルC6に表示されます。
• これにより、利益がゼロになる条件での最適生産単位数、つまりこの事業の損益分岐点の値を求めることができました。

STEP 5：Excelで損益分岐結果を検証する

結果は得られましたが、必ずしも望ましい形式ではない場合があります。そこで、出力結果を理解し、適切に修正する必要があります。例えば：

• 現時点の結果は小数値になっているため、値を調整する必要があります。
• 生産単位数を「0」と入力すると、利益は正の「150」となり、利益が150であることを意味します。

• 一方、空いているセルに整数「53」を入力すると、利益の値がマイナスになることがわかります。
• したがって、利益の値が正となるように選択する必要があります。そうしないと正味現在価値（NPV）も正にならず、事業運営が成功しないことを意味します。
• よって、最終的な最適生産単位数は「55」となります。

損益分岐分析のメリットとデメリット

損益分岐分析には多くのメリットがある一方で、すべての投資家が理解し覚えておくべき注意点も存在します。

メリット

• さまざまな生産・販売水準における損益を計算できる。
• 価格調整が売上に与える影響を把握できる。
• 変動費と固定費の相互関係を分析できる。
• コストや効率の変更が収益性にどう影響するかを判定できる。

デメリット

• 損益分岐計算では一定の状況を前提としており、コスト関数は線形で販売価格は一定とされるが、実際はそうならないケースが多い。
• 損益分岐点では、関数を一定とみなして過去のデータから将来を予測するため、誤りが生じる可能性がある。
• コスト・収益・生産量の関係が線形であるという考え方は、限られた生産量の範囲内でのみ成立するため、長期的な利用には適さない。
• 利益は生産量だけでなく、技術の進歩や管理手法の改善など、この分析では考慮されない他の要因にも左右される。

まとめ

以上、ExcelでNPV損益分岐分析を行う方法を、ステップバイステップの手順で解説しました。練習用のワークブックもダウンロード可能なので、ぜひ実際にこれらの方法を試してみてください。ご質問やフィードバックがあれば、コメント欄からお気軽にお寄せください。