ExcelInsider Team
In a retail shop, calculating the selling price often becomes the responsibility of the shop owner. While a lot of people feel comfortable calculating the selling price using the markup amount, utilizing the margin is not uncommon. In this article, we will learn how to calculate selling price from cost and margin in excel.
➤ Select the cell for the output and write the formula:
=D2/(1-25%)
➤ Replace D2 with the cell that has the cost, and 25% with the margin percentage.
➤ If you are calculating using a table, you might want to autofill other cells in the table for easier calculation.
If you want more of a detailed guide, this tutorial will have the details for you below. Therefore, read the whole guide to get insights into the method and learn how it works.
Steps to Calculate Selling Price from Cost and Margin in Excel
In general, most people will calculate the selling price using the profit markup. For example, if you have a product that costs 80 dollars to make, and you want to make a 20% profit, you will sell it for 80+(80*20%) = 96 dollars.
However, using the margin becomes relevant when you have to calculate the profit you earned on the selling price. This means that to make 20% profit like before, you have to sell the product for 100 dollars, and 100*20%=20 dollars will be your profit. The calculation becomes way different now, so let’s find out how to do it in Excel.
For demonstrating the calculation, we have a dataset that contains some product items. There are the IDs of the products, the locations where the products will be shipped, the product name, and the cost of the products.
Step 1: Adding a Helper Column
First, let’s add a helper column for the selling price. The steps to do that are as follows:
➤ We have a data range instead of a table. So, Excel will not add a column for us automatically. In the E1 cell, write Selling Price ($)
➤ Select all the cells in the Cost ($) In the Home tab, go to the Clipboard section and click on the Format Painter icon.
➤ While you have the Format Painter icon on your mouse, select cell E1 to E10 to complete adding the column.
Step 2: Apply the Price Margin Formula
Now that we have a helper column, let’s use the formula to calculate the selling price.
➤ Write this formula in the E2 cell.
=D2/(1-25%)
➤ Press Enter.
➤ Find the small handle on the bottom right corner of the E2 cell that looks like a plus (+) sign. When you find that, click and drag to the E10 cell to fill all of the cells.
Frequently Asked Questions
How do you calculate sale price in Excel?
For calculating the sale price, use this formula:
=A1*(1-20%)
In this formula, A1 is the original price of the product, and 20% is the discount that you are providing in the sale.
What is the correct formula for selling price?
In general, the formula for calculating selling price is Cost + Profit. Hence, the formula in Excel should be written like the following:
=A1+B1
Here, A1 is the cost, and B1 is the amount of profit.
How to find average selling price in Excel?
You can use the generic average calculation to find the average selling price in Excel. One of the two formulas can be used:
=AVERAGE(A1:A10)
In this formula, the AVERAGE function will calculate the average selling price from the selling prices in A1:A10.
=SUM(A1:A10)/10
Here, the SUM of A1:A10 is calculated first, and then divided by 10, as that many selling prices are counted.
What is the formula for cost of sales sold?
Cost of sales sold or cost of goods sold can be calculated using the formula below:
=(A1+B1) – C1
Here, A1 is the inventory at the beginning of the year, B1 is the purchases in the year, and C1 is the inventory at the end of the year.
How to calculate sales formula in Excel?
Here is the formula to calculate sales percentage:
=(A1 / B1) * 100
A1 is the amount of sales, and B1 is the total amount in this formula.
Wrapping Up
In this article, we have learned how to calculate selling price from cost and margin in excel. We hope that we have been able to make the concept of margin and its calculation clear to you. If you have any other issues calculating in Excel, please leave your concerns below, and we will try to address them in the future.
