Bell Curve

[RLind]

Hello -
I have a project that will cost $10MM to build over 12 months. I would like
to spread the costs out in a bell curve shape. For example, I would like
something similar to the following costs...
Month 1 - $100,000
Month 2 - $150,000
Month 3 - $225,000
.....
Month 7 - $2,000,000
Month 8 - $3,000,000
....
Month 11 - $150,000
Month 12 - $100,000

Is there a formula I can use where I can input the total costs and the time
period to allocate so that it will spread my costs out over that time period
in a Bell Curve fashion?

Thanks for your help!

 

[Mike Middleton]

RLind -

Since the normal distribution (bell curve) theoretically goes from minus
infinity to plus infinity, you have to make a choice about where to start
the twelve intervals on the X axis. Then you can use the NORMSDIST worksheet
function to get the cumulative probability for each of the twelve intervals,
subtract to get the probability in each interval, and then multiply each
probability by the total cost. The probabilities for months one thru six are
the same as the probabilities for months twelve thru seven.

If I start at Z = -2.5 and use steps of 0.5, the probabilities are
0.0062097
0.0165405
0.0440571
0.0918481
0.1498823
0.1914625

If I start at Z = -2.0833 = 25/12 and use steps of 0.4167 = 5/12, the
probabilities are
0.0186104
0.0291799
0.0578594
0.0966786
0.1361327
0.1615389

In general, to use this approach, the steps are one-fifth of the negative
starting value of Z.

- Mike
http://www.mikemiddleton.com

 

[RLind]

Thanks, but can you please write out a sample formula...assuming $10MM over
12 months...once I see the formula and use it I will be able to better
understand the interaction...Thanks!

 

[joeu2004]

I have a project that will cost $10MM to build over 12 months. I would like
to spread the costs out in a bell curve shape. For example, I would like
something similar to the following costs...
Month 1 - $100,000

First, decide what percentage you want at the end-points. In your
example, it is 1% (100K / 10M). Then, if your total amount (10M) is
in A1 and month numbers are in A2:A13, the amounts for each month can
be determined with the following formulas in B2:B13:

B2: =$A$1*(NORMSDIST(NORMSINV(1%)*(1 - (A2-1)*2/10)))
B3: =$A$1*(NORMSDIST(NORMSINV(1%)*(1 - (A2-1)*2/10))) - SUM($B$2:B2)
Copy B3 into B4:B12
B13: =$A$1 - SUM(B2:B12)

Note that the divisor "10" is derived from 12 - 2. If you spread the
costs over 24 months, the divisor would be 22 (24 - 2).

Refinement: You might want to round each of the formulas (except
B13). For example, ROUND($A$1*(...)-SUM(...),-3) rounds everything to
$1K.