This Excel tutorial demonstrates how to calculate the selling price. You’ve put in a lot of effort to manufacture your products, and they’re ready to sell. Are you, however, undervaluing your items when it comes to the price? Or are you overpricing them? To build a successful business with products or services, a good selling price is so much crucial. With selling prices, a businessman must cover the cost of raw materials, production, logistics, and also his profit. As a result, a reasonable selling price must be established. Here, we will take you through 4 easy and convenient methods on how to calculate the selling price in Excel.
What Is Selling Price?
Before calculating the selling price, we all must have to know what the selling price actually is. The selling price is the amount that buyers pay for a product or service. In addition, the selling price depends on other factors as well. Those can be:
- The amount a buyer is willing to pay for a product or service.
- The amount a seller is willing to take as payment.
- The price is competitive with other products of the same type in the market.
- The quality of the product: premium or low-cost.
- Your brand value in the market
Arithmetic Formula to Calculate Selling Price
Onward of calculating the selling price in Excel, we should know the basic mathematical equations of the selling price. To calculate the selling price, the following formulas could be useful.
If we have the Cost and our expected %Markup, then the formula for calculating the Selling price would be like that:
Selling Price = Cost * (1 + %Markup)
On the other hand, if we have the Cost and our expected %Margin, then the formula is as follows:
Selling Price = Cost / (1 - %Margin)
How to Calculate Selling Price in Excel: 4 Methods
It’s very important to understand how to determine the selling price because your company will not survive if you don’t generate a profit. Besides this, you have to secure a strong market position. In a nutshell, knowing how to calculate a product’s selling price prosperously is a win-win situation for both you and your customer. They receive a good deal, and you get a fair price if it’s done correctly.
Suppose, you’ve got a List of Products containing Product Names in column B and Costs in column C.
You’ve to calculate the Selling Price of it. Are you afraid? Don’t be so. Here, we’ll show you 4 handy methods of calculating the selling price in Excel.
Previously, we came to know that, the formula for calculating Selling Price from Cost and Markup is:
But, do you have this question in your mind? What is Markup?
The difference between the selling price of a good or service and the cost is known as markup. It’s usually given as a percentage of the total cost. And that’s called %Markup. To cover the costs of doing business and make a profit, a markup is added to the overall cost borne by the manufacturer of a good or service.
Let’s say, something costs $50 and the markup is 100%, then the selling price will be $(50+50)= $100.
If we use our formula,
Selling price = $50 * (1 + 100%) = $50 * (1 + 1) = $100 [ ∵ 100% means the whole portion]
To do the same task more easily in Excel, follow our steps below.
- At first, select cell E5, paste down the formula below, and press ENTER.
- Then, use the Fill Handle tool to complete the lower portion of column E.
2. Calculating Selling Price from Cost and Margin
From the mathematical formula stated above, we are already acknowledged of calculating the Selling Price from Cost and Margin.
Before exploring further about this method, we’ll inform you what the margin is.
The profit generated after expenditures are deducted from the selling price is referred to margin in the business. It’s also expressed as a percentage. But the difference between Markup and Margin is that Margin is the percentage of Selling Price whereas Markup is the percentage of Cost. To make it more understandable, we are giving it mathematically.
Markup = (Selling Price - Cost) / Cost
Margin = (Selling Price - Cost) / Selling Price
Anyway, follow our steps to calculate the selling price from cost and margin in Excel.
- Firstly, select cell E5, type down the formula below, and press ENTER.
3. Calculating Rounded Selling Price in Excel
In our previous methods, the selling prices come in decimal numbers. Which is sometimes troublesome to maintain. To get out of this trouble, we’ll use the ROUNDUP function. Go after the steps Carefully.
- In the beginning, select cell F5, write down the formula below, and tap ENTER.
Here we took Num_digits as 0 because we want to round up our result to the nearest integer.
- Lastly, use the Fill Handle tool and drag it down to get the remaining results.
4. Applying VBA Code to Calculate Selling Price in Excel
Using VBA code, you can easily create a function to calculate Selling Price and later use that function. Now, we’ll show you how to create and utilize the function to calculate the selling price in Excel.
- Right-click on the Sheet name and select View Code.
- Instantly, the Microsoft Visual Basic for Applications window opens. Right-click on Sheet4 (VBA) > select Insert > Module.
- It opens a code module, where paste the below code down and clicks on the Run button or press F5.
Public Function SellingPrice(Cost As Double, Markup As Double) A = Cost B = Markup SellingPrice = A * (1 + B) End Function
- Now closing the code module return back to the worksheet. Select cell E5 and start typing down =Se, you can automatically see the function SellingPrice which we have just created. Then, select the function with your cursor and press TAB.
- Later, set the variable Cost and Markup by clicking on cells C5 and D5. Then close the parenthesis, and press ENTER.
You can see the same results as method 1 as we worked with markup in our function.
Things to Remember
- Always ensure that the Cost and Selling Price column is formatted in Accounting.
- Also, be careful to format the Markup or Margin column in Percentage.
- Remember the distinction between Markup and Margin. Again we are giving an example to clear your doubt. If a Product costs $50 and is sold for $100, then its Markup is 100% and the Margin is 50%.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.