Apr24
Modeling market adoption in Excel with a simplified s-curve
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
Ken J Says:
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?
Juan C. Mendez Says:
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.
Pushkar Says:
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
Pushkar Says:
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?
Juan C. Mendez Says:
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
Molly Says:
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.
Juan C. Mendez Says:
Molly
Thanks for your comment. I posted a note with the math at http://jcandkimmita.info/jc/2007/07/excel/math-on-the-simplified-market-adoption-s-curve-for-excel/
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!
Juan C. Mendez Says:
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.
Donald Says:
Juan,
I cannot see the formula you have pasted in cel B8.
Where can I find it, if I am may ask?
Kevin Says:
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.
Juan C. Mendez Says:
@Donald, @Kevin
I just posted a screencast that should help with your questions
PhilT Says:
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.
Juan C. Mendez Says:
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!
Ider Says:
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!
Ryan Says:
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
Mathias Says:
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!
Billy Says:
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/interactive-bass-diffusion-model/ . I’ll have a go at doing the same with your approach.
Tim Snijder Says:
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?
Debbie Says:
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!