UPDATE 10/31/2007: Here is a screencast for this model
Often business analysts need to model the adoption of a new product or service for financial planning. There are several approaches, but a common one is the s-curve (see Wikipedia article). Here is a simple implementation in Excel that can be easily added to your spreadsheets. It reduces all the math to just three parameters:
- saturation – What is the maximum expected penetration after the product becomes mainstream? i.e. what is the value that the top of the s-curve will reach?
- start of fast growth – By this year, the penetration will be 10% of the saturation value, and it will start to grow rapidly. 10% was an arbitrary choice to simplify the model, and by doing some math you could change the formula to any value. It is a reasonable choice in most cases. We’ll call this parameter hypergrowth
- takeover time – How long it will take for the product to “catch on”? – The operational assumption in the formula is that this number of years after the start of fast growth, the product would have reached 90% of the saturation value and will start to slow down. Again, 90% is an arbitrary value I chose.
The s-curve model focuses in the early phases of the product lifecycle, until maturity is reached. Penetration decay is NOT covered by this model.
The formula for each year’s penetration would simply be:
=saturation/(1+81^((hypergrowth+takeover/2-year)/takeover))
See it in action:
In the sample spreadsheet above, look at cell B8 where you can see the formula in use. It is the same for all row 8.
saturation, hypergrowth and takeover are names defined for the parameters on rows 2 to 5 (you use names in your models instead of plain cell references, don’t you?)
Very simple, easy to maintain, light on calculation times… happy market adoption modeling!
PS: The chart shown is NeoOffice, an open source alternative to Excel for Macintosh users, based on OpenOffice
Very nice. Simple and practical. I always get customers questioning the shape of the S curve and this is a great way to do it.
One question I have is 81. I assume you get to this based on your 10% in the first part of the tail. What do I use if I want 20% in the first part of the tail?
Ken – Thanks for your comment, and sorry for the late response. I had comment notifications off because of a recent slew of spam.
If you want an s-curve that reaches 20% (of the saturation value) in the first part of the tail (i.e. by the period denoted by the parameter hypergrowth), you can use 16 instead of 81 in the formula. Such curve will only reach 80% at the period hypergrowth+takeover, and it will look much "softer". A "sharper" curve, that reaches 5% in the first part, grows and reaches 95% at hypergrowth+takeover can be obtained using 360 instead of 81.
Best regards, Juan C.
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.
Thanks in advance
I tried the following approach for incorporating the initial adoption:
Lets say we have the following inputs:
saturation = 100%
hypergrowth = 2
takeover = 5
initial_adoption = 20%
then this can be modeled as:
saturation_new = saturation – initial_adoption
hypergrowth_new = hypergrowth
takeover_new = takeover
and the formula for each year's penetration will be:
year_penetration_new = + initial_adoption
Essentially, I have scaled down the curve by reducing saturation level and then shifted the curve upwards to include the initial adoption rate.
Do you think this approach should work good?
Pushkar
Thanks for your comments. Yes, your approach is exactly what is needed. Let's say you have a product that is already in the market and you'll roll out new features you expect will drive a significant increase in adoption. This initial penetration may be 5% and you expect the new features will drive to 35% in 7 time periods (months, years,…). Then you would use saturation = 30% (35-5), and add to the formula an initial_adoption parameter as you did, which will be 5%.
By the way, I only used years as an example because I work in the auto industry where product lifecycles are long. The s-curve formula presented can be used with months (electronics lifecycles), days (disease spreading), or whatever time period desired
Juan – I know that you have mentioned that the 81 in the formula can be changed to represent different growth percentages in the first part of the tail. Could you tell me how that number is calculated so that I may have the option of choosing any number between 0% and 100% instead of just 10%, 20%, and 5% with 81, 16, or 360.
Molly
Thanks for your comment. I posted a note with the math at http://jcandkimmita.info/jc/2007/07/excel/math-on…
Usually I forget the math when I need to use the formula, and the trick I use is to enter the formula with the factor (81,16,360, etc) as a reference to a cell, instead of a fixed value, then use Excel's Goal Seek to change that cell until I get the desired value for the period hypergrowth. Finally, I simply replace the formula in all the cells with the computed constant. Good luck with your modeling!
Pingback: Juan C. Mendez’s pages » Blog Archive » Math on the simplified market adoption s-curve for Excel
The folks at provenmodels.com linked to this page as a resource. http://www.provenmodels.com/570 — They are an excellent source of managerial models and frameworks – highly recommended.
Pingback: Juan C. Mendez’s pages » Blog Archive » Relationship between the Bass and the logistic market adoption models
Juan,
I cannot see the formula you have pasted in cel B8.
Where can I find it, if I am may ask?
Um…
Excuse me. I have a problem.
How to use the simplified formula in Excel?
Cause I have try various way, but I can't draw S-Curve.
Could you send me an example?xls? to my e-mail ?
Thank you so much.
@Donald, @Kevin
I just posted a screencast that should help with your questions
Pingback: Juan C. Mendez’s pages » Blog Archive » System dynamics interpretation of the logistic and Bass models
Hello Juan,
Many thanks for providing a great and comprehensible work around for lpeople (like me) wanting to utilize s curves in an excel model, without really understanding the maths behind it. Could you possibly spell out in idiot proof terms (the idiot being yours truly), the full formula with the 'initial adoption' parameter added as per Pushkars mail. It would be greatly appreciated. Thanks, Phil.
Phil
Thank you for your message. The modified formula, as you would like to use it would be
=initial_adoption + (saturation-initial_adoption)/(1+81^((hypergrowth+takeover/2-year)/takeover))
Hope it helps!
Hello Juan,
I also want to express my grattitute
Could you explain me about how (in what extent) is it reliable to consider that behaviour of the curve before and after hypergrowth period is same in real business. In other words, in real business can we believe that growth size before and after hypergrowth would be same?
Sorry for my poor English if I couldn’t express my question.
Thank you!
If you want a generalizable way to specify the penetration level before the hypergrowth starts, you can add a variable "Penetration Level Before Hypergrowth" and use this instead of 81:
((saturation/penetration_before_hypergrowth)-1)^2
In the example above, penetration reaches 3% (or 10% of the 30% saturation) before hypergrowth, so:
(( 30% / 3% ) – 1) ^ 2 = 9 ^ 2 = 81
Cheers,
Ryan
Hi Juan,
Thanks for your series on the S-Curve, which was most helpful. I needed to be able to specify a curve through any pair of values, and not just 10%/90%, and resolved that question here, in case you are interested. While more flexible, the formula I end up with is also way more complicated / painful, and I would therefore recommend that people stick with yours in general, as it is much more compact and readable!
Pingback: S-shaped market adoption curve
Hi Juan,
I like the way you have rephrased the parameters of the model to useful inputs that people can work with. I use the Bass curve in new product forecasting using an interactive dashboard to get everyone involved in the 'what if' sensitivity analysis process. There is a link to it on my blog page http://acasoanalytics.wordpress.com/2008/06/25/in… . I'll have a go at doing the same with your approach.
Thanks a lot for the great example. I want to create an s-curve that I can use to estimate the effects of marketing expenditures on sales. I know that normally this should be based on transaction data. In this case, I don't have this data so I just want to create a curve where I manually enter saturation levels and base sales (number of sales generated without any marketing expenditures)
Does anybody have an idea how to apply this?
Hi Juan,
Thanks for providing this example. It's very useful! What I would like to do, however, is to find the gradients of the S-Curve at any desired point. How would I find that using excel, after my Curve has been created?
Thanks!
@Benedict
Thank you for the message. I would recommend one of these approaches based on the info you have available, and your particular needs:
(1) estimate by analogy to other historic records of adoption, and perform subsequent refinement (i.e., visual tracking). This requires finding a similar case of adoption (i.e. fax, personal computer, teletype, iPhone, etc.) to use as your reference. For Bass model, you may find p (innovation) and q (imitation) for several industries and products. As a rule of thumb if I don’t have data, I start with p=0.02 and q=0.4 – I think Mahajan, Muller, and Bass (1995) has some listed p and q
(2) fit the logistic or Bass model to past data using nonlinear least squares. Look up Srinivasan and Mason (1986). This is tricky for very early stages of innovation, as there may be huge variability on the “end game” for small variations in your estimated parameters.
Hopefully this points you in the right direction
A late followup question: what do you do when adoption has already started? WHat happened is you already have the real data through to, say, 2007 in your example above? Is there a way to fill in the rest of the S curve adapting to the existing data run? There ought to be a way to drive the variables from the historic data, but I can't quite work out how…
Pingback: Logistic model for the s-curve and project management cost estimation | Juan Carlos Méndez-García
Pingback: PicoBusiness » nice sigmoid function
Hi Juan
First off, thanks so much for this. Even i (a total non-modeller) have been able to create the s-curve using your formula.
I also need to model different products launching at different times and wondered how the base year (month) could be used in the equation to allow this? Apols if this question is not clear!
Mark
@Mark
You can use different sets of parameters for each of your products. Then, set the hypergrowth and takeover accordingly. Lets say you have prod1 and prod2. So you define the formulas for each using different parameters (i.e. hypergrowth1, takeover1 and hypergrowth 2 and takeover 2). So if prod2 launches 5 years after prod1, you set the value for takeover2 to takeover1+5. When modeling more than one product, think how you will incorporate interactions between them (i.e. is prod2 the next generation of prod1, are they substitutes, complementary or independent, etc.)
Hi Juan,
I wondered if you still had the example spreadsheet? I'd really like to see this in action.
Hi Juan
Interesting webpost about sigmoid curves, It's exactly what I'm looking at at the moment, modelling the take up of doordrop coupons for various products.
Only thing is i couldn't download your excel spreadsheet to see what you're doing exactly which would be interesting.
Can you please advise
sincerely
Ben
Um…Excuse me. I have a problem.How to use the simplified formula in Excel?Cause I have try various way, but I can't draw S-Curve.Could you send me an example?xls? to my e-mail ?Thank you so much.
Good morning
Thank you for your post, however, it seems that the link is broken. I can´t entered, or downloaded the file. Would you mind to fix it?
Thank you again
Renny
Dear Sir,
Thanks for the usful information I read about Rogers theory, that you provided, and I need your kind help in the following: Im trying to use Rogers diffusion of innovations theory in my research study which is: the adoption of e-procurement system innovation in the public sector. I need your kind advice to let me know:
1.Do you think I can implement or use Rogers’s theory in this area?
2.is there any scale or model in Rogers that I can use to apply it in my subject? Where can I find it? I mean how I can apply Rogers’s theory in my research in a practical way?
Regards and many thank for your time, hope I can have help and advice from you [I really need it], or if you know some one who can advice me?
Have a good day
If we sum the penetration – are we supposed to get 1 – to represent 100% ?
i am getting 2 plus