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

What Is Cost per Unit?

The cost per unit includes all costs associated with production, delivery, marketing, sales, warehousing, management, etc. per unit of the product.

Formula for Cost per Unit:

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

Fixed Cost:

Fixed costs remain unchanged regardless of the production volume in a period.

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 costs scale with the production volume or amount.

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: Step-by-Step Procedure

Here’s a simplified template that we’ll use to determine the cost per unit of a product.

Step 1 – Make a Template Layout

Make 2 tables for listing the fixed costs and variable costs.
Input the quantity of production.
Make a place to get the result of cost per unit of the product.

Step 2 – List All Costs and Corresponding Values

Input the quantity of product manufactured in the time period.
Input the list of fixed costs and variable costs with their amounts.

Final Step – Insert Formulas to Calculate Cost per Unit

Insert this formula into cell G6.

=F6/$C$4

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.
Insert this formula into cell K6.

=J6/$C$4