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

## 2 Responses

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

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.