Using the NormSDist Function to generate an S Curve

[singcbl]

I been trying to find a simple way to generate an S curve and came across the NormSDist function but I find it daunting. I have searched on the web in other forum but somehow could not find the answer I need.
I have a project which has a certain value let says $1,000,000 which is projected to complete within a time frame of between 12, 14 or 16 months. So what I need is to populate the monthly amount need to be completed within the three different time frame. From past experience the amount completed each month follow very much like the "Bell" curve. So if I can generate a bell curve over the 3 different time frame I should be able to create a S curve by accumulating the monthly amount.
I be grateful if anyone of you can show and guide me how to do this.

Thank you.

 

[singcbl]

Appreciate if any on you can point me to any resource that will help me with this question.

 

[kalak]

Appreciate if any on you can point me to any resource that will help me with this question.

If you just want an "S" curve does it need to be based on the cumulative normal?
There's lots of possible "S" curves. Just for fun (?) you could generate Pierre Verhulst's logistic, which has a number of modern uses.
Try running this code just to see ...

Sub S_Curve()
Dim logistic(1 To 20, 1 To 1), c, q

For Each c In [(row(1:20)-9)]
    q = q + 1
    logistic(q, 1) = 5 / (1 + 2 * 0.55 ^ c)
Next c

[a2].Resize(20) = [(row(1:20)-9)]
[b2].Resize(20) = logistic
[b2].Resize(20).NumberFormat = "0.00"
[b1] = "Logistic"
[a1:b21].Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("$A$1:$B$21")
ActiveChart.ChartType = xlLine

End Sub

 

[StephenCrump]

Appreciate if any on you can point me to any resource that will help me with this question.

Post#5 here may also be helpful: http://www.mrexcel.com/forum/excel-questions/828290-distribute-number-across-cells.html

 

[joeu2004]

I been trying to find a simple way to generate an S curve and came across the NormSDist function but I find it daunting. [....] I have a project which has a certain value let says $1,000,000 which is projected to complete within a time frame of between 12, 14 or 16 months. So what I need is to populate the monthly amount need to be completed within the three different time frame. From past experience the amount completed each month follow very much like the "Bell" curve. So if I can generate a bell curve over the 3 different time frame I should be able to create a S curve by accumulating the monthly amount.

While I agree with others that there is nothing sacrosanct about the normal distribution for your purposes, it is probably what the assignment dictates.

The following demonstrates how to generate the S-curve of the cumulative normal distribution.

	A	B	C
1	total amount	1,000,000.00
2	total months	16
3	mean	8.0000		B3: =B2/2
4	sd	2.0000		B4: =B3/4
5
6	month	cumulative amount	monthly amount
7	1	200.97	200.97	B7: =IF(A7="","",$B$1*(NORMDIST(A7,$B$3,$B$4,1)-NORMSDIST(-4))/(NORMSDIST(4)-NORMSDIST(-4))) C7: =IF(A7="","",B7-N(B6))
8	2	1,318.31	1,117.34
9	3	6,178.39	4,860.08
10	4	22,719.90	16,541.51
11	5	66,779.76	44,059.86
12	6	158,633.63	91,853.87
13	7	308,525.41	149,891.78
14	8	500,000.00	191,474.59
15	9	691,474.59	191,474.59
16	10	841,366.37	149,891.78
17	11	933,220.24	91,853.87
18	12	977,280.10	44,059.86
19	13	993,821.61	16,541.51
20	14	998,681.69	4,860.08
21	15	999,799.03	1,117.34
22	16	1,000,000.00	200.97

<TBODY>
</TBODY>

To make it easier to copy-and-paste, the formula in B7 is:

=IF(A7="","",$B$1*(NORMDIST(A7,$B$3,$B$4,1)-NORMSDIST(-4))/(NORMSDIST(4)-NORMSDIST(-4)))

The formulas in B7 and C7 are copied down through row 22.

Column C is not necessary, if you only want the S-curve. Simply plot column B in an XY-Scatter chart.

The following explains the theory behind the formula in B7.

Ostensibly, the normal distribution is from -infinity to +infinity. And we want the total area under that curve to be the total amount in B1.

But we cannot calculate the mean and std dev of -infinity to +infiinity months.

So in practice, we assign a z-number to the finite limits of the problem. In this case, -4 sd corresponds to 0 months, and +4 sd corresponds to B2 months.

We still want the total area under the curve between -4 sd and +4 sd to be the total amount in B1. So
(NORMDIST(A7,$B$3,$B$4,1)-NORMSDIST(-4)) / (NORMSDIST(4)-NORMSDIST(-4))
is the cumulative percentage of B1 between -4 sd and +4 sd.

Note: Since NORMSDIST(-4) and NORMSDIST(4)-NORMSDIST(-4) are invariant ("constant"), it is more efficient to calculate them one time in cells, and use the cell references in the formula in B7.

Also note that to generate the S-curve for 12 and 14 months, simply change B2. You also need to edit the range in the chart series.

 

[singcbl]

Hi guys,
Thanks for your replies. Let me try out all your suggestion to see if I can get what I want.

 

[singcbl]

Joeu2004,
To me it is easier to use your suggestion to work out my cash flow but I have only one problem. How can I flatten the "Bell" so that the front end and the back end monthly total are higher? I think it has to do with the "mean" and "sd" constant constant.

 

[joeu2004]

Joeu2004, To me it is easier to use your suggestion to work out my cash flow but I have only one problem. How can I flatten the "Bell" so that the front end and the back end monthly total are higher? I think it has to do with the "mean" and "sd"

The mean is always half of the number of months.

Yes, changing the sd alters the first and last month values.

But for my formulas, it is better to change the z-number that corresponds to the last month. That does change the sd.

Download the file "s-curve norm dist.xls" (click here) [1]. Ignore any preview errors, and just download the file. Alternatively, see the modified design description below.

However, the smaller the z-number (and the larger the first and last month values), the less the cumulative curve appears s-shaped.

Empirically, the smallest z-number is about 1.9, IMHO. Then the first and last month values are about $20,677 for a 16-month project of a total of $1,000,000.

The limitation is due to the nature of the normal distribution. If you want larger first and last month values, you might need to look for a different s-curve generator.

I'm afraid I cannot help you with that.

-----

Modified design, highlighted in red....

	A	B	C
1	total amount	1,000,000.00
2	total months	16
3	mean	8.0000		B3: =B2/2
4	sd	4.2105		B4: =B3/B5
5	max Z	1.9000
6
7	month	cumulative amount	monthly amount
8	1	20,677.28	20,677.28	B8: =IF(A8="","",$B$1*(NORMDIST(A8,$B$3,$B$4,1)-NORMSDIST(-$B$5)) /(NORMSDIST($B$5)-NORMSDIST(-$B$5))) C8: =IF(A8="","",B8-N(B7))
9	2	51,308.87	30,631.59
10	3	94,209.41	42,900.54
11	4	151,012.70	56,803.29
12	5	222,117.97	71,105.28
13	6	306,266.78	84,148.81
14	7	400,414.98	94,148.20
15	8	500,000.00	99,585.02
16	9	599,585.02	99,585.02
17	10	693,733.22	94,148.20
18	11	777,882.03	84,148.81
19	12	848,987.30	71,105.28
20	13	905,790.59	56,803.29
21	14	948,691.13	42,900.54
22	15	979,322.72	30,631.59
23	16	1,000,000.00	20,677.28

<tbody>
</tbody>

The formula in B8 is:

=IF(A8="","",$B$1*(NORMDIST(A8,$B$3,$B$4,1)-NORMSDIST(-$B$5))
  /(NORMSDIST($B$5)-NORMSDIST(-$B$5)))

Change the "max Z" in B5. Alternatively, use Goal Seek or Solver to determine the "max Z" for a specified value in the first (and last) month.


-----
[1] https://app.box.com/s/z08ajh3hz1jo8cm86fgjq7ks1qqleti6

 

[singcbl]

joeu2004,
Your suggestion works well for me as I am creating cashflow projection which need only be reasonably true in form. Thank you very much for all your help.

 

[singcbl]

I am coming back to this thread again because I like to find another solution regarding the cashflow issue I am facing. Thanks to Joeu2014 suggestion I have a workable solution but I would to know if this solution can be further simplify in that instead of having to 2 formulas in this suggest I would very much like to have just a formula for the monthly cashflow amount. The accumulative cashflow can be easily generated. Thank you once again for all your help.