Stumped: Inventory Value Usage

[Drunken_Sailah]

Hi,

I am trying to create an inventory spreadsheet where usage removes quantities from a lot until it is completely relieved and then the next lot is subtracted from. I have completed this as below:

Lot	Date Received	Qty	Cost	Beginning Value	Current Qty	Current Value
1	43101	500	60	=D2*C2	=IF(SUM($C$2:C2)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)<0,0,MAX(SUM($C$2:C2)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)))	=F2*D2
2	43132	100	70	=D3*C3	=IF(F2=0,IF(SUM($C$2:C3)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)<0,0,SUM($C$2:C3)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)),Product!$C3)	=F3*D3
3	43160	200	80	=D4*C4	=IF(F3=0,IF(SUM($C$2:C4)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)<0,0,SUM($C$2:C4)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)),Product!$C4)	=F4*D4
4	43191	300	65	=D5*C5	=IF(F4=0,IF(SUM($C$2:C5)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)<0,0,SUM($C$2:C5)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)),Product!$C5)	=F5*D5
5	43221	200	70	=D6*C6	=IF(F5=0,IF(SUM($C$2:C6)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)<0,0,SUM($C$2:C6)-SUMIF(Builds!$A$2:$A$19,"Product",Builds!$B$2:$B$19)),Product!$C6)	=F6*D6

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

Without the Formulas shown below:

Lot	Date Received	Qty	Cost	Beginning Value	Current Qty	Current Value
1	1/1/2018	500	$60.00	$30,000.00	0.00	$0.00
2	2/1/2018	100	$70.00	$7,000.00	100.00	$7,000.00
3	3/1/2018	200	$80.00	$16,000.00	200.00	$16,000.00
4	4/1/2018	300	$65.00	$19,500.00	300.00	$19,500.00
5	5/1/2018	200	$70.00	$14,000.00	200.00	$14,000.00

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

The next step is the issue, however. I am also, on another tab trying to calculate the usage based upon the rows usage data, however, the same row keeps getting increased in usage value until a lot number is completely utilized. I want each line item to calculate the usage per row.

Ingredient	Qty Used	Total Cost
Product	400	$30,000.00
Product	100	$0.00
Product		$0.00
Product		$0.00

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Ingredient	Qty Used	Total Cost
Product	400	=Product!$E2:$E$6-Product!$G2:$G$6
Product	100	=Product!$E2:$E$6-Product!G$2:$G$6
Product		=Product!$E$2:$E$6-Product!$G$2:$G$6
Product		=Product!$E$2:$E$6-Product!$G$2:$G$6

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Drunken_Sailah]

I see a lot of views, but, no responses, does this make sense or should I try to lay it out another way to try and get some help?