how to calculate gross margin in excel

Lets take an example to see the calculation of Gross Margin in a better manner. The main constituents of the gross margin ratio are the operating revenue and expenses. Now, let us find out the gross margin and gross margin percentage. The total revenue is how much your business makes out of net sales. Cookies help us provide, protect and improve our products and services. Steps: Type the following formula in cell F5. Go to Insert Tab >> Module Option. It may include products getting processed or are produced but not sold. As an example, if it costs you $10 for a widget through your supplier, you would enter $10.00. Additionally, type the following formula in that cell: =C7/(1-$C$4) Here, we have created this formula from the generic formula. Calculation of gross margin in Excel can be done as follows: . Gross profit percentage is used by the management, investors, and financial analysts to know the economic health and profitability of the company after accounting for the cost of sales. Selling Price = Cost/(1-Margin%) Let's see the actions that we will perform in STEP 1. Gross profit margin, also known as gross margin, is expressed as a percentage: It is the proportion of money that represents profit. read more; the company earns from $1 of sales. So, it turns out to be one of the primary factors to be checked when a better player from the same industry needs to be picked. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy, Explore 1000+ varieties of Mock tests View more, Special Offer - Investment Banking Course Learn More, You can download this Gross Margin Formula Excel Template here , 250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access, Investment Banking Course (123 Courses, 25+ Projects), Financial Modeling Course (7 Courses, 14 Projects), Cost of Goods Sold = 9.50 million + 6.50 million + 1.20 million, Gross Margin = (27.80 million 17.20 million) / 27.80 million * 100%, Gross Margin = ($217.75 billion $117.51 billion) / $217.75 billion * 100, Gross Margin = ($221.57 billion $120.34 billion) / $221.57 billion * 100, Gross Margin = ($495.76 billion $373.40 billion) / $495.76 billion * 100%. In other words, if you pay $60 for a widget and want a 40% gross margin, subtract 40% from 1 to get .6, so $60 / 0.6 = $100 selling price. Macros & Excel VBA Charlene Rhinehart is an expert in accounting, banking, investing, real estate, and personal finance. In the example here, the formula is: =D4/D2. Otherwise, open the Microsoft Excel program and choose the "New blank workbook" option to create a new file. Calculation with Excel Formulas To calculate the Gross Profit Margin, subtract the total price of raw materials of a company from the total revenue. Below are steps to calculate gross margin: 1. Excel Pivot Table Exercises This Gross Margin Ratio Calculator template will compute the gross profit margin given the Revenue and the Cost of Goods Sold. Read More: Percentage Formula in Excel (6 Examples). GM TY - GM LY = Price Impact + Volume Impact + Mix Impact. Click the cell that will display the gross markup and divide the margin by the cost. To calculate the gross profit margin, use the following formula: Advertisement 1. Then simply fractionate the result by the total revenue. Then on the toolbar select Then divide that net profit by the cost. The company is based out of Germany and it manufactures corrugated boxes that are supplied to parts of the country. Surface Studio vs iMac - Which Should You Pick? Closing stock or inventory is the amount that a company still has on its hand at the end of a financial period. The ratio indicates the percentage of revenue that the company retains as gross profit. The cost of goods sold is how much it costs your business to sell those goods. Excel Templates For example, assume a product sold for $100 and cost $25 to produce. = 0.6174 (61.74%) This means that for every dollar generated, $0.3826 would go into the cost of goods sold, while the remaining $0.6174 could be used to pay back expenses, taxes, etc. Formula for Calculation and Examples, Operating Profit: How to Calculate, What It Tells You, Example, Selling, General & Administrative (SG&A) expense, Input historical revenue from the income statement, Input historical COGS from the income statement, Forecast future revenue growth and apply this percentage to future periods (usually starting with a, Calculate the average of previous gross margins to use as an estimate for gross margins going forward. Gross Margin Model Gross margin is a percentage; it is total sales less the cost of goods divided by revenues. If you are searching for ways to calculate margin percentage in Excel, then you are in the right place. Excel Formula to Add Margin to Cost (4 Suitable Examples), How to Calculate Contribution Margin in Excel (2 Suitable Examples), How to Calculate Negative Margin in Excel (4 Easy Methods), How to Calculate Margin of Error in Excel (7 Effective Ways), How to Calculate Sales Margin in Excel (with Quick Steps), How to Add Complex Numbers in Excel (With Easy Steps), How to Use COUNTIF Function with Array Criteria in Excel, How to Create All Combinations of 4 Columns in Excel (2 Ways), How to Control Multiple Cells with Spin Button in Excel. As Microsoft Inc. and Apple Inc. are in similar fields, we would be able to compare these companies. Let us take the example of Walmart Inc. for the computation of gross margin. Gross profit is defined as (revenue less cost of goods sold). function onCatChange() { Contribution Margin: What's the Difference? It is easy to calculate: Simply take the gross profit for a period and divide it by revenue for that same period. One could take multiple measures to increase the productivity of that particular department. 2. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems. In the above case, Apple Inc. has reached a gross margin of $98,392 and 38% in percentage form. Now, we will get the fraction values of the Operating Profit Margin and add the Percent style to these values. Excel Books Now, go back to the sheet and write the following formula in cell H5. Threats include any threat of suicide, violence, or harm to another. The gross margin represents the amount of total sales revenue that the company retains after incurring the direct costs (COGS) associated with producing the goods and services sold by the company. In percentage the result is: N > M. Pay attention, the extra charge can be 20 000%, and the margin level will never exceed 99.5%. This . First, determine the total sales of all products sold, or total revenue. As an alternative, enter the individual product's wholesale cost. Download Gross Margin Formula Excel Template. Excel Solver Gross profit is Net Sales - Cost of Goods Sold (COGS). dropdown.onchange = onCatChange; Gross profit is defined as revenue minus the cost of goods sold (COGS). Enter your retail price in cell A2. Basically, gross margin determines the profitability after adjusting the costs of production that can be directly assigned to the manufacturing process. Gross margin = (Total revenue - Cost of goods sold) / Total revenue x 100 This gross margin formula gives a percentage value. Answer (1 of 2): Here you go. })(); Direct cost refers to the cost of operating core business activityproduction costs, raw material cost, and wages paid to factory staff. For 2019, Centerfield has a 19.2% gross margin, and generates $400,000 in gross profit. If the gross margin ratio falls, the unproductive department could be easily tracked down and worked upon. To calculate your net profit margin, take your total revenue figure (all types of income) and deduct your total expenses (tax, labour, materials, advertising, debt repayments, etc) to get your net income (or net profit) figure. After importing historical dataand forecasting any future periods, here's how you determine the gross margin: Following best practices in Excel, you want to break all these numbers out separately, so they are easily trackable and auditable. Example: Using the Profit Margin Formula in Excel, calculate (120/200)100 to get a profit margin of 60%. Take that number and divide by Net Sales to get your Gross Profit percentage, also called Gross Margin. Inherently, analysts benchmark the gross margin based on the margins of the industry peers, and any company performing better than that benchmark is considered to be good. After adding the Percent style we are getting the Operating Profit Margin Percentage for the products. Hope you will find it useful. Calculation of gross margin (%) can be done as follows: Gross Margin (%) = ($125843 $42910) * 100 % / $125843. Such directly assignable costs primarily include raw material cost, direct labor expense, and manufacturing overhead, and are they collectively known as the cost of goods sold or cost of sales? ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. Use the below-given data for the calculation of gross margin: , Calculation of gross margin can be done as follows: , Calculation of gross margin % can be done as follows: , Gross margin (%) = ($260174 $161782) * 100% / $260174. Gross Profit Margin is the ratio that calculates the profitability of the company after deducting the direct cost of goods sold from the revenue and is expressed as a percentage of sales. Excel Power Query How to calculate profit in Excel Here are several steps you can follow to calculate profit in Excel: 1. The cost of goods sold includes the price allocated to products and services amounting to $144,996 million and $16,786 million each. Excel Basics 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. As an example, if total revenue was $150 million and total costs were $90 million, then you would get 0.4. Some of the companies report a gross profit as a line item in the income statement. It is easy. 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. Excel Training & Courses According to the annual report for the year 2018, the company achieved net sales of $495.76 billion and incurred the corresponding cost of sales of $373.40 billion. Practice Test & Quiz 3. (The Average DO NOT work because of this formula.) Most businesses use a percentage. As an alternative, enter an individual product's retail price in cell A1, if you prefer to calculate individual profit margins. Type an =, then click the Margin cell, type a / as a division sign and then click the Cost cell. In the example, if you. #1 Hi, I've got this huge file where I calculate the sales margin for certain products through (price sold - costs) / price sold. But it is very difficult to judge what can be considered a healthy gross margin. Complete this by inserting the following formula into cell B9: = (B8)* (1) Here, the gross profit is the returns acquired after considering the cost of goods sold, trade discounts and sales returns for deduction from the total revenue. On the other hand, the corresponding cost of sales was $120.34 billion in 2018 vis--vis $117.51 billion in 2017. To know more details lets start our main article. Summary. Excel for Finance Let's go through the steps below to calculate the gross profit margin. Then, you divide that figure by your total sales revenue. Check the My table has headers option and click OK. Select the cell E5 and start typing the formula, Here, C5 is the Selling Price, D5 is the Cost of Goods Sold. These are the necessary expenditures and can be fixed or variable in nature like the office expenses, administration, sales promotion expense, etc. Here, C5 is the Selling Price, D5 is the Cost of Goods Sold. Value can be positive and negative. Add two new rows at the bottom, the first for Gross Markup and the second for Gross Margin. Account for operating expenses It reflects all direct costs associated with the product or service delivered and is reflected in a company's income statements. comments sorted by Best Top New Controversial Q&A Add a Comment . Open Microsoft Excel If you already have a workbook with data, you can go to your saved files and open it in Microsoft Excel. If you calculate these two figures in numbers the result is: Extra charge = Margin. $100 - $25 = $75. 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. Then, the Visual Basic Editor will open up. The Excel sheet, available for download below, helps a business calculate the weighted average gross margin by entering gross margins and revenue for up to ten product lines. To calculate profit margin as a percentage with a formula, subtract the cost from the price and divide the result by the price. title=">

Steps: Type the following formula in cell E5. Gross profit is the profit a company makes after deducting the costs of making and selling its products, or the costs of providing its services. 00:00 00:00. Press ENTER and drag down the Fill Handle tool. Enter your wholesale cost in cell A1. To calculate markup subtract your product cost from your selling price. if a product cost 1000 to make, and was sold for 3000 you would get (3000-1000)/3000 = 0,6667 sales margin. Claire's expertise lies in corporate finance & accounting, mutual funds, retirement planning, and technical analysis. Steps: Type the following formula in cell H5. Therefore, the Gross Margin of the Company was 38.13% for the year 2018. At the same time, Microsoft Inc. has earned only $82,933 million in the absolute term. [CDATA[ */ Calculation of gross margin in Excel can be done as follows: - Gross Margin = $125843 - $42910 Gross Margin will be - Gross Margin = $82,933. So, we select cell E5. How do I calculate profit margin in Excel? Step 2. In the example shown, the formula in cell E5 is: = (C5 - D5) / C5. By using our website, you agree to our use of cookies (, Examples of Gross Margin Formula(with Excel Template), Relevance and Uses of Gross Margin Formula, In the accrual system, one may record revenue or expenses as and when they occur, irrespective of whether they receive cash. Enter the total sales revenue in cell A1. Apple Inc. has a gross margin of $98,392 million. You are free to use this image on your website, templates, etc., Please provide us with an attribution link. She is a CPA, CFE, Chair of the Illinois CPA Society Individual Tax Committee, and was recognized as one of Practice Ignition's Top 50 women in accounting. As an alternative, enter an individual product's retail price in cell A1, if you prefer to calculate individual profit. 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. 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. Therefore, we will try calculating the gross profit marginGross Profit MarginGross Profit Margin is the ratio that calculates the profitability of the company after deducting the direct cost of goods sold from the revenue and is expressed as a percentage of sales. Practice with VBA Throughout her career, she has written and edited content for numerous consumer magazines and websites, crafted resumes and social media content for business owners, and created collateral for academia and nonprofits. Then use this formula to calculate average profit margin: =SUM (C2:C4)/SUM (A2:A4) or without using. I suggest setting up your pivot table first and then add [Gross Margin] and [Total Sales] to the pivot. The formula below calculates the number above the fraction line. Now, we could calculate the gross margin by reducing COGS from net sales. To calculate gross margin, first identify each variable of the formula and then fill in the values. In addition, our goal is to implement the calculation in such a way that the Price Impact of the Revenue PVM is the same as Price Impact in the Gross Margin PVM. First, determine the total sales of all products sold, or total revenue. Sale Price $ *. 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. In the example, you would get "40.00%.". Image Credit: Screenshot courtesy of Microsoft. Calculate the gross margin of the company if the following cost break-up is available. Ex : a part at $100 sold for $100 x 140% = $140. Cost of goods sold (COGS) is defined as the direct costs attributable to the production of the goods sold in a company. In B2, enter the total cost of goods sold (accounting for raw materials and labor). Excel Charts Calculation of gross margin (%) can be done as follows: Gross Margin (%) = ($125843 - $42910) * 100 % / $125843 Gross Margin (%) will be: - Gross Margin (%) = 66% Explanation The recent annual report states that the company achieved revenue of $221.57 billion in 2018 vis--vis $217.75 billion in 2017. Afterward, the Create Table dialog box will appear. Step-by-Step Procedure to Calculate Sales Margin in Excel For the demonstration purpose, we are going to use the below dataset. Please do it by yourself. The formula of gross margin in numbers and percentage terms is as follows: , Gross Margin Formula (In Absolute Term) = Net Sales COGS, Gross Margin Formula (In Percentage Form) = (Net Sales COGS) * 100 / Net Sales, You are free to use this image on your website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Gross Margin Formula (wallstreetmojo.com), Net Sales = Gross Sales (Refunds + Price Adjustments + Price Deductions), COGS = Opening Inventory + Purchases Closing Inventory, One can do the calculation of the gross margin equation by using the following steps: . I have completed my graduation from BUET. These two heads are vital to the business and help the management to oversee the performance of the production and sales division. Definition, Formula, Calculation, and Example, What is Net Profit Margin? For example, if a product is normally $1,000 and two sales are made with a 5 percent commission, the commission is $200 at the 40 percent margin and $120 at a 30 percent margin. It doesnt include any other expenses into account except the cost of goods sold.read more from the data above. On the other hand, if the revenue side has caused a decline in the ratio, they should consider problems in the sales and distribution department. Start Your Free Investment Banking Course, Download Corporate Valuation, Investment Banking, Accounting, CFA Calculator & others. Net Profit Margin = Net Income / Revenue x 100. if ( dropdown.options[ dropdown.selectedIndex ].value > 0 ) { STEPS: Firstly, select the cell where you want to get the gross profit margin. So, we take opening inventory So, we take opening inventory (. Step 3. Step 1 First of all, we need to prepare the data for the calculation of the profit margin. with respect to the Selling Price of the final products. This 38% gross margin indicates that out of $1 of revenue from net sales, Apple Inc. can make a gross profit of 0.38 cents. Since operational expenses like corporate office rent, sales people's commissions and insurance are not directly involved in the production of the furniture, they would be in a separate expense category, probably called Selling, General & Administrative (SG&A) expense. 5 Ways to Connect Wireless Headphones to TV. The results in column E are decimal values with the percentage number format applied. Formula: T= total revenue. Your business's profit margin is a percentage that represents the value of each dollar of revenue (total business earnings) after taking into account any business-related expenses. Gross Profit = Net Sales Cost of Goods Sold. Step 4: Finally, the formula for gross margin can be derived by dividing the gross profit (step 3) by the net sales (step 1) and then multiply by 100% as shown below. = divide ( [Gross Margin], [Total Sales]) of course [Total Sales] has to return the correct value for this to work regardless of where it is in a pivot table. C= total cost of the product. 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. Cost Price $ *. How do I calculate gross margin in Excel? 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. Divide the gross margin by revenue to calculate gross margin percentage. . } Your email address will not be published. Step 2: Calculate Gross Markup as a Percentage. If you multiply the value with 100 you will get the. 2. Example: Last year a company had a gross margin of 20 percent. How do gross profit margin and operating profit margin differ? Use of Excel Functions By signing up, you agree to our Terms of Use and Privacy Policy. Put the cost of goods sold into column B1. Right-click cell C1 and select "Format Cells." Click "Percentage" from the Numbers tab and click "OK" to convert the gross margin to percentage format. 2013-2022 ExcelDemy.com. To do that, highlight 2 and enter = (A2-B2) into the formula line. Here, C5 is the Selling Price, D5 is the Cost of Goods Sold and E5 is the Operational Cost. There are several methods to earn revenue, but operational revenue is earned by the core business activities that the organization undertakesin its daily operations. 1.Create a new sheet in Microsoft Excel. Operating Profit Margin is the difference between the Selling Price and the Cost of Goods Sold, Operational Cost (Rent, Equipment, Inventory cost, Advertisement, etc.) To calculate the weighted average gross margin for the business it is necessary to weight each product gross margin by its corresponding revenue. N - Indicator of extra charge; M - Indicator of margin; Ct - The price of the goods; S - The cost price. He writes for both online and offline publications, including the Journal of Asian Martial Arts, Samsung, Radio Shack, Motley Fool, Chron, Synonym and more. To calculate gross margin, subtract Cost of Goods Sold (COGS) from total revenue and divide that number by total revenue (Gross Margin = (Total Revenue - Cost of Goods Sold)/Total Revenue). The Cost of Goods Sold (COGS) is the cumulative total of direct costs incurred for the goods or services sold, including direct expenses like raw material, direct labour cost and other direct costs. It doesnt include any other expenses into account except the cost of goods sold. Repeat for the other products sold by company. Calculate total revenue. (function() { Step 2: Next, figure out the cost of goods sold or the cost of sales from the income statement. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Claire Boyte-White is the lead writer for NapkinFinance.com, co-author of I Am Net Worthy, and an Investopedia contributor. By using a simple formula we will calculate it in this section. For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. dropdown.parentNode.submit(); For example, If the company manufactures and sells handmade wooden furniture, the COGS would include all direct costs (such as timber, varnish, and nails), as well as indirect costs related to the production process, such as salaries of workers who assemble and ship the furniture; warehousing; and manufacturing equipment depreciation. The gross margin derives by deducing the cost of goods sold (COGS) from the net revenue or net sales (gross sales reduced by discounts, returns, and price adjustments). This percentage shows the amount of revenue that remains after delivery of goods or services. To begin with, select cell D7. GAAP (Generally Accepted Accounting Principles) are standardized guidelines for accounting and financial reporting. For the year ended June 30, Microsoft Inc. had revenue from products and services and another department of $66,069 million and $59,774 million, respectively. The net margin, by contrast, is only 14.8%, the sum of $12,124 of net income divided by $82,108 in revenue. Steps: Go to Developer Tab >> Visual Basic Option. Instead of just Margin/'Sales Revenue', you could either use the following that will give you 0% when the sales price is 0, regardless of the cost. Note that Gross Profit is not Net Income. Save my name, email, and website in this browser for the next time I comment. Related Topics . If you currently have a sales mix, meaning you sell multiple products, it can be helpful to calculate the margin mix for all of your products individually. Then, you will get the Gross Profit Margin for the products. As of September 28, 2019, Apple Inc. has sold products and services worth $213,833 million and $46,291 million. . Gross income represents the total income from all sources, including returns, discounts, and allowances, before deducting any expenses or taxes. The total receipt from selling the goods or services is, The remaining goods are known as inventory, whether in a finished or unfinished condition. Here is a snippet of the template: More Retail Calculators. Step 3: Next, calculate the gross profit by deducting the cost of goods sold (step 2) from the net sales (step 1). Now, to add percentage select the values of the Gross Profit Margin column and then select the Percent Style option under the Home tab. 1. Power BI Microsoft Information & communications technology Technology . Gross Profit vs. Net Income: What's the Difference? Eventually, we will get the Gross Profit Margin Percentages for the products. Let us take the example of Samsung to illustrate the computation of gross margin for a real-life company. Gross Margin. Step 1: Calculate Gross Margin as a Dollar Amount Image Credit: Screenshot courtesy of Microsoft. Finally, you will get the Net Profit Margin Percentage values for different products. If you have any suggestions or questions, feel free to share them in the comment section. Expressed as a percentage, the net profit margin shows how much of each dollar collected by a company as revenue translates into profit. Verify your margin percentage and add one to the particular margin. Also, in the same period, the cost of revenue Cost Of RevenueThe cost of revenue is the total expense incurred from manufacturing to delivering a product or service to the customer. The company's income statement lists both values. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. For instance, assume your margin is 20 %, so one in addition 0. Therefore, Samsungs Gross Margin deteriorated slightly to decline from 46.03% in 2017 to 45.69% in 2018. We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience. Select the range of your dataset. I earn a small commission if you buy any products using my affiliate links to Amazon.

Operating profit is the total earnings from a company's core business operations, excluding deductions of interest and tax. /* ]]> */, How to Calculate Margin Percentage in Excel (5 Easy Ways), 5 Ways to Calculate Margin Percentage in Excel, Method-1: Calculate Margin Percentage in Excel for Gross Profit Margin, Method-2: Calculate Margin Percentage in Excel for Operating Profit Margin, Method-3: Calculate Margin Percentage in Excel for Net Profit Margin, Method-4: Using Table Option to Calculate Margin Percentage, Method-5: Using a VBA Code to Calculate Margin Percentage. Let us see some simple to advanced models of the gross margin equation to understand it better. so e.g. 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. The formula for gross margin is very simple and can be derived by dividing the difference between the net sales and the cost of goods sold by the net sales, which is then expressed in terms of percentage by multiplying by 100%. From the gross profit, you can calculate the gross profit margins with the following formula: Gross profit margin or gross profit percentage = (Revenue - cost of goods sold) / Revenue X 100. You can learn more about financial analysis from the following articles: , Your email address will not be published. As an example, if total revenue was $150 million and total costs were $90 . Gross profit percentage formula = Gross profit / Total sales * 100%. To calculate this margin percentage follow this method. Harassment is any behavior intended to disturb or upset a person or group of people. Selling = 100 / (1 - 0.4) = $166.67. Gross profit margin is the difference between selling price minus cost of goods divided by selling price. Gross margin may be calculated for an entire company by totaling all sales, or it may be calculated for individual items to describe its individual profit margin. The gross margin equation expresses the percentage of gross profitPercentage Of Gross ProfitGross profit percentage is used by the management, investors, and financial analysts to know the economic health and profitability of the company after accounting for the cost of sales. COGS, as the name implies, include all of the direct costs and expenses attributable to the production of items by a company. Just remember, this figure does not include the costs of running the business, which is subtracted later. Lastly, divide the total revenue into the net profit to get your sales margin. One screenshot. So if revenue is at the top of the income statement, subtracting COGS from it gives you gross profit; further subtracting SG&A expense gives you operating profit also known as earnings before interest and tax (EBIT). Advanced Excel Exercises This formula divides remaining revenue by sales revenue: =IMDIV (B6,B3) Finally, multiply the gross profit ratio by 100 to solve for gross profit margin. Return to the worksheet where you calculated gross margin as a dollar amount.