How to calculate gross profit margin in excel
Gross profit margin measures how much of the company's revenues are retained as profits.
The higher the gross profit margin, the more of every dollar of sales stays with the company.
When comparing gross profit margins, stay within the same industry because margins differ in different industries.
If a margin is lower compared to the rest of the industry, prices at that company may be set too low. By using Microsoft Excel, you can quickly calculate the gross profit margin, as well as how the gross profit margin would change if your sales or revenues changed.
Enter "Total Revenue" in cell A1, "Total Costs" in cell A2 and "Gross Profit Margin" in cell A3.
Enter your total revenue in cell B1 and your total costs in cell B2. The revenues include all income from selling the product. For example, if you sell 10,000 toys at £32 each, your total revenue would be £325,000. The total costs include how much you spend to make each product and get it to market. This includes the cost of the raw materials and labour to produce the product, but not costs that you incur for research and development or to rent the store.
Enter the formula "=(B1-B2)/B1*100" in cell B3. The gross profit margin (expressed as a percentage) will display. For example, if you had £325,000 in revenue and £292,500 in costs, a gross profit margin of "10" (10 per cent) would show in cell B3.
- You can manipulate your spreadsheet to give more detailed gross profit margin results. For example, if you have multiple products, you can list each product in column A, list the revenues from that product in column B, list the costs of the product in column C and enter the formula "=(Bx-Cx)/Cx_100" in column D. However, instead of "x" in the formula, use the row number. For example in the third row, you would enter "=(B3-C3)/C3_100". If you want to later calculate the total gross profit margin, enter "=(SUM(B:B)-SUM(C:C))/(SUM(C:C))*100" in a cell E1.
- Gross profit margin is different from net profit margin because gross profit margin only includes the costs of production. Net profit margin includes all costs including research, interest on debt and taxes.