Allocation of values by time

[ksn]

I would like to take the value of a project and divide it value by the number
of months it takes to construct. Then I would like to be able to take those
values and place in a table where the first entry is the month the project
starts in. There are multiple projects and multiple starting months.

Project Value Start Date Duration (months)

1 $500,000 1/10/2010 60
2 $400,000 2/25/2011 32
3 $900,000 8/15/2010 45

The goal is to be able to develop a cash flow by month over the duration of
all projects. I cannot seem to find any threads that deal with this idea.
The price, start date and duration may change periodically so I don't want to
keep manually reallocating the price.

Project Month 1 Month 2 Month 3 Month 4 Month 5 .....
Month x

1 10 10 10 10
2 30 30
30 30
3 20 20 20
20


Total 10 30 60 60
50 30

 

[RaulDR]

hi ksn

from what i understand to your post it seems that you want to make a monthly
forecast of cash needed to complete a project. you need additional data such
as actual monthly expenses which you can use as assumption to new projects.

 

[Roger Govier]

Hi

Assuming Project in Column A, Value in B, Start Date in C, Duration in D.
Leave column E blank
In F1 enter 01/10/2010
In G1 enter
=DATE(YEAR(F1),MONTH(F1)+1,1)
Copy across sheet as far as required.
Format row 1, Format>Cells>Number>Custom> mmm yy

In cell F2 enter
=IF($B2="","",
IF(COUNT($E2:E2)>$D2,"",
IF(TEXT(F$1,"yymm")>=TEXT($C2,"yymm"),$B2/$D2,"")))
Copy across and down as required

 

[ksn]

Thanks, I will give this a try.