Calculating future stock values...

I deal with fast moving consumer products - the value of which changes on a weekly basis.

I am trying to work out a way to calculate what the future average unit cost of a product will be as a mix between the goods I have in stock now, and the goods that will arrive on the next shipment.

For example:

If I have 100pcs of Product X with an unit cost $10, and I order in another 100pcs with a unit cost of $9. The average unit cost of the product will full 200pcs will be $9.50 (assuming I don't sell any of the original 100 before the next 100 arrives).

Anyway, I do have a working solution for this - just don't think it's particularly elegant - and I'm sure that Excel geniuses out there could simplify my formula or utilise a function command.. Formula is as follows:

=sum(Quantity in stock x Current unit cost)+(quantity on order x landed cost for the new stock)/(quantity in stock + quantity on order)

Can anyone offer any suggestions to improve the above? Or even better, work out a way for me to do the work out the future average unit cost taking into account if i sell X pieces of current stock between now and when the new stock arrives...

Apologies for the long first post - but appreciate any help...

Assuming you're doing first-in, first-out costing, then what you have looks correct aside from some parentheses:

=(stockQty * stockUnitCost + orderQty * orderUnitCost) / (stockQty + orderQty)

Originally Posted by shg

Assuming you're doing first-in, first-out costing, then what you have looks correct aside from some parentheses:

=(stockQty * stockUnitCost + orderQty * orderUnitCost) / (stockQty + orderQty)

Thanks for your reply shg - I don't believe we're using FIFO - when new stock arrives, the total value of the new inventory gets booked into stock - creating an average unit cost blend between the new and old stock... ....and it's this future average unit cost that i'm trying to predict.

Computing the cost of a unit sold and the average cost of remaining inventory depends on whether you consider yourself to be selling (irrespective if the units are fungible, e.g., no expiration dates):

• the oldest unit in stock (FIFO)
• the newest unit in stock (LIFO)
• the most expensive unit in stock
• the least expensive unit in stock