Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
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:
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to spread the cost of the project overtime using excelformula?
|
#8
|
|||
|
|||
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating bell curve in excel | Charts and Charting in Excel | |||
Bell Curve | Excel Discussion (Misc queries) | |||
Trend Line - Bell Curve | Excel Discussion (Misc queries) | |||
Creating Bell Curve Chart | Charts and Charting in Excel | |||
Fit bell curve to histogram | Charts and Charting in Excel |