Excel 2010 Windows Calculate Profits Margins Easy Ways Srp

If you are searching for ways to calculate margin percentage in Excel, then you are in the right place. Margin is basically the difference between the selling price and costs for the production of this product and it is calculated as a percentage of the selling price. To know more details let's start our main article.

Download Workbook

---

5 Ways to Calculate Margin Percentage in Excel

We will use the following dataset of a company containing the details of the selling prices and different costs of various products to calculate different types of margin percentages in Excel. The main 3 types of margin percentages such as the Gross Profit Margin Percentage, Operating Profit Margin Percentage, Net Profit Margin Percentage will be discussed in this article.

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.

---

Method-1: Calculate Margin Percentage in Excel for Gross Profit Margin

Gross Profit Margin is the difference between the Selling Price and the Cost of Goods Sold (Raw material, Labour Cost, etc) with respect to the Selling Price. By using a simple formula we will calculate it in this section.

Steps :
➤ Type the following formula in cell E5

Here, C5 is the Selling Price, D5 is the Cost of Goods Sold.

➤ Press ENTER and drag down the Fill Handle tool.

Then, you will get the Gross Profit Margin for the products.

Now, to add percentage select the values of the Gross Profit Margin column and then select the Percent Style option under the Home tab.
You can also select it using the shortcut key CTRL+SHIFT+%.

Finally, we will have the Gross Profit Margin Percentages for the products.

Read More: How to Calculate Gross Profit Margin Percentage with Formula in Excel

---

Method-2: Calculate Margin Percentage in Excel for Operating Profit Margin

Operating Profit Margin is the difference between the Selling Price and the Cost of Goods Sold , Operational Cost (Rent, Equipment, Inventory cost, Advertisement, etc.) with respect to the Selling Price of the final products. To calculate this margin percentage follow this method.

Steps :
➤ Type the following formula in cell F5

Here, C5 is the Selling Price, D5 is the Cost of Goods Sold and E5 is the Operational Cost.

➤ Press ENTER and drag down the Fill Handle tool.

Now, we will get the fraction values of the Operating Profit Margin and add the Percent style to these values.

After adding the Percent style we are getting the Operating Profit Margin Percentage for the products.

Read More: How to Calculate Net Profit Margin Percentage in Excel

---

Method-3: Calculate Margin Percentage in Excel for Net Profit Margin

The Net Profit Margin is the difference between the Selling Price and the summation of the Cost of Goods Sold , Operational Cost , Interest , Tax with respect to the Selling Price . In this section, we will try to calculate the Net Profit Margin Percentage.

Steps :
➤ Type the following formula in cell H5

Here, C5 is the Selling Price, D5 is the Cost of Goods Sold, E5 is the Operational Cost, F5 is the Interest and G5 is the Tax of the product Shirt1.

➤ Press ENTER and drag down the Fill Handle tool.

Afterward, we will get the fraction values of the Net Profit Margin and now add the Percent style to these values.

Finally, you will get the Net Profit Margin Percentage values for different products.

Read More: Percentage Formula in Excel (6 Examples)

---

Similar Readings:

• How to Calculate Dividend Growth Rate in Excel (3 Methods)
• Apply Percentage Formula for Multiple Cells in Excel (5 Methods)
• Calculate Average Percentage in Excel [Free Template+Calculator]
• Percentage Difference between Two Percentages Excel (2 Easy Ways)
• How to Calculate Reverse Percentage in Excel (4 Easy Examples)

---

Method-4: Using Table Option to Calculate Margin Percentage

Here, we will use the Table option to calculate the Gross Profit Margin of the products in a faster way with the Structured Reference system.

Steps :
➤ Go to Insert Tab >> Table Option.

Afterward, the Create Table dialog box will appear.
➤ Select the range of your dataset.
➤ Check the My table has headers option and click OK.

Then, we will have the following table.

➤ Select the cell E5 and start typing the formula

Here, C5 is the Selling Price, D5 is the Cost of Goods Sold.
But, when start to select the cells C5 and D5, Excel will convert them automatically to the structured reference system and modify the formula as follows

=([@[Selling Price]]-[@[Cost of Goods]])/[@[Selling Price]]

When you will hit ENTER, you will get the Gross Profit Margin values for all of the products automatically and finally add the Percent Style to these values.

Eventually, we will get the Gross Profit Margin Percentages for the products.

Related Content: Excel formula to calculate percentage of grand total (4 Easy Ways)

---

Method-5: Using a VBA Code to Calculate Margin Percentage

Here, we will create a function with the help of a simple VBA code, by which you can calculate the Gross Profit Margin Percentage, Operating Profit Margin Percentage, Net Profit Margin Percentage whatever you want.

Steps :
➤ Go to Developer Tab >> Visual Basic Option.

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.

After that, a Module will be created.

➤ Write the following code

          Function margin(s As Double, c As Double, Optional o As Double, _ Optional i As Double, Optional t As Double) As Variant  margin = ((s - c - o - i - t) / s)  End Function        

It will create a function named margin and here s is for Selling Price, c is for Cost of Goods Sold, o is for Operational Cost, i is for Interest and t is for Tax.

We have declared o, i, and t as Optional because without them also you can calculate the Gross Profit Margin and by including o with it you can calculate the Operating Profit Margin and for adding the additional parameters i and t with it, it will turn into the Net Profit Margin.

Now, go back to the sheet and write the following formula in cell H5

Here, C5 is the Selling Price, D5 is the Cost of Goods Sold, E5 is the Operational Cost, F5 is the Interest and G5 is the Tax of the product Shirt1.
margin will calculate the Net Profit Margin for this product.

➤ Press ENTER and drag down the Fill Handle tool.

After that, we will get the fraction values of the Net Profit Margin and now add the Percent style to these values.

Finally, you will get the Net Profit Margin Percentage values for different products.

Read More: Calculate Percentage in Excel VBA (Involving Macro, UDF, and UserForm)

---

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

---

Conclusion

In this article, we tried to cover the ways to calculate margin percentage in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

---

Related Articles

• How to Add 20 Percent to a Price in Excel (2 Quick Methods)
• Add 10 Percent to a Number in Excel (2 Easy Methods)
• How to Calculate Monthly Growth Rate in Excel (2 Methods)
• Calculate Percentage of a Number in Excel (5 Easy Ways)
• How to Use the Exponential Growth Formula in Excel (2 Methods)

robersonfortudieved96.blogspot.com

Source: https://www.exceldemy.com/calculate-margin-percentage-in-excel/

Share this post