Hi Everyone!

I have a task which needs to be done in excel. I have to calculate collection discount as per company policy which is if payment is received within 30 days customer is entitled to get 2% discount.
I have invoice wise sales data and receipts data in excel. Problem is we don't receive payments invoice wise rather we receive a lumpsum amount for example customer pays us 1000000 for the month against 5 invoices of random amount. I have to allocate this payment on fifo basis.
Is there anybody who could help me in this matter?

I will be really grateful to you.