損益分岐点グラフの作成
損益分岐点グラフの作成
フランチャイズチェーン用
完成ファイル「損益分岐点グラフ」・・・・・・・→ダウンロード
損益分岐点売上高を算出するには、以下の式を使用します。
損益分岐点売上高＝固定費÷(1-変動比率) ：変動比率＝変動費÷売上高
●固定費とは売上高に関係なく発生する費用です。言いかえれば、10万円の売上でも100万円の売上でも変わらない費用のことです。
●変動費は、売上と共に増加する費用のことです。
作成手順
1. 損益計算書の作成
最初に損益計算書を作成します
下の科目を使用し損益計算書を作成します。
| 1 |
売 上 合 計 |
|
| 2 |
商品売上高 |
当初設定 売上予測高を使用します。 |
| 4 |
売上原価 |
変動費：費用の中で一番おおきな費用です。
本来は、次の計算式で求めます、
期首商品棚卸高＋期中商品棚卸高−期末商品棚卸高
ここでは、原価率を74％として計算してあります。 |
| 5 |
売 上 総 利 益 |
商品売上高−売上原価 |
| 7 |
本部運営費
（ロイヤリテイ−） |
変動費：
ロイヤリティは、定額で支払う場合、売上に対して一定の割合を乗じて支払う場合、売上総利益に対して一定の割合を乗じた金額を支払う場合等があります。
今回は、売上総利益の36％をロイヤリティとして計算します。 |
| 9 |
地 代 家 賃 |
固定費 |
| 10 |
人 件 費 |
人件費は、売上高の10.6％とする。
参考資料
- 平成８年度調査平成９年発行 中小企業庁 コンビニエンスストア部門指標 １３．５％（店主収入を含む）。
- 平成８年発行 国民金融公庫総合研究所編 「小企業の経営指標」 中小企業リサーチセンター １０．８％
- 平成７年発行 日本労働研究機構 「コンビニエンス・ストアの経営と労働に関する調査研究」 １０．６％
固定人件費費：610,000円
店舗を運営するには、最低人員が必要です。仮に売上がなくても店舗を管理する人は必要です。
この売上「０」の場合でも必要部分の人件費を」固定費として考えます。
16時間営業の店舗の場合に必要な最低人件費は、パート一人 750円×16ｈ×30＝360000円、社員一人1ヶ月の給与250000円
として人件費の固定費部分を算出しています。
変動人件費：
変動人件費＝売上高×10.6％−６１００００
|
| 11 |
法 定 福 利 費 |
固定費 保険料等 |
| 12 |
水 道 光 熱 費 |
固定費 |
| 13 |
消 耗 品 費 |
変動費 売上高の１％に設定してあります。 |
| 14 |
電 話 料 |
固定費 |
| 15 |
車両燃料費 |
固定費 |
| 16 |
旅費交通費 |
固定費 |
| 17 |
リ − ス 料 |
固定費 |
| 18 |
保 守 修 繕 費 |
固定費 |
| 19 |
支 払 利 息 |
固定費 借入金の利息部分 |
| 20 |
支払手数料 |
固定費 振込み手数料等 |
| 21 |
保 険 料 |
固定費 |
| 22 |
雑 費 |
固定費 |
| 23 |
営 業 費 合 計 |
|
24 |
営業利益 |
|
26 |
建物原価償却費 |
固定費 |
27 |
設備原価償却費 |
固定費 |
28 |
経常利益 |
|
2. 固定費と変動費の区分け
損益計算書の費用項目より、固定費と変動費を区分し
固定費は、金額を求め
変動費は、売上高を割った変動比率を求めます。売上高÷変動費＝変動比率
計算し易い用にD・E列に、「固定費」「変動費」の区分項目を作ります。
計算式を入力します。
「G45」に固定費の合計金額を求めます。 表の中で緑色の部分を足した金額です。
 となります。
「G46」に変動費率の合計を求めます。 表の中で黄色の部分を足します。
 となります。
「G32」には、損益分岐点を求める公式「固定費÷（1−変動費率）」を入力します。
※ G列の売上高対構成比（％）は、F4の売上合計に対する構成比率になります。
構成比を求める場合は、「＄F＄4」のように売上高に、絶対値で指定すれば、式をコピーしてもセル番地がずれません。
なお、売上原価、本部運営費、消耗品費以外は、省略可能です。
■注意：
固定費合計（1982500）/（1-0.916）=２３５７３０９．５２となるはずですが、
上のエクセルで計算した結果は、23,520,301となりました。
この原因は、エクセルの仕様によります。
変動比率合計の表示を少数点第二を四捨五入して表示するように書式で設定しても
エクセルでは表示桁数0.916で計算するのではなく実際の変動比率合計0.9157111・・・（実際の変動比率合計は、0.9157111111・・・となります。）を用いて計算しています。
この為に計算結果に相違が発生しました。
●表示桁数で計算するように設定するには、エクセルを起動後
「ツール
」→「オプション」→「 計算方法」のタブをクリックし ブックオプションの欄にある「表示桁数で計算する」の欄にチェックをいれます。
3.計算式の完成
下図が、このに組み込まれた計算式になります。
以上で、損益計算書の作成は、終了しまた。
5.グラフ作成の為の表の作成
次に損益分岐点売上高グラフ作成用の表を作成します。
作成に必要な項目は、
売上高
総費用（固定費+変動費）
固定費
変動費
損益高（省略可能）
上の項目のうち、売上高と総費用があれば損益分岐点売上高グラフは、作成することができますが、
今回は、上の項目に「損益高」（売上高―総費用を引いたもの）と「固定費」も加えてグラフを作成します。
6.計算式の挿入
表には、下の計算式が入ります。
計算式説明
●売上高 売上高は、0円から始まり、今回は、200万円ずつ加算して4000万円まで作りました。
「L3」「M3」「N3」は、手入力で入力します。
「O3」に「＝＄M＄3+N3」と入力します。この式をコピーすれば、200万円ずつ加算された売上金額が、計算されます。
●総費用： 固定費＋変動費なので[=「L6」+「L5」]と入力する。
●固定費： 3.で既に「G30」に計算結果が算出されていまので、その数値を使用します。
●変動費 ：固定費と同様に3.で既に「G31」に計算結果が算出されていまので、その数値を使用します。
※ 固定費・変動費のあるセル番地は、絶対値で、指定します。
そうすると式をコピーしてもずれません。
●損益高 損益高は、売上高から総費用を差し引いた金額になります。
損益高をもうけると、その時点の売上高の場合の利益高、又は赤字高がわかります。
式が入力できたら、その式をコピーすれば表の完成です。
以上でグラフ作成用の表が完成しました。
※ 表示形式は、「セルの書式設定」「表示形式」タブを選択し「ユーザー定義」で、
「＃、＃＃０、」と指定してあります。
グラフの作成
7. グラフの作成手順
売上高と総費用だけで、損益分岐点のグラフは作成できますが、
今回は、「売上高」「総費用」「固定費」「損益高」を使用してグラフを作成します。
7-1 表を選択します。
「売上高」「総費用」「固定費」を選択します。
次に「Ctrl」キーを押しながら「損益高」を選択します。
7-2 「グラフウィザード」の開始
ツールバーにある「グラフウィザード」を選択します。
7-3 「グラフの種類」の設定
▲現れたグラフウィザードの画面の中の＜グラフの種類＞で「折れ線」を選択し、「次へ」をクリックします。
▲7-4 「グラフウィザード 2/4」の画面で、＜系列＞タブをクリックします。
▲現れた画面から一番下にある＜項目軸ラベルに使用＞にある、 ボタンをクリックします。
7-5 「項目軸の設定」
X軸の項目に使用する「売上高」項目を範囲選択し、 をクリックします。
「次へ」をクリックします。
7-6 「タイトルとラベルの設定」
＜タイトルとラベル＞タブをクリックします。
「グラフタイトル」「Y/数値軸」表示するタイトル名を入力します。
7-7 「凡例」の設定
＜凡例＞タブをクリックします。
＜表示位置>で、「上」にチェックボタンを入れ、「次へ」をクリックします。
7-8 「グラフの場所」の設定
グラフを表示する場所を指定します。
「オグジェクト」にチェックをいれます。
「完了」をクリックします。
※ ここにチェックを入れると、表と同じ場所にグラフが作成されます。
※ 「新しいシート」にチェックを入れると、「grph」という新しいシートが作成され、そこにグラフが作成されます。
7-9 下の画面にあるグラフが作成されます。(グラフサイズは、大きくしてあります。）
ここから、いろいろと設定を行い、グラフを整えていきます。
グラフは、ポイントする場所によって、「ダブルクリック」又は「右クリック」で現れるメニューが異なり設定項目も変わります。
※ポイントする場所によって ポップアップメニューが現れるので、それで確認してください。
グラフの大きさを変更します。
「グラフエリア」の上をクリックしてグラフを選択状態にし、■をドラックしグラフのサイズを大きくします。
同様に「プロットエリア」のサイズも変更します。
7-10 「軸の書式設定」
「数値軸」の上で「右クリック」（ダブルクリックでも可能）すると、「軸の書式設定」というメニューが現れます。
この設定では、「パターン、目盛、表示形式、フォント、配置」などの設定が行えます。
最初にY軸上の数値軸の書式を変更してみます。
千円単位で表示するように「表示形式」変更します。
＜表示形式＞を選択します。
▲「種類」で、｢＃、＃＃０｣と入力します
▲「フォント」タブをクリックし、フォントサイズ「９」にします。
▲「目盛」タブをクリックして、「表示単位」を「千」にし「表示単位のラベルを表示する」のチェックを外します。
7-11 「軸ラベルの書式設定」
「軸ラベル」を「右クリック」（ダブルクリック）すると「軸ラベルの書式設定」の画面が現れます。
「フォント」タブをクリックして「フォント」サイズを「８」にします。
「配置」タブで、文字の角度を「０」度にします。
同様に、変更する軸を選択してから、
▲「項目軸」のフォントサイズを「８」に変更します。
▲「凡例」のフォントサイズを「10」にします。
▲「タイトルラベル」のフォントサイズを「１６」にします。
 |
各ラベルは、ドラッグして移動することができます。
バランスをみて、各ラベルを移動して下さい。 |
ココまでの段階で下のようなグラフができたと思います。
7-12 「系列」軸のグラフの種類の変更
4つの折れ線の「系列」軸によって作成されたグラフを系列ごとに「グラフの種類」を変更することが出来ます。
「系列"損益高"」を棒グラフに変えてみましょう。
「系列"損益高"」を選択して「右クリック」し「グラフの種類」を選択します。
以下の画面が現れます。
 
＜標準＞タブをクリックします。
＜形式＞で、棒グラフを選択します。
＜グラフの種類＞＜オプション＞では、「選択の範囲に摘要」にチェックを入れます。
「OK」をクリックします。
同様に「系列"固定費"」を面グラフに変えてみましょう。
「系列"固定費"」を選択して「右クリック」し「グラフの種類」を選択します。
＜標準＞タブをクリックします。
＜グラフの種類＞＜形式＞で、面グラフを選択します。
＜オプション＞では、「選択の範囲に摘要」にチェックを入れます。
「OK」をクリックします。
以上の操作で「系列」ごとのグラフの種類を変更することが出来ます。
ココまでで、完成したグラフは
7-13 軸の書式設定
ここでは、「系列"損益高"」に使用する目盛をグラフの右側に「第2軸」として設定します。
「系列"損益高"」を選択し「右クリック」して現れるメニューから、
「データ系列の書式設定」をクリックします。
＜軸＞タブをクリックして、＜使用する軸＞で、「第2軸」にチェックを入れます。
「OK」をクリックします。
7-14 「第2軸」の目盛軸の変更
「第2数値軸」を選択し、「右クリック」します
現れたメニューから「軸の書式設定」を選択します。
▲＜目盛＞タブをクリックし、＜表示単位＞で「千」を選択し、「表示単位のラベルをグラフに表示する。」のチェックをはずします。
▲「フォント」タブをクリックしてフォントサイズを「８」にします。
▲「表示形式」で、数値、桁区切りを選択します。
「OK」をクリックします。
「系列、損益高」を選択し右クリックし「データ系列の書式設定」を選択し、「データラベル」タブをクリックし、＜ラベル内容＞で、「値」にチェックを入れ「OK」をクリックします。
「損益高」データラベルの上で、右クリックし、「データラベルの書式設定」をクリックします。
▲「フォント」タブをクリックしてフォントサイズを「８」を選択します。
▲「表示形式」で、数値、桁区切りを選択します。
「OK」をクリックします。
▲「パターン」タブで、「輪郭」で’指定’を選択し、「領域」で、’黄色’を選択します。
▲「配置」タブの「ラベルの位置」で、’内側中央’を選択し「OK」をクリックします。
以上の操作を終了後「オートシェイプ」を使ってコメントなどを入力して完成です。
7-15 グラフの完成
ご苦労さまでした。
|