• 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

Status
Not open for further replies.
N

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!
 
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.
 
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
 
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.
 
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
 
Dear Sajan,


I cant understand the formula.Instead of creating name ranges can you please put formula for my knowledge .Thanks for the support.
 
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
 
Thanks a lot @NARAYANK991 . Your solution was really really helpful.
I have further added the Ageing to it and made Bills and Receipts Tabs separately.

Regards
Mohammed Akram
 

Attachments

  • FIFO_Allocation-2.xlsx
    12.2 KB · Views: 220

harshitjain​

As You have read from Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org

Start a new post every time you ask a question, even if the theme is similar.

This few years old thread is closed now.
 
Status
Not open for further replies.
Back
Top