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

#### diarrheaplanet

##### New Member
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!!!

#### Attachments

• Screen Shot 2020-08-27 at 18.28.31.png
64.6 KB · Views: 7

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### CA_Punit

##### Well-known Member
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

##### New Member
Hi, Thanks for taking a look!

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

#### diarrheaplanet

##### New Member
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

##### Well-known Member

Is this what you want

Book1
CDEFGHIJKLMNO
4Year2020
5Date01-08-202001-09-202001-10-202001-11-202001-12-202001-01-202101-02-202101-03-202101-04-2021
6Month12345678910
7
800100011001210133101611177219491211
9
10    3310    5332
11
12
Sheet1
Cell Formulas
RangeFormula
E10:N10E10=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

##### Well-known Member
I doubt as to what you require as you are ignoring the Value in Month 5 in calculating the Value in Month 10.

#### diarrheaplanet

##### New Member

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

##### New Member
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

#### Attachments

• Screen Shot 2020-08-27 at 23.43.08.png
84.7 KB · Views: 1

#### CA_Punit

##### Well-known Member
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

##### New Member
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!

Replies
0
Views
330
Replies
1
Views
173
Replies
0
Views
285
Replies
2
Views
516
Replies
5
Views
5K