refaclick.blogg.se

I would use input cells for the date criteria and for the material.

It allows you to pull data from multiple workbooks and adjust the date as necessary. I wrote out a general format to accomplish what you are looking for. I would use sumproduct and sumifs to get the data you are looking for. 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. And, the beginning balance is equal to the ending balance from the previous period, in this case, February 2016. 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, to make matters worse, this calculation is usually done monthly, quarterly, or yearly. There are many different raw material items as well. The data is in two different tables, one for purchases and one for materials used. The sum of these two amounts (less a rounding error) equals the $116,000 total actual cost of all purchases and beginning inventory. 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 weighted average cost per unit is therefore $257.78 ($116,000 ÷ 450 units.) The total of all raw materials purchased and beginning inventory units is 450 (150 beginning inventory + 300 purchased). 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).