How to Calculate Gross Profit Margin Using Excel

Gross profit margin measures the amount of revenue a company has after the cost of goods sold is taken into account. If a company has a higher gross margin within its industry and against a benchmark, then it is running more efficiently than its peers and competitors. Gross profit margin is calculated as gross profit divided by total sales. Microsoft Excel can easily calculate gross profit margin to help you analyze various scenarios by changing the revenue and cost of goods sold variables.

Difficulty: Moderate

Instructions

1. Open a new workbook in Microsoft Excel. Enter the sales and cost of good sold data down a column. For example, if you want to calculate the gross profit margin for a company that has sales of $500,000 and cost of goods sold of $200,000 enter the following information in these cells:
   
   A1 - Sales
   B1 - 500000
   A2 - Cost of goods sold
   B2 - 200000
   A3 - Gross profit
   A4 - Gross profit margin

2. Type "=B1-B2" in cell B3 to calculate the gross profit. Gross profit is equal to sales minus cost of goods sold. In this example, the company's gross profit is $300,000.

3. Type "(B3/B1)*100" in cell B4 to calculate the gross profit margin. In this example, the company's gross profit margin is 60%, which indicates that 60% of the revenue the company generates goes toward profit, while 40% goes toward expenses of producing those sales.