Rate of Return (ROS) in Excel is the ratio of the profit earned to the total amount of fixed and working capital used in the same period. From here, we can determine the actual profitability of the company, and know the net profit of the shareholders in that business.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
Gross profit margin
Let’s say your business had total sales of $10,000 in July and a cost of goods sold (COGS) of $4,000.
To calculate gross margin, use the following formula:
Gross profit margin = (Total revenue – Cost of goods sold) / Total revenue
Total Revenue, Cost of Goods Sold (COGS).
1. The formula below calculates gross profit.
2. Divide this result by total sales to calculate gross profit margin in Excel.
3. On the Home tab, in the Number group, click the percentage icon to apply Percentage formatting.
Result:
Operating profit margin
Operating margin also includes operating expenses (OPEX) such as rent, equipment, inventory costs, marketing, etc.
To calculate operating margin, use the following formula:
Operating profit margin = (Total revenue – Cost of goods sold – OPEX)/Total revenue.
Total Revenue, Cost of Goods Sold (COGS).
1. The formula below calculates operating profit.
2. Divide this result by total sales to calculate the operating profit margin in Excel.
3. On the Home tab, in the Number group, click the percentage icon to apply Percentage formatting.
Result:
Net profit margin
Net margin also includes non-operating expenses such as interest (debt) and taxes.
To calculate net profit margin, use the following formula:
Net profit margin = (Total revenue – Cost of goods sold – OPEX – I – T) / Total revenue.
COGS = Cost of Goods Sold
OPEX = Operating expenses
I = Interest
T = Taxes
1. The formula below calculates the number on the fractional line. This is called net income.
2. Divide this result by total sales to calculate net profit margin in Excel.
3. On the Home tab, in the Number group, click the percentage icon to apply Percentage formatting.
Result:
Conclusion: a 35% net profit margin means your business has a net income of $0.35 for every dollar of sales.
In addition, you can also view many other excel articles here.
