Excel で損益分岐点分析を行うには？

新製品を販売するにあたり、1 単位あたりの変動費と総固定費が既知であるとします。このとき、まず想定される販売数量を予測し、その予測に基づいて製品の価格を設定することになります。

1。シンプルな表を作成し、既知のデータを入力しましょう。下記のスクリーンショットをご確認ください：

2。収益、変動費、利益を正確に計算するための数式を以下に入力してください。上記のスクリーンショットをご参照ください：
収益 = 単価 × 販売数量
変動費= 1 単位あたりのコスト × 販売数量
利益 = 収益 − 変動費 − 固定費

3。データ>What-If 分析>ゴールシークをクリックします。

4。表示されたゴールシークダイアログボックスで、次のように操作してください（上記のスクリーンショットを参照）：
(1)目標値の設定セルとして利益セル（この例ではセル B7）を指定します。
(2)目標値を0に設定します。
(3)変化させるセルとして単価セル（この例ではセル B1）を指定します。
(4)OKボタンをクリックします。

5。すると、ゴールシーク ステータス ダイアログ ボックスが表示されます。OK ボタンをクリックして適用します。

これにより、単価は40 から31.579 に変更され、純利益はゼロとなります。したがって、販売数量が50 になると予測される場合、単価が31.579 を下回ると損失が発生します。

複雑な損益分岐点テンプレートをダウンロード

http://office.microsoft.com/en-us/templates/breakeven-analysis-TC001116512.aspx
http://office.microsoft.com/en-us/templates/break-even-analysis-TC001017515.aspx

ゴールシーク機能と比べて、Excel では数式を使って損益分岐点分析をより簡単に実行することも可能です。

1。簡単な表を作成し、既知のデータを入力します。ここでは利益がゼロであると仮定しており、販売数量、1 単位あたりのコスト、固定費はすでに予測済みです。下記のスクリーンショットをご覧ください：

2。次の数式を表に入力します：セル B1 に=B6/B2+B4を入力して単価を計算し、セル B3 に=B1*B2を入力して収益を計算し、さらにセル B5 に=B2*B4を入力して変動費を計算します。下記のスクリーンショットをご覧ください：

その後、予測販売数量、1 単位あたりのコスト、または固定費のいずれかを変更すると、単価が自動的に更新されます。上記のスクリーンショットをご参照ください。

ヒント：範囲をオートテキストエントリとして保存すれば（セルの書式と数式を保持）、いつでも簡単に再利用できます。
損益分岐点分析のたびにセルを参照して複数の数式を手動で適用するのは、非常に面倒ですよね。Kutools for Excel なら、オートテキストユーティリティを使えば、その範囲全体をオートテキストエントリとして保存でき、セルの書式や数式もそのまま保持されます。その後は、どのワークブックでもワンクリックでこの範囲を瞬時に再利用可能！

今すぐ30 日間の全機能無料トライアルを入手してください！

すでに販売データを記録している場合は、Excel でグラフを使用して損益分岐点分析を行うこともできます。この方法では、簡単に損益分岐点チャートを作成できます。

1。下記のスクリーンショットのように販売表を準備します。この例では、販売数量、単位あたりのコスト、固定費が固定されており、単価に基づいて損益分岐点分析を行う必要があります。

2。下記の手順で表を完成させましょう：
(1) セル E2 に数式 =D2*$B$1を入力し、オートフィルハンドルをセル範囲 E2:E13 までドラッグします。
(2) セル F2 に数式 =D2*$B$1+$B$3を入力し、オートフィルハンドルをセル範囲 F2:F13 までドラッグします。
(3) セル G2 に数式 =E2-F2を入力し、オートフィルハンドルをセル範囲 G2:G13 までドラッグします。

これで、後ほど作成する損益分岐点チャートのソースデータが完成です！下記のスクリーンショットをご確認ください：

3。表で、収益列、費用列、利益列を同時に選択し、次に挿入>折れ線グラフまたは面グラフの挿入>折れ線グラフをクリックします。スクリーンショットをご覧ください：

4。これで折れ線グラフが完成しました！グラフを右クリックし、コンテキストメニューから「データの選択」を選択してください。下記のスクリーンショットをご確認ください：

5。データの選択元のデータダイアログボックスで、次のように操作します：
(1) 系列名の範囲セクションで、必要な系列を1 つ選択します。この例では、収益系列を選択します。
(2) 編集ボタンを、水平軸ラベルセクションでクリックします。
(3) 表示された「軸ラベルの範囲」ダイアログボックスで、列名を除いた「単価」列を軸ラベル範囲として指定します。
(4) OK > OKをクリックして、変更を保存します。

これで損益分岐点チャートに、下記のスクリーンショットのように価格が36 に等しいときに損益分岐点が発生することが表示されます：

同様に、下記のスクリーンショットのように販売数量に基づいて損益分岐点を分析する損益分岐点チャートを作成することもできます：

Excel で選択範囲を個別の PDF/CSV/TEXT/HTML ファイルとして簡単にエクスポート／保存

たとえば、Excel で損益分岐点分析を完了した後、その分析結果をもとにプレゼンテーション資料を作成したいこともあるでしょう。そんなときは、Kutools for Excel の範囲をファイルにエクスポートユーティリティを使えば、選択範囲を PDF ファイル、HTML ファイル、CSV ファイル、テキストファイルなど、さまざまな形式で素早く個別のファイルとしてエクスポートできます。