Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Measuring growth-how much did revenue increase using 12 months of
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Measuring growth-how much did revenue increase using 12 months of
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.) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Measuring growth-how much did revenue increase using 12 months
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Measuring growth-how much did revenue increase using 12 months of
On Aug 9, 5:10 pm, Buck wrote:
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/Coeffic..._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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Measuring growth-how much did revenue increase using 12 months
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 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Measuring growth-how much did revenue increase using 12 months
"Buck" wrote:
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Measuring growth-how much did revenue increase using 12 months
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Measuring growth-how much did revenue increase using 12 months
On Aug 10, 12:54 pm, Buck wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: How to forecast 12 months' figures but force a 10% growth? | Excel Worksheet Functions | |||
Measuring Average Changes | Excel Discussion (Misc queries) | |||
VBA - Measuring Time -subsecond | Excel Discussion (Misc queries) | |||
Calculating revenue based on accounting months | Excel Discussion (Misc queries) | |||
how do I forecast a 15% increase over 7 months? | Excel Discussion (Misc queries) |