If you have ever been in a managerial position or handled sales yourself, for example, within your small business, then you will be no stranger to the concept of profit margin or markup.
It is important to establish a healthy level of profit margin or markup. If your customers feel they are overpaying, you may lose them. On the other hand, if your profit margin is not high enough, you may not even make a profit after deducting all your costs.
Margin is one of the economic terms by which a business can measure various values associated with sales.
And it’s not hard to calculate profit margin or markup.
But first, let’s separate the concept of margin from markup because these two terms are often confused.
After that, we’ll give you the formula to calculate your profit margin in Google Sheets (or Excel). Let’s dive in!
What is Markup
The markup tells you how much percent the trader has added to the final purchase price.
Thus a markup is a difference between how much a company pays for a product and how much it sells it for.
In essence, it’s just how much do customers overpay for the product.
How is Markup Calculated
Now let’s demonstrate the calculation of markup on real usage.
Let’s say you have a product, like coffee beans, that you import for a certain price and then sell for a higher price.
For instance, you buy coffee beans for your startup for $70/kg.
But your selling price is higher to make a profit. So you sell one kilogram of the same coffee beans for $100.
We can calculate the markup using the following formula:
Markup percentage = (sell price - cost) / cost * 100
Or in our example: Markup = (100 – 70) /70 * 100 = 42.8%
This mean’s that you’re selling a product at 142% of the price.
But sales directors do not usually use this number as the key performance indicator.
That’s because there are dozens of other costs the firm has to pay. For instance, wages, pay electricity, water, and other utilities, etc…
And markup is just a difference between the buy and sell prices without further costs deducted. This is where profit margin comes in handy. But before we discuss profit margins, let’s take a look at how to calculate markup using Google Sheets.
Calculate Markup Using Google Sheets
Let’s say you have a list of data containing the Sale price in collum B and the Cost to buy that product in collum C. And we can step it to the next level by calculating markups in both percentage value and dollar value. The dollar value is the same as the dollar value of the profit margin.
To calculate markup in dollar value, we can simply subtract the cost price from the sale price. To do that, you can use the following formula:
=B3-C3
Now let’s see what the percentage expression of this is by using the following formula:
=(B3-C3)/C3*100
Alternatively, Google sheets and probably even Excel can automatically multiply the number by one hundred to get a percentage expression. In that case, do not add the *100 value into the formula, but change the number format to percentage.
What is Profit Margin
The margin works the other way around. It tells us how much we’ve made, not how much we have increased the price.
The margin is further reduced by costs such as electricity, employee wages, rental, etc.
How to Calculate Profit Margin
Let’s once again use the same example with coffee beans imported for $100 per kilo and sold for $70. The profit margin is then $70/kg. This means that you make $30 per every kilo sold. That’s in case you don’t have further costs involved.
The profit margin is calculated by dividing the number, not by the cost price, by but the selling price. You can see it in the following formula.
m = ((s - b) / s)*100
Where:
- m = margin
- s = sell price
- b = buy price
Or, in our case, that would be: Margin = ((100-70)/100)*100 = 30%
You need to pay attention to the prices we use. For a correct calculation, prices with VAT cannot be combined with prices without Value-added tax.
Suppose you need to calculate the Net profit margin. You will calculate instead of buying and selling prices with net income.
Easily said, just add up every cost you have on one unit to your buy price and subtract the selling price.
For instance, You import a product for $70/kg and sell it for $100/kg, the same as in the example above.
But this time you have to pay electricity and wages. The electricity cost is $1,000/month, the same as wages.
Suppose you sell 1,000kg per month, then selling one unit of goods costs you $1 in electricity and $1 in wages because 1,000 kg sold equals 2,000 dollars of extra costs.
In that case, your net profit margin is only $28 per kg of goods.
How to Calculate Profit Margin Using Google Sheets
To calculate profit margin using Google sheets, we can use similar formulas to calculate markup. You just need to change the divider like this:
=(B3-C3)/B3*100
If you have further costs involved, don’t forget to add the value to the actual cost collum.
Can Profit Margin be Over 100
The profit margin can be as high as you need it to be, for instance, even over 100.
It all depends on what you are selling for and what your other costs are in addition to the price of the goods. For example average small business profit margin range from 7% to 10%; for a wholesaler, it could be even 200%.
Conclusion
These economic terms can be difficult to understand at first. Not to mention learning how to calculate profit margins. But it forms a basic foundation of economics that anyone around sells should know.
Can we use this knowledge in everyday life? No one is likely to tell you the exact margin of each store. The amount of margin can also vary from store to store. Some traders can have a margin of 10%, others up to 200%.