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

Invoice	Contract	Qty	Price	Total
123456		7500	1.08	8100
123457		6500	1.15	7475
123458	x	7500	1.55	11625
123459		8500	1.18	10030
123460		7500	1.12	8400
123461	x	6500	1.55	10075

<tbody>
</tbody>

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