Gross profit is defined as revenue minus the cost of goods sold (COGS). COGS, as the name implies, include all of the direct costs and expenses attributable to the production of items by a company. For example, If the company manufactures and sells handmade wooden furniture, the COGS would include all direct costs (such as timber, varnish, and nails), as well as indirect costs related to the production process, such as salaries of workers who assemble and ship the furniture; warehousing; and manufacturing equipment depreciation.
Since operational expenses like corporate office rent, sales people's commissions and insurance are not directly involved in the production of the furniture, they would be in a separate expense category, probably called Selling, General & Administrative (SG&A) expense.
So if revenue is at the top of the income statement, subtracting COGS from it gives you gross profit; further subtracting SG&A expense gives you operating profit also known as earnings before interest and tax (EBIT).
Calculating Gross Profit Margin in Excel
It is easy to calculate: Simply take the gross profit for a period and divide it by revenue for that same period. This percentage shows the amount of revenue that remains after delivery of goods or services. Just remember, this figure does not include the costs of running the business, which is subtracted later.
After importing historical data and forecasting any future periods, here's how you determine the gross margin:
- Input historical revenue from the income statement
- Input historical COGS from the income statement
- Forecast future revenue growth and apply this percentage to future periods (usually starting with a consensus estimate)
- Calculate the average of previous gross margins to use as an estimate for gross margins going forward
Following best practices in Excel, you want to break all these numbers out separately, so they are easily trackable and auditable.