In any business, cost per unit calculation is a value calculation. This calculation is defined as a total cost, which includes all costs associated with production, marketing, delivery, warehousing, sales, management, etc., for only a unit of the product. The article will take you through how to do this in Excel.
What is cost per unit?
Cost per unit refers to the amount of money it takes a company/business to produce one unit of a product. It includes both the fixed and variable costs used to produce and deliver one unit. By monitoring cost per unit, businesses set a unit price that guarantees profit.
Cost per unit reflects the efficiency of your business. Breaking down the cost per unit can help you identify the inefficiencies that are eating your profit margins.
Here is the formula for calculating cost per unit in Excel
Cost per unit = (total fixed costs + total variable costs) / total units produced
Where by
Fixed Costs: They are costs that remain unchanged regardless of the production in the period of time. Examples include salaries, insurance fees, property tax, rent or Lease payments, depreciation, interest payments, and utilities.
Variable Costs: These costs change by the amount of production. Basically, it is a cost that varies with the units of products produced. Components of variable costs include direct materials, direct labor, commissions, production supplies, billable staff wages, etc.
Total Units Produced: It refers to the number of goods that are sold/ served.
Example
A company called CerealsCo produced 10000 units of 90kg bags of premium cereals in September 2023. The fixed costs for that month, such as rent and salaries, amounted to $50,000.
On the other hand, the variable cost, which includes labor, electricity, and raw materials, amounted to $10,0000
Let’s calculate the cost per unit for this business.
Cost Per Unit=(Total Fixed Costs +Total Variable Costs)/units produced
Therefore ($50,000 +$10,000)/1000 =$6
1. To calculate Cost per unit in Excel, you need to arrange your information as follows:
2. You can use the sum formula to calculate the total fixed cost and the total variable cost. =SUM(I6:I9)
3. Once you get the total fixed cost and total variable cost in Excel, then use the following formula to calculate cost per unit.
=(D4+D5)/D6
You need to put the total fixed cost and total variable cost in brackets
4. Finally, press enter
Cost Per Unit Excel Calculator
Online Cost Per Unit Calculator
Enter the total fixed costs, total variable costs, and the total units produced to calculate the cost per unit.
How to calculate cost per unit in Excel
1. Classify your variable and fixed costs.
2. Know how many units you are producing.
3. Launch Excel and open the worksheet that you want to calculate the cost per unit in Excel
4. Create three tables in an open workbook (Fixed cost, variable cost, and Calculation table). See the Screenshot example.
Note: Your table features may vary from the example.
1. In the fixed and variable cost table, insert all the items that belong to the table and their corresponding costs.
2. In the calculation table, insert the time frame, whether Annual or monthly. Besides, the total units produced.
3. In this example, say it involves variables like direct labor, rent, direct material, insurance fee, commissions, salaries, billable staff wages, depreciation, shipping cost, property tax, materials, shipping cost, and utilities. The table will look like this.
4. In the Variable and Fixed tables, Calculate the cost per unit, which is the Total direct cost divided by the Total Unit produced. Therefore, click cell D6.
5. Then insert the formula =C6/$K$6. K6 is made to be an absolute reference to make it easy to copy and paste the formula to other cells of the column. Drag the formula up to D11 using the fill handle.
6. Repeat the same process for H6 and insert =G6/$K$6. Drag the formula up to H12 using the fill handle.
7. Calculate the Total fixed cost and its Cost Per Unit using the SUM function. The total fixed cost calculation will be =SUM(C6:C11). Then use the fill handle and drag right to get the total cost per unit.
8. Calculate the Total variable cost and its Cost Per Unit using the SUM function. The total variable Cost calculation will be =SUM(G6:G12). Then use the fill handle and drag right to get the total cost per unit.
9. In the Calculation table, calculate the total cost. Then add the total fixed cost with the total variable cost. Click cell K7. Insert the formula =C14+G14
10. Repeat the same procedure for the calculation of the total cost per unit. Add the total fixed cost per unit with the total variable cost per unit. Click cell K8. Insert the formula =D14+H14.
Note: total cost per unit can also be calculated as =K7/K6
11. There you go. You have your cost per unit is $180.61.