Guided project · 6 steps Cellwise Plus
Product profit report in Excel
Turn a product list into a report that shows what each product earns, the best one, and whether profit covers the fixed costs.
You will use: SUM, MAX, MATCH, INDEX, ROUND, IF
Starting data
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Product | Units | Price | Unit cost | Revenue | Profit | Margin | Total profit | ||
| 2 | Soy candle | 54 | 24 | 7.8 | Most profitable | |||||
| 3 | Bar soap | 97 | 9 | 2.6 | Fixed costs | 3800 | ||||
| 4 | Gift box | 32 | 58 | 26.5 | Covers fixed costs? | |||||
| 5 | Candle workshop | 18 | 45 | 12 | ||||||
| 6 | Wax melts | 40 | 7 | 2.1 |
Build it step by step
Step 1
Revenue is units times price. Write it in E2 and fill it down to E6.
Step 2
Profit is revenue minus what the units cost you. Write it in F2, fill to F6.
Step 3
Margin is profit as a share of revenue. Write it in G2, fill to G6.
Step 4
Total profit across all products, in J1.
Step 5
Name the product that makes the most profit, in J2.
Step 6
Does total profit cover the fixed costs in J3? Show "Covered" or "Short by" and the gap, in J4.