← Financial Modeling in Excel from Scratch
Lesson
Practice: revenue by product and the break-even point
Calculate revenue by product with SUMPRODUCT, pull a price from a price list by name (VLOOKUP with an exact match, INDEX+MATCH), and find the break-even point, the margin of safety, and profit at the plan.
Worked example: Zerno's espresso
Worked example: Zerno's espresso
The price list of the Zerno coffee shop is in the table below: the price, how many sell on a typical day, and the variable cost per unit. Fixed costs are $6,800 a month (rent, salaries, other). Let's work through one row — espresso.
Espresso revenue for the day: =B2*C2 → 3 × 30 = $90. =SUMPRODUCT(prices, quantities) gives the revenue of all rows in one formula: the function multiplies the pairs row by row and adds them up. Contribution margin per cup of espresso: =B2-D2 → 3 − 0.80 = $2.20. If espresso alone covered fixed costs, the break-even point would be 6,800 / 2.20 ≈ 3,091 cups: a fractional cup is rounded up.
You pull a price by product name with a lookup: =VLOOKUP(name, table, column_number, 0) or =INDEX(price_column, MATCH(name, name_column, 0)). Zero (FALSE) means an exact match.
Next we work with cappuccino, the best seller. A simplification: every cup is a cappuccino. It's optimistic: cappuccino has the highest margin. A real model uses the average margin across the sales mix: SUMPRODUCT(margins, quantities) / SUM(quantities).
• Break-even point, cups = fixed costs / contribution margin per cup.
• In dollars = cups × price (or fixed costs / contribution margin ratio).
• Margin of safety = (plan − break-even) / plan.
• Profit = plan × margin per cup − fixed costs.
Zerno's price list, a typical day
| Product | Price, $ | Sold per day | Variable cost per unit, $ |
|---|---|---|---|
| Espresso | 3 | 30 | 0.80 |
| Cappuccino | 5 | 90 | 1.60 |
| Croissant | 4 | 20 | 1.80 |
Lesson notes
Worked example: Zerno's espresso
The price list of the Zerno coffee shop is in the table below: the price, how many sell on a typical day, and the variable cost per unit. Fixed costs are $6,800 a month (rent, salaries, other). Let's work through one row — espresso.
Espresso revenue for the day: =B2*C2 → 3 × 30 = $90. =SUMPRODUCT(prices, quantities) gives the revenue of all rows in one formula: the function multiplies the pairs row by row and adds them up. Contribution margin per cup of espresso: =B2-D2 → 3 − 0.80 = $2.20. If espresso alone covered fixed costs, the break-even point would be 6,800 / 2.20 ≈ 3,091 cups: a fractional cup is rounded up.
You pull a price by product name with a lookup: =VLOOKUP(name, table, column_number, 0) or =INDEX(price_column, MATCH(name, name_column, 0)). Zero (FALSE) means an exact match.
Next we work with cappuccino, the best seller. A simplification: every cup is a cappuccino. It's optimistic: cappuccino has the highest margin. A real model uses the average margin across the sales mix: SUMPRODUCT(margins, quantities) / SUM(quantities).
• Break-even point, cups = fixed costs / contribution margin per cup.
• In dollars = cups × price (or fixed costs / contribution margin ratio).
• Margin of safety = (plan − break-even) / plan.
• Profit = plan × margin per cup − fixed costs.