Lesson 2 of 13 · 22 min
Product profitability and gross margin
By the end of this lesson you can calculate cost of goods sold, gross margin and margin % by product, category and department, handle products with no recorded cost, and adjust margin for returned units.
Gross margin is what is left of sales after paying for the goods that were sold:
| Measure | Definition in this lesson |
|---|---|
| Net sales | Σ (quantity × unit_price − discount_amount), as in the previous lesson |
| COGS (cost of goods sold) | Σ (quantity × products.unit_cost) |
| Gross margin | net sales − COGS |
| Margin % | gross margin ÷ net sales × 100 |
Two modelling assumptions are worth saying out loud, because a finance reviewer will ask:
order_items.unit_priceis the price charged on the order, so revenue uses it.products.unit_priceis today's list price and is the wrong number for history.products.unit_costis the current standard cost. There is no cost history inshop, so every period is costed at today's cost. In a real ERP you would join to the cost that was valid on the order date, exactly as you will do for salaries later in this track.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons