FIFO - Average Cost Per Unit

[schamskevi]

Hello,

I am trying to create a function 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 a simple example:


Average Cost Per Unit 1.142 <<<<<<Custom Function entered here
Inventory Level 25000
Invoice Contract Qty Price Total
123456 7500 1.08 $8,100 2500*1.08= $2,700
123457 6500 1.15 $7,475 $7,475
123458 x 7500 1.55 $11,625
123459 8500 1.18 $10,030 $10,030
123460 7500 1.12 $8,400 $8,400
123461 x 6500 1.55 $10,075
$28,605 ÷ 25000 = $1.142

Any help on this would be fantastic!

 

[schamskevi]

Here is the example organized:

Average Cost Per Unit 1.142 Inventory Level 25000

[TABLE="class: 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]
2500*1.08= $2,700
$7,475
$11,625
$10,030
$8,400
$28,605 ÷ 25000 = $1.142