Measuring growth-how much did revenue increase using 12 months of

[Guest]

I've got a list of data representing monthly revenue. Overall, I can see the
trend is moving up. But I'd like to be able to say precisely how much
revenue has increased over time. I looked at the GROWTH function, but thats
for predicting a trend, not calculating a historical rate.

When I plot the data on a column chart and use a Linear Regression Trendline
I get a positive number R2 = 0.0404 (I'm sure tha's good but I can't explain
why). The values are 6560, 5970, 6290, 6640, 5950, 5900, 5880, 5850, 7370,
6300, 6700, 6310.

How do I intepret r-squared? Can I use r-squared to calculate an annual
growth? Or should I skip that and use some other means of calculating
growth?

 

[Dave O]

Is it possible you're overthinking this? As an alternative, you might
simply represent each period's growth as a percent of a baseline
figure, maybe last period or period 1. Annual growth is usually
(results as of 31 December this year) divided by (results as of 31
December last year).

Just a guess, but if you went into the boss and asked if they wanted r-
squared represented in a linear regression format, they'd give you a
"bunny in the headlights" look. (For my boss, simple is better.)

 

[Guest]

Dave O,
Good point about the linear regression, maybe too much information. But
still, period 1 is higher than period 12, which would report a negative
change. On the other hand, Excel's Linear Regression displays an ascending
trendline which leads me to believe that there is positive growth.
Regression analysis is probably beyond my needs here. But is there some
other way I can calculate growth other than simply saying that Jun-07 is
lower than Jul-06?

 

[joeu2004]

I've got a list of data representing monthly revenue. Overall, I can
see the trend is moving up.

Not really.

I looked at the GROWTH function

The GROWTH function is for exponential change. That is clearly not
the situation that you have.

When I plot the data on a column chart and use a Linear Regression
Trendline I get a positive number R2 = 0.0404 (I'm sure tha's good
but I can't explain why).

Because that is not what an R^2 of 0.0404 tells you. The closer R^2
is to 1, the better the regression curve fits the actual data.
Conversely, an R^2 so close to zero says the regression curve does not
fit the actual data very well. Ergo, the upward slope of the linear
trendline is suspect.

(That is an over-simplified explanation of R^2. Although I believe it
is applicable in this instance, see http://en.wikipedia.org/wiki/Coefficient_of_determination
for a more complete explanation.)

Can I use r-squared to calculate an annual growth?

No. R^2 tells you nothing about the growth rate of the data. It is
only a measure of fit between the regression curve and the data.
Ostensibly, the regression line should tell you something about the
growth of the data. But only if it fits the data well. In this case,
it does not; so the behavior of the regression line is inconclusive,
IMHO.

The values are 6560, 5970, 6290, 6640, 5950, 5900, 5880, 5850, 7370,
6300, 6700, 6310.

When I plot these data points, I see no clear trend of the monthly
amounts. Arguably, there might be some seasonal pattern. But only
you could recognize that, based on your knowlege of the business.

Or should I skip that and use some other means of calculating growth?

Yes. I would plot the cumulative revenue. Then we see a clearly
linear growth rate (R^2 = 0.999). The linear trendline suggests a
constant growth of about 6334 per month. But a simple average
indicates a constant growth of about 6385 per month. Indeed, using
the RSQ function, we find that the cumulative revenue based on the
average fits the actual cumulative revenue just as well as the linear
trendline.

Alternatively, you might look at the sum of or average monthly revenue
for each 1/3 of the year. The data suggests a pattern; but I am
relunctant to draw any conclusion about patterns based on a single
year. The middle third is about 7% lower and the last third is about
8% higher than the first third. Whether that suggests a seasonal
variation and whether that suggests an upward trend in the last third
is anyone's guess. There is too little data to draw any such
conclusions, IMHO. If you compare with similar data from at least 2
prior years, maybe you could draw a credible conclusion.

 

[Guest]

Joeu2004 Many thanks for such a speedy and comprehensive reply.

Yes. I would plot the cumulative revenue. Then we see a clearly
linear growth rate (R^2 = 0.999). The linear trendline suggests a
constant growth of about 6334 per month. But a simple average
indicates a constant growth of about 6385 per month. Indeed, using
the RSQ function, we find that the cumulative revenue based on the
average fits the actual cumulative revenue just as well as the linear
trendline.

You explain that I should plot cumulative revenue. So, just to be clear, I
would use the sum of the first month for point one, and then the sum of
months one and two for point two, then the sum of three months for point
three and so on. Using that I can see how you arrived at (R^2 = 0.999).

But, how can I interpret the trendline from this chart to see a constant
growth of 6334 as you indicate and the simple average of 6385.

Im trying the RSQ function for the first time right now so please check back
on this post.

Thanks,
Buck

 

[Guest]

You explain that I should plot cumulative revenue. So, just to be clear, I
would use the sum of the first month for point one, and then the sum of
months one and two for point two, then the sum of three months for point
three and so on.

Yes. A simple way to set this up is: suppose the monthly amounts are in
A2:A13. Then B2 can be =B1+A2, assuming B1 is blank. Copy B2 down through
B13. B3 will become =B2+A3 automatically, etc.

Using that I can see how you arrived at (R^2 = 0.999).
But, how can I interpret the trendline from this chart to see a constant
growth of 6334 as you indicate and the simple average of 6385.

Using my scheme above, the average monthly increment for the actual data is
simply =AVERAGE(A3:A13).

As for the 6334, when you select the option to "display R-squared", also
select the option to "display equation". You will see that the equation is
of the form y = ax+b. "a" is the average monthly increment for the trendline.

But I must have made an error when I looked at this previously -- or I am
making an error now. In any case, __now__ "a" is about 6271, and AVERAGE
returns about 6287. My conclusions remain the same.

 

[Guest]

Thank you joeu2004.

" "a" is the average monthly increment for the trendline.", is what I was
looking for.

I understand that the regression curve does not fit the actual data as you
mentioned so its difficult to say with confidence that this "trend" is
accurate.

More data is needed but comparison to prior periods is impossible since the
business underwent significant changes in operations.

One last thing: how do you get this data into Excel RSQ? That requires x-
and y- values. I could see using revenue and EBITDA as the two variables,
but you seemed to imply that it could be done, and I did not provide EBITDA.

 

[joeu2004]

Thank you joeu2004.

You're welcome.

I understand that the regression curve does not fit the actual data
as you mentioned so its difficult to say with confidence that this
"trend" is accurate.

No, it isn't difficult, IMHO. That's what RSQ tells us: since it is
close to 1, the regression curve __does__ fit the data closely. Ergo,
the trend of the regression curve is the trend of the data. That's
the whole
point of "trend"lines.

Strictly speaking, there are some caveats to add. But the irony is:
you were quick to accept the linear "trend" of the regression curve
that hardly fit your monthly data points at all, but you express some
doubt about a regression curve that fits the data closely (at least,
one interpretation of it). Klunk!

More data is needed but comparison to prior periods is impossible
since the business underwent significant changes in operations.

Fair enough; I prefer to be conservative myself. So, what you __can__
say at this point is that revenue increased by a constant amount,
which is the average increase per month. Caveat: Since the data
track a linear regression curve, you cannot say that revenue is
increasing by a constant rate (i.e. x%). If you graph something that
increasing at a constant rate, you will see an exponential trend, not
a linear trend.

One last thing: how do you get this data into Excel RSQ?
That requires x- and y- values.

The Help description is confusing, IMHO. In many such functions,
"known_y" values are derived from "known_x" values. That is,
"known_x" values are on the X axis, and "known_y" values are on the Y
axis.

That is not the case for RSQ. In this case, "known_y" and "known_x"
are simply to sets of data (not directly related) whose correlation is
to be determined. By "not directly related", I mean: known_y is not
derived from known_x or vice versa.

So if your original data is in A2:A13, and the corresponding data
derived from the average revenue growth amount is in C2:C13, you could
write =RSQ(A2:A13, C2:C13).

I could see using revenue and EBITDA as the two variables,
but you seemed to imply that it could be done, and I did not
provide EBITDA.

I was talking about correlating the actual cumulative revenue data to
the estimated cumulative revenue data based on the average change per
month. If A14 is =AVERAGE(A3:A13) and C2 is =A2, then C3 is =C2+$A$4,
which you can copy down through C13.

(You should see that C13 is about the same as A13, subject to
difference in decimal places to the far right due to round-off error
inherent in all binary computer arithmetic.)

A close correlation simply means that you could simplify things and
use the average instead of the regression curve computed by the TREND
function or the Chart trendline option.


This goes far beyond any Excel question. You are asking questions
about rudimentary statistical inference. I suggest that you read a
book or take a class on introductory statistics, or read some of the
many web sites that offer introductory statistics, or defer to someone
in your company or nearby to tutor your or handle the statistical
analysis.