メインコンテンツへスキップ

Excelで損益分岐点分析を行う方法は?

損益分岐点分析は、純利益がゼロの場合、つまり総収益が総費用に等しい場合にポイントを取得するのに役立ちます。 コストと売上を予測できる場合は、新製品の価格を設定すると非常に便利です。


ゴールシーク機能を使用して損益分岐点分析を行う

新製品を販売する予定で、ユニットあたりの変動費と固定費の合計がわかっているとします。 次に、可能な販売量を予測し、それに基づいて製品の価格を設定します。

1。 簡単なテーブルを作成し、テーブル内の指定されたデータをアイテムに入力します。 以下のスクリーンショットを参照してください。

2。 適切な式を入力して、収益、変動費、および利益を計算します。 上記のスクリーンショットを参照してください。
収入 =単価x販売台数
可変経費 =ユニットあたりのコストx販売されたユニット
利益 =収益–変動費–固定費

3。 クリック 且つ > What-If分析 > ゴールを目指す.

4。 最初のゴールシークダイアログボックスで、次のようにしてください(上のスクリーンショットを参照)。
(1)を指定します セットセル 利益セルとして、この場合はセルB7です。
(2)を指定します 貴ぶ as 0;
(3)を指定します セルを変更する 単価セルとして、この場合はセルB1です。
(4)をクリック OK

5。 次に、[ゴールシークステータス]ダイアログボックスが表示されます。 クリックしてください OK それを適用するボタン。

これで、単価が40から31.579に変更され、純利益が0に変更されます。したがって、販売量が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のゴールシーク機能を使用して損益分岐点分析を行う


Kutools for Excel: 300 を超える便利なツールをすぐに利用できます。 機能制限なしの 30 日間の無料トライアルを今すぐ始めてください。 今すぐダウンロード!

複数のワークシート/ワークブック/ CSVファイルをXNUMXつのワークシート/ワークブックに簡単に組み合わせる

異なるワークブックの数十枚のシートをXNUMX枚のシートにまとめるのは面倒かもしれません。 しかし、Kutools forExcelの 組み合わせる(ワークシートとワークブック) ユーティリティ、あなたはそれを数回クリックするだけで成し遂げることができます!


広告コンバインシートブック1

式を使用して損益分岐点分析を行う

ゴールシーク機能と比較すると、Excelで損益分岐点分析を簡単に行うために数式を適用することもできます。

1。 簡単なテーブルを作成し、テーブル内の指定されたデータをアイテムに入力します。 この方法では、利益が0であると仮定し、販売台数、XNUMX台あたりのコスト、および固定費をすでに予測しています。 以下のスクリーンショットを参照してください。

2。 表に、式を入力します = B6 / B2 + B4 単価を計算するためにセルB1に、数式を入力します = B1 * B2 収益を計算するためにセルB3に入力し、数式を入力します = B2 * B4 変動費についてはセルB5に。 以下のスクリーンショットを参照してください。

そして、販売予測単価、単価、固定費のいずれかの値を変更すると、単価の値が自動的に変更されます。 上記のスクリーンショットを参照してください。

ヒント: 将来再利用するために、範囲を定型句(残りのセル形式と数式)として保存します
セルを参照し、毎回損益分岐点分析に複数の数式を適用するのは非常に面倒です。 Kutools for Excelは、次のかわいい回避策を提供します。 定型文 範囲を定型句として保存するユーティリティ。セルの形式と数式を範囲内に残すことができます。 そして、どのワークブックでもワンクリックでこの範囲を再利用できます。



チャートで損益分岐点分析を行う

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

1。 以下のスクリーンショットに示すように、販売テーブルを準備します。 この場合、販売台数、単価、固定費は固定であると想定しており、損益分岐点を単価で分析する必要があります。

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

これまでのところ、後で作成する損益分岐点チャートのソースデータは完成しています。 以下のスクリーンショットを参照してください。

3。 表で、[収益]列、[コスト]列、[利益]列を同時に選択し、[ インセット > 折れ線グラフまたは面グラフを挿入 > LINE。 スクリーンショットを参照してください:

4。 これで折れ線グラフが作成されます。 グラフを右クリックして、コンテキストメニューから[データの選択]を選択してください。 以下のスクリーンショットを参照してください。

5. [データソースの選択]ダイアログボックスで、次のことを行ってください。
で(1) 凡例エントリ(シリーズ) セクションで、必要に応じてシリーズのXNUMXつを選択します。 私の例では、 収入 シリーズ;
(2)をクリックします 編集 ボタンの 水平(カテゴリ)軸ラベル セクション;
(3)[軸ラベルのポップアウト]ダイアログボックスで、[単価]列(列名を除く)を軸ラベルの範囲として指定してください。
(4)をクリック OK > OK を入力して変更を保存してください。

損益分岐点チャートでは、以下のスクリーンショットに示すように、価格が36に等しいときに損益分岐点が発生することがわかります。

同様に、以下のスクリーンショットに示すように、損益分岐点チャートを作成して、販売台数ごとの損益分岐点を分析することもできます。

デモ:Excelのグラフで損益分岐点分析を行う


Kutools for Excel: 300 を超える便利なツールをすぐに利用できます。 機能制限なしの 30 日間の無料トライアルを今すぐ始めてください。 今すぐダウンロード!

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

たとえば、Excelで損益分岐点分析を終了するときに、この損益分岐点分析を使用してプレゼンテーションを作成することができます。 この状態で、Kutools forExcelを適用できます。 範囲をファイルにエクスポート 選択範囲を個別のPDFファイル、HTMLファイル、CSVファイル、テキストファイルなどとしてすばやくエクスポートするユーティリティ。


doc損益分岐点分析広告2

最高のオフィス生産性向上ツール

人気の機能: 重複を検索、強調表示、または識別する   |  空白行を削除する   |  データを失わずに列またはセルを結合する   |   数式なしのラウンド ...
スーパールックアップ: 複数の基準の VLookup    複数の値の VLookup  |   複数のシートにわたる VLookup   |   ファジールックアップ ....
詳細ドロップダウン リスト: ドロップダウンリストを素早く作成する   |  依存関係のドロップダウン リスト   |  複数選択のドロップダウンリスト ....
列マネージャー: 特定の数の列を追加する  |  列の移動  |  Toggle 非表示列の表示ステータス  |  範囲と列の比較 ...
注目の機能: グリッドフォーカス   |  デザインビュー   |   ビッグフォーミュラバー    ワークブックとシートマネージャー   |  リソースライブラリ (自動テキスト)   |  日付ピッカー   |  ワークシートを組み合わせる   |  セルの暗号化/復号化    リストごとにメールを送信する   |  スーパーフィルター   |   特殊フィルター (太字/斜体/取り消し線をフィルター...) ...
上位 15 のツールセット12 テキスト 工具 (テキストを追加, 文字を削除する、...)   |   50+ チャート 種類 (ガントチャート、...)   |   40+ 実用的 (誕生日に基づいて年齢を計算する、...)   |   19 挿入 工具 (QRコードを挿入, パスから画像を挿入、...)   |   12 変換 工具 (数字から言葉へ, 通貨の換算、...)   |   7 マージ&スプリット 工具 (高度な結合行, 分割セル、...)   |   ... もっと

Kutools for Excel で Excel スキルを強化し、これまでにない効率を体験してください。 Kutools for Excelは、生産性を向上させ、時間を節約するための300以上の高度な機能を提供します。  最も必要な機能を入手するにはここをクリックしてください...

kteタブ201905


Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
Comments (6)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi,
Thanks for explaining all the details about making a grafiek. It was verry usefull. Only thing I am missing is in how many time u have u're break-even. I can see what the price it needs to be to have a break even, but when u just start u have no idea how many sold units u sell in how many time. So if we just estimate it, on what we estimate it?
This comment was minimized by the moderator on the site
How do I use this same formula when the variable cost differs based on quantity sold? Like the variable cost =$10 if I sell between 0-100, $9 if I sell 101-200, etc. Is this when I am supposed to used a data table? Totally confused, any help appreciated!
This comment was minimized by the moderator on the site
Hi, is it possible that I don't have any fixed costs? I'm buying and selling stuff from home. If I have a cost per unit amount, would this also be considered a 'fixed cost'? Thanks Felix
This comment was minimized by the moderator on the site
cost per unit is a variable cost. (It VARIES on how many units you sell is a good way to remember it)
This comment was minimized by the moderator on the site
When attempting to download the example using a MAC you get a .cat file that is.... useless...
This comment was minimized by the moderator on the site
Simply, understandable and efficient way of doing it! Thanks for simplyfying economy :P
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations