How to Calculate Cost of Goods Sold in Excel

What is Cost of Goods Sold?

Cost of goods sold (COGS) is the total cost of producing or purchasing the goods or services that a business sells in a given period. It includes the cost of materials, labor, and overheads that are directly related to the production or acquisition of the goods or services. COGS is an important metric for measuring the profitability and efficiency of a business, as it reflects how well the business manages its inventory and production costs.

How to Calculate Cost of Goods Sold?

There are different methods for calculating COGS, depending on the type and nature of the inventory. The most common methods are:

• First-in, first-out (FIFO): This method assumes that the first units of inventory purchased or produced are the first ones to be sold. Therefore, the COGS is based on the oldest inventory costs.
• Last-in, first-out (LIFO): This method assumes that the last units of inventory purchased or produced are the first ones to be sold. Therefore, the COGS is based on the newest inventory costs.
• Average cost: This method calculates the average cost of all the units of inventory available for sale during the period. Therefore, the COGS is based on the weighted average of the inventory costs.

The general formula for calculating COGS is:

$$\text{COGS}=\text{Beginning Inventory}+\text{Purchases}-\text{Ending Inventory}$$

However, this formula may vary depending on the method used and the type of inventory. For example, for manufacturing businesses, the formula may include the cost of direct materials, direct labor, and manufacturing overheads.

How to Calculate Cost of Goods Sold in Excel?

To calculate COGS in Excel, you need to have the following data:

• The beginning inventory value at the start of the period
• The purchases or production costs of inventory during the period
• The ending inventory value at the end of the period
• The method of inventory valuation (FIFO, LIFO, or average cost)

You can use the following steps to calculate COGS in Excel:

1. Enter the data in a worksheet, such as the one shown below:

2. Choose the method of inventory valuation and enter it in a cell, such as E1. For example, enter “FIFO” in E1.
3. Use the IF function to apply the formula for COGS based on the chosen method. For example, enter the following formula in F2 and drag it down to F4:

=IF($E$1="FIFO",B2+C2-D2,IF($E$1="LIFO",B2-D2+C2,IF($E$1="Average",(B2+C2)/2-D2,"Invalid Method")))

This formula will calculate the COGS for each product using the FIFO, LIFO, or average cost method, depending on the value in E1. If the value in E1 is not one of these methods, it will return “Invalid Method”. 4. Use the SUM function to calculate the total COGS for all products. For example, enter the following formula in F5:

=SUM(F2:F4)

This formula will add up the COGS for each product and give the total COGS for the period.

The final worksheet should look like this:

Example

To illustrate how to calculate COGS in Excel, let’s use a simple example scenario. Suppose you run a bakery that sells three types of bread: white, wheat, and rye. You want to calculate the COGS for the month of January 2024, using the FIFO method. Here are the data you have:

• The beginning inventory value of each type of bread on January 1, 2024
• The purchases or production costs of each type of bread during January 2024
• The ending inventory value of each type of bread on January 31, 2024

The data are shown in the table below:

To calculate the COGS for each type of bread and the total COGS for the bakery, you can follow the same steps as above. Enter the data in a worksheet, choose the FIFO method in E1, use the IF function to apply the formula for COGS in F2:F4, and use the SUM function to calculate the total COGS in F5. The final worksheet should look like this:

The result shows that the COGS for the bakery for January 2024 is $6800, using the FIFO method. This means that the bakery spent $6800 to produce or purchase the bread that it sold in January 2024.