Growth due to increase in rate & due to increase in unit

[vigneshjan24]

This is actually a mathematical question..

Sales 2014: Rs.36
Sales 2015: Rs.62
Here we can straightly say growth rate is 72%.

But, in depth i need deviation of this 72% as how much due to increase in rate and how much due to increase in no. of units.

I have attached the template.

Thanks in advance.

 

[Luke M]

Formula in J8 (units)
=AVERAGE((F6:G6-C6:D6)/C6:D6)
Formula in K8 (rate)
=AVERAGE((F7:G7-C7:D7)/C7:D7)

Both formulas should be confirmed as an array via Ctrl+Shift+Enter.

Math is that since there are 2 numbers for the year, each representing an equal duration (6 months), we take the percent increase for each 6 month period and average.

 

[NARAYANK991]

Hi ,

I think you can make it as complicated as you wish , depending on what you wish to prove !

When you ask what is the growth , let us look at the various growths :

Growth in Sales Volume = from 10 to 12 , which is 20 %.

Growth in Revenue = from 36 to 62 , which is 72 %.

When you ask , how much of the 72 % is due to growth in sales volume , what you mean is , if the sales volume had not increased , what would the revenue have been ; obviously , it is implied that the rate increase should be taken into account. If the rate increase has taken place , and the volume remains 10 units , can we assume that the sales volume would have been 5 and 5 for the two halves ? In this case , the sales revenue would be 5 * 4 + 5 * 6 = 50

The growth would therefore be 50 - 36 = 14 , which is 38.89 %

On the other hand , if the rate per unit had not increased , the sales volume of 12 would have contributed 5 * 4 + 7 * 4 = 48

The growth would therefore be 48 - 36 = 12 , which is 33.33 %.

You can see that 50 + 12 = 14 + 48 = 62.

Narayan

 

[vigneshjan24]

@Luke.. thanks man..

but how about for different month gap.
I mean, What if the rates varies for 4 & 8 months in 1st year and 5 & 7 months in 2nd year?

 

[Luke M]

If rates were consistent year to year, then you would want to weight each group with appropriate number.

No. of units:
=SUMPRODUCT((F6:G6-C6:D6)/C6:D6*{4,8})/12

Rate per unit
=SUMPRODUCT((F6:G6-C6:D6)/C6:D6*{4,8})/12

These are regular formulas, not arrays.

If there's different, then I think you need to rethink your question. Just because we have two numbers doesn't always mean we can compare them. IF you have numbers representing different things, it is unfair to compare them and expect answer to make sense.