Hlookup and push out based on Payment Terms

ThatExcelLife

New Member
Joined
Nov 2, 2017
Messages
18
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.




Purchase OrderPayment TermsWeek #25262728
232Net 120 Days EOM23
244Net 120 Days EOM22
555Net 120 Days EOM44

<tbody>
</tbody>
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hi,

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


Book1
ABCDEFG
1Purchase OrderPayment TermsWeek #25262728
2232Net 120 Days EOM23
3244Net 120 Days EOM22
4555Net 120 Days EOM44
CF



Book1
ABCDEFGHIJKLMN
1Purchase Order/Week #40414243444546474849505152
223200230000000000
324400022000000000
455500004400000000
Main
Cell Formulas
RangeFormula
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)
 
Upvote 0
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.


ABCDEFGH
Purchase OrderCurrencyPayment Termsweek#25262728
232USDNet 120 days EOM23
244CADNet 90 days EOM33
555CADNet 30 days EOM24

<colgroup><col span="2"><col><col span="5"></colgroup><tbody>
</tbody>


Thanks again, @Aryatect !
 
Upvote 0
Hi,

Sure, I think below changes should do the trick:


Book1
ABCDEFG
1Purchase OrderPayment TermsWeek #25262728
2232Net 120 Days EOM23
3244Net 130 Days EOM22
4555Net 150 Days EOM44
CF



Book1
ABCDEFGHIJKLMN
1Purchase Order/Week #40414243444546474849505152
223200230000000000
324400002200000000
455500000000440000
Main
Cell Formulas
RangeFormula
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 :)
 
Last edited:
Upvote 0
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 !!!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top