Developing an S-Curve

[dallr]

Good Morning Guys,

I am looking to develop an S-curve in excel and have searched all over the internet but not getting any suitable ones. The best one i found in terms of shape was the following:
http://www.clear-lines.com/blog/file.axd?file=S-Curve.xls

However this one is built in years and I want one to represent weeks. Also the data inputs for the planned and actual (as seen in the long and short version) is a formula when I need to enter the data there.

Can anyone please provide some guidance.

Dallr

 

[oldbrewer]

the shape is driven by the probability distribution you choose - start with the cumulative normal distribution and plot on a log scale.....

 

[dallr]

Thanks so much for the quick reply OldBrewer!

However you just spoke in Excel and I can only speak simple English. I cannot begin to understand how to attempt what you have advised.

 

[oldbrewer]

ok say your data is 1,2,4,7,12,16, 19,20,19,16,12,7,4,2,1

total is 142

plot cumulative percentage so first point is 1/142 second point is (1+2)over 142 and so on
use excel to plot that

 

[dallr]

Thanks again.

I understand and have applied your suggestion. It smoothed out the graph a bit more but it still does not get the full effect of the s-curve shape. I am assuming this is because of the data that I am using in my examples. In the latter set of your data "19,16,12,7,4,2,1" the numbers start to decrease. However in a project environment the actual percentages will not decrease but continue increasing until 100% of the project is completed. Given this scenario is there a difference formula i need to use?

Also if i use your formula I would be able to reproduce a curve for the planned percentages of work. However how would I get the values for the actual to to determine the denominator? Hope my feedback makes sense.

 

[oldbrewer]

by definition my last point will be 100% remember you are plotting " cumulative percentage"

if my numbers were 1,2,4,2,1
first point is 1/10 second is 3/10 third is 7/10 fourth is 9/10 fifth is 10/10 = 100%

 

[dallr]

Thanks OldBrewer, I understand your last comment and that is what I was doing. However, i was making the point that the graph design does not look like a true 2-curve.

Also do you have any thoughts on my second point pertaining to plotting the actual data from the project where I would not have a true denominator until the project ends?

 

[oldbrewer]

before I retired I tracked the "cash" for a large department within a large organisation. For each month I had agreed budget figures which I called "planned". Under these I put actuals and below them "variation". I encouraged department heads to update the planned for future months according to whether there was overspend or underspend. Planned always had to total the total budget, obviously. You can do the same for planned work and actual work. So money spent (and money planned to be spent ) can be plotted against work done (and work planned to be done) . AS you progress through your financial year amendments will make the plots better able to represent any mismatch between work done and money spent.....

 

[dallr]

Thanks for the feedback and your guidance. Still not getting what i need but your assistance was welcomed.