Logistic Growth Formula

[BWMagee]

In A12 I have the value 8855
In J24 I have the price $346,400
In K24 I have the value 39.1
In N24 I have the value 2
In O24 I have the value 50
In P24 I have the formula, =(O24-K24)*$A$12*IF((N24>=1)*(N24<9),(N24/8),1), which gives the price $24,088

The key area I’d like to focus on in the formula is N24/8, in this instance 2/8. The denominator will always be 8, but the numerator may change. We can see if I change the value of N24 to 1, I get $12,044, and if I change the value to 3, I get $36,131 … That is, the price rises by 1/8, or $12,044 each time. The value in N24 is number of weeks, and what I am measuring is profit increase over time. However, I would like the value in P24 to rise in logistic growth model fashion - that is, profit should rise by a greater amount early, and lesser amount late. For example, the profit increase between week 7 and week 8 should be < $12,044, while the profit increase between week 1 and 2 should be > $12,044, instead of the current formula, which is the same amount ($12,044) each week.

My question, how can I rewrite the formula in P24 according to logistic growth model?
Many thanks.

 

[KRice]

Could you say a little more about what you expect to see? A typical logistic growth model would have a sigmoidal shape (a stretched out "S" shape), so the early part of the curve still rises relatively slowly before rapidly increasing...followed by the latter phase where growth continues to slow. Are you expecting a model like that, or one whose greatest rate of increase is early followed by ever diminishing growth...like a logarithmic function where there is no slow growth initially? What time period do you expect maximum profit to be reached, and how is max profit determined from the values you've described?

 

[BWMagee]

Could you say a little more about what you expect to see? A typical logistic growth model would have a sigmoidal shape (a stretched out "S" shape), so the early part of the curve still rises relatively slowly before rapidly increasing...followed by the latter phase where growth continues to slow. Are you expecting a model like that, or one whose greatest rate of increase is early followed by ever diminishing growth...like a logarithmic function where there is no slow growth initially? What time period do you expect maximum profit to be reached, and how is max profit determined from the values you've described?

Yes, my mistake. A logarithmic model is a better fit than a logistic one. Maximum profit will be reached after 8 weeks.
At its simplest, maximum profit is: (O24-K24)*$A$12... in the above example (50-39.1)*8855
Thanks for your reply.

 

[KRice]

Ideally there would be some theory or other basis for describing the profit growth model, and that information could be used to establish the formula. If that information is lacking, and you are looking for a monotonically increasing function (meaning every time step leads to a greater value), but whose derivative is monotonically decreasing (meaning every subsequent time step leads to smaller and smaller increases), then there are several approaches. You could simply define a lookup table of expected profit percentages and use an interpolating polynomial, if necessary, to return the percentage of maximum profit gained for any input value of elapsed time. Alternatively, you could select any of numerous mathematical functions with the desired characteristics to determine if they yield results that are considered reasonable representations of reality. On this second point, I played around with a 2-parameter Weibull distribution, specifically its cumulative distribution function (cdf). The form of this equation is F(x) = 1 - exp(-((x/lambda)^kappa)), where lambda is a scale parameter and kappa is a shape parameter. These two parameters cause the function to take on substantially different shapes as the cdf values increase from 0 to 1...although 1 is approached asymptotically (very gradually) at large values of x (time in this case). This asymptotic behavior is inherent in many of the functions mentioned (logistic, logarithmic, and the current exponential), and that potentially limits their usefulness for your application. To overcome this issue, I propose making an adjustment by evaluating the cdf at the time when maximum profit is known to be achieved (x=T=8 weeks in this case), and then using that value to normalize the entire cdf. This forces the modified function to produce values between (0,1) over the time domain (0,8), and this can be used to determine the fraction of maximum profit achieved.

I would suggest first trying out the modified Weibull cdf to produce a curve that makes sense. This is done by changing the values lambda and kappa in cells T8 and T9, respectively. I've added some scroll bars that are linked to cells under them, and those are used to change T8:T9 in increments of 0.1. This makes it convenient to see how the curve changes as the parameters are varied. Note that the plotted curve is normalized, and cell T10 represents the time in weeks when 100% profit is to be achieved. For this example, lambda = 2.9 and kappa = 0.8 is one feasible curve, although you might experiment with other parameters to find a more sensible curve. You can review the difference between adjacent function values beginning in cell T13 and down to confirm that the increases are diminishing. A snapshot of the curve is shown below.

I've incorporated this modified function in cell P24 and altered your formula somewhat to account for cases where time might be less than 1 (if that is possible). For example, since the modified function is continuous and defined for x>=0, a value can be returned for day 1 (week = 1/7 = 0.143). To offer some idea about the profit vs. time produced by this model and set of scale/shape parameters, I've extended the formula down to week 10 (see O24:O34). The modified function makes use of the three blue cells T8:T10, so if this approach is adopted, you might want to find some place on your sheet for the contents of R8:T10...or hardwire into the formula the values of the scale and shape parameters. Since XL2BB won't capture the functionality of the scroll bars, you might find it easier to work with the sample worksheet I created, which is available at:

MrExcel20210131.xlsx

Shared with Dropbox

www.dropbox.com

MrExcel20210131.xlsx
	A									J	K	L	M	N	O	P	Q	R	S	T
4																		based on normalizing relative to F(T)=1
5																		CDF 2-parameter Weibull distribution
6																		F(x) = 1- exp(-((x/l)^k))	x>=0
7																		F(x) = 0	x<0
8																		scale parameter, lambda	l	2.9
9																		shape parameter, kappa	k	0.8
10																		normalize to F(T), so F(x)/F(T)=1	T	8.0
11																		x	F(x)/F(T)	F_(i+1) - F_i
12	8855																	0.0	0.0000
13																		0.2	0.1241	0.1241
14																		0.4	0.2071	0.0830
15																		0.6	0.2759	0.0688
16																		0.8	0.3354	0.0595
17																		1.0	0.3881	0.0527
18																		1.2	0.4354	0.0473
19																		1.4	0.4782	0.0428
20																		1.6	0.5172	0.0390
21																		1.8	0.5530	0.0358
22																		2.0	0.5859	0.0329
23														# of wks				2.2	0.6163	0.0304
24										346400	39.1			0	50	0		2.4	0.6444	0.0282
25														1		37463.66		2.6	0.6706	0.0261
26														2		56548.87		2.8	0.6949	0.0243
27														3		69260.86		3.0	0.7176	0.0227
28														4		78274.66		3.2	0.7388	0.0212
29														5		84884.41		3.4	0.7586	0.0198
30														6		89838.68		3.6	0.7772	0.0186
31														7		93611.38		3.8	0.7946	0.0174
32														8		96519.5		4.0	0.8110	0.0164
33														9		96519.5		4.2	0.8264	0.0154
34														10		96519.5		4.4	0.8408	0.0145
35																		4.6	0.8545	0.0136
36																		4.8	0.8673	0.0129
37																		5.0	0.8795	0.0121
38																		5.2	0.8909	0.0115
39																		5.4	0.9017	0.0108
40																		5.6	0.9119	0.0102
41																		5.8	0.9216	0.0097
42																		6.0	0.9308	0.0092
43																		6.2	0.9395	0.0087
44																		6.4	0.9477	0.0082
45																		6.6	0.9555	0.0078
46																		6.8	0.9629	0.0074
47																		7.0	0.9699	0.0070
48																		7.2	0.9765	0.0067
49																		7.4	0.9829	0.0063
50																		7.6	0.9889	0.0060
51																		7.8	0.9946	0.0057
52																		8.0	1.0000	0.0054
53																		8.2	1.0052	0.0052
54																		8.4	1.0101	0.0049
55																		8.6	1.0148	0.0047
56																		8.8	1.0192	0.0045
57																		9.0	1.0235	0.0042
58																		9.2	1.0275	0.0040
59																		9.4	1.0313	0.0039
60																		9.6	1.0350	0.0037
61																		9.8	1.0385	0.0035
62																		10.0	1.0419	0.0033
BWMagee

Cell Formulas
Range	Formula
T8:T9	T8	=U8/10
S12:S62	S12	=(1-EXP(-((R12/$T$8)^$T$9)))/(1-EXP(-(($T$10/$T$8)^$T$9)))
T13:T62	T13	=S13-S12
P24:P34	P24	=($O$24-$K$24)*$A$12*IF(N24<=0,0,IF((N24>=0)*(N24<=$T$10),(1-EXP(-((N24/$T$8)^$T$9)))/(1-EXP(-(($T$10/$T$8)^$T$9))),1))

 

[BWMagee]

@KRice
Thanks very much for the detailed reply! I'm about to dive right in and check it all out. Before I do, if it makes any difference, the value 50 is the maximum at 8 weeks, but the important thing is not so much that there 8 weeks, but that the maximum number of weeks is predetermined. That is, I could change that value to say 9, and the value 50 would be achieved at 9 instead of 8. To do this I plan to put the value of max weeks (eg. 8) in a cell (eg. A1), then reference the cell in the formula, not the value, giving me the flexibility to change the value if desired. The value 39.1 is the minimum at 0 weeks, and there cannot be less than 0 weeks. I hope not to require such large tables, but to place the formula in a single cell (and the relevant data in their own column, as per my first post). Based on your graph the Weibull curve looks fine. I don't need this to highly accurate. More a general idea.

 

[BWMagee]

Update, have tested and works perfectly! Thank you so much!
I can tweak the lambda and kappa to preference. As I don't know the underlying rationale for how those values are arrived at it is just trial and error until I get something that feels right, but it is certainly good enough for my purpose.

 

[KRice]

Thanks for the update. I wanted to clarify something...you don't need the large tabular listing that I added. That was done only to create the values needed for plotting a profit percentage vs. time curve so that you could visualize whether the curve made sense. Think of that only as a tool for making somewhat arbitrary adjustments to lambda and kappa while being able to visualize their affect on the curve. Once lambda and kappa are known, the entire right side of the table can be eliminated, except for the values of lambda, kappa, and T (the time at which maximum profit should be achieved). You mentioned putting this latter variable in cell A1. In the working sample below, I've done that...with T, the time to maximum profit, in cell A1, and lambda and kappa are listed as variables below that in A2:A3. The formula in P24 references all three of those variables and checks whether the time in N24 (for when the profit earned should be estimated) lies within the range (0,T), and if so, then the modified Weibull cdf is used to compute the profit.

MrExcel20210131.xlsx
	A	B								J	K			N	O	P
1	8.0	Max profit at T weeks
2	2.9	scale parameter, lambda
3	0.8	shape parameter, kappa
4
11
12	8855
13
22
23														# of wks
24										346400	39.1			2	50	56548.87
BWMagee_trimmed

Cell Formulas
Range	Formula
P24	P24	=($O$24-$K$24)*$A$12*IF((N24>=0)*(N24<=$A$1),(1-EXP(-((N24/$A$2)^$A$3)))/(1-EXP(-(($A$1/$A$2)^$A$3))),1)