Challenging CAGR as growth comparison metric
As a business analyst you may be required to compare growths of different entities over several periods. Conventionally, one would think of percentage growth or CAGR (Compounded Annual Growth Rate) to compare growth rates. These are also the metrics your management is mostly familiar with. However, in many cases these metrics may not do a good job.
Let us say you are in B2B industry and your boss asks you to identify the fastest growing customer in the last 6 months (or 6 years). At the outset, this question seems simple and straightforward.
As you start working on this, you will realize that this is not straight forward. This is very different from comparing your portfolio returns using CAGR or finding the fastest growing country. You cannot simply measure the percentage growth between the first month and the sixth month and use that for comparisons.
The reason being, each of your partner could be from a different industry and could have different purchasing cycles. This will distort your comparison. In that case, you may say, it is wrong to look at growth rates for such a narrow time window. (Fair, but we want to see who has fared well under the current financial climate).
Let us rule out the problem of cyclical purchasing patterns. The second problem is the base. The reference for CAGR is always the first period. The base year can seriously distort the growth numbers. Unlike the inflation calculation we cannot simply use a normal year since a normal year for one partner could have been a severe year for another.
The third problem with conventional method is that it fails to capture recent movements. There are only two reference points for most metrics used to compare growth.
What I am proposing as an alternative is, using the slope of the best fit straight line between points.
The following formula give the slope of the line.
CAGR is calculated as (Ending Value is cumulative)
Let us look at a couple of cases to understand my viewpoint better.
Illustration 1:
Here the partner has cyclical purchasing pattern and it is trending downwards. However CAGR and Grwoth numbers report a positive growth.
Illustration 2:
Here we are looking at two partners X1 and X2 who have done same numbers from period 2 to period 6. However, in P1 they have different numbers. You will see that in one case(of X1) the grwoth is 46% and in the other growth is staggering 1000%.
You will notice that Slope is list sensitive to base value. And % growth based on beginning and ending values is extremely sensitive to base value.
When you are comparing growth over several periods slope is the best measure. Although, it may appear a little complicated, it is fairly simple, if you are using excel.
Use the =SLOPE( formula to calculate the slope of best fitting line. Just make sure you do not interchange X and Y Values.
Let us say you are in B2B industry and your boss asks you to identify the fastest growing customer in the last 6 months (or 6 years). At the outset, this question seems simple and straightforward.
As you start working on this, you will realize that this is not straight forward. This is very different from comparing your portfolio returns using CAGR or finding the fastest growing country. You cannot simply measure the percentage growth between the first month and the sixth month and use that for comparisons.
The reason being, each of your partner could be from a different industry and could have different purchasing cycles. This will distort your comparison. In that case, you may say, it is wrong to look at growth rates for such a narrow time window. (Fair, but we want to see who has fared well under the current financial climate).
Let us rule out the problem of cyclical purchasing patterns. The second problem is the base. The reference for CAGR is always the first period. The base year can seriously distort the growth numbers. Unlike the inflation calculation we cannot simply use a normal year since a normal year for one partner could have been a severe year for another.
The third problem with conventional method is that it fails to capture recent movements. There are only two reference points for most metrics used to compare growth.
What I am proposing as an alternative is, using the slope of the best fit straight line between points.
The following formula give the slope of the line.
| m | = |
|
xy) 
CAGR is calculated as (Ending Value is cumulative)
Let us look at a couple of cases to understand my viewpoint better.
Illustration 1:
Here the partner has cyclical purchasing pattern and it is trending downwards. However CAGR and Grwoth numbers report a positive growth.
(P1 is period 1 P2 is Period 2 and so on X is the company and the numbers in Blue table are their sales for the respective periods.)
| Period | P1 | P2 | P3 | P4 | P5 | P6 | CAGR | % Growth | Slope |
| X | 100 | 130 | 90 | 120 | 80 | 110 | 35.90% | 10.00% | -2 |
Illustration 2:
Here we are looking at two partners X1 and X2 who have done same numbers from period 2 to period 6. However, in P1 they have different numbers. You will see that in one case(of X1) the grwoth is 46% and in the other growth is staggering 1000%.
| Period | P1 | P2 | P3 | P4 | P5 | P6 | CAGR | % Growth | Slope |
| X1 | 75 | 90 | 100 | 100 | 105 | 110 | 18.59% | 46.67% | 6.29 |
| X2 | 10 | 90 | 100 | 100 | 105 | 110 | 92.89% | 1000.00% | 15.57 |
You will notice that Slope is list sensitive to base value. And % growth based on beginning and ending values is extremely sensitive to base value.
| If P1 were | CAGR | % Growth | Slope |
| 75 | 18.59% | 46.67% | 6.29 |
| 50 | 22.21% | 120.00% | 9.86 |
| 25 | 28.98% | 340.00% | 13.43 |
| 10 | 38.88% | 1000.00% | 15.57 |
| 5 | 47.02% | 2100.00% | 16.29 |
| 1 | 68.01% | 10900.00% | 16.86 |
| 0.5 | 77.99% | 21900.00% | 16.93 |
| 0.25 | 88.57% | 43900.00% | 16.96 |
| 0.01 | 146.57% | 1099900.00% | 17.00 |
When you are comparing growth over several periods slope is the best measure. Although, it may appear a little complicated, it is fairly simple, if you are using excel.
Use the =SLOPE( formula to calculate the slope of best fitting line. Just make sure you do not interchange X and Y Values.
Comments
Post a Comment