Problems with computing the average rate for proforma financial statements

CFA Level I
#1

Hello!

I have asked some questions about CAGR recently, but I have decided to open a new thread because here I have more specific questions.

I will be very grateful for your help. I truly need your help and advise to get through these calculations, because I am at my wits end.

Please, take a look at the data below, my calculations, and questions. A headscratcher for me.

Goal: to find a so called average rate of Depreciation and Amortization expense for pro forma income statement. The usual computation

for this is take each year’s depreciation expense from income statement, divide by the sum of average gross ppe and average intangibles; compute average of these for each year. See excel spreadsheet I have uploaded to yandex disk.

2 approaches:

(1) Using simple arithmetic average. I show it here because it was presented in a course I was taking, and it clearly demonstrates the purpose, so you can understand what confuses me later in my calculations.

rate = ARITHMETIC AVERAGE( 31 / ( average(286,234) + average(32,18) ) , 38 / ( average(352,286) + average(25,32) ) , 52 / ( average(528,352) + average(28,25) ) , 70 / ( average(738,528) + average(33,28) ) ) = 10,9%

(2) I have also computed a geometric average.

Here is the link to the file - if you open the link you see the spreadsheet, and you can also download it to see formulas.

https://yadi.sk/i/g5O8RZWm3Btkwf

So far so good, and seems to be clear enough. But:

(3) What if any of the sums equal 0 (no matter which approach I use - geometric or arithmetic); what if the sum is zero? How in this case can I compute the overall rate; what number (ratio) should be used instead of that item, in which the denominator is zero?

For example:

what if sum2 = 0 ; then 38 / 0 is illegal; what should I use here to substitute in the overall rate equation?

(4) CAGR and geometric mean:

Usually CAGR is used to compute the growth rate, for example, of sales by using the formula:

(Final value / Initial value) ^ (1 / number of periods)

But how to apply the same formula to the data above? Is it possible?

I have tried but I get surely incorrect results.

cagr gross ppe 1.258

cagr intangibles 1.129

sum of two above 2.387

cagr depreciation 1.229

cagr depreciation / sum 0.515

Please, help me to understand this. It is so important.

Huge thanks! Lots of thanks!

EDIT: edited the format, deleted numbers and added a link to excel spreadsheet to show computations.

EDIT: took away the word growth in initial question, it might have been misleading.

#2

Dude chill out. Depreciation and amortization rates are not random numbers. They are dictated by accounting policies. They don’t grow YoY. To estimate proforma FS use a ratio analysis. Calculate PPE and depreciation expense as a ratio of total assets and revenue respectively and use those ratios as constants in future BS and IS respectively.

#3

Thank you for your reply. I need to compute the rate this way, and use it for proforma Income Statement, not to the proforma Balance sheet. If you take a look at numbers, you will see that this computes the rate of depreciation expence for the income statement based on ppe and intangibles from the balance shee. It is a much more correct way, and I truly need help to solve the issues I raised.

#4

Can you upload a proper spreadsheet with columns? The existing post is cancerous to the eye.

#5

Truly sorry!

I am trying to find how I can attach at least a screen shot but the icon asks for URL, and doesn’t allow me to download the image.

I have edited the original message and added a link.

#6

This whole exercise it utter nonsense to me and there are arithmetic violations.

You said your goals is to find average growth rate in depreciation and amortization and you found it to be 1.229. What else do you want? The growth rate is 1.229-1= 22.9% In other words the expense increases each year by 23% on average.

Adding up cagr gross ppe and cagr intangibles is arithmetically wrong and the number 0.515 has no useful interpretation. It is a “CAGR over the sum of two CAGRs”. It’s blasphemy.

#7

You are too harsh with me, I am learning and tryting to see why math doesn’t work and how to make it correct )

In my post I have 2 questions:

question marked (3) about possible zeros in the denominator and how to deal with those when I compute arithmetic and geometric means to find the rate; how to account for zeros and how to compute that period’s rate, where zero occurs in the denominator, so to use the rate in the geo mean calculation i show; this is the calculation used to determine the depreciation rate to use for pro forma income statements; and this rate is more correct than simple common size rate depreciation/sales;

and question marked (4) is about how to use cagr, which is, as said everywhere, should be the same as the geo mean, to compute the exactly same rate I get by computing arithmetic and geometric means.

I truly need help on both questions.

I have edited the original post by taking away the word “growth” referring to the rate. This might have been misleading.

#8

what do you mean by this?

to compute the exactly same rate I get by computing arithmetic and geometric means.

As far as I know that will never happen in real life. arithmetic and geometric means are two different things and if you have two different numbers x and y you are trying to solve for

(x+y)/2 = xy -->

or x+y = 2xy

which is only solved if x=y …

so that is a very trivial solution.

#9

Yes, in real life arithmetic mean would be quite different from a geometric one.

But I agree that was an incorrect wording from my side: I should have said “arithmetic OR geometric mean”, and, of course, better - just “geometric mean”.

#10

The problem is not wording but the concept and your approach to problem. Your goal was simple and the answer is there. The whole spreadsheet is a hapless manipulation of data with no useful outcome.

#GIGO

#11

Which approach is incorrect? Could you, please, be more specific?

Which approach and which question are you referring to? I am asking for help, because I truly need it, but words like “you are posting nonsense” are not helpful, they are abstract.

#12

I will be grateful is anyone can help me.

Or at least could you, please, advice me which forum to go to and ask for help on such issues. It is very important for me to learn and understand this topic. Thank you very much!

#13

ducol

what are you trying to do here?

cagr gross ppe 1.258

cagr intangibles 1.129

sum of two above 2.387

cagr depreciation 1.229

cagr depreciation / sum 0.515

Gross PPE is a Balance sheet number - and may indicate that the company was a growing company because of which there was addition of PP&E over time. Similarly that Intangibles grew also indicates a growing company.

What does Sum of the two really mean? (It is something I cannot for my life figure out).

Depreciation - uses an accounting method (maybe straight line, sum of digit-years, or some other type - that it is growing as shown by the CAGR # for depreciation purely means the company was not fudging numbers. But there could have been a change in methodology which is definitely NOT identifiable by looking at the number.

And CAGR Depreciation - is a Growth rate. What does CAGR Depreciation / (CAGR PPE + CAGR Intangibles) mean? (Other than nonsense)???

#14

Thank you very much for getting back to me. Let’s for now forget about CAGR formula.

I have a much more important question there, which everyone ignores. The main problem is how to account for possible zeros in the denominator, and as I see that you don’t understand what I am doing, so let me, please, explain. The formula depreciation&amortization expense / (average gross ppe + average intangibles) allows to compute a more correct depreciation expense rate to use for construction pro forma statements; therefore you use not a common size depreciation rate (not depreciation expense / sales), but the ratio of depreciation&amortization expense the to average of all balance sheet data for ppe and intangibles; this is a much more correct ratio to use in forecasting.

And my question was how to account for possible zeros in the denominator; even though this would be a very rare case, but that doesn’t matter, I want to account even for rare cases.

#15

your denominator can NEVER be zero. (Because it is a PP&E Amount) - not a CAGR PP&E that you have…

If A=B => A/B will be 1 (because that is your CAGR).

#16

But what if it is zero? The exactly same calculations are used to compute the interest expense rate, but the balance sheet ltd is in the denominator. The answer “the denominator can never be zero” is not an answer on the question what to do if denominator is zero.

#17

You are looking at a balance sheet of a company right? So ask yourself a question before you ask me. (you are asking the question, but failing to see what it is you are asking). If the PP&E number is 0 - what would it mean to depreciation? Since depreciation is a % Rate times PP&E.

When would PP&E of a company be zero?

Answer those questions for yourself, and before you start to do some analysis (any analysis) try to see what it is you are trying to do. you can generate numbers and think you are doing very well on the analysis front, but what you are actually doing is just crunching numbers. (no meaning to that).

#18

I understand your point, while you fail to understand my questions. I need exactly, as you call it, number crunching in this case, because as I have explained the same approach is used not only relative to depreciation but also relative to interest expense. Depreciation was just one of the examples to make it easier to see numbers, but my question does relate to numbers and how to compute the rate if denominator is zero.

Please, understand me correctly, but if you do analysis as you do, then I don’t see how it can ever be a correct one. I am surprised to see your example: please, think what you have asked and written; do you really believe that if in a given period ppe is zero that means that in this period no depreciation will occur? Then you first need to recall what depreciation is.

#19

There is NO POINT in trying to explain to you.

#20

If it’s 0 you can try excluding that period in the formula.

For e.g.

P0 = 100

P1 = 0

P2 = 200

CAGR (0-1) = -100%

CAGR (1-2) = undefined, exclude

However, CAGR (0-2) = 41.42%. Use this.