Applying FIFO for Items using Excel

[Hamzah]

Hi guys,

I have been struggling to find an automated excel solution to look up a sale of an item (in separate sheet) to be returned against the oldest purchase of the same item (also in separate sheet) but with no luck. Similar to FIFO.

Could you guide me.....anyone.

Attached is the sample data set. Thanks

 

[Asheesh]

First of all welcome to the Forum..

I guess, I do not understand the question, could you please update the sample result in the attached...

 

[Hamzah]

Thanks a lot for the warm welcome Asheesh. I was given a notification that i did not title the thread apprpriately. I guess those are newbie problems.

Anyway, i have 3 items 1123, 1771 and 1542. I purchase & sell those 3 items over time and so i have a purchase work sheet and a sales work sheet.

But i purchase chunks of the same item at various cost i.e. like i am buying a financial asset. Then in the other sheet i sell the chunks of the same item also at different times/dates.

Now, the problem is: I am looking for a way to match a oldest sale of an item to the oldest purchase, then, if there is any balance of the sale that has not been allocated to the oldest purchase, then this is allocated to the second oldest purchase. In essence, i would like these sales to appear along side the corresponding purchase in the "Purchase worksheet"

That is what i am finding challenging

 

[Asheesh]

Just double checking: For 1771, should this be the answer - See Image

 

[Hamzah]

Hi Asheesh, for 1771, i am trying to get something like this:

 

[Hamzah]

Just double checking: For 1771, should this be the answer - See Image

View attachment 37603

Hi Asheesh,

Hope you're well. The solution you shared earlier is of help. Could you please share formulaes or workbook for the same? Thanks