Loading exercise…
Loading exercise…
Exercises are built for a keyboard. Read the task here, then open this page on a laptop to solve it.
The pricing team wants to see which December order lines were sold at thin margins because of discounts. The band thresholds sit in K1 and K2 so they can be tuned.
Fill H2:H25 with a band for each line. Margin = (net revenue - cost) / net revenue, where net revenue = Qty * Unit price * (1 - Discount) and cost = Qty * Unit cost. The band is "High" when the margin is at least K1, "Medium" when it is at least K2, otherwise "Low". Use LET to name the revenue, the cost and the margin, and point at K1 and K2.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order ID | Product | Qty | Unit price | Discount | Unit cost | Band | High from | 52% | ||
| 2 | 51006 | Toner Cartridge Black | 1 | $59.00 | 0% | $28.00 | Medium from | 48% | |||
| 3 | 51006 | Whiteboard Markers (12) | 4 | $13.50 | 5% | $6.30 | |||||
| 4 | 51006 | Espresso Beans 1kg | 6 | $24.00 | 0% | $11.50 | |||||
| 5 | 51006 | Microfiber Cloths (50) | 5 | $23.50 | 0% | $11.00 | |||||
| 6 | 51007 | Paper Towels (24) | 7 | $25.00 | 10% | $12.40 | |||||
| 7 | 51008 | Envelope Box (250) | 12 | $12.50 | 5% | $6.10 | |||||
| 8 | 51008 | Whiteboard Markers (12) | 11 | $13.50 | 0% | $6.30 | |||||
| 9 | 51008 | Recycled Paper A4 (500) | 5 | $6.20 | 5% | $3.60 | |||||
| 10 | 51009 | All-Purpose Cleaner 5L | 3 | $14.00 | 0% | $6.70 | |||||
| 11 | 51010 | Espresso Beans 1kg | 6 | $24.00 | 5% | $11.50 | |||||
| 12 | 51010 | All-Purpose Cleaner 5L | 6 | $14.00 | 5% | $6.70 | |||||
| 13 | 51010 | Toner Cartridge Black | 1 | $59.00 | 0% | $28.00 | |||||
| 14 | 51011 | Oat Milk 12-pack | 6 | $21.50 | 0% | $10.20 | |||||
| 15 | 51011 | Paper Towels (24) | 4 | $25.00 | 5% | $12.40 | |||||
| 16 | 51012 | Oat Milk 12-pack | 13 | $21.50 | 10% | $10.20 | |||||
| 17 | 51013 | Label Roll (1000) | 3 | $9.90 | 0% | $4.20 | |||||
| 18 | 51014 | Oat Milk 12-pack | 3 | $21.50 | 0% | $10.20 | |||||
| 19 | 51014 | Toner Cartridge Black | 2 | $59.00 | 0% | $28.00 | |||||
| 20 | 51015 | Sticky Notes (24) | 12 | $12.00 | 10% | $5.60 | |||||
| 21 | 51015 | Toner Cartridge Black | 8 | $59.00 | 0% | $28.00 | |||||
| 22 | 51015 | Espresso Beans 1kg | 10 | $24.00 | 0% | $11.50 | |||||
| 23 | 51016 | Envelope Box (250) | 3 | $12.50 | 0% | $6.10 | |||||
| 24 | 51016 | Microfiber Cloths (50) | 1 | $23.50 | 0% | $11.00 | |||||
| 25 | 51016 | Espresso Beans 1kg | 5 | $24.00 | 0% | $11.50 | |||||
| 26 | |||||||||||
| 27 | |||||||||||
| 28 |
=IF(B2>=1.5,SUM(C2:C9),0)Language
Full plan
Excel ✓ · SQL and Power BI also included
Cancel anytime · 14-day full refund · Secure payment by Stripe