Want to see our dashboard in action?
you can try it here with theDemo Store!

3 Best Excel Formulas for Calculating Markup vs. Margin

By Rosana Antoni
Kirsty Macdougall - Editor for BeProfit
Edited by Kirsty Macdougall

Updated May 5, 2023.

Using Excel to calculate these is simple to use.

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 differences between gross margin & gross profit 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.

Basic Markup Percentage Formula


» 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.

Basic Margin Percentage Formula



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 With Markup Formula


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:

Sales Pricing With Margin Formula


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.

Discounted Sales Price Formula


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)