FIFO Inventory (Average Inventory Cost) - Excel Formula Help

[tommiexboi]

Hello,

Scenario

I have a total of 500,000 On Hand Quantity of X Product and 350,000 On Hand Quantity of Y Product.

I have another sheet that shows all X & Y Product purchases throughout the month with the purchase cost, purchase order number, and date.

I'm trying to figure out if there's a formula, which will:

1) If I have 500,000 Units of X Product & 350,000 Units of Y Product, I would like to know which purchase date I bought it from and what cost using FIFO method.
2) I would also like to average out the available on hand cost using FIFO

End Result:

Product	On hand	Purchase Date (Need)	Purchase Cost (Need)	Average Inventory Cost (Need)
Product X	500,000​	12/5/2019​	$1.80​	$1.84​
Product Y	350,000​	12/17/2019​	$3.00​	$2.96​

Purchase Sheet Sample:

Date	Product	Purchase QTY	Purchase Cost
12/29/2019​	X	30,000​	$1.80​
12/23/2019​	X	50,000​	$1.85​
12/21/2019​	X	100,000​	$1.85​
12/21/2019​	X	25,000​	$1.87​
12/18/2019​	X	125,000​	$1.80​
12/17/2019​	X	75,000​	$1.87​
12/15/2019​	X	25,000​	$1.95​
12/12/2019​	X	1,500​	$2.20​
12/7/2019​	X	1,000​	$2.30​
12/5/2019​	X	100,000​	$1.80​
12/2/2019​	X	50,000​	$1.85​
12/2/2019​	X	30,000​	$2.00​
12/29/2019​	Y	85,000​	$3.00​
12/23/2019​	Y	100,000​	$2.80​
12/21/2019​	Y	50,000​	$3.00​
12/21/2019​	Y	25,000​	$3.10​
12/18/2019​	Y	35,000​	$3.10​
12/17/2019​	Y	61,000​	$3.00​
12/15/2019​	Y	45,000​	$3.00​
12/12/2019​	Y	180,000​	$2.70​
12/7/2019​	Y	20,000​	$3.10​
12/5/2019​	Y	2,000​	$4.00​
12/2/2019​	Y	1,000​	$4.00​
12/2/2019​	Y	2,000​	$4.00​

 

[ExcelGzh]

tommiexboi

My interpretation of FIFO is that it means First In First Out which I then take to mean that you want to know the date of the oldest stock and what it cost to purchase on that date. If my interpretation is correct, why are you nominating dates that are not the oldest?

Also, what version of Excel are you using? You should nominate this in your profile. If you are using Excel 365 then the formula will be a lot simpler than if you have an older version.

 

[tommiexboi]

Hello,

I'm following up on this question/help.

@ExcelGzh, I'm not quite sure what you mean by nominating the dates. Also, I updated my profile to show which Excel version I have.

Thanks everyone