The Smart Method Logo

Publishers of the world’s most comprehensive and up-to-date Excel tutorials

Calculating the original number after a percentage increase

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

Related Articles

x-lookup-tutorial-part-one

Understand XLOOKUP (part two)

note In most cases, the binary search option is not useful The XLOOKUP function uses a sophisticated linear search method by default. It is my

x-lookup-tutorial-part-one

Understand XLOOKUP (part one)

note XLOOKUP will not work in legacy Excel versions (Excel 2019 and earlier) The XLOOKUP function was added in the Jul 2020 semi-annual version 2002.

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.

14 − five =