Excell 2010 Windows Calulate Profits Margins Easy Ways
If you need to calculate gross profit margin percentage, you can easily do it in excel. Here, we can use a very easy and simple formula to compute the gross profit margin percentage. In this article, we will learn how to calculate the gross profit margin percentage with a formula in Microsoft Exce l.
Download Practice Workbook
You can download the workbook and practice with them.
What is Gross Profit Margin Percentage?
Gross profit margin is used to measure a company's financial condition by estimating the amount of money left over after subtracting the cost of goods from the selling price. We can get the gross profit margin by dividing the selling price from (selling price – cost of goods). It is mainly expressed as a percentage of the below formula shown in the picture.
2 Methods to Calculate Gross Profit Margin Percentage with Formula in Excel
To calculate gross profit margin percentage with a formula in Excel, we are going to use the following dataset of ABC Company. The dataset contains some products in column B, selling prices in column C, cost of goods in column D to calculate the gross profit margin in column E. So, let's get into the methods of calculating the gross profit margin in excel.
1. Excel Formula to Calculate Gross Profit Margin
The distinction between the Selling Price and the Cost of Goods in relation to the Selling Price is known as the Gross Profit Margin. We can calculate the gross profit margin by using a simple formula. Let's go through the steps below to calculate the gross profit margin.
STEPS:
- Firstly, select the cell where you want to get the gross profit margin. So, we select cell E5.
- Secondly, write down the formula there.
Here, C5 is the selling price and D5 is the cost of goods.
- Thirdly, press Enter and we will get the Gross Profit Margin as a result.
- Now, to add a percentage (%), first choose the values in the Gross Profit Margin column range E5:E10, then go to the Home tab from the ribbon and select the Percent option in the Number group.
- Or, alternatively, we can also use the keyboard shortcut key 'Ctrl + Shift + %'.
- Now, we can see the result in percentage.
- After that, drag the Fill Handle down.
- And, finally, we get the Gross Profit Margin Percentage.
Related Content: How to Calculate Net Profit Margin Percentage in Excel
Similar Readings:
- Apply Percentage Formula for Multiple Cells in Excel (5 Methods)
- How to Calculate Margin Percentage in Excel (5 Easy Ways)
- Calculate Percentage Increase in Excel (Examples with All Criteria)
- How to Calculate Total Percentage in Excel (5 Ways)
- Subtract a Percentage in Excel (Easy Way)
2. User-Defined Formula Using VBA Code to Find Gross Profit Margin
We are now creating a user-defined function using the Excel VBA to calculate the gross profit margin percentage. Let's demonstrate the steps below about how we can create the function and use the function in our spreadsheet.
STEPS:
- In the beginning, go to the Developer tab from the ribbon.
- After that, click on Visual Basic to open the Visual Basic Editor.
- Another way to open the Visual Basic Editor is simply to press Alt + F11.
- Or, right-click on the sheet, then select View Code.
- Next, go to Insert and select Module from the drop-down menu.
- After that, copy and paste the VBA code below.
VBA Code:
Function GrossProfitMargin(s As Double, c As Double) As Variant GrossProfitMargin = ((s - c) / s) End Function - Then, save the code by pressing Ctrl + S.
This will create a function named GrossProfit Margin, and here, s is selling price and c is the cost of goods.
- Then back to the worksheet and select the cell where we want the gross profit margin result to appear. So, we select cell E5.
- After that, type the formula we have just created.
=GrossProfitMargin(C5,D5)
- Press Enter.
Here, C5 is the selling price and D5 is the cost of goods.
- And, we will get the result in cell E5. The user-defined formula will show in the formula bar.
- Drag down the Fill Handle over the range of the Gross Profit Margin column.
- And, there you go! The result for Gross Profit Margin Percentage is in column E.
Read More: Calculate Percentage in Excel VBA (Involving Macro, UDF, and UserForm)
Conclusion
The has been a guide to the gross profit margin percentage formula in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!
Related Articles
- How to Add 20 Percent to a Price in Excel (2 Quick Methods)
- Calculate Monthly Growth Rate in Excel (2 Methods)
- How to Calculate Percentage of a Number in Excel (5 Easy Ways)
- Use the Exponential Growth Formula in Excel (2 Methods)
- How to Use Profit and Loss Percentage Formula in Excel (4 Ways)
Source: https://www.exceldemy.com/gross-profit-margin-percentage-formula-in-excel/
0 Response to "Excell 2010 Windows Calulate Profits Margins Easy Ways"
Postar um comentário