• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Allocate payment based on FIFO Basis

nandhamnk

New Member
Dear All,


I need a formula help on allocating payment received from Customer on First cum first basis.

For example,


Customer Value

A 100

B 125

A 110

A 285

A 300


Payment is received for A is 500.It should be allocated as below:


Customer Value Payment

A 100 100

B 125

A 110 110

A 285 285

A 300 5


Request your support please!
 

keymaster

New Member
Interesting question. I could not come up with a quick solution with formulas alone, so VBA to the rescue.


The basic logic is simple,


1. Have two tables one for dues, one for payments.

2. For each payment, we will go thru rows in dues and see

3. if customer matches, and whether can allocate any more payment to it

4. If so, we allocate the payment and subtract it from current value

5. Repeat


See this attached file. It uses your data but to test it I have added few dozen more rows. Seems to work ok for me.


http://img.chandoo.org/playground/fifo-payment-allocation.xlsm


Click the blue button to allocate payments, gray one to clear them.
 

kaushik03

Member
Hi nandhamnk,


I am not very clear about your requirenet properly. Can u plz clarify the following points?


1) Do you have the payment data list separately somewhere in your excel file?

2) How are you distributing the 500 payment amount for 4 As in your list (However the total payment is coming up 510

in your examplpe above...please clarify why!)

3) Is there any specific relation between valu and payment column?


Regards,

Kaushik
 

Sajan

Excel Ninja
Hi,

I think we can use the following formula based approach as well.


My sample data setup is as follows. (Allocation was calculated.)

[pre]
Code:
Customer	Receivable	Allocation
A	        100	         100
B	        125	         25
A	        110	         110
A	        285	         285
A	        300	         5
i.e. Customer heading is in cell A1, with data in A2 onwards

Receivable is in column B

Allocation in column C


The payment data sample I used is as follows:

Customer	Payment
A	        500
B	        25
[/pre]
For ease of reference in the formula, I named the payment data using Excel Names:

"PaidBy" is the data range "A"; "B" above

"Payments" is the data range "500;25" above


Allocation can be calculated as (formula shown is for cell C2):

=MIN(B2, SUMIFS(Payments, PaidBy, A2) - SUMIFS(C$1:C1, A$1:A1,A2))


It can be copied down to additional rows in column C.


Hope this helps.


Cheers,

Sajan.
 

Hui

Excel Ninja
Staff member
This is the same logic as discussed in: http://chandoo.org/wp/2012/10/11/formula-forensics-no-031/

Including associated links at the end of the post
 

nandhamnk

New Member
Dear Sajan,


I cant understand the formula.Instead of creating name ranges can you please put formula for my knowledge .Thanks for the support.
 

TARUN GARG

New Member
Hello All,

I am looking for the answer to the below query which is a little bit similar to above one:

I want to adjust "Advance received from customer" into the Debtors Aging buckets of 0-30, 31-60, 61-90, 91-180, >180 days on FIFO basis.

Note: Advance received is anonymous.

Let's take an eg. as below:

Case 1:


Tenant Name 0-30 31-60 61-90 91-180 >180 Advance
A 100 80 165 -20 40 -140

Case 2:

Tenant Name 0-30 31-60 61-90 91-180 >180 Advance
A 100 80 165 -20 -40 -140

Case 3:

Tenant Name 0-30 31-60 61-90 91-180 >180 Advance
A 100 80 165 -20 -40 -20


The whole idea is to settle the advance amount starting from >180 days and ending at 0-30 days.

Kindly help with an excel based file. (Macros enabled or formula based).

Thanks in advance.


Tarun
 
Top