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.
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.
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.
