How to Calculate Cost per Unit in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

Calculating cost per unit is very important in every business and an Excel template will be very useful for you. You can call it a Product Costing Template, Manufacturing  Cost Calculation Template, Average Cost per Unit Calculator, or Production Cost Calculator in Excel. So, in this article, I am sharing with you a free template to calculate the cost per unit in Excel, along with the procedure to make it.


What Is Cost per Unit?

Cost per unit defines total cost which includes all costs associated with production, delivery, marketing, sales, warehousing, management, etc but for a unit of the product only.

It is very important to set a price which can make a profit for you. Cost per unit makes a relationship between various types of costs and profits so you can take decisions easily on what to do to make a profit. It is a must to make a pricing strategy.

All types of costs are of two types of costs. 1. Fixed Cost 2. Variable Cost

Formula for Cost per Unit:

Cost per unit = (Total fixed costs + Total variable costs) / Total units produced


What Are Fixed and Variable Costs?

Fixed Cost:

Fixed cost includes the costs which remain unchanged whatever the production is in a time period.

The business owner has to pay for the fixed costs in time even if there is no activity in production. So fixed cost per unit will decrease if the production volume will increase and cost per unit will increase if the production volume decreases.

Components of Fixed Cost:

  • Rent or Lease Payments: The charge to pay for the assets which the company has either rented or leased for a certain period.
  • Salaries: The fixed payment that disburses to the employees on a regular basis.
  • Insurance Fee: The routine charge for any insurance service of the company.
  • Property Tax: The tax that the company has to pay to the government according to the value of assets.
  • Interest Payments: The charge that the business needs to pay on the loan taken by the company. It will become a fixed cost if the interest rate become fixed or the repayment amount is fixed for a period.
  • Depreciation: Depreciation cost is the cost to overcome the gradual decrease of asset value over a time period.
  • Utilities: It includes all other types of costs which the company has to pay after a time period regularly like electricity, gas, etc bills.

Variable Cost:

Variable cost includes the costs which change by the amount of production. All the costs which vary with the units of product produced will be listed as Variable Costs.

So, the variable cost will increase when the production level increases and decrease when the production level decreases. It is a proportional relation.

Components of Variable Cost:

  • Direct Material: The raw materials that the product contains in itself.
  • Direct Labor: The wages paid to the workers as per the amount of the unit produced. It is the variable cost when the workers are on a temporary contract which depends on the production volume. For the permanent workers, it will be listed as a fixed cost.
  • Billable Staff Wages: Sometimes, the company does pay wages on the billable hours. It can be due to overtime of work or temporary hiring.
  • Commissions: In business, to motivate the salespersons, the company pays a commission to them on the sold products.
  • Production Supplies: The necessary tools and supplies which vary with the production level like machinery oils.
  • Shipping Cost: During the shipping of a product, the company pays the shipping cost which varies with the number of units.

How to Calculate Cost per Unit in Excel: Steps-by-Steps Procedures

Suppose, you have a product and you want to make a pricing strategy and for this, you want to calculate the cost per unit of a product. In this article, I will show you how to make an Excel template to calculate the cost per unit of a product.

How to Calculate Cost Per Unit in Excel


Step 1: Make a Template Layout

  • First, make 2 tables for listing the fixed costs and variable costs.
  • Then, make a place to input the quantity of production.
  • And finally, make a place to get the result of cost per unit of the product.

How to Calculate Cost Per Unit in Excel

  • Now, the layout of the template is ready.

Step 2: Insert List of All Costs and Corresponding Values

  • Then, input the data of the quantity of product manufactured in the time period.
  • After that, input the list of fixed costs and variable costs with their amount.

How to Calculate Cost Per Unit in Excel


Final Step: Insert Formulas to Calculate Cost per Unit

  • Now, you have to calculate the costs per unit for each cost in the list.
  • So, insert this formula into cell G6.
=F6/$C$4

So, it will divide the cost of total direct material cost in cell F6 by the number of products in cell C4. Make cell C4 of the absolute reference. So you can copy and paste the formula to the other cells of the column to divide by the product quantity also.

How to Calculate Cost Per Unit in Excel

  • Now, paste the formula to the other cells by dragging the Fill Handle icon or use the shortcuts Ctrl+C and Ctrl+P to copy and paste.
  • Similarly, do the same thing for the fixed costs. Insert this formula into cell K6.
=J6/$C$4

So, it will divide the value of Rent in cell J6 by the product quantity in cell C4 to get the rent cost per unit product.

How to Calculate Cost Per Unit in Excel

  • Now, use the SUM function to calculate the total of each table. Insert this formula into cell G13 to get the total variable cost per unit.
=SUM(G6:G12)
  • And, use this formula in cell K13 to get the total fixed cost per unit product.
=SUM(K6:K12)

Insert Formulas to Calculate

  • Finally, you have to add the total fixed cost per unit with the total variable cost per unit to get the total cost per unit of the product. Insert this formula into the cell C7:
= G13+K13

Insert Formulas to Calculate

 


Things to Remember

You can add new rows in the Fixed Cost and Variable Cost table to insert new costs without changing the formula. To do this follow the steps below:

  • First, right-click on the 13th row on the leftmost button.
  • Then, select the Insert option.

add new rows in excel

  • As a result, there will create a new row above the final sum.
  • So, you can input there the new list of costs and it will update automatically.

add new rows in excel


Download Sample Workbook

You can download the practice workbook from here:


Conclusion

In this article, I am sharing a free template to calculate the cost per unit of a product in Excel. Also, here you have found a brief idea about what is the cost per unit, fixed cost, and variable costs. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.


<< Go Back to Cost | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo