Position Input data in Cash Flow table

[Scotty]

I hope I can explain myself clearly!!

I am setting up a worksheet to enable the construction of a cash flow chart
relating to property development. I have an input section where I can select
the month in which an activity will commence, how long it will run for and
when it will end. I can also select the months in which payments should be
made, as a percentage of the total payments.

For example, I might input that the construction stage commences in month 3
and run for 10 months. I am struggling to get my mind around how to set up
worksheet so that the relevant costs for construction are shown in the cash
flow as commencing in month 3 and as continuing for 10 months.

I also wish to show costs as being apportioned at stages along the 10 month
period again as per data entered into input section.

I really hope this makes sense. Can anybody give me any advice as to how I
might be able to link the input data with the Cash Flow as per my rough
explanation above?

Many thanks.

 

[Matthew Herbert]

I hope I can explain myself clearly!!

I am setting up a worksheet to enable the construction of a cash flow chart
relating to property development. I have an input section where I can select
the month in which an activity will commence, how long it will run for and
when it will end. I can also select the months in which payments should be
made, as a percentage of the total payments.

For example, I might input that the construction stage commences in month3
and run for 10 months. I am struggling to get my mind around how to set up
worksheet so that the relevant costs for construction are shown in the cash
flow as commencing in month 3 and as continuing for 10 months.

I also wish to show costs as being apportioned at stages along the 10 month
period again as per data entered into input section.

I really hope this makes sense. Can anybody give me any advice as to how I
might be able to link the input data with the Cash Flow as per my rough
explanation above?

Many thanks.

Scotty,

Look into using the OFFSET function and/or some sort of switch. The
switch cell can be a 1 or 0 (1 if it meets your criteria or 0 if it
does not meet your criteria) and your formula for the Cash Flow can be
=FORMULA * SWITCH. You could also potentially have a cell with the
date, another cell to test if the date is equal to three months from
the date (i.e. your switch of 1 or 0), and then the cell with your
formula multiplied by the switch.

Best,

Matthew Herbert

 

[Scotty]

Matthew,

many thanks for replying so quickly. Much appreciated. I wasn't sure if
anyone would understand what I was babbling about.

I will use the suggested method and I am sure this will get me off the hook.

Thanks again!!

Cheers....Scotty