Excelで損益分岐点分析を計算する方法

Excelには多くの機能があります。通常の集計以外に、Excelは、損益分岐点分析など、さまざまなビジネス計算に役立ちます。
損益分岐点分析式

Excelで損益分岐点分析を計算する方法

損益分岐点分析は、売上高または販売台数の調査であり、企業は利益または損失を考慮せずにすべての費用を賄う必要があります。これは、事業運営のすべての固定費と変動費を組み込んだ後に発生します(出典:Investopedia)。



この投稿では、焦点を当てますExcelを使用して損益分岐点分析を計算する方法について。



損益分岐点分析とは

ビジネスの損益分岐点は、商品(またはサービス)の販売量と生産量が等しい場合です。この時点で、ビジネスはすべてのコストをカバーできます。経済的な意味では、損益分岐点は、損益がゼロであるときの危機的な状況の指標の点です。通常、この指標は量的または金銭的な単位で表されます。

損益分岐点が低いほど、企業の財務の安定性と支払能力は高くなります。



損益分岐点分析は、事業計画と企業財務において重要です。これは、コストと潜在的な売上に関する仮定によって、企業(またはプロジェクト)が収益性に向かって進んでいるかどうかが決まるためです。

損益分岐点分析は、組織/企業が変動費とそのユニットの生産に関連する固定費の一部をカバーする前に販売する必要のあるユニットの数を決定するのに役立ちます。

損益分岐点分析式

損益分岐点を見つけるには、次のことを知っておく必要があります。



  • 固定費
  • 変動費
  • ユニットあたりの販売価格
  • 収益

損益分岐点は、次の場合に発生します。

総固定費(TFC)+総変動費(TVC)=収益

  • 総固定費は、家賃、給与、光熱費、支払利息、償却、減価償却などの既知の項目です。
  • 総変動費には、直接材料、手数料、請求可能な労働力、料金などが含まれます。
  • 収益は単価です * 販売台数

貢献利益

損益分岐点分析を計算する重要な要素は、ユニットを生産するために変動費を差し引いた後、売上からどれだけのマージンまたは利益が生み出されるかを理解することです。これは貢献利益と呼ばれます。したがって:

貢献マリン=販売価格-変動費

Excelの損益分岐点の数式

次の2つの点に関して損益分岐点を計算できます。

ヘッドフォンがPCで動作しないのはなぜですか
  1. 金銭的同等物:(収益*固定費)/(収益-変動費)。
  2. 自然単位:固定費/(価格-平均変動費)。

これを念頭に置いて、Excelで損益分岐点を計算する方法はいくつかあります。

  1. ゴールシーク機能(組み込みのExcelツール)を使用して損益分岐点分析を計算する
  2. 式を使用して損益分岐点分析を計算します
  3. チャートを使用して損益分岐点分析を計算する

ゴールシークで損益分岐点分析を計算する

場合 :新製品を販売したいとします。単位当たりの変動費と固定費の合計はすでにわかっています。可能な販売量を予測し、これを使用して製品の価格を設定します。これがあなたがする必要があることです。

  1. 簡単にする テーブル 、および記入してください アイテム/データ
    Excelテーブル

  2. Excelで、適切な数式を入力して、収益、変動費、および利益を計算します。
    1. 収益=単価x販売台数
    2. 変動費=ユニットあたりのコストx販売されたユニット
    3. 利益=収益–変動費–固定費
    4. これらの式を計算に使用してください。
      Excelの数式計算

  3. Excelドキュメントで、[データ]> [What-If分析]をクリックし、[ゴールシーク]を選択します。
    ゲイルシーク分析
  4. [ゴールシーク]ダイアログボックスを開いたら、次のようにします。
    1. 指定します セットセル この場合の利益セルとしては、セルB7です。
    2. 指定します として評価するには 0
    3. 指定します セルを変更することにより として 単価セル 、この場合はセルB1です。
    4. クリック OK ボタン
      ゴールシークダイアログボックス
  5. [ゴールシークステータス]ダイアログボックスが表示されます。 [OK]をクリックして適用してください。
    ゴールシークステータス

ゴールシークは単価を40から31.579に変更し、純利益は0に変更します。損益分岐点での利益は0であることを忘れないでください。したがって、販売量を50と予測する場合、単価は31.579を下回ることはできません。 。そうしないと、損失が発生します。

数式を使用してExcelで損益分岐点分析を計算する

数式を使用して、Excelの損益分岐点を計算することもできます。方法は次のとおりです。

  1. 簡単なテーブルを作成し、アイテム/データを入力します。このシナリオでは、販売されたユニット、ユニットあたりのコスト、固定費、および利益がわかっていると想定しています。
    データ表

  2. 不足しているアイテム/データを計算するための式を使用してください。
    1. 式を入力= B6 / B2 + B4 セルB1に入力して、単価を計算します。
    2. 式を入力= B 1 * B2 セルB3に入力して、収益を計算します。
    3. 式を入力= B2 * B4 変動費を計算するためにセルB5に入力します。

Excelの数式を使用して損益分岐点を計算します
注意
:たとえば、販売予測単価、単価、固定費などの値を変更すると、単価の値が自動的に変更されます。

チャートを使用して損益分岐点分析を計算する

すでに売上データを記録している場合は、Excelのグラフで損益分岐点を計算できます。方法は次のとおりです。

  1. 販売テーブルを準備します。この場合、販売されたユニット、ユニットあたりのコスト、および固定費はすでにわかっていると想定し、それらは固定されていると想定します。単価で損益分岐点分析を行う必要があります。
    販売表
  2. 式を使用してテーブルの計算を終了します
    Excelの計算
    1. セルE2に、数式= D2 * $ B $ 1と入力し、オートフィルハンドルを範囲E2:E13までドラッグします。
    2. セルF2に、数式= D2 * $ B $ 1 + $ B $ 3を入力し、オートフィルハンドルを範囲F2:F13までドラッグします。
    3. セルG2に、数式= E2-F2と入力し、オートフィルハンドルを範囲G2:G13までドラッグします。
  3. この計算により、損益分岐点チャートのソースデータが得られるはずです。
    ソースデータ
  4. Excelテーブルで、 収益列 コスト列 n、および 利益の列 同時に、クリックします インサート >> 行を挿入 または 面グラフ >> ライン 。これにより、折れ線グラフが作成されます。
    折れ線グラフの作成

  5. 次に、チャートを右クリックします。コンテキストメニューから、をクリックします データを選択します。
    データの選択

  6. の中に データソースを選択 ダイアログボックスで、次の手順を実行します。
    1. の中に 凡例エントリ(シリーズ) セクションで、必要に応じてシリーズの1つを選択します。この場合、 収益 シリーズ
    2. の[編集]ボタンをクリックします 水平(カテゴリ)軸 ラベル セクション
    3. AxisLabelsという名前のダイアログボックスが表示されます。ボックスで、 単価列 (列名を除く)軸ラベル範囲として
    4. クリック OK >> OK 変更を保存します。
      データソースを選択
  7. 損益分岐点チャートと呼ばれるチャットが作成されます。価格が36に等しいときに発生する損益分岐点に気付くでしょう。
    損益分岐点チャート
  8. 同様に、損益分岐点チャートを作成して、販売単位ごとの損益分岐点を分析できます。

完了です。その単純な。

まとめ

データセクションとデザインツールを使用して、データの外観を微調整できます。 Excelを使用すると、データを使用して他の多くのことを実行できます。

より多くのガイドを探している場合、またはより多くの技術関連の記事を読みたい場合は、ニュースレターの購読を検討してください。チュートリアル、ニュース記事、ガイドを定期的に公開しています。

おすすめの読み物

  1. あなたをプロにするための13のExcelのヒントとコツ
  2. 生産性を高めるトップ51のExcelテンプレート
  3. Microsoft OfficeExcelのチートシート
  4. 最も便利なExcelキーボードショートカット
  5. どのバージョンのExcelを使用していますか?

エディターズチョイス


ActiveDirectoryとLDAPの違い

ヘルプセンター


ActiveDirectoryとLDAPの違い

ディレクトリサービスプロバイダーを選択するときは、ActiveDirectoryとLDAPのどちらかを選択できます。それらの違いと仕事上の関係については、こちらをご覧ください。

続きを読みます
SeanとConorPriceがリムリックのArdScoil Mhuireでステージに登場!

ニュース


SeanとConorPriceがリムリックのArdScoil Mhuireでステージに登場!

3月23日金曜日、コーバリーの女子校、Co.Limerickは熱心に...の到着を待ちました。

続きを読みます