I’ve got a number of questions on the simplified Excel s-curve formula I published some time ago, so here are more details for those interested in the math behind it. The previous posting focused on how business analysts sometimes need to model market adoption, and provided a simple and easy to maintain formula to do so in Excel.
The formula =saturation/(1 + 81^((hypergrowth + takeover/2 – year)/takeover)) suggested for Excel is a simplification of the formula for a sigmoid function (See the Wikipedia article)
The graphic below shows the shape of both functions is identical. The saturation parameter just scales the function to a desired value, instead of going from 0 to 1. The factor 81 on the Excel formula determines how “sharp” the curve is, in this particular case, reaching 0.1 at the period hypergrowth and 0.9 at hypergrowth + takeover. Note that 81^x can be re-written as e^(ln(81)*x), so whatever factor is used there is simply going to affect the shape by compressing or expanding it horizontally.
This is how the scaling factor can be computed. Let’s say we want the penetration to be 5% at the period specified by hypergrowth. We can work out the solution off the second function. We need to solve for 1/(1+e^(-x) == 0.05, which gives x=-2.94444. Since the function is symmetrical, we also know for x=2.94444 P(x) == 0.95.
Since factor^((hypergrowth + takeover/2 – year)/takeover)) can be re-written as e^(ln(factor)*(hypergrowth + takeover/2 – year)/takeover)), we can solve ln(factor)*(hypergrowth + takeover/2 – (hypergrowth + takeover))/takeover == 2.94444. Reducing all the math, we arrive to
1/(1 + e^(-0.5*ln(factor))) == 0.95, and factor would be 361. If the desired penetration at hypergrowth is 20%, then we solve 1/(1 + e^(-0.5*ln(factor))) == 0.80, leading to factor == 16
Pingback: Juan C. Mendez’s pages » Blog Archive » Modeling market adoption in Excel with a simplified s-curve
Very interesting. How does this relate to the Bass diffusion model? ( http://en.wikipedia.org/wiki/Bass_diffusion_model ). I suppose I could examine the formulas to find out, but thought you might have already looked into this?
Bob – Thanks for your comment. I just posted an entry that addresses your question. http://jcandkimmita.info/jc/2007/07/business/rela….
I just posted a screencast that should help with using the model
Juan,
I was wondering if there is a way to develop a Bass Model to predict how a technology cascades (technology spillover) from one segment to another segment within a given industry. For example, how a technology cascades from a luxury segment to a compact segment within the automotive industry.
Pingback: Juan C. Mendez’s pages » Blog Archive » System dynamics interpretation of the logistic and Bass models
Juan,
Your example shows in years. However, I need to do my calculations in months, tried to convert, but doesn't work correct. Any suggestions?
@Edward
The model works regardless the time units, as long as everything is consistent. Let’s say saturation is 25, hypergrowth is month 8, and takeover is month 28. So when you use the same formula
f(month)=saturation/(1 + 81^((hypergrowth + takeover/2 – month)/takeover))
you would have for different months
Thanks a lot… I worked on the formula over the weekend and figured it out.
However, I’m now having one other problem… hope you don’t mind looking at.
————————
Let’s say you have a project starting on 1/1/07 and during the life of the project, it is estimated to use 45GB of space. I want to calculate monthly growth and track total growth. My table has 3 columns (Month, Growth, % Growth). Month should be 1 – 23, Growth = % * Data_Volume, and % Growth = f(month).
I’ve tried several different things:
1. Calculating % Growth = f(month) – ends up getting 151GB & 335% for total space.
2. Calcuating % Growth = current f(month) – prior f(month) – now get 11GB and 24% for total space.
What I need, is the month Growth total never to exceed estimated total size (45GB) and total Growth % to equal 100%. Make sense?
Thanks for all your help.
Conditions:
1. Base_month = 1
2. # of Project Months = 24
3. Saturation = 25%
4. Hypergrowth = 4 (=ROUND(takeover*0.3,0))
5. Taekover = 14 (=ROUND((MAX(Monthly_Growth_Table[Month])*takeover_rate),0))
6. takeover_rate = 60%
7. Data_Volume = 45GB
@Edward – I have trouble understanding your problem description.
“during the life of the project, it is estimated to use 45GB of space” — Are you going to add data each month data[m] so SUM(data[m] for m=1..24) ~= 45GB, right?
Then, Growth is NOT =f(m) as you tried.
If I understand correctly, total_data[m] = f(m), where f is my formula with parameters saturation=45 [GB], hypergrowth=4, takeover=14.
It is somewhat counter intuitive to define takeover in terms of a takeover_rate applied over a growth table. takeover is simply an assessment of when you believe the technology is “mature” or “quite adopted”.
With that you would have an S-shaped curve that grows from 0GB to ~45GB by month 24.
The rest of your variables, you would derive backwards, and it would be something like
data[m]=growth[m]=total_data[m]-total_data[m-1] assuming total_data[0]=0
growth_pct[m]=growth[m]/total_data[m] if I define growth_pct as the percentage by which the data grows each month versus the accumulated data. Other ways to define growth (month over month, etc) can be also derived backwards very easily.
However, that doesn’t jive with your other statements “What I need, is the month Growth total never to exceed estimated total size (45GB) and total Growth % to equal 100%. Make sense?”
Nope. I’m confused.
Is 45GB then the maximum allowable growth for a month? That would be described as follows:
For any i in 1..24 data[i]< =45GB, which is quite different from SUM(data[m] for m=1..24) ~= 45GB
With that formulation, it explains that you got ~151GB for total space.
Two more questions...
"total Growth % to equal 100%"... at which month?
Why you chose saturation = 25%
Hope it helps. I would recommend to check http://en.wikipedia.org/wiki/System_dynamics to help you clarify which variables you want to model as “stocks” and which ones are “flows”
Anyone have a macro for the "broken stick rule" which describes impact of order of entry.
Jaimin – Apologize for the delay responding – I've been swamped with work. Could you please elaborate on your comment? Take a look at http://www.lieb.com/NEWS22/2nd.htm
Is that along the lines you need?
Juan, you're impressive