In any business organization, large or small, you need to know some bookkeeping basics, e.g. how to calculate profit or loss. Profit is the difference between the selling price and the cost price of the product. Profit, divided by the cost price then multiplied by 100 gives the profit percentage. In this article, we will show 3 methods to calculate profit percentage in Excel.
We will show how to calculate 3 types of profit margin percentages with examples in this section.
Assume that we have the following dataset of a clothing store with their selling price and the cost.
1. Using Excel Formula to Calculate Gross Profit Percentage
Gross profit is the simplest form of profit. We just deduct the cost of the product from the total revenue, and we get this. We do not consider other costs of business in this profit margin. It is a preliminary profit idea.
Now we will show the calculation process.
- First, we’ve added two more columns to show profit and percentage.
- Now we will find out the profit using the Price and Cost. Go to Cell E4 & put the following formula.
=C4-D4
- Now, drag the Fill Handle icon.
Here, we get the profit by subtracting cost from revenue.
- Now, we will find out the percentage. Divide the profit by the price or revenue. Go to Cell F4 Then type the below formula.
=E4/C4
- Now, double click the Fill Handle icon.
- You can see that we’ve got the results in decimal form. Now, we will convert this value into percentage form. Select all the cells of the Percentage column.
- Then, choose the Percentage (%) format from the Number group.
Now, look at the result.
Finally, we get the Gross Profit Percentage.
Read More: How to Calculate Gross Profit Margin Percentage with Formula in Excel
2. Calculating Operating Profit Percentage in Excel
We will get the Operating profit by deducting the operating cost and the cost of the product from the revenue. The operating cost includes transportation, the salary of employees, rent, marketing costs, and maintenance costs. The total operating cost is also known as SG&A.
The formula for Operating Profit is as follows:
Now, we see the calculation process of the operating profit. In the below dataset, we have different operating costs from the product cost.
We will find out the operating profit by following the steps below.
Steps:
- First, we will find out the total operating cost using the SUM function. Go to Cell C11.
- Write down the following formula.
=SUM(C6:C9)
- Press Enter to get the result.
We get the total operating cost here.
- We will find out the operating profit by subtracting the cost of goods and operating costs from the revenue. Put the following formula on Cell C12.
=C4-C5-C11
- Again, hit the Enter button.
- Now, divide the profit by the revenue.
- Divide the profit by the revenue. Put the following formula on Cell C13.
=C12/C4
- Finally, press the Enter button.
Read More: How to Use Profit and Loss Percentage Formula in Excel
3. Determining Net Profit Percentage in Excel
In this example, we will show how to determine the Net profit. We know that we need to pay a certain amount of tax to the authority. Also, the company has to pay interest on bank loans. This net profit is determined after deducting all the tax and interest along with the remaining costs.
Steps:
- We have tax and interest in our dataset. And the operating cost already calculated.
- We have tax and interest in our dataset. And the operating cost has already been calculated.
- Determine the profit by subtracting the tax, interest, and other costs. Put the formula on cell C14.
=C4-C5-C13-C10-C11
- Press the Enter button to execute.
- Go to Cell C15.
- Put formula on that cell.
=C14/C4
- Hit the Enter button again. Convert the result into a percentage.
Read More: How to Calculate Net Profit Margin Percentage in Excel
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, we described how to calculate the profit percentage in Excel. We showed three different types of profit calculation here. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
