Looking for ways to know how to calculate selling price per unit in Excel? Then, this is the right place for you. In regular life, we have to calculate different parameters, especially purchasing various products. Sometimes we need to calculate the selling price per unit for those products. Here, you will find 3 different step-by-step explained ways to calculate selling price per unit in Excel.
Download Practice Workbook
What Are Margin and Markup?
The profit made from the selling of a product or service is known as the sales Margin.
It’s used to look at earnings at the level of a single sale transaction rather than a whole organization.
The equation for calculating Margin,
Margin (%) = (Selling Price - Cost) / Selling Price
The amount by which the cost of a product is increased to get at the selling price is known as Markup.
The equation for calculating Markup,
Markup (%) = (Selling Price - Cost) / Cost
The parameters used to calculate Margin and Markup are the same, but they return different values. The basis of Markup is cost and for Margin it is revenue. So, when the cost is changed over time, the Markup value is more likely to change than the Margin value.
What Is Selling Price Per Unit?
Selling Price is the final price of any product or the price at which any customer will buy the product. Selling Price Per Unit is the price of 1 unit of that product.
The equation for calculating Selling Price Per Unit,
Selling Price Per Unit = Total Selling Price / No of Products
3 Ways to Calculate Selling Price Per Unit in Excel
Here, we have the following dataset containing the No and Cost of some Products. We will calculate the selling price per unit of these products by using the following methods.
1. Calculating Selling Price Per Unit with Margin
For the first method, we will calculate the selling price per unit with Margin. Here, for the dataset, the Margin value is 20%.
Follow the steps given below to do it on your own.
Steps:
- In the beginning, select Cell E5.
- Then, insert the following formula.
=D5/C5
Here, we divided Cost of the products by No of Product to get the Cost Per Unit.
- Now, press ENTER.
- Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Thus, you will get the Cost Per Unit of these products.
- Next, select Cell F5.
- Then, insert the following formula.
=E5/(1-$C$11)
Here, first, we Subtracted the Margin value from 1 (Here, 1 means 100%) then we divided the Cost Per Unit by the subtracted Margin to get the value of the Selling Price Per Unit.
- After that, press ENTER.
- Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Finally, you will get the Selling Price Per Unit for the products using Margin.
Read More: How to Calculate Selling Price from Cost and Margin in Excel
Similar Readings
- How to Calculate Price Increase Percentage in Excel (3 Easy Ways)
- Calculate Price Per Pound in Excel (3 Easy Ways)
- How to Calculate Coupon Rate in Excel (3 Ideal Examples)
- Calculate Retail Price in Excel (2 Suitable Ways)
- How to Calculate Weighted Average Price in Excel (3 Easy Ways)
2. Using Markup to Calculate Selling Price Per Unit
We can also calculate the selling price per unit using the Markup value. Here, for the dataset, the Markup value is given as 15%.
Go through the following steps to calculate the selling price per unit for your dataset.
Steps:
- First, select Cell E5.
- After that, insert the following formula.
=D5/C5
Here, we divided the Cost of the products by No of Product to get the Cost Per Unit.
- Now, press ENTER.
- Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Thus, you will get the Cost Per Unit of the products.
- Now, select Cell F5.
- Then, insert the following formula.
=E5*(1+$C$11)
Here, first, we Added the Markup value with 1 (Here, 1 means 100%) then we multiplied the Cost Per Unit with the added Markup to get the value of the Selling Price Per Unit.
- After that, press ENTER.
- Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Finally, you will get the Selling Price Per Unit of the products using Markup.
Read More: How to Calculate Variable Cost Per Unit in Excel (with Quick Steps)
3. Use of Generic Formula to Calculate Selling Price Per Unit
Finally, we can also calculate the selling price per unit using the Generic Formula. Here, we have a dataset of Products containing the No of Products and Total Selling Price. We will calculate the Selling Price Per Unit of these products using the Generic Formula.
Follow the steps to do it on your own.
Steps:
- In the beginning, select Cell E5.
- After that, insert the following formula.
=D5/C5
Here, we divided Total Selling Price of the products by No of Product to get Selling Price Per Unit.
- Then, press ENTER.
- After that, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Finally, the Selling Price Per Unit of these products is calculated using the Generic Formula.
Read More: How to Calculate Unit Price in Excel (3 Quick Methods)
Practice Section
You will get a dataset like the image given below in this article to practice on your own.
Conclusion
So, in this article, you will find 3 ways to calculate selling price per unit in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit ExcelDemy for many more articles like this. Thank you!
Related Articles
- How to Calculate Production Cost in Excel (3 Effective Ways)
- Calculate Average Price in Excel (7 Useful Methods)
- How to Calculate Average Selling Price in Excel (3 Easy Methods)
- Calculate Price Per Square Meter in Excel (3 Handy Methods)
- How to Calculate Bond Price in Excel (4 Simple Ways)
- Calculate Projected Cost in Excel (4 Effective Ways)
- How to Calculate Discount Price in Excel (4 Quick Methods)