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...