FIFO Approximation

[ikasama]

Hi all, Can i get help on a way to calculate the FIFO Average Cost for a list of SKU's.

Data set has SKU, Q ordered, Price paid, date ordered. Then i have another sheet with the SKU, Current Inventory.

So the calculation would need to look at the current inventory, then, somehow, output the average cost for that inventory by FIFO.

Either by formula or VBA would work for me... I assume, if by formula, it would need an array... but, I can't figure it out...

Thanks!

 

[Skybluekid]

Hi all, Can i get help on a way to calculate the FIFO Average Cost for a list of SKU's.

Data set has SKU, Q ordered, Price paid, date ordered. Then i have another sheet with the SKU, Current Inventory.

So the calculation would need to look at the current inventory, then, somehow, output the average cost for that inventory by FIFO.

Either by formula or VBA would work for me... I assume, if by formula, it would need an array... but, I can't figure it out...

Thanks!

Could you post some data and expected results

 

[ikasama]

Could you post some data and expected results

abc; 500; 2/1/2012; $3
abc; 750; 3/1/2012; $5
abc; 1000; 10/1/2012; $8

abc; 1999

So, with 1999 being the current inventory on hand, that would be all 1000 of the $8 inventory, all 750 of the $5 inventory and then 249 of the $3 inventory. for a total cost of $12,497; so, dividing that by the 1999 current on hand would be about $6.25 as the average cost of the current on hand inventory.

If the on hand were 1625, it would be $6.85
1100 then $7.73
less than 1000, it would be only the $8 inventory.

Ideally, i would like to have a formula where i can drag down for a long list of SKU's and current on hand.

Thank you for your help!

 

[ikasama]

Pardon me as i bump my thread.