Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 8. June 2008 06:47

One of my clients recently asked me to modify an Excel model, so that the adoption of products entering the market would follow a S-curve. After some digging and googling, I came across this excellent post by Juan C. Mendez, where he proposes a clean and very practical way to use the logistic function, and calibrate it through 3 input parameters: the peak value, and the time at which the curve reaches 10% and 90% of its peak value.

The beauty of his approach is that his function is compact so it can be typed in easily in a worksheet cell, and the input very understandable. However, I found it a bit restrictive: transforming it for values other than 10% and 90% requires some recalibration, and more importantly, it cannot accomodate values that are not "symmetrical" around 50%.

So I set to work through a generalized solution to the following problem: find a S-Curve that fits any arbitrary value, rather than just 10% and 90%.

The solution

The formula I ended up with is, not surprisingly, quite a bit longer (and unpleasant) than Mendez's solution:

=Peak/(1+EXP(-((LN(1/Value1-1)-LN(1/Value2-1))

/(Time2-Time1))*(Time-(LN(1/Value1-1)

/((LN(1/Value1-1)-LN(1/Value2-1))

/(Time2-Time1))+Time1))))

(I broke the formula into 4 pieces to make sure it fit on screen. The formula should be in one piece in a single cell.) 

Peak represents the peak market share, i.e. the long-term value of the share of the product (called "Saturation" in Mendez's post). Value1 and Time1 represent the percentage of the Peak share that the product has already reached at time 1, and Value2 and Time2 the percentage of peak share the product has reached at time 2. Time is the time at which the function is to be evaluated.

Illustration: suppose that your product has a long-term market share of 80%, and that it will reach 50% of its peak share (i.e. 50% of 80%, that is, a 40% market share) in April 1st, 2008, and 90% of its peak share (i.e. 90% of 80%, that is, a 72% market share) in July 1st, 2012. In that case, the parameters would be

Peak: 80%

Time1: 2008.25

Value1: 50%

Time2: 2012.5

Value2: 90%

The Excel sheet attached illustrates the curve in action. Given how lengthy the formula for the curve is, I would recommend to consider first whether the formula proposed by Juan Mendez is sufficient for your needs, and, if you really want to go ahead with mine, to write it as a user-defined function, so that you won't have to keep such a large formula in your cells.

S-Curve.xls (26.00 kb)

The math

The equation for the S-curve is given by:

We need to be able to transform this curve so that we control when the growth happens, and its speed. To that effect, we will transform the original curve by adding two parameters Alpha and T0:

In essence, T0 shifts the timeline of the curve, and alpha stretches or compresses time. The chart below illustrates the impact of these parameters on the curve. The Blue curve corresponds to the original S-Curve, with Alpha = 1 and T0 = 0. The Red curve has a value of T0 of 2, which "moves" the curve by 2 units to the right: it reaches 50% at t=T0, instead of t=0. The Green curve has a value of Alpha = 2; it still crosses 50% at t=0, but its growth happens "twice as fast" as the original curve. Where the original curve takes (roughtly) 4 periods to grow from 10% to 90%, the Green curve achieves the same growth in just 2 periods.

Our goal is the following: given two values f1 and f2, and two dates t1 and t2, we want to find the two values Alpha and T0 such that f(t1) = f1 and f(t2) = f2. Playing a bit with the equation f(t1) = f1 yields the following:

Doing the same exercise on f(t2) = f2, we end up with a system of 2 linear equations in two unknowns Alpha and T0:


That system is easily solved and gives us the following values for Alpha and T0:

Comments

6/6/2008 12:36:38 PM #

Billy Boyle

Hi Mathias

I've just left a message on Juan's page and saw your generalised solution and thought I'd leave a similar message. I use an interactive Bass curve dashboard to get everyone involved in the ‘what if’ sensitivity analysis process. There is a link to it on my blog page acasoanalytics.wordpress.com/.../. I’ll have a go at doing the same with your approach.

Billy Boyle United Kingdom | Reply

7/30/2012 9:51:28 PM #

Arvind Ravi


Hi Mathias:

I have been reading this S curve discussion from this site. Thanks for sharing good information. However, I am having difficulty in converting this example for a project schedule. For example,
My Y axis on the right will contain percentages 0% to 100% and my x axis will include months e.g Jan, Feb, Mar

My data will look like the below, any insights on how to get an S curve will greatly help!!
Main Task 1     Start date 1     End Date 1
Main Task 2     Start date 2     End Date 2
Main Task 3     Start date 3     End Date 3

Arvind Ravi Australia | Reply

6/15/2008 5:54:38 AM #

pingback

Pingback from acasoanalytics.wordpress.com

Intuitive Bass Diffusion  « Acaso Analytics

acasoanalytics.wordpress.com | Reply

6/15/2008 6:09:36 AM #

Billy Boyle

Hi Mathias,
I've just posted an interactive dashboard of your S curve. The link is here acasoanalytics.wordpress.com/.../
Took me a little while to troubleshoot as the software doesn't support named ranges (only cells) so it kept returning a 'not a number' error. Seems to work fine now. Cheers - Billy

Billy Boyle United Kingdom | Reply

6/26/2008 10:00:15 PM #

Mathias

@ Billy Boyle: very effective (and visually pleasing) way to illustrate the impact of parameters on the shape of the Bass curve. Definitely let me know if you end up creating a dashboard for "my" curve!

Mathias United States | Reply

7/31/2012 3:40:39 PM #

Arvind

Hi Mathias:

I have been reading this S curve discussion from this site. Thanks for sharing good information. However, I am having difficulty in converting this example for a project schedule. For example,
My Y axis on the right will contain percentages 0% to 100% and my x axis will include months e.g Jan, Feb, Mar

My data will look like the below, any insights on how to get an S curve will greatly help!!
Main Task 1     Start date 1     End Date 1
Main Task 2     Start date 2     End Date 2
Main Task 3     Start date 3     End Date 3

Arvind Australia | Reply

7/18/2009 5:15:55 AM #

best casino to play slot machine online

Very nice formula to get the S-curve.

However these curves do not take initial adoption (adoption at t=0) as an input and probably assumes it to be 0. How should I modify the formula so that it takes the initial adoption rate as input as well.

best casino to play slot machine online United States | Reply

7/18/2009 5:50:37 AM #

Mathias

@rana: thanks for the feedback.
Strictly speaking, if you are really talking about adoption, the initial value should be 0%, because no one has adopted yet. I can understand your question two ways. First, there could already be a population that adopted, and the "new" adoption is adding to it. In that case, replace the formula by InitialValue + formula, with Peak value replaced by Peak-InitialValue. Now if you want the initial rate of growth to be set to a value you define yourself, that's a more complicated enterprise...

Mathias United States | Reply

9/1/2009 6:19:24 PM #

Thomas Mampilly

Hi Mathias,
                       I have used your excel file for a thesis work @ universtiy or Lugano and ETH Zurich, i have been working on Innovation as a theory for a case study on virtual world, i have used your excel file for generating the S-curve. Hope thats alright. Please let me know if possible.

Thanks in advance !

cheers,
  Tom

Thomas Mampilly Switzerland | Reply

9/1/2009 6:42:25 PM #

Mathias

Hi Tom,
I am very flattered! Thank you, and yes you have my blessing to use that file, and... I'd love to hear more about your research.
Mathias

Mathias United States | Reply

9/23/2009 3:41:33 PM #

Bertus

Hi Mathias,

I found this very useful indeed after spending a few hours googling the S-Curve.  I am trying to convert this into a payment profile for a model in the construction industry where saturation would be 100% and adoption 0%. How would you need to change the formula to force saturation at a specified date to replicate a payment profile with a start and end date?

Thanks

Bertus

Bertus United Kingdom | Reply

9/24/2009 4:23:23 AM #

Mathias

Hi Bertus,
If I understand you correctly, you want a curve which begins at 0% and ends at 100% over time, matching certain levels over time. There is one issue here, which is that the logistic curve never reaches 0% or 100%: the curve tends to 0%, and to 100%, if you set the peak value to 100% - so you won't be able to fit a curve starting at 0% and ending at 100%. What I would suggest you do is to set the peak value at 100%, and set value1 and value2 close to 0% and close to 100%, at the dates you desire.
Hope this is helpful!

Mathias United States | Reply

10/21/2009 12:24:47 PM #

M Tuin

Thank you so much for this. I started with Juan C. Mendez' function, but struggled with exactly the same things you indicated. Thank you so much for providing this solution, and even more for explaining it clearly so everyone can implement it.

M Tuin Netherlands | Reply

10/21/2009 4:37:53 PM #

Mathias

Thank you for the encouragement, Mercedes - and I am glad it was helpful!

Mathias United States | Reply

11/19/2009 5:58:58 AM #

khalil

Hello Mathias,

By using you Excel program, how can I control/change the x-axis range (time).
You set the range from Q12000 t0 Q42010 which give 41 unit of x-axis; but I wanted more units in x-axis.
Will appreciate your help.

Khalil.

khalil Malaysia | Reply

11/27/2009 4:29:06 AM #

Matt

Hello,

Billy Boyle's interactive model is nice , but I do believe that p & q are mislabelled.

p is coeff of innovation
q is coeff of imitation

Best,

>matt

Matt United States | Reply

11/27/2009 6:36:20 PM #

Mathias

@ Khalil: my apologies for the late reply. To add more periods, you need to do 2 things:
1) just drag down the last 2 rows of calculations, so that more periods and formulas are added. At that point, you should see an error, because the formulas refer to the named range "Time", which doesn't get automatically extended when you drag down the cells.
2) The "time" range is initially A14:A54: you want to edit that named range, so that it covers all your new values in column A.
Hope this helps!

Mathias | Reply

11/27/2009 6:43:54 PM #

Mathias

@Matt: I believe you are right - you should probably contact him! It's a totally minor issue, but hey, why not aim for perfection...

Mathias | Reply

11/27/2009 11:02:12 PM #

resep masakan

thank you, this is very good information... and useful

resep masakan Indonesia | Reply

11/30/2009 7:32:46 PM #

Shared Web Hosting

Took me a little while to troubleshoot as the software doesn't support named ranges (only cells) so it kept returning a 'not a number' error. Seems to work fine now. Cheers

Shared Web Hosting United States | Reply

9/23/2010 11:04:31 AM #

Ron

Having a recordset with data that cleary seemed to be distributed in an s-shaped fashion, I started searching on the internet to find a logistic regression method to apply to these data. After having searched in vain for an awful lot of time I finally found this very helpful page. I found especially the second equation from the math section most helpful. In Excel I then used the Solver add-in to estimate the alpha and T0 parameters, using them in a  least sum of squares problem. In the end I obtained a correlation of 0.996 between the observed data and the s-curve, which is an extremely satisfying result.

Ron Netherlands | Reply

4/18/2011 12:10:16 AM #

siersplit

Hai Mathias,

Thanks for this nice post! I also was wondering how to make more units in x-axis?? Any idea?

siersplit Netherlands | Reply

8/6/2011 10:44:12 PM #

Dave

Hey Mathias

My name is Dave and I came across your S-Curve adaption excel spreadsheet. I am attending the University of Maryland taking a technology management class and would like to have your permission to use your S-Curve excel spreadsheet.

thanks,

Dave United States | Reply

9/18/2011 9:37:18 PM #

Vijay

Hey Mathias,
I am Vijay. i find your S- curve interesting. And, i would like to use this your curve. Hope you do not mind.
I have the scenario as given below:
TIme1=25; Value1=50 and Time2=70 and Value2 =90. And Peak =100 and i want to see this peak at Time=100 and then on, the value should stabilize at 100. How can i change your curve to fit to my constraints. Any suggestions would be appreaciated.

Thanks,

Vijay India | Reply

10/25/2011 9:52:49 AM #

pingback

Pingback from d-marketingspot.com

Strategy+Business Lessons: Financial Ratios | D-Marketingspot.com

d-marketingspot.com | Reply

10/26/2011 5:30:42 AM #

pingback

Pingback from nyatiphotography.com

nyati photography  » Blog Archive   » Accenture   Financial Ratios Knowing the Market Drivers

nyatiphotography.com | Reply

2/10/2012 12:00:23 AM #

pingback

Pingback from edebat.shikshik.org

Mathias boyle | Edebat

edebat.shikshik.org | Reply

2/12/2012 9:12:23 AM #

pingback

Pingback from h3maths.edublogs.org

H3 Maths -   What’s the Difference between Algebra and Geometry?

h3maths.edublogs.org | Reply

5/13/2012 4:40:13 PM #

pingback

Pingback from seidoch.allergiesaid.com

Thomas mampilly | Seidoch

seidoch.allergiesaid.com | Reply

5/20/2012 4:31:34 PM #

david

Hello Mathias, wow...what an awesome resource.

I guess I am not clear on where you go to make the change to Time1 and Time0

david United States | Reply

5/27/2012 12:42:45 PM #

Mathias

Thanks for the words of encouragement! I believe you should be able to change T0 and T1 on top of the spreadsheet without too much problem - or am I misunderstanding your issue?

Mathias United States | Reply

5/29/2012 5:22:57 PM #

Adam

Mathias,

This is beautiful...Keep up the great work!

Adam Japan | Reply

6/5/2012 11:42:03 AM #

Greg Hall

This was a great help.  In a much more abstract sense, this is akin to the Pareto principle.  In this case, close to 80 percent of market adoption happens during (the middle) 20 percent of the time.  Various other factors can be added onto this based on specifics of the product type, market, etc.   I used it as a baseline (instead of a linear baseline) for city growth planning to target population 30 years from now.

Greg Hall United States | Reply

3/5/2013 6:51:47 AM #

Quintin Graves

This article was extremely helpful.  I my company ever needs this kind of help again, I know just the consulting firm.  =)  

Thanks again.

Quintin Graves United States | Reply

3/22/2013 3:26:07 PM #

Mathias

Thanks Quintin - and glad it helped!

Mathias | Reply

8/21/2013 6:33:04 AM #

Scott

Mathias,

I am wondering if you have combined Mr. Mendez's approach to modeling the adoption curve on an annual approach, basically showing the time derivative of your total market adoption curve, from the point of view of the input parameters.  I need to model and integrate over time the monthly revenue of a product for a curve defined by the max annual sales and the percentage of max sales at two points in time.   Can you tell me if this has been done by someone already?

Scott United States | Reply

8/24/2013 4:45:55 AM #

Mathias

Hi Scott,
I don't know of an existing implementation - good luck with your project!
Cheers,
Mathias

Mathias United States | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS