Weighted Average Cost for Inventory & Cost of Materials Used

[queenbean84]

I am trying to value inventory and cost of materials used using the weighted average costing method. This is how it works in theory:

When using the weighted average method, divide the cost of raw materials available by the number of units available, which yields the weighted-average cost per unit. In this calculation, the raw materials available is the sum of beginning inventory and net purchases. You then use this weighted-average figure to assign a cost to both ending raw material inventory and the cost of raw materials used.

[TABLE="width: 102%"]
<tbody>[TR]
[TD][/TD]
[TD]Quantity
Change [/TD]
[TD]Actual
Unit Cost [/TD]
[TD="colspan: 2"]Actual
Total Cost[/TD]
[/TR]
[TR]
[TD]Beginning raw material inventory[/TD]
[TD]+150[/TD]
[TD]$220[/TD]
[TD]$33,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Less: raw materials used in production[/TD]
[TD]-125[/TD]
[TD]--[/TD]
[TD]--[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Raw materials purchased[/TD]
[TD]+200[/TD]
[TD]270[/TD]
[TD]54,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Less: raw materials used in production[/TD]
[TD]-150[/TD]
[TD]--[/TD]
[TD]--[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Raw materials purchased[/TD]
[TD]+100[/TD]
[TD]290[/TD]
[TD]29,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ending inventory[/TD]
[TD]= 175[/TD]
[TD][/TD]
[TD]= $116,000[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The actual total cost of all raw materials purchased and beginning inventory in the preceding table is $116,000 ($33,000 + $54,000 + $29,000). The total of all raw materials purchased and beginning inventory units is 450 (150 beginning inventory + 300 purchased). The weighted average cost per unit is therefore $257.78 ($116,000 ÷ 450 units.)

The ending inventory valuation is $45,112 (175 units × $257.78 weighted average cost), while the cost of raw materials used in production is $70,890 (275 units × $257.78 weighted average cost). The sum of these two amounts (less a rounding error) equals the $116,000 total actual cost of all purchases and beginning inventory.

The data is in two different tables, one for purchases and one for materials used. There are many different raw material items as well. And, to make matters worse, this calculation is usually done monthly, quarterly, or yearly.

How do I get this calculation to work for a specified time period, like March 2016, for each raw material? The data is changing constantly as new purchase and used transactions occur. And, the beginning balance is equal to the ending balance from the previous period, in this case, February 2016.

I want the user to be able to choose the time period and the raw material name to see the inventory balance value and the cost of raw materials used value.

 

[dmg2016]

I would use sumproduct and sumifs to get the data you are looking for. I wrote out a general format to accomplish what you are looking for. It allows you to pull data from multiple workbooks and adjust the date as necessary. I would use input cells for the date criteria and for the material. For the user interaction, you could use filters or data validation depending on your needs.

avg cost = SUMPRODUCT((date range>=start date)*(date range<=end date)*(material range=material)*1,qty purchased range,actual unit cost range)/SUM(qty purchased range)

ending inventory qty = sumifs(qty range purchase, date range, start date, date range, end date, material range, material)-sumifs(qty range used, date range, start date, date range, end date, material range, material)

weighted avg cost = avg cost * ending inventory

cost used = sumifs($ range used, date range, start date, date range, end date, material range, material)

ending inventory $ = sumifs($ range purchase, date range, start date, date range, end date, material range, material)-sumifs($ range used, date range, start date, date range, end date, material range, material)

There are some other ways to do it, if that doesn't work.

 

[queenbean84]

Does this take into consideration a beginning balance that is the same as the previous period's ending balance?

 

[dmg2016]

If I am understanding your report correctly, then the ending balance would depend entirely on the date range you select. It would expand or contract based on the range you set, so it would take into the account the ending a the beginning.