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:
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).
And you could also do this.
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.
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%.
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:
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:
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:
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.
This results in $185.18.