How to Calculate Cost of Goods Sold in Excel (4 Examples)

In accounting, the cost of goods sold is one of the most important parts to prepare for the financial statements. The Cost of Goods Sold directly affects a company’s income and prospects, which is why every business needs to calculate this properly. COGS represents a part of the direct costs, and while Excel does not provide a direct function to calculate it, it is easy to prepare the COGS sheet in the program. In this article, we will learn how to calculate the cost of goods sold in Excel.

What is the Cost of Goods Sold?

Cost of goods sold includes the costs incurred to buy the goods that are sold by the retailer. In bigger companies, COGS can include all the direct expenses, including merchandise, raw materials, and direct labor. The formula for calculating cost of goods sold is: Beginning Inventory + Purchases – Ending Inventory. By subtracting the COGS/Direct expenses from the revenue, a company can calculate the gross revenue.

The easiest form of COGS, often used by retailers, requires only a few entries to calculate the cost of goods sold. In the table below, we have the beginning inventory, purchases, and the ending inventory. We will calculate the COGS from these values:

➤ In the B7 cell, calculate the total purchases using the following formula:

➤ Calculate the goods available for sale in the B8 cell with the following formula:

➤ Finally, use the formula from below in the B11 cell to calculate the cost of goods sold:

The generic way is not something big companies will use for calculating the cost of goods sold. Instead, they will use one of these three methods: FIFO, LIFO, and Weighted Average. In the FIFO method, the price that was used to buy the oldest product is counted for the sale. For example, if you bought two products, one at $8 and one at $10, and are about to sell one product, the cost of goods sold will be $8, as that was bought first. FIFO stands for First In-First Out.

To calculate COGS using these three methods, we have a table of transactions with the dates, transaction types, units, unit costs, and total cost. We will create another table with the COGS calculation with the FIFO method.

➤ Create a table with the following columns:

• Date
• Type
• Units In
• Unit Cost In
• Units Out
• Unit Cost Out
• COGS
• Inventory Units
• Inventory Value

➤ Now, let’s examine each transaction and fill the table. First, we can add the beginning inventory. The inventory value can be directly copied from the total cost in the source table.

➤ We need a helper table for keeping track of the inventories. Create one from A19 with four columns: Purchase Date, Unit Price, and Total Price.
➤ The first one will go from the beginning inventory. Calculate the total price in D20 using the following formula:

➤ The next transaction is a purchase. This time, to calculate the inventory units, we need the following formula:

➤ Autofill the rest of the rows of the column so that for the next entries, the inventory units are calculated automatically.
➤ Currently, all of the cells show 300, but it will change soon, don’t worry.
➤ Add the purchase to the helper table as well.

➤ To calculate the inventory value, use the following formula:

➤ Autofill the whole column with the formula. It will show 2800 for the rest, and change as we fill up other rows.
➤ The next one is a purchase as well, just copy the units in and unit cost from the original table, and the rest will be filled automatically.

➤ Add the purchase to the helper table to keep track.

➤ Now, 80 units are going to be sold in the next transaction. Put the units out as stated, then for the unit cost out, use 9, as that is the oldest price in this table, according to the beginning inventory. To calculate COGS, use the following formula:

➤ Subtract the units from the helper table using the following formula in B20.

➤ The next one is a purchase, so that can be added without issues; just add a new row to the helper table.
➤ The sale for 1/15/2025 zeroes out the beginning inventory.

➤ After a few transactions, we face a tricky sale on 2/5/2025. This one requires 200 items to be sold. In the helper table, 10 items are remaining from the 1/1/2025 purchase,150 items from the 1/5/2025 purchase, 200 items from the 1/12/2025 purchase, and more. To handle this, we will write ‘10/12/11 in the Unit Cost Out cell, which is F11 in this case. The first apostrophe is put on so that Excel does not count this as a date.

➤ In the G11 cell for COGS, we can write the following formula:

➤ Update the helper table as well:

➤ After completing the FIFO calculation for the whole table, we have to calculate the total COGS. Use the following formula in the G15 cell:

LIFO, or Last In-First Out method, is when the price of the latest purchase is used for the sales. This is the exact opposite of FIFO, and another table with the same structure will be needed to use this method.

➤ Similar to the FIFO method, put the first three transactions in the table:

➤ Create the helper table as well:

➤ For the sale on 1/10/2025, we take the inventory from the last purchase. Hence, the Unit Cost Out is 12 in this case. Here is how the helper table will look after the sale:

➤ The tricky sale of this table comes from 2/5/2025. The last purchase is 150 units, and we have to sell 200 units. Hence, we combine units from the previous sales, 1/20/2025, and 1/12/2025.
➤ Write the following formula in the G11 cell to calculate the COGS for this sale:

➤ Finally, we can use the same formula from FIFO to calculate the Total COGS in the G15 cell:

This method is very different from the FIFO/LIFO method. We don’t need any helper table for it, and although it is difficult to calculate COGS using this method manually, it is the easiest method to do in Excel. Follow the instructions below:

➤ The table structure is the same as the FIFO/LIFO methods. The first three transactions, and their computations, will be the same as well:

➤ Now, for the sales, we are going to average out the cost by dividing the inventory valuation by the inventory units. Use the following formula in the F5 cell:

➤ Copy cell F5 to all the sales from now, and Excel will fix the cell references automatically.
➤ To calculate the COGS, use the same formula from FIFO and LIFO:

Frequently Asked Questions

Why do we calculate COGS?

COGS is usually calculated as part of the gross profit calculation of a business. However, it is required to create all of the financial statements of a company.

What are COGS called today?

In accounting, COGS is often referred to as direct costs. However, COGS usually does not exist in a service business, and not all businesses include direct labor and commission in the COGS. Therefore, it is not a widely used term for COGS.

Is COGS an asset or expense?

Cost of goods sold is an expense that was incurred to sell the goods for profit. It is not an asset, although it goes into the debit, which might confuse some people.

Are COGS higher under LIFO or FIFO?

In the LIFO method, the COGS is higher and the inventory is lower. In the FIFO method, the COGS is lower than in the LIFO method, while the ending inventory is usually higher. This happens because, under inflation, the cost of goods increases, and FIFO counts the items that were bought first.

Are COGS still used today?

Yes, but only for companies that sell physical goods. COGS is irrelevant for companies that sell services to their customers. Instead, the term “direct cost” is used for those businesses.

Wrapping Up

In this article, we have learned four methods of calculating COGS in Excel. Some methods were more sophisticated than others, but the article tries to teach you everything there is to know about COGS, and we have not cut a corner doing this. We hope that you learned something from this tutorial, and as always, stay tuned, and we will see you in the next one.