how to use pivot tables in excel youtube

Payment 1 would be applied to the remaining balance on invoice 1, leaving a balance of 101.26. The two prepayments would be applied to the invoice 1, leaving a balance of 274.96. If we assume that all payment amounts are always first applied to the oldest open invoice and if all invoices are due on the 30th day after invoicing, (1)how can we find out which invoices still have not been paid in full, (2)what amount still remains open on the invoice, and (3) how would you suggest to calculate the interest amount on the open invoices.ģ-Apr-14 VENDOR-0012 $(38.91) -> prepaymentĤ-Apr-14 VENDOR-0012 $(12.41) -> prepaymentĤ-Apr-14 VENDOR-0012 $326.28 -> invoice 1ĥ-Apr-14 VENDOR-0012 $843.90 -> invoice 2ħ-Apr-14 VENDOR-0012 $38.91 -> invoice 3Ĩ-Apr-14 VENDOR-0012 $144.41 -> invoice 4ĥ-Apr-14 VENDOR-0012 $(173.70) -> 1st paymentħ-Apr-14 VENDOR-0012 $(25.20) -> 2nd paymentĩ-Apr-14 VENDOR-0012 $(105.14) -> 3rd paymentĥ-Apr-14 VENDOR-0012 $(75.10) -> 4th payment

Looking at your example, let's say the positive amounts for the same vendor in the table (the invoices) all have different dates (the invoice dates) and the negative amounts (the payments) for the same vendor also have different dates (the payment dates). Would you happen to have a solution for that also? Pivot tables indeed do provide a faster solution.Ĭan we further expand on the problem? Generally, you would also want to know which invoices were paid for completely and which are still (partially) outstanding, as you apply interest on the invoice amount starting the day it became overdue.

How to use pivot tables in excel youtube