Essential to Expert Skills progression

Excel tutorials

The only Excel books you will ever need

  • The only constantly updated Excel 365 titles more…
  • Thoroughly covers Power Pivot and Power Query more…
  • Covers business intelligence and OLAP features more…
  • Simple, easy, understandable, 2-page lessons more…
  • Available as both printed books and e-Books more…
  • Used by schools, colleges and universities  more…
  • Available for 365, 2019, 2016 and Mac versions more…

Calculating the original number after a percentage increase

Gross Margin and Markup

If you’ve arrived at this page it is likely that you are working in a sales environment. For that reason I’ll start this article with a brief explanation of Gross Profit and Markup (excerpted from my Expert Skills book).

Newcomers to the retail business often confuse mark-up with gross profit. In general business, gross profit is the more widely accepted metric when discussing profits.

Here’s an example of mark up:

Bob buys a watch for $500 and marks it up by 50%. This means that he adds 50% to the cost price and sells it for $750. In this case, the formula is:

Selling Price = Cost Price * (1+MarkUp%)

The Excel formula that could be used is:

=500*(1+.5)

If you use Excel’s percentage format a value of 50% is stored by Excel as 0.5. For this reason you could use the following formula (which is exactly the same).

=500*(1+50%)

And you could also do this.

=500*(100%+50%)

Or even.

=500*150%

All four formulas are doing exactly the same thing. (The percentage format has the potential to cause great confusion amongst Excel beginners).

Here’s an example of Gross Profit:

Bill buys a hard disk for $100. His company needs to make 50% gross profit on all goods sold. This means that they need to sell the hard drive for $200, making 50% of the selling price ($100) in profit.

In this example, the formula is:

Selling Price = Cost Price/(1-Gross Profit%)

The Excel formula that would be used for this example is.

=100/(1-0.5)

In this example that would result in a selling price of $200.

You can see from the above example that a gross profit of 50% is the same as a mark-up of 100%.

Terminology
I use the terms Gross Profit when discussing both cash amounts and percentages.

There’s a lot of controversy about correct terminology. Some purists would argue that the correct terms are Gross Margin (or simply Margin) for Percentage Gross Profit, and Gross Profit for a Gross Profit cash value.

Calculating the original number after a percentage increase

The problem you are probably struggling with is this:

Bob sold his watch for $750 after marking it up by 50%. What was Bob’s cost price?

Many student’s first instinct is to reduce the selling price by 50% and use the formula:

=750*50%

Of course that results in $325 which is the wrong answer (though it would be the right answer if 50% was the gross profit percentage rather than a mark-up percentage).

Let’s look again at the formula Bob used to calculate his selling price:

=500*(1+50%)

Bob had multiplied the cost price by (1+50%) to arrive at his selling price.

This means that the selling price of the watch is now 150% of the cost price. To return to the cost price you’d have to multiply by 100/150.

The formula that is needed is thus:

Cost Price = Selling Price * 1/(1+50%)

In the example of Bob’s watch the Excel formula would be:

=750*1/(1+50%)

Rounding the result

In the simple example above rounding isn’t an issue. When you work with real-world data rounding can become an issue. Consider this calculation:

I buy an item for $123.45 and mark it up by 50%.

This results in a selling price of: $185.175. You’d probably want to sell the item for $185.18.

Excel’s ROUND function can solve this.

Here’s a formula you could use to solve the problem above.

=ROUND(123.45*1.5,2)

This results in $185.18.

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Recent Articles

Calculating loan payment dates

Calculating Loan Payment Dates

This article shows how to calculate loan payment dates in Excel, using a formula that can calculate loans of any length and number of periods.

Subtracting percentages in Excel

Subtracting percentages in Excel

This article explains how Excel handles percentages and shows you how to subtract a percentage from a number in Excel using two different methods.

2 Responses

  1. Hi, this is very helpful but, not exactly what I am looking to solve.
    Here is the real world example I am trying to figure out:
    I’ll keep the numbers simple to make it as easy as possible to build the Excel formula (and an example where you know the answer .
    You have TWO pieces of information (column A and column B).
    Column A your is current price of stock, $20,
    Column B is the other piece of info you are provided and is “% off peak price”, in this example it is -80%.
    Column C is where the formula needs to be built. In this example you know that if you are at $20 and are 80% off peak you need to a 400% % increase to get back to your starting price (in this case we know that is $100, because to grow from $20 to $100 is a 400% gain.
    I am trying to build a formula for column C that uses the actual data in column A (current stock price in $) and in Column B (the % off peak stock price).
    It is probably a simple solution but, I simply cannot figure out how to build it in Excel (and I was a Math major!).
    Thank you,
    Dan

    1. Hi Dan

      If you only want to cater for negative percentages then (assuming your values are in row 1) the formula you need is:

      =A1/(1+B1)

      If you wanted to cater for positive percentages as well you would need to use an IF function with B1>0 as the logical test.

Leave a Reply

Your email address will not be published.

one + one =