Profit Margin Formula in Excel: A Step-by-Step Guide
Profit margin is the simplest and the most common financial analysis metric.
If you want to quickly sketch your head of how a company is doing – get its profit margin ratios. Three numbers are you are all sorted.
Yes, you heard that right, three. Each company has three profit margin ratios. What are those and how do you calculate them in Microsoft Excel?
Learn with me along the way through this tutorial. And as you follow me, do not forget to get your free practice workbook for this guide here 📚
What is the Profit Margin and its types?
Profit margin is the ratio that tells you about the overall profitability and efficiency of a company.
A profit margin expresses the profit of a company relative to its revenue.
The formula for profit margin is:
Yes, only this simple. Divide the profit of a company by its revenue and multiply it by 100 to express it in terms of percentage ➗
The Statement of Profit & Loss (P&L) of a company would broadly look like this.
Shows that the profit of a company can take three forms. The Gross profit, the operating profit, and the net profit.
Gross profit
The gross profit is calculated at the first step of the P&L. It is the gross profit before deduction of any ancillary expenses but simple revenue less the cost of goods sold 📝
The expenses are already expressed as negative values hence I have summed the numbers up (which will automatically deduct the negative values from the revenue).
Kasper Langmann, co-founder of SpreadsheetoYou take your sale proceeds. And deduct the direct cost of making the goods you sell from it to reach the gross profit.
Operating profit
Next is the operating profit.
It comes next to the gross profit after you deduct the operating expenses from the gross profit.
To sell clothes, you need to buy clothes and stitch them – alright. That is the cost of goods sold (COGS) 👚
But you also need to incur some expenses to keep your business operational like promoting your goods (marketing expenses), paying staff to deal with customers (admin expenses), and so on.
Deducting operating expenses from the gross profit you get the operating profit for a business.
Net profit
And on the last step of profit calculation comes the net profit – the bottom line.
Net profit is calculated by deducting all expenses from a company’s revenue including taxes and interest expense.
This is the final profit of a company (after all the expenses have been deducted) ready for distribution among the owners/shareholders 💰
How to calculate profit margin in Excel
Once you have figures of profits ready, calculating profit margin in Excel is a piece of cake. I will now show you how to calculate all types of profit margins in Excel.
Gross Profit Margin
To calculate the Gross Profit Margin (GPM) in Excel, divide the figure for “Gross profit” by the “Total Revenue”.
Comes out to be 50%. This tells that even after you deduct the COGS, you still have 50% sales proceeds on your hand.
A 50% profit margin also conveys that just in case the COGS increases some time (due to inflation, shortage, etc.) or any other reason, you can still stand the cost increase since you have a 50% margin 😎
Gross profit margin tells how much margin you are earning from your revenue after COGS.
The results are not expressed as percentages but as decimals. instead of a percentage?
Step 1) Select the cell containing the results (the decimal number)
Step 2) Go to the Home tab > Number group > click the percentage icon.
Applying the percentage format to it will give you the profit margin as a percentage.
Operating Profit Margin
To calculate the Operating Profit Margin (OPM) in Excel, divide the figure for “Operating Profit” by the “Revenue”.
Very logically, as we go down the P&L, the margins continue to shrink as more and more costs add up.
The Operating Profit margin comes out as 25%. This tells us that after the COGS and all the operating expenses are deducted from the revenue, we still have a 25% margin to operate 💪
Net Profit Margin
To calculate the Net Profit Margin (NPM) in Excel, divide the figure for “Net profit” by the “Revenue” through the following formula.
The net profit margin percentage is 14.6%. This is what the business earns from the revenue proceeds.
How to interpret profit margins?
Calculating profit margins is quite easy but interpreting them might not be equally simple.
A Company made a 20% profit margin is never enough information to tell if the company is doing well or not. Profit margins are always interpreted in a relative sense, by comparison.
- Profit margins must be seen in the context of the business’s industry, the performance of the industry overall, and the economic conditions. For example, if your company made a net profit margin of 20% while the industry average was 15% – you know you rocked it 🤘
- Compare year-over-year profit margins to see how the company has performed over the year. Increasing profit margins denote better performance and vice versa.
Here are some pointers to help you interpret profit margins better.
- A lower GPM means you need to work on improving the cost of your goods. Find better vendors, optimize production levels, etc.
- A high GPM but less OPM means you need to cut down your operating expenses ✂
- A high OPM but less NPM means you’re paying too much in interest in taxes. While the tax will be a legislated rate, you can optimize your borrowing levels to cut down the interest cost of your business.
- An excessively high profit margin may not necessarily be good. Probably means you’re charging your customers excessively high and may lose them to your competitors.
- Low profit margins may not necessarily be bad. Maybe that’s because your business is on the runway and you’re focusing on building a customer base.
Conclusion
Profit margins are simple numbers that give you a helicopter view of how a business is doing and help you identify the areas of improvement for it.
Profit margins are an excellent way of analyzing the financial health and growth of a business. Plus point, they are super easy to calculate and very insightful to interpret.
If you too like to bring your business’s numbers to an Excel spreadsheet, I suggest you read my other Excel tutorials on some very interesting topics, including:
Written by Kasper Langmann
My name is Kasper Langmann, and I’m the co-founder of Spreadsheeto and a certified Microsoft Office Specialist.
This tutorial reflects over 14 hours of dedicated research and writing, based on my 10+ years of professional Microsoft Excel experience.
Last updated on April 3rd, 2024.
One last thing before you go: make sure to sign up for my free Microsoft Excel course!