Forum Discussion
FIFO formula for payments receivable
How to use the FIFO concept in maintaining the payments received from the customers. Let us assume that there are 4 line items for an invoice and the customer has made a partial payment. Now, I need to assign the partially paid amount, line item wise. For example, 4 line items in the invoice has the value of $500 each. The customer has paid $1,250. I have a column by the name "Amount Due" in which the first and second line items has to be marked $0. The third line item has to be $250 and the forth must be $500. Please refer to the attached sample image. Please help me.
MrManikandanRaju yes I already used SUMIFS so you just need to change the criteria and add another set like this:
=MIN([@[Net Total]], MAX(0, SUMIFS(Table1[[#Headers],[Net Total]]:[@[Net Total]],Table1[[#Headers],[Acc Yr]]:[@[Acc Yr]],[@[Acc Yr]],Table1[[#Headers],[Inv No]]:[@[Inv No]],[@[Inv No]])-SUMIFS([Payment Rec''d],[Acc Yr],[@[Acc Yr]],[Inv No],[@[Inv No]])))
5 Replies
MrManikandanRaju In the attached is an example. I used the following formula:
=MIN([@[Net Total]], MAX(0, SUMIFS(Table1[[#Headers],[Net Total]]:[@[Net Total]],Table1[[#Headers],[Customer]]:[@Customer],[@Customer])-SUMIFS([Payment Rec''d],[Customer],[@Customer])))I formatted the data as a TABLE so I could use table references.
Line 1 just says use the min between this line's Net Total and the following calculation (i.e. max value for amount due on this line is the Net Total of this line)
Line 2 says use the max between 0 and the following calculation (i.e. don't use negative values)
Line 3 is where it takes the sum of all Net Total values from the header down to this line if the customer is this line's customer, and then subtract ALL payments by this customer.
- Hi Matt. Thank you so much for the help. Appreciate it. In your example you have chosen the criteria as customer name. But I have to have the criteria as both invoice number and accounting year. I think it can be done within SUMIFS function. Please correct me if I am wrong. Let me also try it and let you know. Once again thanks!!
MrManikandanRaju yes I already used SUMIFS so you just need to change the criteria and add another set like this:
=MIN([@[Net Total]], MAX(0, SUMIFS(Table1[[#Headers],[Net Total]]:[@[Net Total]],Table1[[#Headers],[Acc Yr]]:[@[Acc Yr]],[@[Acc Yr]],Table1[[#Headers],[Inv No]]:[@[Inv No]],[@[Inv No]])-SUMIFS([Payment Rec''d],[Acc Yr],[@[Acc Yr]],[Inv No],[@[Inv No]])))
