Hi, I would like to find a way of more easily doing the following:-

To put it simply, say project X is made up of 10 cost items, lets say
the total forecast value of the project is £100, and each of the line
items represents a forecast of £10. The project is set to run for 10
months, and line item 1 has forecast costs of £10 in month one, line
item 2 has forecasts costs of £10 in month two... and so on, down to
line item 10 which has forecast costs of £10 in month 10.

Another way of stating the above would be to say that line item 1 has
forecast costs falling into the period 0-10% of the total project time
frame, and line item 10 has forecast costs falling into the period
90-100% of the total project time frame.

What I want to know is, that if I then have another project of a very
similar nature (say project Y), that has the same total forecast value
of £100, but it being run over a period of 20 months, what way can I
use excel to adapt my forecast from 10 mths to 20 mths (clearly here I
have just multiplied my project period by 2, but it could just as
easily be by 3, 1.5, 1.7 etc etc)

In the scenario of project Y, line item 1 representing £10 of forecast
costs would still fall into the first 10% of the project, but now,
instead of the total cost falling into month one, it would have to be
split over the first two months - 50% in each

Of course this is a very simplistic version of the forecasts we really
do, but I thought if I should start simple!!!

What I really want to do is build a model where I can plug in my new
forecast value and tell it the total time period the project is running
over, and it will then indicate to me in what months (or weeks) that
cost will fall (assuming that the project conforms to the usual
"model").

Any help would be very much appreciated.

Thanks in advance :)

Laura

Hi Laura:

Row 1 will be the baseline project
Row 2 will be the forecasted project

Set A1= 18-Jan-07 (start date)
Set B1 = 18-Nov-07 (end date)
Set C1 = 100 (baseline total cost)
Set D1 = DATEDIF(A1,B1,"m") (total months in baseline project)
Set E1 = C1/D1 (baseline cost per month)

now the forecast row

Set A2 = 18-Jan-07 (start date)
Set B2 = 18-Sep-08 (end date)
Set C2 = C1 (forecast total cost same as above)
Set D2 = DATEDIF(A2,B2,"m") (total months in forecast project)
Set E2 = C2/D2 (forecast cost per month)

--
Gary''s Student


"Laura" wrote:

Hi, I would like to find a way of more easily doing the following:-

To put it simply, say project X is made up of 10 cost items, lets say
the total forecast value of the project is £100, and each of the line
items represents a forecast of £10. The project is set to run for 10
months, and line item 1 has forecast costs of £10 in month one, line
item 2 has forecasts costs of £10 in month two... and so on, down to
line item 10 which has forecast costs of £10 in month 10.

Another way of stating the above would be to say that line item 1 has
forecast costs falling into the period 0-10% of the total project time
frame, and line item 10 has forecast costs falling into the period
90-100% of the total project time frame.

What I want to know is, that if I then have another project of a very
similar nature (say project Y), that has the same total forecast value
of £100, but it being run over a period of 20 months, what way can I
use excel to adapt my forecast from 10 mths to 20 mths (clearly here I
have just multiplied my project period by 2, but it could just as
easily be by 3, 1.5, 1.7 etc etc)

In the scenario of project Y, line item 1 representing £10 of forecast
costs would still fall into the first 10% of the project, but now,
instead of the total cost falling into month one, it would have to be
split over the first two months - 50% in each

Of course this is a very simplistic version of the forecasts we really
do, but I thought if I should start simple!!!

What I really want to do is build a model where I can plug in my new
forecast value and tell it the total time period the project is running
over, and it will then indicate to me in what months (or weeks) that
cost will fall (assuming that the project conforms to the usual
"model").

Any help would be very much appreciated.

Thanks in advance :)

Laura

Hi Gary's Student, thanks so much for taking the time to reply.. I have
reveiwed your suggestion, but I am not sure it is entirely what I am
looking for, as the costs are not split evenly over the job, we build
houses, so we have some costs falling at the beginning, and some at the
end of a project, so I cannot just average the total costs over the
total months (I hope that makes sense!)

Any further suggestions would be welcome.

Kind regards

Laura :)


Gary''s Student wrote:
Hi Laura:

Row 1 will be the baseline project
Row 2 will be the forecasted project

Set A1= 18-Jan-07 (start date)
Set B1 = 18-Nov-07 (end date)
Set C1 = 100 (baseline total cost)
Set D1 = DATEDIF(A1,B1,"m") (total months in baseline project)
Set E1 = C1/D1 (baseline cost per month)

now the forecast row

Set A2 = 18-Jan-07 (start date)
Set B2 = 18-Sep-08 (end date)
Set C2 = C1 (forecast total cost same as above)
Set D2 = DATEDIF(A2,B2,"m") (total months in forecast project)
Set E2 = C2/D2 (forecast cost per month)

--
Gary''s Student


"Laura" wrote:

Hi, I would like to find a way of more easily doing the following:-

To put it simply, say project X is made up of 10 cost items, lets say
the total forecast value of the project is £100, and each of the line
items represents a forecast of £10. The project is set to run for 10
months, and line item 1 has forecast costs of £10 in month one, line
item 2 has forecasts costs of £10 in month two... and so on, down to
line item 10 which has forecast costs of £10 in month 10.

Another way of stating the above would be to say that line item 1 has
forecast costs falling into the period 0-10% of the total project time
frame, and line item 10 has forecast costs falling into the period
90-100% of the total project time frame.

What I want to know is, that if I then have another project of a very
similar nature (say project Y), that has the same total forecast value
of £100, but it being run over a period of 20 months, what way can I
use excel to adapt my forecast from 10 mths to 20 mths (clearly here I
have just multiplied my project period by 2, but it could just as
easily be by 3, 1.5, 1.7 etc etc)

In the scenario of project Y, line item 1 representing £10 of forecast
costs would still fall into the first 10% of the project, but now,
instead of the total cost falling into month one, it would have to be
split over the first two months - 50% in each

Of course this is a very simplistic version of the forecasts we really
do, but I thought if I should start simple!!!

What I really want to do is build a model where I can plug in my new
forecast value and tell it the total time period the project is running
over, and it will then indicate to me in what months (or weeks) that
cost will fall (assuming that the project conforms to the usual
"model").

Any help would be very much appreciated.

Thanks in advance :)

Laura