How to Calculate Cost Per Unit in Excel (3 Effective Ways)

In a product-based business, determining the cost per unit is crucial for setting prices and calculating profit. Cost-per-unit calculations are useful for inventory management, pricing strategies, and project control. In this article, we will learn how to calculate cost per unit in Excel.

Key Takeaways

➤ Write this formula in the output cell:
=D2/C2
➤ Replace D2 with the total cost cell, and C2 with the unit quantity cell.
➤ Autofill other cells if required.

That method can only be applied to a specific calculation in business. However, in an industry, you will have to deal with various situations where you will have to use other approaches to calculate the cost per unit. In this tutorial, we will show all the tactics you can use to do that. Therefore, don’t miss any of the methods described below.

Download Practice Workbook

What is Cost Per Unit Calculation?

Cost per unit is the summation of fixed and variable costs for each unit of a product. In manufacturing businesses, products are usually produced in large quantities. As a result, it is often not possible to calculate the cost of each unit while producing that unit. Moreover, there are differences between fixed and variable costs as well.

In order to determine the selling price, profit per unit, project costs, cost sector etc. a business must calculate the cost per unit.

Calculating Cost Per Unit Using Total Cost

In this dataset, we have the shipment IDs of some products, their corresponding product types, the unit counts, and the total costs. We don’t have to worry about variable or fixed costs here, which makes the calculation easier. Proceed with the steps below:

➤ Take a column on the right to calculate the cost per unit.
➤ Copy this formula to the cell:

=D2/C2

Explanation

Here, we divide the total cost by the unit count, which is the basic formula for calculating cost per unit. Here. D2 is the total cost, and C2 is the quantity of units; you will have to change these cell references according to your dataset.

➤ Press Enter, then find the (+) sign on the lower right edge of the E2 Drag that using your mouse to the E13 cell to autofill all of the rows in the column.

Determining Cost Per Unit Using Total Fixed Cost

Usually, the fixed costs in a factory are incurred no matter how many products are produced. The variable costs change, but the fixed costs don’t. In this example, we have a dataset with a fixed cost associated with each product category, along with variable costs that vary for each shipment.

➤ The formula below will calculate the cost per unit:

=(E2/C2)+D2

Explanation

Here, the total fixed cost is in the E2 cell. We divide that by the unit count in C2 to obtain the fixed cost per unit. Finally, we add it to the variable cost per unit in D2 to calculate the total cost per unit.

➤ Press Enter and use the autofill drag handle to fill other rows in the column.

Finding Cost Per Unit with Fixed Cost Per Unit

In the previous example, there was a total fixed cost that we divided to get the cost per unit. However, it is possible that the fixed cost is fixed per unit rather than for the entire batch. In order to calculate the cost per unit using those values, the steps below need to be followed:

➤ Write the formula in a column to the right of the table like you did for the previous methods:

=(C2*(E2+D2))

Explanation

We are multiplying the units in C2 with the fixed cost per unit from E2 and the variable cost per unit from D2.

➤ Autofill other cells after you press Enter.

Frequently Asked Questions

What is the formula for cost per unit?

The formula for cost per unit is Total Cost/Units Produced. However, the formula might change for different scenarios. Another commonly used formula for cost per unit is Fixed Cost Per Unit + Variable Cost Per Unit.

How do I calculate units in Excel?

Divide the total cost by the cost per unit to calculate the number of units in Excel. The formula can be structured like this:
=B2/A2
Here, B2 is the total cost, and A2 is the cost per unit.

How to calculate total cost in Excel?

Add the variable cost to the fixed cost in order to calculate the total cost in Excel. The formula should be written as follows:
If the fixed cost is per unit:
=A1+B1
In this formula, A1 is the fixed cost, and B1 is the variable cost.
If the fixed cost is for the whole batch:
=A1/C1+B1
Here, C1 is the unit count, and the rest are the same as the previous formula.

How to calculate in Excel formula?

You can use the signs in a formula to do the calculations. The equal sign (=) at the front denotes that a formula is being written here. Then you can use the cell names and the math signs like plus, minus, asterisk, and forward slash ( + – * / ) to do the job.

What is per unit calculation?

Some companies calculate using all of the production costs at once. That is useful for smaller companies, but bigger ones use per-unit calculation, where the fixed costs are normalized per unit to extract the average cost for each and every unit.

Wrapping Up

In this tutorial, we have calculated the cost per unit in Excel using three methods. All of those methods are useful for unique situations, and we hope that you have learned how to calculate cost per unit in excel from this article. The workbook for this tutorial can be accessed and used for free. We love your feedback, so don’t forget to leave some below.

Post Views: 22