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
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
