#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default 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!
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 762
Default 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!



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default 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!




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default 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.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,180
Default 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
  #8   Report Post  
Junior Member
 
Posts: 20
Default

Quote:
Originally Posted by Herbert Seidenberg View Post
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).
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating bell curve in excel Lukedug Charts and Charting in Excel 2 April 22nd 23 08:08 AM
Bell Curve Todd Nelson Excel Discussion (Misc queries) 3 December 7th 07 02:36 PM
Trend Line - Bell Curve David Excel Discussion (Misc queries) 1 July 23rd 06 03:34 AM
Creating Bell Curve Chart MikeR-Oz Charts and Charting in Excel 3 February 2nd 06 01:19 PM
Fit bell curve to histogram cwinters Charts and Charting in Excel 1 June 14th 05 02:03 AM


All times are GMT +1. The time now is 11:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"