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 a

* 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 whe
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.

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.

"andyfw" wrote:

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 a

* 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 whe
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.

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.

"Herbert Seidenberg" wrote:

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.