By Rosana Antoni
Edited by Kirsty Macdougall
Updated May 5, 2023.
According to the NYU Stern School of Business, the average gross profit margin across all industries is 36.28%, with online retailers generating an average gross profit of 42.78%. How does yours compare?
As an e-commerce entrepreneur, knowing your profit margin and focusing on the factors that influence your profits and contribute to a robust bottom line is crucial. Grasping the concepts of margin and markup is a key component of this process, and thankfully, Excel makes calculating these vital metrics a breeze!
In this post, we'll delve into the top Excel formulas for determining markup and margin and how you can leverage them to make informed pricing decisions and boost your profitability.
Understanding the Difference Between Markup and Margin
Markup
Markup refers to the amount you charge above the cost to cover expenses and generate a profit.
In the marketplace, markup is expressed as a percentage, that is, the percentage added to the cost of a product or service to determine its selling price. To work out your markup, you need to accurately calculate the cost of goods sold (COGS).
Margin
Margin represents the portion of each sale that contributes to covering expenses and generating profit.
We also express margin as a percentage: the percentage difference between the selling price and the cost expressed as a percentage of the selling price. So, in summary:
- Markup is the percentage increase in price over the cost of the product
- Margin is the profit made as a percentage of the selling price
Below are three key pairs of Excel formulas you can use to calculate your markup and margin.
» Learn more about the so you don't get them confused
1. Basic Percentage Formula
To gain a better insight into a company's pricing strategy and make informed decisions on pricing and sales, we can use two basic formulas that apply to markup and margin.
Markup Calculation Formula
Markup = [(Selling Price - Cost) / Cost] x 100
For example, if a T-shirt costs $23 to produce and the company wants to sell it at a price of $30, the necessary markup would be [($30 - $23) / $23] x 100. This works out to be a markup of 30.43%.
To calculate this in Excel, enter the cost and selling price in separate cells, such as A2 and B2. In cell C2, input the formula:
=(B2-A2)/A2*100
Alternatively, enter the formula =(B2-A2)/A2 and select "Percentage" from the dropdown list in the "Number" field in the top menu. This automatically expresses the value as a percentage.
» Simplify calculations and optimize profits. Book a demo for BeProfit's profit-tracking app
Margin Calculation Formula
Margin = [(Selling Price - Cost) / Selling Price] x 100
Using the same example as above, your calculation would be [($30 - $23) / $30] x 100. The gross margin, therefore, works out to be 23.33%.
As with the markup formula, input the cost and selling price in separate cells, such as A2 and B2. In cell C2, enter the formula:
=(B2-A2)/B2*100
Or you can enter the formula =(B2-A2)/A2 and select "Percentage" in the "Number" field in the top menu.
2. Sales Price Formula
Considering both the cost of producing the product and the desired profit margin is something businesses must do when setting a sales price. Sale prices are another key factor in margin and markup calculations.
Sales Price Using Markup
To calculate the sales price with the desired markup in mind, use this formula:
Sales Price = [1 + (Markup/100) x Cost Price]
Using the same figures are the previous examples, the sales price = [1 + (30.43/100) x 23]. This means your sales price should be $30.
An Excel formula is a much quicker way to calculate the sales price. If you enter the cost price in the cell A2 and the markup in cell C2, the formula you would enter in cell B2 is:
=(1+C2)*A2
Make sure that for B2, you select "Number" from the dropdown list from the top menu, as you can see below:
Sales Price Using Margin
To determine the sales price using the profit margin, use this formula:
Sales Price = Cost Price / [1 - (Margin Percentage/100)]
For example, if you want to achieve a margin of 23.33% on the T-shirt that costs $23, your sales price = $23 / [1 - (23.33/100)]. This works out to be $30.
For a simpler way to calculate the sales price, you can use this formula in Excel:
=A2/(1-C2)
Again, be sure to select "Number" from the dropdown list for cell B2, as you can see below:
By using these sales price formulas, businesses can ensure that they're setting prices that will result in the desired profit margins.
3. Discount Formula
Another common strategy businesses use is discounts to attract customers and increase sales. When it comes to margin and markup calculations, discounts can affect profit margins. If a company offers a discount on a product, the sales price will decrease, which may impact the margin percentage.
Firstly, if you have a discount percentage in mind, you need to calculate what the new discounted sales price will be. To do that, use this formula:
Discounted Sales Price = Selling price x [1 - (Discount Percentage/100)]
To illustrate with our same example, to work out the discounted sales price for our T-shirt with a 10% discount, the calculation would be: discounted sales price = $30 x [1 - (10/100)]. This works out to be $27.
To calculate this in Excel, enter the sales price in cell A2 and the discount percentage in cell B2. Then input this formula in cell C2:
=A2*(1-B2)
Remember to select "Percentage" from the dropdown list for cell B2 and use "Number" for cells A2 and C2.
Once you have this amount, you can work out your new markup and margin values. Use the basic percentage formulas, but replace the original selling price with the discounted sales price you just calculated.
This means the new markup formula will be:
New Markup = [(Discounted Sales Price - Cost) / Cost] x 100
Using our example, the new markup after the discount = [($27 - $23) / 23] x 100. This comes to 17.39%.
The formula to calculate the new margin will be:
New Margin = [(Discounted Sales Price - Cost) / Discounted Sales Price] x 100
Using our example, the new margin = [($27 - $23) / $27] x 100. This works out to 14.81%.
You can use the same basic percentage formulas in Excel, but just make sure you replace the selling price in cell B2 with the discounted sales price. In other words, replace $30 with $27.
By calculating the new markup and margin after applying a discount, you can better understand the impact of the discount on your profits. This will help you make informed decisions about whether or not to offer a discount and how to adjust your pricing strategy to maintain profitability.
» Looking for more help with discounts? Check out more easy Excel discount formulas
Factors That Can Impact Markup and Margin
We already established that markup and margin are key metrics that impact businesses and their profitability. However, several factors can impact these metrics, including competition, market demand, production costs, and pricing strategies.
In highly competitive markets, businesses may need to lower their prices to remain competitive, which can impact both markup and margin. Production costs, including raw materials, labor, and overhead, can also impact markup and margin. Finally, different pricing strategies can impact these metrics, as businesses may need to adjust their prices to reflect changes in their overall pricing strategy.
» Remember to keep your pricing strategy flexible. Learn how with dynamic pricing
Calculate Markup and Margin in Excel for Effective Pricing
In today's competitive market, it's crucial to calculate markup and margin accurately to ensure profitability and sustainability. Using the above formulas in Excel is a simpler, more time-efficient, and more accurate way to calculate these key metrics. By using Excel to calculate markup and margin, businesses can make informed decisions about pricing that will ensure customer satisfaction and boost customer lifetime value (CLV) while still maintaining profitability.
Once you have effective pricing in place, it's essential that you continually monitor, track, and analyze your profits and expenses. BeProfit is one of Shopify's best profit calculators to get the job done. It's an all-in-one analytics tool that auto-syncs your order and expense data to provide real-time product, order, marketing, and expense reports. With a clear overview of your business finances, you can make informed and strategic decisions to scale your store and boost your bottom line.
BeProfit: Profit Analysis Dashboard
4.8/5(320 reviews)
Book a Demo
