Tough Inventory Weighted Average Price Problem, Please Help!

Please See Attached
I am designing an Inventory and Sales tracking spreadsheet to track multiple things such as inventory count, Purchases (On Order) count, unit cost, revenue, and profits. It is a work in progress but basically there is a master inventory list with all the inventory items listed. Then purchases/sales are entered on a month by month sheet basis.

Here is the problem I am having: I would like to have the excel spreadsheet automatically calculate an average unit cost for the Current inventory.

For Example:

I have an item, Thing 1. My beginning inventory on the master inventory sheet is listed as 0, so in the January purchases/sales sheet when I enter Thing 1 in the id field it automatically identifies the item and that there are 0 in current inventory. I purchase 1000 units at a price of $2/unit = $2000 Total Cost. I then also purchase another 2000 units of Thing 1, at $1.50/unit = $3000 Total Cost.
The master sheet is reading that there are now 3000 units. I want it so that these units do not appear on current inventory until they are marked as arrived on the january sheet.

Now the hard part, once these units are marked as arrived I want them to them to display in the Master Inventory and show a Weighted Average Unit Cost. So:
1000 units @ $2.00 = $2000
2000 units @ $1.50 = $3000
$5000
$5000 Total Cost / 3000 units = $1.67 per unit. So the Master Inventory should now report 3000 units at $1.67/unit.

The January sheet should now show the same unit cost when selling units, but not when purchasing them.

Hope this is clear enough, I'm having lots of troubles trying different formulas!


Thanks!