Don’t forget to share this post

For any company, the initial objective is to break even. However, the main aim is to become profitable as soon as possible. Profit margins are some of the most commonly used metrics in the analysis of a company’s profitability, together with other financial ratios. Since the term profit is rather broad, specific ratios focus on different profit categories.

In this article, you will learn about profit margins and the formulas used to calculate them. You will also learn how to calculate the most commonly used margins using Google Sheets: gross profit margin, operating profit margin, and net profit margin. To learn about other profitability ratios, check out our article on Profitability Analysis: Step-By-Step Guide.

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE

What is Profit Margin?

The profit margin is the proportion of revenue that represents profit. Since profit is a rather broad term, there are multiple ratios that focus on different aspects of it. Some use broader interpretations of profit, such as the gross profit margin, and some use more specific ones, such as the net profit margin.

Additionally, you should check what margins are commonly used within your industry. Depending on the industry and business model, you need to consider different types of expenses and liabilities. When calculating these margins, remember to compare them to similar companies in your industry.

What Is A Good Profit Margin?

The range of acceptable profit margin values depends on many factors, including industry, company size, and business model. You will see references to general rules of thumb, indicating that 10% is average and 20% is good. However, this is meaningless without identifying competitors and benchmarking your industry.

In addition to their individual values, it’s important to analyze the differences between ratios. For instance, how large is the difference between gross profit and operating profit? If the second is much lower, you may want to look into current operational procedures and identify areas for improvement.

How to Calculate Profit Margin?

Generally speaking, you can calculate the profit margin by dividing profit by revenue and multiplying by 100. However, depending on the specific margin you want, you can use different formulas to calculate profit: gross, operating, and net profit are the three most commonly used.

Profitability Analysis Step By Step Guide
Profitability Analysis: Step-By-Step Guide

Profitability analysis helps to keep track of the performance and optimize the profitability of a business. Here’s how to perform the analysis step-by-step.

READ MORE

How to Calculate Gross Profit Margin?

This is probably the most commonly used and broadest measure of profitability. However, it only considers the cost of goods sold. To calculate the gross profit margin, you first need to subtract the value of the cost of goods sold from the value of revenue. Next, divide by the value of revenue and multiply the result by 100.

gross profit margin = ((revenue - COGS) / revenue) * 100

In some industries, using the adjusted gross profit margin is more common, as it also considers inventory carrying costs.

How to Calculate Operating Profit Margin?

The operating profit margin, aka the EBIT margin, is more restrictive than the gross profit margin but less than the net profit margin. It expresses the proportion of revenue the company earns from operations before deducting interest and taxes. To calculate it, divide operating profit by revenue and multiply by 100.

operating profit margin = (operating profit / revenue) * 100

How to Calculate Net Profit Margin?

The net profit margin, or bottom line, provides an overall picture of profitability after all expenses have been considered, including taxes and interest. The fact that this margin accounts for all costs makes it a comprehensive measure of net profit. However, this also means it will include one-off gains and losses that are not likely to be repeated.

net profit margin = (net income / revenue) * 100

How to Calculate Profit Margin in Excel or Google Sheets?

As you can see, you can calculate these margins using the data available in the company’s financial statements. If you use templates for these statements in Excel or Google Sheets, you can add the profit margin formulas to your templates.

If you don’t currently use templates for your financial statements, you can use these free Balance Sheet and Income Statement templates. Using templates will allow you to calculate these margins for new data quickly. If you work in a team and use multiple files, you may want to consider a tool like Layer. You can synchronize data across multiple locations and formats and easily keep track of progress and results.

In the next section, you have examples of the three main profit margin ratios calculated directly using the free Income Statement template.

Cost-Volume-Profit Analysis: CVP Formula and Examples

A Cost-Volume-Profit Analysis helps companies to better understand their overall selling performance. Here’s how to perform a CVP Analysis step-by-step.

READ MORE
Cost Volume Profit Analysis CVP Formula and Examples

Profit Margin Examples

The following examples are based on this free Income Statement template, using data for ThisCompany between 2020 and 2022, as shown below.

How to Calculate Profit Margin Formula Examples Income Statement Data
How to Calculate Profit Margin (Formula & Examples) - Income Statement Data
  1. 1. Below the data, under the corresponding year, type in the formula for gross profit margin. Divide ‘Gross Profit’ by ‘Total Net Revenue.’ Instead of multiplying by 100, you can simply pre-format the cells as percentages, and Google Sheets will do it automatically. Click on the button encased in a red square, as shown below.
How to Calculate Profit Margin Formula Examples Gross Profit Margin Formula
How to Calculate Profit Margin (Formula & Examples) - Gross Profit Margin Formula
  1. 2. Drag the formula to the right to cover as many years as you need.
How to Calculate Profit Margin Formula Examples Drag Gross Profit Margin Formula
How to Calculate Profit Margin (Formula & Examples) - Drag Gross Profit Margin Formula
  1. 3. Below the ‘Gross Profit Margin’, add the formula for ‘Operating Profit Margin’ below the corresponding year. Divide ‘Operating Profit’ by ‘Total Net Revenue’ and remember to format the cell as a percentage.
How to Calculate Profit Margin Formula Examples Operating Profit Margin Formula
How to Calculate Profit Margin (Formula & Examples) - Operating Profit Margin Formula
  1. 4. Drag the formula to the right to calculate the ‘Operating Profit Margin’ for additional years.
How to Calculate Profit Margin Formula Examples Drag Operating Profit Margin Formula
How to Calculate Profit Margin (Formula & Examples) - Drag Operating Profit Margin Formula
  1. 5. Next, type the formula for the ‘Net Profit Margin’ below the corresponding year. Divide ‘Net Profit’ by ‘Total Net Revenue’ and format it as a percentage.
How to Calculate Profit Margin Formula Examples Net Profit Margin Formula
How to Calculate Profit Margin (Formula & Examples) - Net Profit Margin Formula
  1. 6. Finally, drag the ‘Net Profit Margin’ formula to the right to calculate this margin for the years you want.
How to Calculate Profit Margin Formula Examples Drag Net Profit Margin Formula
How to Calculate Profit Margin (Formula & Examples) - Drag Net Profit Margin Formula

That’s it! You now have the profit margin values for three consecutive years.

How to automate your FP&A on top of Google Sheets?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

Conclusion

As you have seen, profit margins are easy to calculate and can provide valuable insight into a company’s profitability. By comparing the values for different types of profit margins for the same company, you can also get insights into the distribution of costs. Since they are expressed as percentages, it’s also easy to compare with other companies. However, be careful when comparing, as values vary significantly by industry.

You now know about profit margins and how to calculate the three most commonly used. You also know how to add the formulas to your financial statements in Excel or Google Sheets to automate calculations. By using a tool like Layer, you can enjoy additional benefits, like multi-platform and multi-format synchronization, scheduled updates, and full control over access to your data.

To learn more about other financial ratios and analyses, check out these articles :

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Nov 2 2022, Updated Nov 23 2022