Nested Formulas and automated cells updates

[Mari81]

Hi All,

I am new to Excel so apologies if my question will miss pieces of information or is not clear.

We work within the Construction Industry and make monthly projections of our turnover based on prospect.

BACKGROUND

We assume based on the size of the fee - which is related to the size of the project - that the invoicing will roll for either 12, 18, 24, or 30 months, and assign to each of it a weight:

12 2 3 5 10 15 20 20 10 5 5 3 2
18 2 3 5 8 10 13 13 10 8 5 5 3 3 2 2 2 2 1
24 1 1 1.5 1.5 2.5 2.5 5 5 7.5 7.5 10 10 10 10 5 5 2.5 2.5 2.5 2.5 1.5 1.5 1 1
30 1 1 1.5 1.5 2.5 2.5 5 5 8 9 10 10 10 5 5 5 2 2 2 1.5 1.5 1 1 1 1 1 1 1 1 1


So that for a fee of £45K lasting 12 months we would invoice on the first month: £45*2/100, on the second month £45K*3/100

We have then assumed a starting date for each project and based on the TODAY date if today is the 19/08/19 and the starting date is July-19 we obtain

Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Jan-20 Feb-20 Mar-20

0 900 1350 2250 4500 6750 9000 9000



the formula used being

=IF(AND(SEP-19+1>START DATE, SEP-19>TODAY DATE), £45k*2/100,0)

we assume that Sept must be > than the starting date and > than today date because if the project doesn't start when we assume and it is still a prospect we need Sept to turn 0 and the other months to keep rolling.


PROBLEM


With this formula what I obtain at the end of August is that Sept turns 0 and October still shows 1350. But what I really need is that October reflects this change by assuming that the projection starts back from the beginning of the weighted period, so that when Sept turn 0 Oct turns 900 and Nov 1350.

Is there any way I can add a piece of formula to obtain this result and automatically have my projections up to date? The spreadsheet is attached below.

Thanks all in advance

 

[Snakehips]

You cannot attach a file so detail of what you have/want is not entirely clear to me, nor likely to others, judging by the lack of response.

Does something like the below approach offer any possibilities?

Example is for the 12 month roll out.
Vary the fictitious start date to see how it computes for dates where Today is pre-project, in-project and post-project.

Excel 2010
	A	B	C	D	E
1	Fee	Start Date			12 Stage Fee this month
2	45000	19/10/2019			0
Sheet3

Cell Formulas
Range	Formula
E2	=IFERROR((CHOOSE(DATEDIF(B2,TODAY(),"m"),2,3,5,10,15,20,20,10,5,5,3,2))*A2/100,0)

Excel 2010
	A	B	C	D	E
1	Fee	Start Date			12 Stage Fee this month
2	45000	19/06/2019			1350
Sheet3

<br /

Excel 2010
	A	B	C	D	E
1	Fee	Start Date			12 Stage Fee this month
2	45000	19/07/2018			0
Sheet3