Inventory Costing - FIFO

[schamskevi]

Hello,

I am trying to create a formula in MS Excel that will find the average cost per unit using the FIFO costing method based on the units in inventory and a purchase ledger (quantity, price, total etc.). If a purchase is marked as contract I would NOT like it included in the average cost per unit.

Here is the example organized:

Inventory Level 25000

[TABLE="class: cms_table_grid, width: 400"]
<tbody>[TR]
[TD="align: center"]Invoice[/TD]
[TD]Contract[/TD]
[TD]Qty[/TD]
[TD]Price[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD][/TD]
[TD]7500[/TD]
[TD]1.08[/TD]
[TD]8100[/TD]
[/TR]
[TR]
[TD]123457[/TD]
[TD][/TD]
[TD]6500[/TD]
[TD]1.15[/TD]
[TD]7475[/TD]
[/TR]
[TR]
[TD]123458[/TD]
[TD]x[/TD]
[TD]7500[/TD]
[TD]1.55[/TD]
[TD]11625[/TD]
[/TR]
[TR]
[TD]123459[/TD]
[TD][/TD]
[TD]8500[/TD]
[TD]1.18[/TD]
[TD]10030[/TD]
[/TR]
[TR]
[TD]123460[/TD]
[TD][/TD]
[TD]7500[/TD]
[TD]1.12[/TD]
[TD]8400[/TD]
[/TR]
[TR]
[TD]123461[/TD]
[TD]x[/TD]
[TD]6500[/TD]
[TD]1.55[/TD]
[TD]10075[/TD]
[/TR]
</tbody>[/TABLE]

Calculation
2500*1.08= $2,700
$7,475
$11,625
$10,030
+$8,400
$28,605 ÷ 25000 = $1.142​

 

[Roderick_E]

maybe look into sumif and sumifs function?