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?
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?
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.
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.
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.
- 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.
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.
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.
- 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.
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.
- 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.
- And, use this formula in cell K13 to get the total fixed cost per unit product.
- 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:
Read More: How to Calculate Price Per Pound in Excel
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.
- 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.
Download Sample Workbook
You can download the practice workbook from here:
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.