Using Google Sheets
Average Cost Calculation
The average-cost method allocates the cost of goods available for sale on the
basis of the weighted-average unit cost incurred for a given period. Several businesses use this methodology when inventory units are similar in nature. Note, the calculated average does not refer to a per unit cost only; instead, uses the extended cost divided by the total units available. Therefore, the result is a weighted average cost.
Solution:
Creating a simple spreadsheet using Google Sheets or Microsoft Excel should allow you to create your schedule using the Average Cost for inventory costing. In the image, you can follow the steps listed below:
First, list all units (beginning balance and purchases) with the unit cost. You should segregate, purchases with different unit costs to properly calculate the weighted average.
Second, multiply individual units times unit cost, the result – extended cost. As stated before, we do not calculate average on units only; our calculation also include the total cost of the units as a whole.
Third, place totals, using the sum() function in the columns units and extended cost. You can add individually, when few items; however, with long lists I recommend to use formulas. I normally use formulas and functions to improve speed and automation of my spreadsheets.
Fourth, Divide the extended cost and the total units, to get a weighted average of cost.
Fifth, apply the weighted average cost to your calculated ending inventory in units and apply to the calculated sold items to obtain the cost of goods sold.
Conclusion:
The Average cost of inventory spreadsheet is a simple way to present and resolve this methodology. Units or items accounted for this method maintains a homogeneous characteristics; thus, making possible to use. If multiple items; then segregating similar items and calculating for each group could be a good idea.