エクセル2003(OS XP)で赤字の営業利益(営業損失)の数年分の増減率を算出し、今後の赤字予測を計算したいです。

【ケース】
・初年度
売上100万円
営業利益-100万円
・2年目
売上200万円
営業利益-200万円
・3年目
売上300万円
営業利益-300万円

売上ではCAGRが使えると思います。
(3年目売上/1年目売上)^(1/2)=((300)/(100))^(1/2)-1=73.2%

営業利益は「-」なので、単純にCAGRの式に代入してしまうと
=((-300)/(-100))^(1/2)-1=+73%
となってしまいます。

CAGRのように、過去の増減率を計算し、その数値を元に今後の増減率を把握するにはどのようにしたら
よろしいでしょうか?
赤字、つまり「-」のときは使えないとしたら、どのような計算方法で、過去の増減率から、
将来の赤字予測が出来ますでしょうか?

よろしくお願いいたします。

回答の条件
  • 1人5回まで
  • 登録:
  • 終了:2010/11/17 19:25:07
※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

ベストアンサー

id:hathi No.2

回答回数216ベストアンサー獲得回数49

ポイント35pt

CAGR(平均成長率)ですか。この数式は、

 (最終年度の実績÷初年度の実績)の比率になる、【(平均成長率)の(経過期間)乗】の(平均成長率)を出しているのですね。

 

 エクセル(2003でも、他でも)次の式になります。

  平均乗率 = POWER(最終年度の実績/初年度の実績,1/経過年数)

  平均成長率 = 平均乗率 - 1

  f:id:hathi:20101111215745j:image

 【400が800になった & その期間は4年間】であると、

  平均乗率 = POWER(800/400,1/4)= 1.189207  (Excelの POWER 関数は便利です)

  平均成長率 = 1.189207 - 1 = 0.189207  平均で年間18.92%の成長率

 

 しかし、上の図にあるように、途中が、定額増額、定率増、最後で急増、山なり の区別もなくて

 CAGR(平均成長率)が計算されることを気をつける必要があります。

 そうしないと、10年目(最終年度の翌年)以降の予測としては、おかしなことになります。

 

 ☆ 最初も最後もマイナスの場合で、同じように使えます。

   =POWER((-800)/(-400),1/4)  平均乗率 = 1.189207

 ☆ 【元データに下駄を履かす】ことも可能です。

   =POWER((1000+800)/(1000+400),1/4)  1000を下駄にしました 平均乗率= 1.064844

   =POWER((1000-800)/(1000-400),1/4)  1000を下駄にしました 平均乗率= 0.759836

   履かせた下駄の高さで、平均乗率は変わってしまいます。

 

 エクセル関数を使うかどうかに関係なく、

  数学的には 期間が離れた2つの値について、平均乗率、掛け率、伸び率、成長率を計算するのは簡単です。

 しかし、そうして出てきた値を、次の値を予測するときに使えるかどうかは、単純ではありません。

 上図グラフに示した状態を見てください。

 

 何かを予測するなら、2ポイントだけを元データに使う方式は避けた方が良いでしょう (一般的には)

 http://kabukiso.com/siryou/cagr.html

 ここの終わりの方に書かれていることです。

  一見、万能そうに見えるCAGRですが、私は欠点が2つあると思います。

  一つ目は、どこの値をとっているかで、CAGRが大きく変わってしまうことです。

  (中略)

  もう一つは、CAGRの数字は過去の結果に過ぎず、今後もこの成長率が約束されたものではないということです。

  当たり前のことですね…。CAGRは過去業績分析に適していると思いますが、将来分析予測にそのまま使ってしまうのは危険だと思います。

 

 上記の計算の中で 元データに下駄を履かせて計算するのは、普通はしないことです。(してはいけない)

 利益は(収益-費用)の数字です。 ある意味では、マイナスの下駄を履かせたような数字が利益です。

 こうしたデータである利益に、時系列分析が有効なのか、ちょっと疑問です。

  

その他の回答1件)

id:Silvanus No.1

回答回数180ベストアンサー獲得回数71

ポイント35pt

このご質問は、エクセルとは直接関係ありませんよね?

CAGRは複数年で達成された成長率を1年毎の成長率に換算したものですから、

過去3年間の売上の成長率から得たCAGRが+73.2%であり、

それが次年度(4年目)も達成できれば売上は約520万円になります。

しかし、CAGRがより有効なのは、平均の成長率を得る対象となる期間(複数年)が長い場合です。

期間が短かい場合、そして更に、その期間が会社の創立間も無い時期であったり、

好景気・不景気に当たる等の要因が加わると、ほとんど意味がなくなります。

今回の例で、単年度の成長率を計算すると、2年目は+100%、3年目は+50%となっており、

見方によってはいわゆる「頭打ち」が生じているとも解釈できる数値であり、

この様な短い期間で算出されたCAGRを以て将来の予測を立てることはほとんどナンセンスです。

また、営業利益については、売上と違い、正負の符号付き数値であること、

「売上」自身から「売上とは比例しない、必ずしも増減が一致しない成分(経費)」を

減じたものであること等から、CAGRの考え方を当て嵌めるのは不適当と思われます。

id:hathi No.2

回答回数216ベストアンサー獲得回数49ここでベストアンサー

ポイント35pt

CAGR(平均成長率)ですか。この数式は、

 (最終年度の実績÷初年度の実績)の比率になる、【(平均成長率)の(経過期間)乗】の(平均成長率)を出しているのですね。

 

 エクセル(2003でも、他でも)次の式になります。

  平均乗率 = POWER(最終年度の実績/初年度の実績,1/経過年数)

  平均成長率 = 平均乗率 - 1

  f:id:hathi:20101111215745j:image

 【400が800になった & その期間は4年間】であると、

  平均乗率 = POWER(800/400,1/4)= 1.189207  (Excelの POWER 関数は便利です)

  平均成長率 = 1.189207 - 1 = 0.189207  平均で年間18.92%の成長率

 

 しかし、上の図にあるように、途中が、定額増額、定率増、最後で急増、山なり の区別もなくて

 CAGR(平均成長率)が計算されることを気をつける必要があります。

 そうしないと、10年目(最終年度の翌年)以降の予測としては、おかしなことになります。

 

 ☆ 最初も最後もマイナスの場合で、同じように使えます。

   =POWER((-800)/(-400),1/4)  平均乗率 = 1.189207

 ☆ 【元データに下駄を履かす】ことも可能です。

   =POWER((1000+800)/(1000+400),1/4)  1000を下駄にしました 平均乗率= 1.064844

   =POWER((1000-800)/(1000-400),1/4)  1000を下駄にしました 平均乗率= 0.759836

   履かせた下駄の高さで、平均乗率は変わってしまいます。

 

 エクセル関数を使うかどうかに関係なく、

  数学的には 期間が離れた2つの値について、平均乗率、掛け率、伸び率、成長率を計算するのは簡単です。

 しかし、そうして出てきた値を、次の値を予測するときに使えるかどうかは、単純ではありません。

 上図グラフに示した状態を見てください。

 

 何かを予測するなら、2ポイントだけを元データに使う方式は避けた方が良いでしょう (一般的には)

 http://kabukiso.com/siryou/cagr.html

 ここの終わりの方に書かれていることです。

  一見、万能そうに見えるCAGRですが、私は欠点が2つあると思います。

  一つ目は、どこの値をとっているかで、CAGRが大きく変わってしまうことです。

  (中略)

  もう一つは、CAGRの数字は過去の結果に過ぎず、今後もこの成長率が約束されたものではないということです。

  当たり前のことですね…。CAGRは過去業績分析に適していると思いますが、将来分析予測にそのまま使ってしまうのは危険だと思います。

 

 上記の計算の中で 元データに下駄を履かせて計算するのは、普通はしないことです。(してはいけない)

 利益は(収益-費用)の数字です。 ある意味では、マイナスの下駄を履かせたような数字が利益です。

 こうしたデータである利益に、時系列分析が有効なのか、ちょっと疑問です。

  

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

回答リクエストを送信したユーザーはいません