Bell Curve
On Feb 13, 4:48 pm, RLind wrote:
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.
|