Auto-calculating spend broken across year by dates

[Scott_Hall]

Hey folks! I've been trying to develop a formula on my worksheet to generate trends and spend data for my project management group.

I have the following fields:
Design Budget
Construction Budget
Design Start Date
Design End Date
Const. Start Date
Const. End Date
Design Weight: X%
Const. Weight: Y%

What I would like is that I fill in those values and a chart or graph or list of months or weeks is generated with the proper spend rate, i.e. 15% of the budget spread across the design dates, then 85% of the budget spread across the construction period. There may or may not be a gap between Design and Construction, so it should drop to no spend in between.

Ultimately I was to generate a chart with two lines: one for the budget evenly trended (x dollars per day forming a flat line) + the more accurate spend curve based on the weighting and actual project dates.

Can anyone give me some guidance?

Thank you,
Scott

[Scott_Hall]

No one? It seems like this should be possible; assigning values to specific dates across the calendar seems like many folks would want to have that option

[shg]

There's a workbook that does this using NASA's beta spend curve at https://app.box.com/shared/ipkvdyc4bt

[Scott_Hall]

Wow thanks SHG. That is a great workbook. Somewhat complex but it gives me a lot to get started with.

If you are very familiar with the worksheet, can you tell me why there are either 20 or 60 (days?) in the time period? Shouldn't it 52 or so?

[shg]

Pardon? 20 or 60 days??

[Scott_Hall]

i.e. on the "Spread" or "Example" worksheets, the chart duration is only 19 days...not 30, or 365. The period chart on the "Spread" worksheet has 60 periods on it.

I'm thinking that this should all go towards 365 days or 52 weeks or something correct? So what is it pointed towards by default? The preexisting numbers don't make any sense to me.

[shg]

They are just periods -- they could be days, weeks, months, ..., and there could be any number.