Hlookup and push out based on Payment Terms

[ThatExcelLife]

Hey guys

Got a little trickier one. What I have is a weekly cash flow statement that I am attempting to project when we will receive cash payments on jobs invoiced. I have two tabs: the main CF tab and the invoice tab (see below). What I want to be able to do is for a given week, is to push out the cash flows based on the payments terms. For example, on the invoice tab, take week 25. The amount invoiced is 23. With a 120 payment term (say 16 weeks), I would want, on the main tab, for week 41 (25 + 16) to show the 23. I just dont know if I can possibly model a formula for each week on the main tab so it can look up all the relevant cash flows based on the invoice tab.


Ultimately, I want to just paste in a worksheet on the invoice tab and voila! the main tab does its thing. I just dont know if there is any other way versus have to create an intermediary sheet that will condense the data even further to make it workable.




[TABLE="width: 506"]
<tbody>[TR]
[TD]Purchase Order[/TD]
[TD]Payment Terms[/TD]
[TD]Week #[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]232[/TD]
[TD]Net 120 Days EOM[/TD]
[TD][/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]244[/TD]
[TD]Net 120 Days EOM[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]555[/TD]
[TD]Net 120 Days EOM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]44[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aryatect]

Hi,

Are looking for something like below? I have identified weeks to be added by 120/7:

Book1
	A	B	C	D	E	F	G
1	Purchase Order	Payment Terms	Week #	25	26	27	28
2	232	Net 120 Days EOM		23
3	244	Net 120 Days EOM			22
4	555	Net 120 Days EOM				44
CF

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Purchase Order/Week #	40	41	42	43	44	45	46	47	48	49	50	51	52
2	232	0	0	23	0	0	0	0	0	0	0	0	0	0
3	244	0	0	0	22	0	0	0	0	0	0	0	0	0
4	555	0	0	0	0	44	0	0	0	0	0	0	0	0
Main

Cell Formulas
Range	Formula
D2	=IFERROR(INDEX(CF!$D$2:$G$4,MATCH(Main!$A2,CF!$A$2:$A$4,0),MATCH(D$1-ROUNDDOWN(120/7,0),CF!$D$1:$G$1,0)),0)

 

[ThatExcelLife]

OH thanks for the reply and help, @Aryatect !

The use of index and match is something I am not familiar with so thanks for the help.

If I could add some complexity to the issue? I want to be able to change the lag of cash by "Payment Terms". My example above only had "Net 120 Days EOM" which was my mistake. I should of included other options, such as "Net 90 Days EOM", etc. See below for the edited version. Essentially, each invoice will have different payment terms, so 120 would be 16 weeks, 90 would be 12 weeks, etc. In the case below, Week 25 ($23) is pushed to week 41 and Week 26 ($33) is pushed to Week 38. Further to complicate it all, some are USD so will be converted to CAD at some FX rate (hardcoded on the Main tab).

Sorry to add to the original query! The more I attempt to find a solution the more complex it seems to get.

Finally, I am trying to get away from needing to show all invoice numbers on the main tab. what I would like to get to is just the total CF per week. That being said, I can easily work with leveraging the invoice number as you have used and creating a intermediary tab to sum up all cash flows, then its a simple pull into the main tab.


[TABLE="width: 637"]
<colgroup><col span="2"><col><col span="5"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]Purchase Order[/TD]
[TD]Currency[/TD]
[TD]Payment Terms[/TD]
[TD]week#[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]232[/TD]
[TD]USD[/TD]
[TD]Net 120 days EOM[/TD]
[TD][/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]244[/TD]
[TD]CAD[/TD]
[TD]Net 90 days EOM[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]555[/TD]
[TD]CAD[/TD]
[TD]Net 30 days EOM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks again, @Aryatect !

 

[Aryatect]

Hi,

Sure, I think below changes should do the trick:

Book1
	A	B	C	D	E	F	G
1	Purchase Order	Payment Terms	Week #	25	26	27	28
2	232	Net 120 Days EOM		23
3	244	Net 130 Days EOM			22
4	555	Net 150 Days EOM				44
CF

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Purchase Order/Week #	40	41	42	43	44	45	46	47	48	49	50	51	52
2	232	0	0	23	0	0	0	0	0	0	0	0	0	0
3	244	0	0	0	0	22	0	0	0	0	0	0	0	0
4	555	0	0	0	0	0	0	0	0	44	0	0	0	0
Main

Cell Formulas
Range	Formula
D2	=IFERROR(INDEX(CF!$D$2:$G$4,MATCH(Main!$A2,CF!$A$2:$A$4,0),MATCH(D$1-ROUNDDOWN((MID(INDEX(CF!$B$2:$B$4,MATCH($A2,CF!$A$2:$A$4,0)),5,SEARCH(" ",INDEX(CF!$B$2:$B$4,MATCH($A2,CF!$A$2:$A$4,0)),5)-5)*1)/7,0),CF!$D$1:$G$1,0)),0)

Happy to help

 

[ThatExcelLife]

Oh my! This is such a great formula! Honestly I learn so much from such solutions. It's incredible what endless possibilities exist in Excel.

Thanks a million times over, @Aryatect !!!

 

[Aryatect]

Happy to help