Quarterly cash flow modeling -> Every fourth month bring back inflow from previous quarter

[diarrheaplanet]

Hi guys,

Hope all is good!

I've been busting my head for a cash flow modeling scenario, but I can't figure out a way around it.

Basically, what I'm trying to do, is model a quarterly cash flow that happens the month after that quarter has finished.

I think an example explains it better:

If a client starts using my service for month 1 (USD 50), month 2 (USD 50) and month 3 (USD 50), I will get the real inflow from those three months (USD 150) on month 4... And then on month 7 the cash flow from the fees from month 4,5 and 6... And so on.

Basically, the real cash flow would havw to look like:

Month 1: No inflow
Month 2: No inflow
Month 3: No inflow
Month 4: Inflow from 1,2,3,
Month 5: No inflow
Month 6: No inflow
Month 7: Inflow from 4,5,6

Also, I'm trying to model this to go alongside the date that the payment from the client starts, as one of the inputs of the model is that revenue starts based on an certain date.

Tried a few things using offset and substracting past months, but not getting even close to it.

Would anybody have any suggestions?
Would really appreciate the help!!!

 

[diarrheaplanet]

For reference, here it is when I try to paste it as an array

 

[CA_Punit]

Please find the attached Sheet

Aggregrate.xlsx

Microsoft Excel Workbook

1drv.ms

And please share it it solve your requirement

 

[diarrheaplanet]

See next post only, had to edit this one

 

[diarrheaplanet]

Managed to use the XL2BB to post my real code, which you'll find below. The row Im trying to solve is G223

20200827 - FORECAST DETAIL (V4).xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
216	Client
217	Kg Processed					-	-	-	-	-	-	1,000	1,100	1,210	1,331	1,464	1,611	1,772	1,949	2,144	2,358	2,594	2,853	3,138	3,452	3,797	4,000	4,000	4,000	4,000	4,000	4,000	4,000	4,000	4,000
218	Total Fees					-	-	-	-	-	-	15,000	16,500	18,150	19,965	21,962	24,158	26,573	29,231	32,154	35,369	38,906	42,797	47,076	51,784	56,962	60,000	60,000	60,000	60,000	60,000	60,000	60,000	60,000	60,000
219	Pre-payment amortization				300,000	300,000	300,000	300,000	300,000	300,000	300,000	290,000	279,000	266,900	253,590	238,949	222,844	205,128	185,641	164,205	140,626	114,688	86,157	54,773	20,250	-	-	-	-	-	-	-	-	-	-
220
221	Amortization Fees					-	-	-	-	-	-	10,000	11,000	12,100	13,310	14,641	16,105	17,716	19,487	21,436	23,579	25,937	28,531	31,384	34,523	37,975	40,000	40,000	40,000	40,000	40,000	40,000	40,000	40,000	40,000
222	Fees to					-	-	-	-	-	-	5,000	5,500	6,050	6,655	7,321	8,053	8,858	9,744	10,718	11,790	12,969	14,266	15,692	17,261	36,712	60,000	60,000	60,000	60,000	60,000	60,000	60,000	60,000	60,000
223	Fees cash flow
Forecast

Cell Formulas
Range	Formula
G217:AJ217	G217	=IF(G$10=$F$195,$C$195,IF(G$10>$F$195,MIN(F217*(1+$D$195),$C$190),0))
G218:AJ218	G218	=G217*$E$195
F219	F219	=VLOOKUP($B216,$B$174:$C$185,2,)
G219:AJ219	G219	=IF(F219-G221>0,F219-G221,IF(F219-G221<0,0))
G221:AJ221	G221	=G218-(G217*5)
G222:AJ222	G222	=IF(G219>0,G217*5,IF(G219=0,G218-F219))
G223:AJ223	G223	=IF(ISNUMBER(MATCH(COUNTIF($G$218:G218,">0"),ROW($A$1:$A$31)*4,0)),SUM(INDEX($F$218:F218,0,AGGREGATE(14,6,COLUMN($F$218:F218)-COLUMN($F$218)+1,{1,2,3}))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[CA_Punit]

i am confused with your requirement.

In the earlier post you had layout which was completely different and now you have changed it.
If you see the post 14, from where i should start the calculation. is it from F223 or P223.

 

[diarrheaplanet]

Hi Punit! Actually found a super easy way to do it on my own.

Thank you so much for your support anyway!

Take care