• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Invoice and Payment matching

Sagduel

New Member
Hi,

Presently i am working on data that seems like this which relates to payment
Payment dateDocument no.AmountInterestDue date
12-Apr-171204011000010001-Apr-2017
10-May-171204022000020001-Apr-2017
30-Jun-1712040310000100030-May-17








Now we have linked this payments based on the FIFO basis for the the invoice where the following are the detailed present in invoice.
Invoice dateInvoice NumberAmountDue Date
1-Mar-2017120301300001-Apr-2017
1-May-20171205011500030-May-2017

Data which i Have
Involves debit and credit (Positive Payment) Negative (Invoice) in single sheet and if we total up debit and credit the sum=0. I.e i have all the invoice and payments matched data.

What i require is For which invoice the payment has been matched and how many times the payment is adjusted to the invoice and how much interest is charged against which invoice.

The due date is the only constant figure here as there is no other reference.,

Challenges faced while doing this excercise.
1. Multiple invoice contains same due dates.
2. Payments are made in multiples for single invoice.
3. Payments are adjusted to multiple invoices.

It's urgent Kindly provide any idea.

Presently we are giving reference number manually for all the payments and invoice. Where we have 60000 above line items in one month as it will take more than 6 months of manual reference for doing one year matching.

I have attached the document
 

Attachments

Deepak

Excel Ninja
Yes... But it was done manually by matching all the invoices u can see the sheet attached 1st sheet is the solution 2nd sheet is the query
Then It's not a challenge anyhow. Have wait and one will revert after getting it.
 
Top