How to Calculate Cost of Goods Sold - formula COGS in Excel

The term COGS (Cost Of Goods Sold) refers to the total amount paid for all sold goods. This is one of the key factors in calculating gross profit. When working with ERP systems (Enterprise Resource Planning) for inventory management, which operates continuously, the cost of goods sold is calculated each time a sale is made. In simpler systems, the cost of this indicator is calculated based on the financial state of inventory at the end of the accounting period.



Example of a Formula for Calculating COGS in Excel

The image below illustrates a formula for calculating the cost of goods sold based on the inventory at the beginning and end of the accounting period, as well as the total purchases made during that period.

Source data table:

Beginning Inventory	$1,235,642.25
Purchases	$641,152.77
Goods Available for Sale	$1,876,795.02
Ending Inventory	$1,111,903.23
Cost of Goods Sold (COGS)	$764,891.79

Download an example of the COGS calculation formula in Excel

The total cost of all products available for sale is the sum of the beginning inventory and the cost of all purchases. This operation represents an intermediate step in calculations, showing what the inventory would look like if nothing were sold.

Calculating the COGS in Excel is quite simple: from the total cost of all products available for sale, subtract the ending inventory at the close of the accounting period. If there were goods at the beginning of the period or purchases made during the period but none at the end, it means they were all sold.