Finance / Toolkit
Excel Lab — Margin, Markup and Break-Even
Separate gross margin, markup and contribution, then calculate break-even using an explicit product mix. Includes a live two-product Excel model.
Return to your library resultsHow to use this toolkit.
Workflow
- Review the main results on Profit lab.
- Edit the amber planned baskets, fixed costs and product inputs.
- Resolve all input warnings, then compare gross margin, markup and contribution.
- Test the price and zero-contribution exercises.
Assumptions & limitations
Synthetic IDR data. One basket contains 3 units of A and 2 units of B. Constant mix, linear prices and costs, sufficient capacity, and production equals sales. COGS is treated as variable in this example. Fixed operating costs cover the entire planned period. No taxes, financing, inventory timing, discounts or step costs are modelled. Two products only.
Example scenario
A sells for IDR 100,000 with IDR 50,000 COGS and IDR 10,000 other variable cost. B sells for IDR 150,000 with IDR 80,000 COGS and IDR 20,000 other variable cost. At 200 baskets and IDR 30 million fixed costs, profit is IDR 14 million.
| Metric | Illustrative result |
|---|---|
| Basket contribution | IDR 220,000 |
| Planned revenue | IDR 120,000,000 |
| Operating profit | IDR 14,000,000 |
| Break-even baskets | 137 |
| Break-even product units | 685 |
Sample data from the downloadable file; values are illustrative.
## The question behind the spreadsheet
A selling price can look attractive under one percentage and much less attractive under another. Which denominator are you using, which costs have you included, and how much volume must the business sell to cover fixed operating costs?
This lab uses two fictional products and an explicit sales basket. It connects pricing vocabulary to an operating decision. Review revenue, variable cost and fixed cost concepts in the Finance map before changing the model.
## Read the assumptions first
The original basket contains three units of A and two units of B. Product A has price 100,000, COGS 50,000 and other variable cost 10,000. Product B has price 150,000, COGS 80,000 and other variable cost 20,000. All amounts are IDR. The plan is 200 baskets and IDR 30 million of fixed operating costs.
The model assumes COGS is variable, costs are linear and the mix stays fixed. A different business may need fixed production overhead, discounts, capacity limits or inventory timing treated separately.
## Distinguish the three calculations
Gross profit per unit is price less COGS. Gross margin is gross profit divided by price. Markup on COGS is the same gross profit divided by COGS. Product A therefore has a 50% gross margin and a 100% markup.
Contribution per unit then deducts other variable costs. Product A contributes 40,000 and product B contributes 50,000. Using gross profit as contribution would ignore IDR 10,000 or IDR 20,000 of variable selling or delivery cost per unit.
The worked basket formula in B8 is =SUMPRODUCT(E23:E24,H23:H24). The first range contains units of each product per basket. The second contains contribution per unit. Matching the two ranges gives 3 × 40,000 + 2 × 50,000 = IDR 220,000.
## Calculate a usable break-even quantity
Fixed cost divided by basket contribution gives 136.36 baskets. B10 uses ROUNDUP to return 137 complete baskets. At the fixed 3:2 mix, that is 411 units of A and 274 units of B, or 685 product units. It is not 137 individual products.
At 137 baskets, profit is IDR 140,000. At 136 baskets it is a loss of IDR 80,000. These two checks explain the upward rounding. Planned revenue is IDR 120 million and operating profit is IDR 14 million.
If contribution is zero or negative, extra baskets cannot cover a positive fixed cost. The model shows No break-even. A zero COGS denominator makes markup unavailable; it does not make markup 0%.
## Practise with a decision
1. Reduce product A price to 90,000. Basket contribution falls to 190,000 and break-even rises to 158 baskets. Explain whether the implied increase in volume is feasible.
2. Restore the price. Set A price to 60,000 and B price to 100,000. Both contributions are zero and there is no finite break-even for the positive fixed costs. Restore the example.
3. Change the mix only after explaining what a basket now means. Recalculate the required units of each product, not just the total count.
## Self-check
Could gross margin rise while contribution falls? Yes: a higher variable delivery or commission cost can reduce contribution without changing COGS. Ask which cost changes with the decision and keep fixed-period costs separate.
## Continue through the pillar
Return to Finance Apply, then connect the result to customer mix, capacity and cash. A profitable plan can still require funding before customers pay.
Put the perspective to work.
Use these questions to connect the resource to your own business context.
What business decision does this measure inform?
Which volume, price, mix or cash driver explains the result?
What data and assumptions support that explanation?
What action should the owner take and monitor?
References & context.
Use the framework and jurisdiction applicable to your work. Original examples and templates do not imply endorsement by the referenced organisations.
A question, correction or missing source?
The resource link will be included in your message form.