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!!!

 

[CA_Punit]

In the picture you have posted the Inflow is in 5th Month. Should that be in 4th Month???

Please post the expected result manually so that it can help

 

[diarrheaplanet]

Hi, Thanks for taking a look!

No, because the payment is made the next month immediately after the quarter is finished

 

[diarrheaplanet]

Please take into consideration that the sequence in that picture is starting in month 2, therefore that quarter consists of months 2,3,4 and the payment is received in month 5

the model is not supposed to work on calendar quarters, but to be dynamic based on when sales start

 

[CA_Punit]

Is this what you want

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O
4		Year	2020
5		Date	01-08-2020	01-09-2020	01-10-2020	01-11-2020	01-12-2020	01-01-2021	01-02-2021	01-03-2021	01-04-2021
6		Month	1	2	3	4	5	6	7	8	9	10
7
8		0	0	1000	1100	1210	1331	0	1611	1772	1949	1211
9
10							3310					5332
11
12
Sheet1

Cell Formulas
Range	Formula
E10:N10	E10	=IF(MOD(COUNTIF($D$8:D8,"<>"&0),4)=3,SUM(INDEX($D$8:D8,0,AGGREGATE(14,6,COLUMN($D$8:D8)-COLUMN($D$8)+1,{1,2,3}))),"")

 

[CA_Punit]

I doubt as to what you require as you are ignoring the Value in Month 5 in calculating the Value in Month 10.

 

[diarrheaplanet]

Hi!

I'm sorry, I noticed I had some errors on my previous image, so here is a sequence that I think better explains it...

This is the end result:

What is happening is the following:

And so forth...

Basically what I want is a formula for the line Actual Inflow that I can roll forward and allows me to, once a quarter of payments has happened, to receive the inflow of that previous quarter on the following month... The formula needs to be able to adapt to the fact that the project start date, which is an input, can change, and therefore, it adjusts correspondingly.

I almost found a way to do it using a flag line based on the month intervals + sum ifs, but worths for the first couple of quarters and then doesn't fit in well anymore!

Thank you so much for this help!!!

 

[diarrheaplanet]

So actually, I tried your formula, and it almost does it! It does adapt perfectly to starting based on then the payments start, but when I try it, instead of adding the three previous months like your example does, mine just brings back the last number. This is the formula and picture below

=IF(MOD(COUNTIF($D$8:D8,"<>"&0),4)=3,SUM(INDEX($D$8:D8,0,AGGREGATE(14,6,COLUMN($D$8:D8)-COLUMN($D$8)+1,{1,2,3}))),"")

I'd really love to understand the engineering behind the formula, as I really quite don't get how you're building it!

Thanks a lot

 

[CA_Punit]

Enter the formula with control +shift+enter and not just enter..

I would love to help you but today it's already 4 am in the morning so have to go to bed. Will ping you back

 

[diarrheaplanet]

Thanks, you're a legend!!!

Unfortunately, when I do that to enter it as an array formula, all the cells just end up blank.

Thanks for the help, get some rest and looking forward to hearing from you tomorrow!