Auto Distribution of data over months

[MAZUSM]

Hi fellows,
I have of project activities with three variables. Start Date, End Date and cost estimate of that activity.
I have dates and cost on left hand side of sheet and months on top row for distribution of data.
I want to build a formula so that I can distribute the cost of this activity over months (rounded up duration in month).
At this moment I am talking just straight average distribution but eventually my target is to get that distribution based on s-curve.

For Example Start Date is November 05, 2010 and End Date is February 12, 2011. Cost is 2 million which means that it will be distributed over 4 months @ 500,000 each starting from November EOM.

Can anybody help me in straight distribution over months?
Appreciate your help.

 

[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 curve. The two parameters A and B control the shape of the curve.

 

[MAZUSM]

Hi SHG,
Thanks for your reply but you know it was too sophisticated for me or may be I did not understand it completely.
At this moment I am stuck in the explained data variables and would appreciate anyone's help in building the model.

 

[shg]

nPer in B16 is the numer of periods (months, typically, but could be anything else) in the period of performance. Amount in F21 is the amount of dollars or labor hours (or anything else) to be spread across the period of performance.

A and B are the parameters that control the shape of the spend curve. It can make it relatively level loaded, or front loaded, or back loaded. There are example values in col D, and the values used by Johnson Space Center are in D15 and D16.

F122 and down tells you the amount of dollars or labor speread in each period.

If you want simple level loading by month, see http://www.box.net/shared/7xm2kt408n

 

[MAZUSM]

Wonderful. This is exactly what I wanted. Now, I just need to learn working with ranges. Appreciate your help Shg!

 

[478874]

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 curve. The two parameters A and B control the shape of the curve.

In this particular work book, the named value "frmT" that appears multiple times in the formula "=IF(frmT>1, NA(), 10*frmT^2 * (1 - frmT)^2 * (ptrA + ptrB*frmT) + frmT^4 * (5 - 4*frmT) )" used in the cumulative fraction column is a actually what calculation?

I find this workbook immensely helpful but I can't quite seem to duplicate the results my own and this the main problem I've encounter. TIA

 

[Xiangwu]

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 curve. The two parameters A and B control the shape of the curve.

Hi, shg,

This is an excellent model, and I have the same problem. But, I have another question. How did you calculate and determine alpha and beta because these two parameters determine the shape of the distribution? For example, I put 20 as period, and I chose alpha=0 and beta=1. I assume it is the normal distribution. But, now my situation changes, I dont want normal distribution. How can I change the alpha and beta based on different distributions?

Also, for project management purpose, beta distribution has a(optimistic time), m(most likely time), b(pessimistic time). If I want to apply this to the model, how I can do so?

I know my question may be out of the box (Excel), but I look forward to hearing from you!

Thanks,

Xiangwu

 

[shg]

How did you calculate and determine alpha and beta because these two parameters determine the shape of the distribution?

I didn't calculate the values at all. The combination of the two selects a front-loaded, back-loaded, or relatively flat spend curve over the period of performance. For a given program in the planning stage, I expect they are selected subjectively based on similarity to prior programs of the same type.

For example, I put 20 as period, and I chose alpha=0 and beta=1. I assume it is the normal distribution.

It's not a normal distribution for any set of values, they are all polynomial.

 

[Xiangwu]

I didn't calculate the values at all. The combination of the two selects a front-loaded, back-loaded, or relatively flat spend curve over the period of performance. For a given program in the planning stage, I expect they are selected subjectively based on similarity to prior programs of the same type.

Thank you for your reply. I'm not familiar with the beta distribution. For the front-loaded, back-loaded, and flat spend, are these types general beta distribution shapes? I just try to find the logic behind it. The distribution types can be endless, but why did you choose these three types? When I present this to my manager, I might need to give them one or two objective reasons that these three are commonly used.

Thanks again!

 

[shg]

Did you look at the workbook at the link? I though it was explained pretty well.

NASA looked at cumulative spend curves on programs and decided they could be reasonably fit by a fifth-order polynomial. Then they designed one with just two coefficients that has two simplifying properties:

F(T=0) = 0 (no spending before the project starts)

F(T=1) = 1 (all spending complete when the project ends)

The constraints on A and B (that they each be between 0 and 1, and that their sum be between 0 and 1) adds the further property that the function is monotone increasing from T=0 to T=1 (as any cumulative distribution must be).