Posting Multiple Payments to Multiple Invoices – FIFO Method [SOLVED]

[B0035930]

How to Posting Multiple Payments to Multiple Invoices – FIFO Method


Example Data as below :

[pre]

Open/ Oustanding/Pending Invoices Details 

Account    InvoiceNo    Invoice_Date    Invoice_Amt
6524001     1654321       01-Jan-13       6,589.00
6524001     1654350       01-Feb-13       6,548.00
6524001     1654377       01-Mar-13       3,562.00
6524001     1654410       01-Apr-13       6,524.00
6524001     1654443       01-May-13       5,124.00
7521436     1654355       01-Feb-13       3,256.00
7521436     1654388       01-Mar-13       6,589.00
7521436     1654421       01-Apr-13       5,874.00
7521436     1654454       01-May-13       5,984.00
5487541     1654366       01-Feb-13       9,542.00
5487541     1654399       01-Mar-13       7,542.00
5487541     1654432       01-Apr-13       6,325.00
5487541     1654465       01-May-13       3,562.00

Payment Received Details 

Account    Recvd_Amt       Chq_No          Chq_Dtd
6524001    20,005.20       002546         05-May-13
7521436    15,745.60       652114         05-May-13
5487541    18,000.00       065214         05-May-13

I want the Result as below.

Account    Invoice no    Invoice_Date    Invoice_Amt    Amt to be Posted
6524001      1654321       01-Jan-13       6,589.00         6,589.00
6524001      1654350       01-Feb-13       6,548.00         6,548.00
6524001      1654377       01-Mar-13       3,562.00         3,562.00
6524001      1654410       01-Apr-13       6,524.00         3,306.20
6524001      1654443       01-May-13       5,124.00             -
7521436      1654355       01-Feb-13       3,256.00         3,256.00
7521436      1654388       01-Mar-13       6,589.00         6,589.00
7521436      1654421       01-Apr-13       5,874.00         5,874.00
7521436      1654454       01-May-13       5,984.00            26.60
5487541      1654366       01-Feb-13       9,542.00         9,542.00
5487541      1654399       01-Mar-13       7,542.00         7,542.00
5487541      1654432       01-Apr-13       6,325.00           916.00
5487541      1654465       01-May-13       3,562.00             -

[/pre]
Please provide the excel template

 

[NARAYANK991]

Hi ,


The file I have uploaded uses a helper column.


https://www.dropbox.com/s/6o0ey2f42dil1tc/Example.xlsx


Narayan

 

[Sajan]

Hello,

Assuming that your invoice details are in the range A:D, and that the AmtToBePosted is in column E, calculate the AmtToBePosted with the following formula (shown for cell E2):

=MIN($D2, SUMIFS(PaymentAmounts,PaymentAccounts,$A2)-SUMIFS($E$1:E1, $A$1:A1,$A2))


Copy down to the rest of the rows.


Here D2 refers to the InvoiceAmount, and A2 refers to the Account.

PaymentAmounts and PaymentAccounts refer to the payment details.


I got the following output. (The last two rows are test rows I added.)

[pre]

Account	  InvoiceNo	        Invoice_Date	Invoice_Amt	AmtToBePosted
6524001	  1654321               1-Jan-13	6,589.00	6,589.00
6524001	  1654350	        1-Feb-13	6,548.00	6,548.00
6524001	  1654377	        1-Mar-13	3,562.00	3,562.00
6524001	  1654410	        1-Apr-13	6,524.00	3,306.20
6524001	  1654443	        1-May-13	5,124.00	0.00
7521436	  1654355	        1-Feb-13	3,256.00	3,256.00
7521436	  1654388	        1-Mar-13	6,589.00	6,589.00
7521436	  1654421	        1-Apr-13	5,874.00	5,874.00
7521436	  1654454	        1-May-13	5,984.00	26.60
5487541	  1654366	        1-Feb-13	9,542.00	9,542.00
5487541	  1654399	        1-Mar-13	7,542.00	7,542.00
5487541	  1654432	        1-Apr-13	6,325.00	916.00
5487541	  1654465	        1-May-13	3,562.00	0.00
7521436	  1234567	        12-May	        4,500.00	0.00
9898989	  9768687	        13-May	        2,700.00	0.00

[/pre]
Cheers,

Sajan.

 

[B0035930]

Dear Sajan/Narayan,


Thank you so much for your prompt positive response.

Thank you "Chandoo.org"

 

[denver_4114]

hi there, i have a follow-up question to this. For the account#6524001, there still a Outstanding balance of 3,217.80 for invoice#1654410 and 5,124 for the invoice#1654443.

what if I am going to apply the remaining balance to a multiple deduction. how to do it.

example deduction:
Rebates - 6,000
RTV - 2,000
EWT - 341.80

result:

ACCOUNT	INVOICE	DEDUCTION	AMOUNT
6524001	1654410	Rebates	3,217.80
6524001	1654443	Rebates	2,782.20
6524001	1654443	RTV	2,000.00
6524001	1654443	EWT	341.80

thank you for reply.

 

[vletm]

denver_4114
Please, open a new thread
with sample Excel-file which has some sample data as well as expected results.
as You've read from Forum Rules.

 

[denver_4114]

Ok thank you.