EXCEL: Calculating cost of goods sold

Cost of goods sold is the amount you paid for all the goods you sold. It is a critical component to calculating gross. If you use a perpetual inventory system, you calculate the cost of goods sold for every sale made. For simpler systems, however, you can calculate it based on a physical inventory at the end of the accounting period.

Figure below shows how to calculate the cost of goods sold with only the beginning and ending inventory counts and the total of all of the inventory purchased in the period.

Goods Available for Sale

=SUM(C2:C3)

Cost of Goods Sold

=C4-C5

The goods available for sale is the beginning inventory plus all of the purchases made. It is an intermediate calculation that shows what your ending inventory would be if you didn’t sell anything. 

The cost of goods sold calculation simply subtracts the ending inventory from the goods available for sale. If you had the goods at the start of the period or you bought them during the period but you don’t have them at the end of the period, then they must have been sold.

Now you know how to compute Cost of Goods Sold using Excel.