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. This calculation helps you analyse various business scenarios if you change the revenue and cost of goods sold variables.

Open Excel and select a new workbook. Enter the sales and cost of good sold data labels in the first two rows of your first column, then enter their associated costs in the second two rows of your second column. Use the third row to store your calculations. For example, to calculate the gross profit margin for a company that has sales of £325,000 and cost of goods sold of £130,000, enter "Sales" in cell A1, "Cost of Goods Sold" in A2; 500000 in B1 and 200000 in B2. In cell A3, enter "Gross Profit" and "Gross Profit Margin" in cell A4.

Type the formula "=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 £195,000.

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

About the Author

Shreya Mehta graduated from the University of Massachusetts with a Bachelors degree in business administration with a double concentration in finance and MIS. She attended Bentley College to obtain a MBA in finance and Masters in IT. She has been working for a financial software company for the past three years as an associate content manager.

Try our awesome promobar!