Challenge Scenario
In retail and commerce, business owners constantly set prices. Two critical terms are frequently mixed up: Profit Margin (how much profit you keep from the selling price) versus Markup (how much percentage you added on top of your cost).
As a Pricing Analyst, you are analyzing four product SKUs:
- Gross Profit (Column D): Subtract unit cost from selling price:
=C2-B2.
- Profit Margin % (Column E): Divide gross profit by selling price:
=(C2-B2)/C2 (or =D2/C2).
- Markup % (Column F): Divide gross profit by unit cost:
=(C2-B2)/B2 (or =D2/B2).
- Total Portfolio Profit (Cell D7): Sum all gross profits across items using
SUM(D2:D5).
- Average Profit Margin % (Cell E7): Compute the average margin percentage across items using
AVERAGE(E2:E5).
Your Sheet Layout
Understanding the pricing audit structure:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:C5 |
Product, Cost, Price |
Catalog Pricing |
Raw cost and retail price data |
Read-only catalog price points. |
D2:D5 |
Gross Profit ($) |
Profit Calculation |
=C2-B2 |
Calculates dollar profit per unit sold. |
E2:E5 |
Margin % |
Margin Zone |
=(C2-B2)/C2 |
Profit as a share of revenue (Selling Price). |
F2:F5 |
Markup % |
Markup Zone |
=(C2-B2)/B2 |
Profit as a markup above cost. |
D7 |
Total Profit |
Summary |
=SUM(D2:D5) |
Combined gross profit across all products ($155.00). |
E7 |
Average Margin % |
Summary |
=AVERAGE(E2:E5) |
Average portfolio profit margin (42.5%). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cells D2:D5, calculate the Gross Profit per unit (= Selling Price - Cost).
2
In cells E2:E5, calculate the Profit Margin % (= Gross Profit / Selling Price).
3
In cells F2:F5, calculate the Markup % (= Gross Profit / Unit Cost).
4
In cell D7, calculate total gross profit with SUM, and in E7 calculate average margin with AVERAGE.
Solution & Formula Breakdown
Let's examine how Margin vs Markup math works step-by-step:
1. Gross Profit Dollar Calculation (D2:D5)
=C2-B2
- Row 2 (Desk): $200 - $120 = $80.00 profit.
- Row 3 (Mouse): $25 - $15 = $10.00 profit.
- Row 4 (Keyboard): $90 - $45 = $45.00 profit.
- Row 5 (Monitor Arm): $50 - $30 = $20.00 profit.
2. Profit Margin vs Markup Ratio (E2:F5)
- Margin % (
=D2/C2): For the Keyboard, $45 profit / $90 price = 50.0% margin.
- Markup % (
=D2/B2): For the Keyboard, $45 profit / $45 cost = 100.0% markup! You doubled your cost to reach the selling price.
3. Portfolio Metrics (D7 & E7)
- Cell D7 (Total Profit):
=SUM(D2:D5) → $155.00.
- Cell E7 (Average Margin %):
=AVERAGE(E2:E5) → 42.5%.
Profit margin can never exceed 100%, but Markup % can be 200%, 500%, or higher! Remembering that Margin divides by Price and Markup divides by Cost prevents expensive pricing mistakes.