Profit and Margin by Product
Your task: In E2:F7, calculate each product's profit and margin, plus the totals.
Full instructions, scenario and goal
Scenario
The owner of an office-furniture shop wants to know which products really make money. For each of five products you have the units sold last quarter, the selling price and the unit cost. You need the profit and the margin of each product, and then the total profit and the margin for the shop as a whole.
Your task
- Look at the units, price and cost for each product in A1:D6.
- In E2, calculate the profit of the first product: units sold times (price minus cost). In F2, calculate its margin: profit divided by sales (units times price).
- Copy E2:F2 and paste them over E3:F6.
- In E7, calculate the total profit. In F7, calculate the overall margin: total profit divided by total sales.
- Select Check Answer.
You are done when
- E2:F6 contain formulas for each product's profit and margin, and E7:F7 contain the totals.
- The overall margin is total profit divided by total sales, not an average of the margins.
- All results follow the units, prices and costs in the sheet.
What you will practise
Turn unit figures into profit and margin, and understand why an overall margin is not the average of the product margins.
The idea in 30 seconds
Profit is what is left of the sales after the costs: for one product, the units sold times the difference between the unit price and the unit cost. Margin expresses that profit as a share of the sales, so a margin of 38% means that each dollar of sales leaves 38 cents of profit. It is a ratio, so it compares products of very different sizes fairly.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell E2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
Profit is what is left of the sales after the costs: for one product, the units sold times the difference between the unit price and the unit cost. Margin expresses that profit as a share of the sales, so a margin of 38% means that each dollar of sales leaves 38 cents of profit. It is a ratio, so it compares products of very different sizes fairly.
To get an overall margin, add up the profits and divide by the total sales. Averaging the product margins is a common mistake: it treats a product that sells a handful of units the same as a best-seller. SUMPRODUCT multiplies two ranges row by row and adds the results, which gives the total sales in one step without a helper column.
Skills you will practise
- Profit
- Margin
- SUM
- SUMPRODUCT
- Percentages
Complete first: Total, Average and Highest Monthly Spend
Common mistakes to avoid
- Dividing profit by cost
- Profit divided by cost is the markup. Margin is profit divided by the selling price (sales).
- Averaging the margins
- An average of the margins ignores how much each product sold. Divide total profit by total sales instead.
- Leaving a product out of the total
- A SUM range that stops one row early drops a product from the total, which is easy to miss.