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

diarrheaplanet

New Member
Joined
Jun 26, 2016
Messages
13
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
    Screen Shot 2020-08-27 at 18.28.31.png
    64.6 KB · Views: 7

Some videos you may like

Excel Facts

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

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 26, 2016
Messages
13
Hi, Thanks for taking a look!

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

diarrheaplanet

New Member
Joined
Jun 26, 2016
Messages
13
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
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 26, 2016
Messages
13

ADVERTISEMENT

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:

Screen Shot 2020-08-27 at 23.07.14.png


What is happening is the following:

Screen Shot 2020-08-27 at 23.07.48.png


Screen Shot 2020-08-27 at 23.08.02.png


Screen Shot 2020-08-27 at 23.08.24.png


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
Joined
Jun 26, 2016
Messages
13
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}))),"")

Screen Shot 2020-08-27 at 23.44.31.png


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
    Screen Shot 2020-08-27 at 23.43.08.png
    84.7 KB · Views: 1

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 26, 2016
Messages
13
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! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,072
Messages
5,545,822
Members
410,707
Latest member
SanTrapGamer
Top