TOP > ARCHIVE - 2017年05月

CVP分析/損益分岐点分析 Excelテンプレートで、期末着地点損益予測を実際にやってみる

■ 理論倒れのCVP分析。どっこい、期中の損益予測には使える理由とは?



「CVP分析(Cost-Volume-Profit Analysis)」「損益分岐点分析(break-even point Analysis, BEP- Analysis)」は、ある程度、変動費比率と固定費が決まっている時、言い換えるなら、期末の目指すべき着地点損益を達成するために、期中の販売数量や販売金額(販売単価)の見込を元に、変動費比率と固定費発生額をコントロールするためのツールとして使用します。

特に、損益トントンとなる「損益分岐点売上高(売上数量)」「ブレーク・イーブン・ポイント(BEP)」を常に意識しながら、右手でコスト発生高、左手で販売数量を同時に測ることで、目標利益の達成を目指します。

財務分析(入門編)_CVP分析のチャート

その損益分析は、コストを変動費と固定費に分解することで、

① 与件となる売上高、変動費比率、固定費発生額からBEPを一点に探し当てる
② 目標となる利益を達成するために、売上高、変動費比率、固定費発生額のいずれが2つから残りのひとつの値を探し当てる


ことを可能にします。

以下に、CVP分析が有効であるための条件を挙げます。

(1)CVP分析モデルが確定モデルである
将来の企業活動によって生じる収益および費用は、確実に予測できること。
→モンテカルロ法や、リアルオプション法などを使用した統計確率的なシミュレーション手法ではない。

(2)CVP分析モデルは1次関数モデルであるCVPの関係は線形モデル(1次関数モデル)が維持されていること。
→正常操業度の範囲内においては、製品一単位当たりの販売単価、原価単価は一定であるとの仮定を置く。すべての売上やコストは直線で図示される。

(3)原価計算は「直接原価計算」の採用を仮定する
コストは、営業活動量の増減に比例的に発生する「変動費」と、営業活動量の大小を問わず一定額のみ発生する「固定費」とに2分されること。
→全部原価計算における在庫増減の影響がない(生産量と販売量が同じ)という仮定をおく。

上記の前提条件の3つ全てを満たすのは、おそらく、継続的なビジネスを営む企業の単年度損益予算管理の範疇ではないかと筆者は推測しています。

① 目標利益が年度予算から与えられていること
② 来期予算立案のため、標準原価(予定原価)計算制度から、変動費比率情報が得られること
③ キャパシティコストなど、年間の固定費発生額はある程度の幅で予測可能であること


それゆえ、本テンプレートは、年度予算を元に、期中の想定外の4つの変数の変化を入力していくことで、年度末の損益予測を直ちに割り出すことを目的として使用します。それは、すなわち、年度損益予算達成のため、4つの変数のどれが未達原因なのか、あるいはどこを操作すれば目標達成に近づけるのか、示唆を得ること目的としているとも言えます。


■ CVP分析による損益シミュレーションの計算構造とは?



まず、売上高(営業収益)を「金額」で把握するか、「数量」で把握するかでシミュレーションモデルの組立方法が異なります。

「金額ベース」
売上高 = 変動費比率 × 売上高 + 固定費 + 利益
    = 変動費 + 固定費 + 利益

財務分析(入門編)_CVP分析のチャート ① 売上高ベース

「数量ベース」
売上単価 × 売上数量 = 変動費単価 × 売上数量 + 固定費 + 利益

財務分析(入門編)_CVP分析のチャート ② 売上数量ベース

このExcelテンプレートは、「数量ベース」で作られていますが、「金額ベース」として使用したい方にも入力を工夫して頂ければお使い方頂けるようにしています。


■ このテンプレートの使い方



「入力シート」
このシートに、月別に、予算値や見込値を事前に分かる範囲で入力してください。入力箇所は、ベージュ色でハッチングがかかった次の4つのセルです。

① 販売数量
② 販売単価
③ 変動費単価
④ 固定費(月別)


20170527_CVP_simulator_年度予算策定

「金額ベース」でしか営業量(売上高)が捉えることができない場合、
「販売数量」には、「販売金額」や「売上高」を入力してください。
「販売単価」には「1」を入力してください。
「変動費単価」には「売上高変動費比率」を入力してください。
そうすることで、金額ベースでもこのExcelテンプレートを使って期末着地点損益予測を行うことができます。

ただし、入力シートにある「BEP台数」は、必ず「BEP売上高」と同値となり、「安全余裕率-台数 (%)」は、必ず「安全余裕率-金額 (%)」と同値となり、2項目の違いが出ることはありません。また、グラフシートの「台数ベース」は、「金額ベース」と同じものを表示するようになります。


「グラフシート」
このシートは、「入力シート」で入力された情報を可視化したグラフを2つ表示するものです。グラフは、Excelの基本グラフ描画の中の「散布図」を使用しています。「散布図」の作り方や数字の拾い方をトレースできるように、各グラフの左側に、グラフ元数字を列挙してあります。ご参考ください。

20170527_CVP_simulator_グラフ(金額ベース)
20170527_CVP_simulator_グラフ(台数ベース)


■ このテンプレートによる実践的な損益予測の方法



(1)年度予算策定
期初の年度損益予算を立案する際に使用する場合、12ヶ月トータルの目標売上高しか分からず、変動費率も固定費も年度一本数値しかない場合、下表のような入力がお勧めです。

20170527_CVP_simulator_年度予算策定

上記のケースでは、年間売上高予算が「3600」、年間予算販売数量が「120」、変動費単価が「5」、年間固定費が「1200」であると分かっている時の入力方法になります。

(2)経過月の実績値から期末損益を予想
将来予測値や見込値が全く入手できなかったり、見込精度が悪くて信頼できなかったりした場合、過去実績値の平均値で残月の予想値を自動で算出し、年度末着地点損益を予想します。

20170527_CVP_simulator_経過月実績から予測

上記のケースでは、6月までの実績が分かっているものとしています。「販売単価」と「変動費単価」は、4~6月の3ヶ月の加重平均値(販売数量で重みづけされた月別の単価の平均値)を求め、7月以降の残り9ヶ月の値として算出しています。固定費は、算術平均値を12倍して年間合計値に変換しています。

(3)経過月の実績値+残月の月別予算値から期末損益の見通しを立てる
将来予測値や見込値が全く入手できなかったり、見込精度が悪くて信頼できなかったりした場合、過去実績値と未経過月の月別予算の合算値で期末損益の見通しを立てます。積極的に、将来予測をすることが実力的にできない、予算や目標値を重視する管理手法を採る場合に多く見られる方法です。

20170527_CVP_simulator_経過月実績+未経過月予算

上記のケースでは、4~6月は実績値、7月以降は月別予算が入力されています。

(4)各変数をバラバラに予測した結果を統合して期末損益を予想
将来予測値や見込値を求める際、月別に揃っていないといけないという思い込みが強い企業がまだまだ多いようです。営業部門は、期末まで月別の販売数量予測値を持っていても、工場はせいぜい向こう3ヶ月のコスト見通ししか持っていないかもしれません。月別に揃っていなくても、Excelシートに組んである「加重平均」「算術平均」機能で、期末着地点損益を算出することが可能です。

20170527_CVP_simulator_予測月度がバラバラの場合

上記のケースでは、販売見込は年度末まで、変動費見込は9月まで、固定費は6月実績までしか把握できていないものをサンプルとして表示しています。

ここで「安全余裕率」の見方の留意点をひとつ。

安全余裕率 = (売上高 - 損益分岐点売上高)÷ 売上高 × 100

上記のケースの様に、「販売数量」「販売単価」「変動費単価」の月別予測値が揃っていない場合、「安全余裕率-台数 (%)」と「安全余裕率-金額 (%)」は異なる数値を示すようになっています。残月の限界利益率(売上高に占める変動費の割合)と限界利益単価(台当たりの限界利益)に違いが出ることがその理由です。予想や見通しの精度を比較し、どちらのほうが当たるか、もしくは低い方を採用して保守的な損益予想を立てるか、使用目的に沿って使い分けてください。

⇒ダウンロードはこちらから(DL先は小職の兄弟ブログサーバ内なのでご安心ください)
  「CVP_simulator テンプレート(無償版)」← MS Excel 2010版
⇒兄弟ブログのダウンロードページへはこちらから
   「財務分析テンプレート『「CVP_simulator テンプレート』(無償版)取扱説明とダウンロード

・問い合わせ先:
兄弟ブログ内各ページ右にあるコンタクトフォームからご連絡いただけます。
⇒「経営コンサルタントによる経営戦略と経営管理に効く経営管理会計
20170528_トップページ


関連記事

Amazon ファイナンス・経営書 売上ランキング

楽天 経理・財務管理 書籍 売上ランキング

最新記事
カテゴリ
緊急!?アンケート
より面白いブログづくりにご協力お願いします!m(_ _)m
ブログ内検索
カレンダー
04 | 2017/05 | 06
- 1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31 - - -
全記事表示リンク

全ての記事を表示する

月別アーカイブ
プロフィール
経営管理とITとProject Management

小林友昭

Author:小林友昭
現役の経営コンサルタントが管理会計をテーマに情報発信します。

問い合わせはコチラから
他の方には公開されません

名前:
メール:
件名:
本文:

ブログランキング
記事をお読みになって応援したくなったらワンクリックお願いします!   (ランキングページに飛ぶだけです)

FC2Blog Ranking
経理・会計・税金 ブログランキングへ

最新コメント
リンク
最近の読書遍歴
筆者が最近読んだ(読んでいる)中でこのブロクに関連している本の紹介です。
    ↓粉飾決算の見抜き方指南

会計不正はこう見抜け

新品価格
¥3,024から
(2015/4/19 12:31時点)

    ↓人工知能(AI)をいたずらに恐れない

AIの衝撃 人工知能は人類の敵か (講談社現代新書)

新品価格
¥864から
(2015/4/19 12:30時点)

    ↓クラウド時代のSIビジネスの処方箋

システムインテグレーション崩壊 ~これからSIerはどう生き残ればいいか?

新品価格
¥1,814から
(2015/4/19 12:28時点)

    ↓待望の改訂第3版 金融危機反映済み

ファイナンシャル・マネジメント 改訂3版---企業財務の理論と実践

新品価格
¥4,752から
(2015/3/2 11:50時点)

    ↓最強のエクセル資料作成指南

ビジネスエリートの「これはすごい!」を集めた 外資系投資銀行のエクセル仕事術---数字力が一気に高まる基本スキル

新品価格
¥1,836から
(2015/2/28 18:22時点)

    ↓超お手軽に競争戦略が分かる

最強の「イノベーション理論」集中講義

新品価格
¥1,728から
(2015/2/28 18:20時点)

    ↓グローバル事業戦略を確認

コークの味は国ごとに違うべきか

新品価格
¥2,057から
(2015/2/6 22:54時点)

    ↓増補改訂版が出ました

起業のファイナンス 増補改訂版 ベンチャーにとって一番大切なこと

新品価格
¥2,484から
(2015/2/6 22:41時点)

    ↓姉妹書の「経営戦略全史」もどうぞ

ビジネスモデル全史 (ディスカヴァー・レボリューションズ)

新品価格
¥3,024から
(2014/10/9 21:25時点)

    ↓入門書に最適なシンプルさです

新・企業価値評価

新品価格
¥4,320から
(2014/10/9 21:23時点)

    ↓ちょっと専門的ですよ!

起業のエクイティ・ファイナンス---経済革命のための株式と契約

新品価格
¥3,888から
(2014/10/9 21:22時点)

    ↓事例集として使えます

ビジネスモデルの教科書: 経営戦略を見る目と考える力を養う

新品価格
¥1,944から
(2014/10/9 21:21時点)

    ↓経営コンサルタントの暴露本です

申し訳ない、御社をつぶしたのは私です。

新品価格
¥1,728から
(2014/10/9 21:16時点)

アクセス数
オンラインアクセス数
現在の閲覧者数:
アクセスランキング
[ジャンルランキング]
ファイナンス
23位
アクセスランキングを見る>>

[サブジャンルランキング]
経理・会計
9位
アクセスランキングを見る>>
読まれている記事
注)現在、PCとモバイル経由は別々に集計されています。記事名に「-」が付いているとモバイル
掲示板
RSSリンクの表示
ブロとも申請フォーム

この人とブロともになる

QRコード
QR
会計とマネジメントとITの参考図書
セレクションは偏っていますよ。だって本当に読んでいる中からお勧めしているから!
    ●会計基礎
    ↓財務諸表の基本が分かる(1)

決算書がスラスラわかる 財務3表一体理解法 (朝日新書 44)

新品価格
¥778から
(2014/12/4 21:17時点)

    ↓財務諸表の基本が分かる(2)

財務3表一体分析法 「経営」がわかる決算書の読み方 (朝日新書)

新品価格
¥842から
(2014/12/4 21:20時点)

    ↓財務諸表の理解を深める(1)

企業の経営戦略を決算書から見抜く 「財務諸表」読解入門

新品価格
¥2,160から
(2014/12/4 21:22時点)

    ↓財務諸表の理解を深める(2)

[決定版] ほんとうにわかる財務諸表

中古価格
¥2,881から
(2014/12/4 21:23時点)

    ●管理会計
    ↓管理会計的考え方が身につく

決定版 ほんとうにわかる管理会計&戦略会計

新品価格
¥3,888から
(2014/12/4 21:25時点)

    ↓管理会計の全領域を押さえる

管理会計(第五版)

新品価格
¥5,184から
(2014/12/4 21:27時点)

    ↓予算編成と予実管理なら

高田直芳の実践会計講座 「管理会計」入門

新品価格
¥3,024から
(2014/12/4 21:35時点)

    ↓意思決定会計の初学用

ビジネスマンの基礎知識としての損得計算入門

新品価格
¥1,620から
(2014/12/4 21:37時点)

    ●ファイナンス
    ↓会計とファイナンスの基礎(1)

道具としてのファイナンス

新品価格
¥2,592から
(2014/12/4 21:39時点)

    ↓会計とファイナンスの基礎(2)

新版 ファイナンシャル・マネジメント ― 企業財務の理論と実践

新品価格
¥4,752から
(2014/12/4 21:41時点)

    ↓企業価値評価の王道(難)

企業価値評価 第5版 【上】

新品価格
¥4,320から
(2014/12/4 21:44時点)

企業価値評価 第5版 【下】

新品価格
¥4,320から
(2014/12/4 21:44時点)

    ●経営戦略
    ↓経営戦略の歴史を学ぶ

経営戦略全史 (ディスカヴァー・レボリューションズ)

新品価格
¥3,024から
(2014/12/4 21:51時点)

    ↓経営戦略論の体系を理解したい

戦略サファリ 第2版 -戦略マネジメント・コンプリート・ガイドブック

新品価格
¥4,536から
(2014/12/4 21:52時点)

    ↓経営学のほんとうの姿は?

世界の経営学者はいま何を考えているのか――知られざるビジネスの知のフロンティア

新品価格
¥2,052から
(2014/12/4 22:18時点)

    ↓ビジネスモデル作ってみる?

ビジネスモデル・ジェネレーション ビジネスモデル設計書

新品価格
¥2,678から
(2014/12/4 21:56時点)

    ●経営管理
    ↓制約理論(TOC)を学ぶ

ザ・ゴール ― 企業の究極の目的とは何か

新品価格
¥1,728から
(2014/12/4 22:00時点)

ザ・ベロシティ

新品価格
¥1,728から
(2014/12/4 22:01時点)

    ↓収益モデルを考える

プロフィット・ゾーン経営戦略―真の利益中心型ビジネスへの革新

新品価格
¥2,592から
(2014/12/4 22:02時点)

    ↓会社組織の形を考える(1)

組織の経済学

新品価格
¥5,940から
(2014/12/4 22:05時点)

    ↓会社組織の形を考える(2)

戦略の経済学

新品価格
¥6,912から
(2014/12/4 22:06時点)

    ↓アーキテクチャを考える(1)

ものづくり経営学―製造業を超える生産思想 (光文社新書)

新品価格
¥1,296から
(2014/12/4 22:09時点)

    ↓アーキテクチャを考える(2)

ビジネス・アーキテクチャ―製品・組織・プロセスの戦略的設計

中古価格
¥915から
(2014/12/4 22:11時点)

    ↓稲盛イズムの原点を知る

稲盛和夫の実学―経営と会計

新品価格
¥566から
(2014/12/4 22:13時点)

    ↓岡目八目。他人のふり見て、、、

失敗の本質―日本軍の組織論的研究 (中公文庫)

新品価格
¥823から
(2014/12/5 10:30時点)

    ●ビジネスマンとしての教養
    ↓アドラー心理学でも身に着ける?

嫌われる勇気―――自己啓発の源流「アドラー」の教え

新品価格
¥1,620から
(2014/12/4 22:25時点)

    ↓自己啓発本の古典です

人を動かす 新装版

新品価格
¥1,620から
(2014/12/5 00:02時点)

    ↓メンターとの良き出会い

そうか、君は課長になったのか。 (ポケット・シリーズ)

新品価格
¥972から
(2014/12/5 10:33時点)

    ↓歴史と社会からビジネスを斬る

東大講義録 文明を解く I (日経ビジネス人文庫)

新品価格
¥771から
(2014/12/5 00:05時点)

東大講義録 文明を解くII―知価社会の構造分析 (日経ビジネス人文庫)

新品価格
¥720から
(2014/12/5 00:07時点)

    ↓文明史からビジネスを斬る(1)

文庫 銃・病原菌・鉄 (上) 1万3000年にわたる人類史の謎 (草思社文庫)

新品価格
¥972から
(2014/12/5 00:07時点)

文庫 銃・病原菌・鉄 (下) 1万3000年にわたる人類史の謎 (草思社文庫)

新品価格
¥972から
(2014/12/5 00:10時点)

    ↓文明史からビジネスを斬る(2)

文明崩壊 上: 滅亡と存続の命運を分けるもの (草思社文庫)

新品価格
¥1,296から
(2014/12/5 00:14時点)

文明崩壊 下: 滅亡と存続の命運を分けるもの (草思社文庫)

新品価格
¥1,296から
(2014/12/5 00:15時点)

    ↓文明史からビジネスを斬る(3)

昨日までの世界(上)―文明の源流と人類の未来

新品価格
¥2,052から
(2014/12/5 00:12時点)

昨日までの世界(下)―文明の源流と人類の未来

新品価格
¥2,052から
(2014/12/5 00:12時点)

    ↓文明史からビジネスを斬る(4)

17歳のための世界と日本の見方―セイゴオ先生の人間文化講義

新品価格
¥1,836から
(2014/12/5 00:34時点)

    ↓文明史からビジネスを斬る(5)

誰も知らない 世界と日本のまちがい 自由と国家と資本主義

新品価格
¥1,944から
(2014/12/5 00:35時点)

    ●ITと業務プロセス
    ↓ITの上流設計をやる!

業務システムのための上流工程入門―要件定義から分析・設計まで

新品価格
¥2,592から
(2014/12/5 00:23時点)

    ↓データモデリング(1)

業務別データベース設計のためのデータモデリング入門

新品価格
¥3,240から
(2014/12/5 00:28時点)

    ↓データモデリング(2)

生産管理・原価管理システムのためのデータモデリング

新品価格
¥3,240から
(2014/12/5 00:29時点)

    ↓データモデリング(3)

販売管理システムで学ぶモデリング講座 (DB Magazine SELECTION)

新品価格
¥2,592から
(2014/12/5 00:25時点)

    ↓データベースと業務設計(1)

グラス片手にデータベース設計~販売管理システム編 (DBMagazine SELECTION)

新品価格
¥2,592から
(2014/12/5 00:30時点)

    ↓データベースと業務設計(2)

グラス片手にデータベース設計 生産管理システム編 (DB Magazine SELECTION)

新品価格
¥2,592から
(2014/12/5 00:32時点)