see file uploaded with example / info.
I have a number of buyers that buy from different suppliers/brands.
Each supplier/brand offers different buying terms (P,Q,R)
I want to track total order value $ as well as understand when the invoices need to be paid (in the future). Since each of the supplier offer different payment terms I need my buyers to input the following:
- total order value / amount Col: I
- order values for delivery slots 1-3 Col: J,K,L
- date of delivery slots (there are up to 3 possible delivery dates or slots) Col: M,N,O
- payment terms in days (from delivery date) Col: P,Q,R
I then need to see a report or summary per month or week showing the total expected $ amounts to be paid
In my example above I calculate my "payment due date" (Col: S,T,U) from the two fields "expected delivery date + payment terms in day".
The $ amount to be paid at this date resp. in this month is already shown in Col J,K,L
Where I struggle is to transfort this data into a report or even graph. I should be able to immediatly filter / tell what $ amounts are due to be paid in Feb, March, April etc...
I need this as a full year or even ongoing table for the month / years to come.
Feel free to build a totally different excel, this one is just meant to illustrate which data I have and which inputs I need to make.
Many thanks - Marc