Automatic update to cashflow spread sheet
 

I am creating a five year cash flow table. Months from Jan 06 to Jan 10 will
be along the top and long running construction projects will be listed on the
left hand side.

The variables associated with each project are

Cost
Commencement date
Duration in months

I would like the Cashflow to spread the cost (on a straight line basis) over
the duration, with the first months expenditure in line with the
commencement date for each project and update automatically when the
variables change each month.

Can anybody help me?


Many thanks.

Hi

The way I would tackle this is to set up headings starting in A1 of
Start, Duration, End, Cost, Project
then in cell F1 enter Jan-05
Use the fill handle (hover over the bottom corner of the cell until he
cursor changes to a small cross, hold the left mouse button down and drag
across through cells G1:BY1 which will fill in Feb-05, Mar-)5 etc.

In cell A1 enter the start date for first project as say Jun-05
In B2 enter project duration, say 15 months
In cell C2 enter =DATE(YEAR(A2),MONTH(A2)+B2-1,DAY(A2))
In cell D2 enter the total project cost, say 200,000
In cell E2 give the project a name.

In F2 =IF(AND(F$1=$A2,F$1<=$C2),$D2/$B2,"")
Copy across through G2:BY2
Copy F2:BY2 and paste through F3:F100 or as far as you need to go.


Regards

Roger Govier


Struggling of Essex wrote:
I am creating a five year cash flow table. Months from Jan 06 to Jan 10 will
be along the top and long running construction projects will be listed on the
left hand side.

The variables associated with each project are

Cost
Commencement date
Duration in months

I would like the Cashflow to spread the cost (on a straight line basis) over
the duration, with the first months expenditure in line with the
commencement date for each project and update automatically when the
variables change each month.

Can anybody help me?


Many thanks.