ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Bell Curve (https://www.excelbanter.com/excel-worksheet-functions/130611-bell-curve.html)

RLind

Bell Curve
 
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!

ExcelBanter AI

Answer: Bell Curve
 
Hello! Yes, there is a formula you can use in Excel to spread your costs out in a bell curve shape. Here are the steps:
  1. Open a new Excel spreadsheet and enter the following headers in row 1: "Month", "Cost", and "Cumulative Cost".
  2. In column A, enter the numbers 1 through 12 to represent the 12 months.
  3. In cell B2, enter the formula
    Formula:

    "=NORM.DIST(A2,7,2,TRUE)*1000000" 

    , which will calculate the cost for each month based on a normal distribution with a mean of 7 months and a standard deviation of 2 months. You can adjust the mean and standard deviation values to change the shape of the curve.
  4. Copy the formula in cell B2 and paste it into cells B3 through B12 to calculate the costs for each month.
  5. In cell C2, enter the formula
    Formula:

    "=SUM(B2:B2)" 

    , which will calculate the cumulative cost for the first month.
  6. Copy the formula in cell C2 and paste it into cells C3 through C12 to calculate the cumulative costs for each month.

Your spreadsheet should now show the costs for each month and the cumulative costs up to that point in time, with a bell curve shape. You can adjust the mean and standard deviation values in the formula to change the shape of the curve as needed.

Mike Middleton

Bell Curve
 
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" wrote in message
...
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!




RLind

Bell Curve
 
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!

"Mike Middleton" wrote:

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" wrote in message
...
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!





joeu2004

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.


dalila hima

how to spread the cost of the project overtime usingexcel formula?
 
Hi,
How to use excel formula to spread the cost of the project knowing that its time vary from 12 months to 48 months and that it does not always follow an s curve? like the following list of data:
total cost of the project :$310135272.2

Month Percentage of Cost
1 0.6708%
2 19.0990%
3 0.0259%
4 0.0259%
5 0.0259%
6 0.0259%
7 1.1473%
8 0.6898%
9 3.5729%
10 2.9887%
11 2.9156%
12 2.3157%
13 28.4282%
14 28.2524%
15 1.0606%
16 1.3427%
17 1.2938%
18 6.1188%
so, if the number of months is 48 how can I spread those % keeping the same type of graph and the same cost of the projects ( slow progress)?
Thanks.


Herbert Seidenberg

how to spread the cost of the project overtime using excelformula?
 
Excel 2007
Curve fit with S curves:
http://www.mediafire.com/file/nnyzdzwjmlj/12_19_08.xlsx

Gizzmo

Quote:

Originally Posted by Herbert Seidenberg (Post 771542)

Was wondering if you would be willing to share a working file of this, as I am trying to work on a similar exercise, but I also want to be able to change the 'spread' and 'skew' of the curve over time (i.e. months).


All times are GMT +1. The time now is 02:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com