S Curve / Bell Curve / Burn Rate Help

[mcdavison1]

I am looking to create a burn table for some new projects in our future pipeline. I have researched S curves, bell curves, and burn rate tables, but nothing is coming up with just what I need. For example:

Project X is going to be for $80M, and will last 72 months
Project Y is going to be for $20M, and will last 24 months
Project Z is going to be for $5M, and will last 6 months

I am looking to figure how much Revenue we will burn in month 1, 2, 3, .... 72.... for each of our projects. We want to have a ramp up period, and a ramp down period. A project with 6 months is obviously going to burn at a different rate than one that will last 72 months, and I am looking to create a table that I can reference, based on the varying projects' duration. We have about 100 different projects, with different duration periods. My problem with the Bell curve, was that I needed to calc a different standard deviation for each duration period, and was really struggling with it.

Any ideas on how I can create myself a table with burn schedules out for 72 (maybe more) months? The duration will be down left, and the months across the top, I think like this below. For example, a 6 month project will burn 5% in the first month, 15% in the second month, and so on til the final 6th month. Per below:

[TABLE="width: 708"]
<colgroup><col><col span="9"></colgroup><tbody>[TR]
[TD]Duration [/TD]
[TD]1 Month[/TD]
[TD]2 Month[/TD]
[TD]3 Month[/TD]
[TD]4 Month[/TD]
[TD]5 Month[/TD]
[TD]6 Month[/TD]
[TD]7 Month[/TD]
[TD]8 Month[/TD]
[TD]9 Month[/TD]
[/TR]
[TR]
[TD]0 Month[/TD]
[TD="align: right"]0.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1 Month[/TD]
[TD="align: right"]100.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2 Month[/TD]
[TD="align: right"]50.00%[/TD]
[TD="align: right"]50.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3 Month[/TD]
[TD="align: right"]25.00%[/TD]
[TD="align: right"]40.00%[/TD]
[TD="align: right"]35.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4 Month[/TD]
[TD="align: right"]10.00%[/TD]
[TD="align: right"]30.00%[/TD]
[TD="align: right"]35.00%[/TD]
[TD="align: right"]25.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5 Month[/TD]
[TD="align: right"]10.00%[/TD]
[TD="align: right"]20.00%[/TD]
[TD="align: right"]25.00%[/TD]
[TD="align: right"]30.00%[/TD]
[TD="align: right"]15.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6 Month[/TD]
[TD="align: right"]5.00%[/TD]
[TD="align: right"]15.00%[/TD]
[TD="align: right"]20.00%[/TD]
[TD="align: right"]25.00%[/TD]
[TD="align: right"]25.00%[/TD]
[TD="align: right"]10.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7 Month[/TD]
[TD="align: right"]4.00%[/TD]
[TD="align: right"]12.00%[/TD]
[TD="align: right"]17.00%[/TD]
[TD="align: right"]20.00%[/TD]
[TD="align: right"]20.00%[/TD]
[TD="align: right"]15.00%[/TD]
[TD="align: right"]12.00%[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8 Month[/TD]
[TD="align: right"]3.00%[/TD]
[TD="align: right"]7.00%[/TD]
[TD="align: right"]11.00%[/TD]
[TD="align: right"]12.00%[/TD]
[TD="align: right"]20.00%[/TD]
[TD="align: right"]20.00%[/TD]
[TD="align: right"]17.00%[/TD]
[TD="align: right"]10.00%[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

Welcome to the board.

The workbook at http://www.box.net/shared/ipkvdyc4bt spreads stuff (cost, labor hours, ...) over the period of performance using NASA's Beta polynomial. The two parameters A and B control the shape of the curve.