How to use FIFO method in payments received from customers?

How to use the FIFO concept in maintaining the payments received from the customers?

In case if there are 4 line items for an invoice and the customer pays money for it partially. I need to assign the partially paid amount line item wise.

For example, 4 line items costs $500 each (Total $2,000). The customer pays $1,250 (Balance= $750).

I have a column named "Amount due". In this column, the first and second line items has to be marked $0 since the amount paid is greater than the individual line item. And the third line item has to be $250 and the forth has to be $500.

Please look at the attached image for reference. The last column "Amount Sue" has to be automatically calculated.

How to achieve this? Please help me.

1. So different customers can have he same invoice number? I bet your accounting department love you!

2. If there is a payment made, is that to go against the invoice or the customer?
eg. If customer Kelly has invoices 1 and 2 and a payment is entered against invoice 2, does it pay off Invoice 1 first if there is an outstanding amount on it?

3. This will require VBA, are you happy with that?

4. Are the transactions always in date order? Do you want to pay off in date order or the order on the worksheet?

So many questions here, and no attached workbook (see yellow banner).

Hi Croweater. Please understand it this way.

1- No. For the same accounting year, different customers cannot have the same invoice number. I am working for a small company for which I manage the data. Our accounting system / software automatically generates the numbers from 1 to n. For each transaction the invoice number is auto-incremented. Once the accounting year is completed it is reset to 1 again. I hope this point is clear to you.

2- Payments are adjusted against respective customers who pays. We know which customer is paying. And it is adjusted against whichever invoice is older corresponding to that customer. Let us look at the below example.

Kelly has:
Invoice 1 dated 1-Jan-2020 with the total invoice value of $1,500 (Line item 1)
Invoice 1 dated 1-Jan-2020 with the total invoice value of $2,500 (Line item 2)
Invoice 2 dated 15-May-2020 with the total invoice value of $5,000 (Only one line item for this invoice)

She made payment of $2,000. It will be adjusted against the oldest invoice which is due. In this case Invoice 1's 1st line item will be updated as fully paid. Remaining $500 will be adjusted in 2nd line item and will be marked partially paid. The due will be $2,000 for that particular invoice (i.e Invoice 1).

3- No I am not looking for VBA. I am looking for some simple formula that can be incorporated.

4- This is explained in point 2.

Please help me with the solution. Attached the workbook with this message.

Thanks!!

Hi John Topley. I am not getting your point!

Please note. Payments received data will be manually entered and it is mentioned in the column "Payments received".
But it is grouped according to the invoice number. Please refer to image / workbook.

For Accounting year 2021 - Invoice no 1, the payment received is $11,220. This piece of information will be manually updated in my data.
If Invoice no 1 has 4 line items, then whatever the payment I received from Alex will be entered in the 4th line item.

But then, the "Amount Due" column must be automated. That is my problem statement.

Hi John Topley. This is not working. I have already tried this. Also the desired outcome is not as shown in the image & workbook that I have attached. Thank you for taking time and replying my post. If you find the solution, please let me know.