Cash flow charts
 

I am creating a five year cash flow table in excel. 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?

Hi
I answered this to your original posting on 27/09/05
In case you can't see it, I repeat it below

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 in excel. 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 I'll give it a try

"Roger Govier" wrote:

Hi
I answered this to your original posting on 27/09/05
In case you can't see it, I repeat it below

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 in excel. 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?