Excel で損益分岐点分析グラフを作成する:ステップバイステップ ガイド

このデータセットには、単位での販売数量、販売単価、固定費の構成要素、および変動費の構成要素が含まれます。

ステップ 1 – さまざまなコスト構成要素を計算する

手順:

• C7 を選択します 次のように入力します。

• SUM 関数をダブルクリックします。 選択するか、Tab を押します。 .

• C8:C12 を選択します。 (固定費)。

• C7 でこの数式を使用します :

• ENTER を押します。

• この数式をC8で使用します 変数の合計の Cos を見つけます。

平均変動費 (各製品単位の変動費の平均) は、変動費の合計を生産単位の合計で割ることで求めることができます。

• 数式を使用します。

続きを読む: Excel の数式を使用して損益分岐点分析を計算する方法

ステップ 2 – 損益分岐点売上高を計算する

手順:

• C10 を選択します 以下の数式を入力します。

• ENTER を押します。 .

• C11 に移動します 次の数式を入力します。

• ENTER を押します。 .

6071 売却後 ユニットが増えれば、会社は損益分岐点に達するでしょう。販売額は$30,357となります。 .

続きを読む: Excel の数式を使用して損益分岐点売上高を計算する方法

ステップ 3 – コスト、収益、利益の表を作成する

手順:

• B4:G14 で表の基本的なアウトラインを作成します。 .

• 単位に単位を昇順で入力します。 列。

• C5 に移動します 次の数式を入力します。

• ENTER を押します。 .

• C5 の右下隅にあるフィル ハンドルを見つけます。 :プラス (+ ) に署名します。
• それをダブルクリックします。

他のセルは自動的に入力されます。

• D5 を選択します 以下の式を使用してください。

ユニットの数 平均変動コストを掛けたもの 。 絶対セル参照

が使用されました。

• 変動費を固定費に加算して総コストを取得します。 E5 .

• 収益を求めるには、 B5 の単位を掛けます。 C5 の販売単価による 損益分岐点ワークシートに記載します。
• F5 に移動します 以下の数式を入力します。

• ENTER を押します。 .

• G5 に移動します 次の式を使用します。

• ENTER を押します。 .

ステップ 4 – 損益分岐点グラフの挿入

手順:

• 利益のない表を選択します 列。
• 挿入で [散布図 (X、Y) またはバブル チャートの挿入] をクリックします。
• 滑らかな線とマーカーを使用した散布を選択します。 .

チャートが表示されます。

• 任意の系列の線上にあるマーカーを右クリックします。

コンテキスト メニュー内:.

• [データ シリーズのフォーマット…] をクリックします。 .

データ系列の形式:

• 塗りつぶしと線をクリックします。 .
• マーカーを選択します .
• マーカー オプションを展開し、なしを選択します。 .
• 塗りつぶしを展開します。 セクションで [塗りつぶしなし] を選択します。 .

他のシリーズでも同じ手順に従います。

固定費と変動費の境界を区別するため。

• 固定費の行を右クリックします。
• データ シリーズのフォーマット… を選択します。 .

データ シリーズのフォーマット内 :

• 塗りつぶしと線をクリックします。 .
• ライン内 、ダッシュの種類を設定します。 長鎖点として .

これが出力です。

ステップ 5 – グラフの損益分岐点を決定する

手順:

• プロット領域内で右クリックします。
• [データの選択…] を選択します。 .

• [追加] をクリックします。  [データ ソースの選択] ダイアログ ボックスで。

シリーズの編集内 :

• 「BEP」と入力します。 シリーズ名に .
• 損益分岐点に移動します ワークシートに C10 の参照を指定します。 とC11 シリーズ X の値 およびシリーズ Y の値 .
• [OK] をクリックします。 .

• [OK] をクリックします。 .

• データ シリーズのフォーマットを開きます 新しいシリーズのために。
• マーカー オプションに移動します。 .
• 組み込みを選択します [タイプ] で円マーカーを選択します。 .
• マーカーのサイズを設定します 8 まで .

これが出力です。

ステップ 6 – 損益分岐点を適用する

手順:

• プラス型のグラフ要素をクリックします。 アイコン。
• 誤差範囲の右側にある矢印をクリックします。
• [その他のオプション…] を選択します。 .

エラーバーのフォーマット内 作業ウィンドウ:

• エラーバー オプションを選択します。 .
• マイナスを選択します 方向に  そしてキャップなし 終了スタイルで .
• エラー量 、パーセンテージを選択します。 そして100%に設定します。 .

• エラーバー オプションのプルダウン矢印をクリックします。 .
• 系列「BEP」Y エラーバーを選択します。 垂直誤差バーについても同じ手順に従います。 .

これが出力です。

Excel のゴール シーク ツールを使用して BEP (損益分岐点) での販売価格を計算する

T手順:

• 収益を計算するには 、C9に次の数式を入力します。 .

• この式を使用して利益を決定します。 C10 で .

損益分岐点とは、企業またはビジネスが利益を上げ始める前の状態です。

• データに移動します。 タブ。
• [予測] で [What-If 分析] をクリックします。 .
• ゴール シークを選択します .

• ゴール シーク中 ダイアログ ボックスで、以下に示すように参照を入力し、OK をクリックします。 .

C10 0 に設定されます C5 の値を変更することで 。
出力は次のとおりです:3.90 販売単価 .

練習セクション

練習 ここ。

練習用ワークブックをダウンロード

次の Excel ワークブックをダウンロードします。

関連記事

• Excel で損益分岐点を計算する方法
• Excel で複数製品の損益分岐点分析を行う方法
• Excel でゴール シークを使用して損益分岐点分析を行う方法

<<損益分岐点分析 Excel に戻る |  財務用 Excel | Excel を学ぶ

ソリューション付きの高度な Excel 演習を無料で入手しましょう!