Growth Curves

A

andyfw

I asked aquestion on 26 Dec about modelling growth rates and got a very
helpful reply from Herbert Seidenberg (Thank-you), but in trying to apply the
suggested formula there is clearly something I have not fully understood.

I am trying to model growth of service subscribers where the
parameters/variables are:

* Ultimate projected number of subscribers (eg 500 members, 560 members...)
* Time to reach Ultimate subscriber numbers (eg 9months, 14months,
20months...)
* Maximum number of net new signups a month (eg 50 members, 70 members...)
* Month in which max new signups achieved (eg month 5, month 7...)

I want to be able to model the growth of the total subscriber-base for
scenarios where:
1) The number of months for the subscriber-base to plateau at a given level
is variable
2) The maximum number of net new subscribers achieved in a month and the
month that is achieved (eg 70 net new in month 7) is variable
3) The rate of net new signups is increasing up to the month of maximum net
new subscribers and declining thereafter (ie an s-curve) and trending to zero
growth/steady state at the end of the defined growth period.

I need to be able to model the net new signups each month for say:

Total subscriber base when mature = 560
Months to build to maturity = 14
Maximum New sign-ups in month = 70 in month 5 (maximum could be expressed as
a multiplier of the average -ie 1.75*40 = 70).

Any further help that can be offered will be much appreciated.
 
J

Joel

You can build yourself a simple model like the one below without any
complicated formulas. I think you have to different models. One with a
fixed increase each month and one where the multiplying factor can change
each month. I put number 1 to 15 in column A to specify the month, and the
multiplying factor in column D. columns B and E contain formula shown below.


A B D E
Sign ups
1 a month 50 50


3 Month Mult Factor
4 0 0 0
5 1 50 1.75 87.5
6 2 100 1 137.5
7 3 150 1 187.5
8 4 200 1 237.5
9 5 250 1.5 312.5
10 6 300 1.75 400
11 7 350 2 500
12 8 400 1 550
13 9 450 3 700
14 10 500 1 750
15 11 550 1 800
16 12 600 1 850
17 13 650 1 900
18 14 700 1 950
19 15 750 1 1000



Put 0 in B4 and E4
formula B5
=B4 + $B1 copy down column

Formula in E5
=E4 + (D5*E$1) copy down column.
 
H

Herbert Seidenberg

The s curve is modeled on the formula:
=k_a+k_c/((1+k_t*EXP(-k_b*(x-k_m)))^(1/k_t))
where
x =month
k_a 0 lower asymptote (zero subscribers)
k_b 0.5 growth rate
k_c 560 upper asymptote minus k_a ( max subscribers)
k_m 5 time of maximum growth (month # 5)
k_t 0.2 asymmetry of max growth time

Enter your numbers for k_a, k_c and k_m
0, 560 and 5
Enter guesses into k_b and k_t.
Give the numbers defined names, using
Insert > Name > Define or Create ....
Create a sequence of numbers, the months, and name it xs:
{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14}
Create a cell named MaxGrowth with this array formula:
(enter it with CTRL+SHIFT+ENTER)
=MAX(k_c*k_b*EXP(-k_b*(xs-k_m))/(1+EXP(-k_b*(xs-k_m)))^2)
Tools > Goal Seek > Set Cell: MaxGrowth > To Value: 70 >
By Changing Cell: k_b
Create a cell named Start with this formulas:
=k_a+k_c/((1+k_t*EXP(-k_b*(0-k_m)))^(1/k_t))
Tools > Goal Seek > Set Cell: Start > To Value: 1 >
By Changing Cell: k_t
Now all your coefficients are defined correctly and
you can interpolate the number of subscribers at any time
using the formula at the first line.
Plotting this curve will help a lot.
 
A

andyfw

Herbert Seidenberg said:
The s curve is modeled on the formula:
=k_a+k_c/((1+k_t*EXP(-k_b*(x-k_m)))^(1/k_t))
where
x =month
k_a 0 lower asymptote (zero subscribers)
k_b 0.5 growth rate
k_c 560 upper asymptote minus k_a ( max subscribers)
k_m 5 time of maximum growth (month # 5)
k_t 0.2 asymmetry of max growth time

Enter your numbers for k_a, k_c and k_m
0, 560 and 5
Enter guesses into k_b and k_t.
Give the numbers defined names, using
Insert > Name > Define or Create ....
Create a sequence of numbers, the months, and name it xs:
{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14}
Create a cell named MaxGrowth with this array formula:
(enter it with CTRL+SHIFT+ENTER)
=MAX(k_c*k_b*EXP(-k_b*(xs-k_m))/(1+EXP(-k_b*(xs-k_m)))^2)
Tools > Goal Seek > Set Cell: MaxGrowth > To Value: 70 >
By Changing Cell: k_b
Create a cell named Start with this formulas:
=k_a+k_c/((1+k_t*EXP(-k_b*(0-k_m)))^(1/k_t))
Tools > Goal Seek > Set Cell: Start > To Value: 1 >
By Changing Cell: k_t
Now all your coefficients are defined correctly and
you can interpolate the number of subscribers at any time
using the formula at the first line.
Plotting this curve will help a lot.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top