payments allocation to bills in fifo basis

**harshit8999** · 07-13-2023, 02:04 AM

we have two tables 1 table includes payment date payment number payment vendor name and payment amount
and second table has bills date bill number bill vendor name and bill amount
we need to a allocate the payment to the bills On FIFO Basis in Excel
we have three bills scenario
first one is multiple payments to a single bill
second is single payment to single bill
and third is single payment to multiple bills
give me solution with sample data

**johnnyL** · 07-13-2023, 02:13 AM

I am pretty sure you may want to attach a file that shows some beginning data, as well as data that you expect as a result of what you want to do.

**harshit8999** · 07-13-2023, 02:22 AM

FIFO SAMPLE DATA 001 (2).xlsx
this is the sample data

**JeteMc** · 07-13-2023, 09:55 PM

Hello and Welcome to Excel Forum.
Perhaps this will help.
Paste the payments transpose in the top four rows of the Bills sheet
Use the following formula to get the FIFO amounts: =IF($B6<>F$3,"",MIN($D6-SUM($E6:E6),F$4-SUM(F$5:F5)))
Let us know if you have any questions.

**harshit8999** · 07-14-2023, 01:03 AM

thnak you very much for spending your time and helping me out but sadly thats not the output i am looking for ive shared the desired output in this thread please do look at it and please try to help me out thank you

**harshit8999** · 07-14-2023, 01:05 AM

thnak you very much for spending your time and helping me out but sadly thats not the output i am looking for ive shared the desired output in this thread please do look at it and please try to help me out thank youFIFO SAMPLE DATA 001 (2) (1).xlsx

**JeteMc** · 07-15-2023, 01:58 PM

This proposal employs the strategy used in the file attached to post #4 and adds a column (E) to the payments data.
Column E is populated using:

Formula:

Please Login or Register  to view this content.

The ouput is populated using four formulas
For the payment date column:

Formula:

Please Login or Register  to view this content.

For the payment number through payment amount columns:

Formula:

Please Login or Register  to view this content.

For the allocated payment column:

Formula:

Please Login or Register  to view this content.

For the allocated bill column:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

payments allocation to bills in fifo basis

LinkBack

Thread Tools

Rate This Thread

Display

payments allocation to bills in fifo basis

Re: payments allocation to bills in fifo basis

Re: payments allocation to bills in fifo basis

Re: payments allocation to bills in fifo basis

Re: payments allocation to bills in fifo basis

Re: payments allocation to bills in fifo basis

Re: payments allocation to bills in fifo basis

Thread Information

Users Browsing this Thread

Similar Threads

Weighted Avg Collection Days on FIFO basis

How to use FIFO method in payments received from customers?

Allocate value on FIFO basis

Fifo allocation of inventory & extract name of associated bin & store name.

Fifo po allocation

[SOLVED] Apply fifo when i am making payments to my vendors

match FIFO (first in first out) basis on stock

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions