Gross profit percentage is a measure of how much profit a business makes from its sales, after deducting the cost of goods sold (COGS). It is also known as gross margin or gross profit ratio. It shows how efficiently a business uses its resources to produce and sell its products or services.
The formula for gross profit percentage is:
where:
- Gross Profit = Total Revenue – COGS
- Total Revenue = The amount of money received from selling the products or services
- COGS = The amount of money spent on producing or purchasing the products or services
How to Calculate Gross Profit Percentage in Excel Using a Formula
To calculate gross profit percentage in Excel using a formula, you need to have the following data in your worksheet:
- Total Revenue: The amount of money received from selling the products or services
- COGS: The amount of money spent on producing or purchasing the products or services
For example, suppose you have the following data in cells A1 to B5:
| Product | Revenue | COGS |
|---|---|---|
| A | 1000 | 400 |
| B | 2000 | 800 |
| C | 1500 | 600 |
| D | 2500 | 1000 |
| E | 3000 | 1200 |
To calculate the gross profit percentage for each product, follow these steps:
- In cell C1, type the following formula:
=(B1-A1)/B1 - Press Enter to get the result.
- Drag the fill handle down to cell C5 to copy the formula to the rest of the cells.
- Select cells C1 to C5 and click the percentage symbol (%) on the Home tab, in the Number group, to apply the percentage format.
You should get the following results:
| Product | Revenue | COGS | Gross Profit Percentage |
|---|---|---|---|
| A | 1000 | 400 | 60% |
| B | 2000 | 800 | 60% |
| C | 1500 | 600 | 60% |
| D | 2500 | 1000 | 60% |
| E | 3000 | 1200 | 60% |
This means that for every product, the gross profit percentage is 60%, which means that the business makes 60 cents of profit for every dollar of revenue.
How to Calculate Gross Profit Percentage in Excel Using a Pivot Table
Another way to calculate gross profit percentage in Excel is to use a pivot table. A pivot table is a tool that allows you to summarize and analyze large amounts of data in a simple and interactive way.
To calculate gross profit percentage in Excel using a pivot table, you need to have the same data as before, but in a different format. You need to have one column for the product name, one column for the revenue, and one column for the COGS. For example, suppose you have the following data in cells A1 to C11:
| Product | Revenue | COGS |
|---|---|---|
| A | 1000 | 400 |
| A | 500 | 200 |
| B | 2000 | 800 |
| B | 1000 | 400 |
| C | 1500 | 600 |
| C | 750 | 300 |
| D | 2500 | 1000 |
| D | 1250 | 500 |
| E | 3000 | 1200 |
| E | 1500 | 600 |
To calculate the gross profit percentage for each product using a pivot table, follow these steps:
- Select the data range from A1 to C11.
- Click the Insert tab, in the Tables group, click PivotTable.
- In the Create PivotTable dialog box, choose where you want to place the pivot table. For example, you can choose to place it in a new worksheet or in the existing worksheet. Click OK.
- In the PivotTable Fields pane, drag the Product field to the Rows area, the Revenue field to the Values area, and the COGS field to the Values area again.
- Right-click any cell in the Revenue column and choose Summarize Values By > Sum.
- Right-click any cell in the COGS column and choose Summarize Values By > Sum.
- Right-click any cell in the COGS column and choose Show Values As > % of Column Total.
You should get the following results:
| Product | Sum of Revenue | Sum of COGS | % of Column Total |
|---|---|---|---|
| A | 1500 | 600 | 40% |
| B | 3000 | 1200 | 40% |
| C | 2250 | 900 | 40% |
| D | 3750 | 1500 | 40% |
| E | 4500 | 1800 | 40% |
| Grand Total | 15000 | 6000 | 40% |
This means that for each product, the gross profit percentage is 40%, which means that the business makes 40 cents of profit for every dollar of revenue.
How to Interpret Gross Profit Percentage in Excel
Gross profit percentage is an important indicator of the profitability and efficiency of a business. It shows how much of the revenue is left after paying for the cost of goods sold. A higher gross profit percentage means that the business has a higher profit margin and can cover its other expenses and generate more net income. A lower gross profit percentage means that the business has a lower profit margin and may struggle to pay its other expenses and generate less net income.
However, gross profit percentage alone is not enough to evaluate the performance of a business. It does not take into account other factors such as operating expenses, interest, taxes, and competition. Therefore, it is advisable to compare the gross profit percentage of a business with its industry average, its historical trend, and its competitors. This can help to identify the strengths and weaknesses of the business and to make informed decisions.
Other Approaches to Calculate Gross Profit Percentage in Excel
There are other approaches to calculate gross profit percentage in Excel, such as using the IF function, the SUMIF function, or the SUMPRODUCT function. These functions can be useful when you have more complex or conditional calculations. For example, you can use the IF function to calculate the gross profit percentage for different categories of products, such as high-end or low-end. You can use the SUMIF function to calculate the gross profit percentage for different regions or markets, such as North America or Europe. You can use the SUMPRODUCT function to calculate the gross profit percentage for different combinations of criteria, such as product and region.
Here are some examples of how to use these functions to calculate gross profit percentage in Excel:
- Using the IF function: Suppose you have the following data in cells A1 to D5:
| Product | Category | Revenue | COGS |
|---|---|---|---|
| A | High-end | 1000 | 400 |
| B | Low-end | 2000 | 800 |
| C | High-end | 1500 | 600 |
| D | Low-end | 2500 | 1000 |
| E | High-end | 3000 | 1200 |
To calculate the gross profit percentage for the high-end products, you can use the following formula in cell E1:
=SUMIF(B2:B5,"High-end",C2:C5)-SUMIF(B2:B5,"High-end",D2:D5))/SUMIF(B2:B5,"High-end",C2:C5)
This formula will sum the revenue and the COGS for the products that have the category “High-end” in column B, and then subtract the COGS from the revenue and divide by the revenue. The result is 0.6 or 60%.
- Using the SUMIF function: Suppose you have the following data in cells A1 to D5:
| Product | Region | Revenue | COGS |
|---|---|---|---|
| A | NA | 1000 | 400 |
| B | EU | 2000 | 800 |
| C | NA | 1500 | 600 |
| D | EU | 2500 | 1000 |
| E | NA | 3000 | 1200 |
To calculate the gross profit percentage for the NA region, you can use the following formula in cell E1:
=SUMIF(B2:B5,"NA",C2:C5)-SUMIF(B2:B5,"NA",D2:D5))/SUMIF(B2:B5,"NA",C2:C5)
This formula will sum the revenue and the COGS for the products that
have the same region “NA” in column B, and then subtract the COGS from the revenue and divide by the revenue. The result is 0.6 or 60%.
- Using the SUMPRODUCT function: Suppose you have the following data in cells A1 to D5:
| Product | Region | Category | Revenue | COGS |
|---|---|---|---|---|
| A | NA | High-end | 1000 | 400 |
| B | EU | Low-end | 2000 | 800 |
| C | NA | High-end | 1500 | 600 |
| D | EU | Low-end | 2500 | 1000 |
| E | NA | High-end | 3000 | 1200 |
To calculate the gross profit percentage for the high-end products in the NA region, you can use the following formula in cell E1:
=SUMPRODUCT((B2:B5="NA")*(C2:C5="High-end")*C2:C5)-SUMPRODUCT((B2:B5="NA")*(C2:C5="High-end")*D2:D5))/SUMPRODUCT((B2:B5="NA")*(C2:C5="High-end")*C2:C5)
This formula will multiply the revenue and the COGS by the criteria of region and category, and then sum the products that match the criteria. Then, it will subtract the COGS from the revenue and divide by the revenue. The result is 0.6 or 60%.
