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

diarrheaplanet

New Member
Joined
Jun 26, 2016
Messages
13
For reference, here it is when I try to paste it as an array

Screen Shot 2020-08-28 at 9.28.58.png
 

Attachments

  • Screen Shot 2020-08-28 at 9.27.03.png
    Screen Shot 2020-08-28 at 9.27.03.png
    129.7 KB · Views: 0

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
Please find the attached Sheet


And please share it it solve your requirement
 

diarrheaplanet

New Member
Joined
Jun 26, 2016
Messages
13
Managed to use the XL2BB to post my real code, which you'll find below. The row Im trying to solve is G223

Cell Formulas
RangeFormula
G217:AJ217G217=IF(G$10=$F$195,$C$195,IF(G$10>$F$195,MIN(F217*(1+$D$195),$C$190),0))
G218:AJ218G218=G217*$E$195
F219F219=VLOOKUP($B216,$B$174:$C$185,2,)
G219:AJ219G219=IF(F219-G221>0,F219-G221,IF(F219-G221<0,0))
G221:AJ221G221=G218-(G217*5)
G222:AJ222G222=IF(G219>0,G217*5,IF(G219=0,G218-F219))
G223:AJ223G223=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

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

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