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.

 

[Xiangwu]

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).

Thank you so much for the reply. That's an excellent model, and I learned a lot from it.

 

[shg]

You're welcome.

 

[toddevan]

I was looking for ways to distribute a bell curve data set across a variable time frame and found this post. Really impressive! It does exactly what I'm looking for, but when i try to use the BetaPer function in my own excel worksheet, it is not recognizing that formula. Do i need to manually create the BetaPer formula through a script, and add it to my function list? How do i use the BetaPer function outside the file that you create? Thanks for your help!

Todd

 

[Xiangwu]

I was looking for ways to distribute a bell curve data set across a variable time frame and found this post. Really impressive! It does exactly what I'm looking for, but when i try to use the BetaPer function in my own excel worksheet, it is not recognizing that formula. Do i need to manually create the BetaPer formula through a script, and add it to my function list? How do i use the BetaPer function outside the file that you create? Thanks for your help!

Todd

I was impressed by shg's model as well. It's very powerful, and I learned a lot from it. As for the BetaPer, you may need to copy all codes the shg wrote to your spreadsheet. But, don't forget to keep shg's name. He is the original hero. Also, put all codes into a new Module, instead of the single workbook.

 

[ibrahimdaas]

toddevan
I think for the Beta function no way for you to create it in a new Excel sheet ... that one you downloaded here is "Custom built" ... and he said so in his post ,
now .. if you have an amount of cost and you want to distributed that to be front-loaded or rear loaded in a skewed bell-curve you have to know that Excel doesn't appear to support any skew for a normal distribution..
the good news is for a a bell shape curve ..i.e. normal distribution allocation you can use the following for the normal Bill curve distribution for one of the costs :

=-((NORMDIST(F39,Building_Sale/2,Dev,TRUE)-NORMDIST(E39,Building_Sale/2,Dev,TRUE))*Soft_Costs*Factor)


Where :

Soft_Costs, is the amount of the Cost I wanted to distribute

Building_Sale , is a named cell for the month number of selling the Building which is month number 150

F39 & E39, are the month's numbers being the F39 the month number 1

And using a Deviation equals to 100 , and a Factor equals to : = 1 / (1 - 2 * NORMDIST(0, Building_Sale/2, Dev, TRUE) )

 

[Jamal AlMustafa]

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

Hi

Can I have copy of the excel files?
Thanks,