Build a model.
Make a better decision.
Start with one product, find the sales needed for a target, and then choose a profitable two-product mix.
Use one small bakery model to practice Excel, then use AI to help explain the setup and check the logic. We’ll refresh just the accounting you need.
Build the model
Connect the inputs to revenue, costs, income, and labor using formulas.
Plan for uncertainty
Find the sales needed for a target and compare sensible next-month scenarios.
Use limited capacity well
Choose a product mix that earns the most within labor and demand limits.
A tiny refresher before we bake.
What does one more box contribute?
A quick refresh before Excel: a sale pays its variable costs first. The amount left helps cover fixed costs and profit.
| Driver | Classic |
|---|---|
| Selling price per box | $20 |
| Variable cost per box | $12 |
| Contribution margin per box | $8 |
| Starting quantity | 400 |
| Labor hours per box | 0.10 |
Put the pieces together
400 Classic boxes contribute 400 × $8 = $3,200 toward the $4,000 monthly bills.
$3,200 − $4,000 = −$800 operating income.
All boxes produced are sold; there are no inventory changes. We use a contribution-margin income statement and ignore interest and taxes. Operating income is not cash flow.
Instructor note · Contribution margin
Ask: “If we sell one more Classic box, what changes?” Revenue rises $20; variable costs rise $12; contribution margin and operating income rise $8, provided capacity and fixed costs do not change. Total fixed costs stay $4,000.
Start with the amount still needed.
How many Classic boxes reach the target?
First build the formulas in the blank calculation cells on the Goal Seek sheet. Watch the animation to see each cell click and operator. Use the Classic-only model. Prices, unit costs, fixed costs, and labor per box stay fixed. Goal Seek changes just the Classic quantity in B8.
Try another target
Classic is the only product in this model. All other assumptions stay fixed.
Then check the plan
There are 90 labor hours and demand for at most 1,000 Classic boxes.
Try a $4,000 target. The equation can be solved, but can the bakery deliver?
Goal Seek reaches a target by changing one input. It does not enforce labor or demand limits.
Instructor solution · Classic targets
Set cell B20; To value 3000; By changing cell B8. Result: 875 boxes; 87.5 hours; $3,000 income. Break-even: 500 boxes and 50 hours. A $4,000 target needs 1,000 boxes and 100 hours, exceeding capacity.
What changes when we add Premium?
We will then have two product quantities to choose. Goal Seek can change only one at a time. Solver can choose both together and respect the business limits.
Find a plan that fits.
Find the best feasible product mix.
Now the bakery offers Premium too. The two-product demand estimates are 600 Classic and 300 Premium. Solver will choose both quantities, maximize income, and enforce labor, demand, and whole-box constraints.
Try a production plan
This browser activity evaluates whole-box plans. Practice the Solver setup in Excel too.
Translate the decision into a model
Flip one card at a time. Match its color and cell reference to the worksheet.
Constraints
Add Premium to the model
Keep fixed costs at $4,000 and labor capacity at 90 hours. For the two-product case, use these demand estimates.
| Driver | Classic · column B | Premium · column C |
|---|---|---|
| Price · row 5 | $20 | $30 |
| Variable cost · row 6 | $12 | $18 |
| Starting boxes · row 8 | 400 | 200 |
| Hours per box · row 9 | 0.10 | 0.20 |
| Demand limit · row 10 | 600 | 300 |
Revenue B16: =B8*B5+C8*C5
Variable costs B17: =B8*B6+C8*C6
Labor B13: =B8*B9+C8*C9
B18 = B16−B17 and B20 = B18−B19 still connect contribution margin to income.
Before Solver: contribution per labor hour
Adding Premium gives us two products competing for the same 90 labor hours. Each Classic box contributes $8 and takes 0.10 hour; Premium contributes $12 and takes 0.20 hour.
Which box earns more from a scarce hour?
Premium earns more per box, but takes twice as long. When labor is scarce, compare the contribution from one labor hour.
Refresh: contribution margin per limiting factor
The limiting factor is the resource that restricts activity. Here it is labor time.
Imagine one hour of available labor. It can make 10 Classic boxes or 5 Premium boxes. Which choice provides more toward the monthly bills and profit?
Priority has a limit
Compare the contribution per hour on the two cards. Give the higher-earning product priority, up to its demand limit, then use remaining hours for the other product.
The bakery can sell at most 600 Classic boxes. Producing extra boxes that nobody buys will not earn extra contribution margin.
This simple ranking works here because labor is the only shared capacity constraint, margins are positive, and fixed costs stay constant. More complex constraints are a reason to use Solver.
Instructor note · Why the product ranking changes
Per box: Premium wins ($12 versus $8). Per scarce hour: Classic wins ($80 versus $60). Do not allocate fixed costs to decide which product gets the next available hour; total fixed costs do not change in this case.
Excel help: save a Solver solution as a scenario
Instructor solution · Best feasible mix
600 Classic + 150 Premium. Revenue: $16,500. Variable costs: $9,900. Contribution margin: $6,600. Operating income: $2,600. Labor: 90 hours.
Classic uses 60 hours at its 600-box demand limit. The remaining 30 hours make 150 Premium boxes. Labor and Classic demand are binding; Premium demand has slack of 150 boxes. The $3,000 target is unattainable under the original 90-hour limit.
Solver scenarios: at 100 available hours, the best mix is 600 Classic and 200 Premium; contribution margin is $7,200 and operating income is $3,200. The added 10 hours create $600 before an extra capacity fee, so $600 total ($60/hour) is the break-even fee. Existing unit labor costs remain in variable costs; this comparison evaluates an additional capacity fee.
Same bakery. A different kind of month.
What if next month looks different?
A scenario is a named set of assumptions. Use it to compare possible futures before choosing a plan.
Four demand forecasts
Compare four Classic demand forecasts: 500, 700, 900, and 1,000 boxes. Price, variable cost per box, and $4,000 fixed costs stay unchanged.
| Assumption | Classic |
|---|
Compare income if all demand is met
Each profit assumes all forecast demand is filled. Check required labor against the 90 hours available.
Instructor solution · Demand comparison
Low: $0; 50 hours. Expected: $1,600; 70 hours. High: $3,200; 90 hours. Very high: $4,000; 100 hours.
Save B8 as the changing cell. Summary result cells are B20 and B13. Only Very high demand exceeds capacity.
Your turn: a new business, familiar tools.
Riverfront Print Studio
A campus print studio sells mugs and tote bags. Use a new dataset to practice Scenario Manager and Solver, then check your decisions below.
The monthly setup
Fixed costs are $1,500. The studio has 50 finishing hours available. All items finished are sold; there are no inventory changes.
| Driver | Mug | Tote bag |
|---|---|---|
| Selling price | $18 | $28 |
| Variable cost | $10 | $16 |
| Finishing hours per item | 0.10 | 0.25 |
| Maximum monthly demand | 300 | 120 |
Use the Practice Scenarios and Practice Solver worksheets in the Excel download. Build the formulas in the blank calculation cells, then choose the Excel setup from the business information above.
What if customer demand changes?
| Demand forecast | Mugs | Tote bags |
|---|---|---|
| Low demand | 200 | 80 |
| Expected demand | 250 | 100 |
| High demand | 300 | 120 |
- Open Practice Scenarios. Set up and save the three demand forecasts in Scenario Manager. Choose the changing cells yourself.
- Create a Scenario Summary showing operating income and finishing hours used.
- Decide which forecasts can be fulfilled within the available 50 hours. Keep prices, unit costs, and fixed costs unchanged.
Hint · Watch the Excel setup
Which mix should the studio finish?
Use the existing demand limits in the worksheet: 300 mugs and 120 tote bags. These are the maximum quantities the studio can sell. Use Solver to choose how many of each to produce within the 50 finishing hours available.
- Open Practice Solver. Choose the objective and the two changing cells.
- Build the constraints from the business limits. The studio produces whole items.
- Solve for the highest operating income. Save the result as Best mix - 50 hours.
Hint · Watch the Excel setup
Optional bonus · Ten more finishing hours
Continue from your saved 50-hour plan. Keep prices, unit costs, fixed costs, and demand limits unchanged.
- Increase capacity to 60 hours, solve again, and save Best mix - 60 hours.
- Compare the operating income from the two plans. What is the most the studio could pay for the ten extra hours before the extra profit disappears?
Hint · Watch the bonus in Excel
Instructor solution · Optional capacity bonus
Change B14 to 60 and rerun Solver with the same objective, changing cells, and constraints. Save Best mix - 60 hours. Saved Solver scenarios store quantities; restore the matching capacity B14 when reviewing each plan.
60-hour Solver plan: 300 mugs and 120 totes earn $2,340. The ten extra hours add $480 before any additional capacity fee. That is the break-even fee for the ten hours, or $48 per hour. Existing per-item costs stay in the model.
Instructor solution · Riverfront practice
Scenario setup: changing cells B8:C8; summary result cells B20 and B13.
Solver setup: maximize B20 by changing B8:C8. Constraints: B13 ≤ B14; B8 ≤ B10; C8 ≤ C10; B8:C8 ≥ 0; B8:C8 integer. Use Simplex LP with capacity B14 at 50. Save the result as Best mix - 50 hours.
| Demand forecast | Operating income | Hours needed | Within 50 hours? |
|---|---|---|---|
| Low demand | $1,060 | 40 | Yes |
| Expected demand | $1,700 | 50 | Yes |
| High demand | $2,340 | 60 | No |
50-hour Solver plan: 300 mugs and 80 tote bags. Contribution margin = 300 × $8 + 80 × $12 = $3,360. Operating income = $3,360 − $1,500 = $1,860. Hours = 300 × 0.10 + 80 × 0.25 = 50.
Why mugs first? Mugs contribute $80 per finishing hour; totes contribute $48 per finishing hour. Fill mug demand, then use the remaining hours for totes.
A little extra on the menu.
See a range of possible results.
Optional extension: repeat the same model across many inputs. Start with one changing input; then try two.
A quick distinction
Goal Seek: find an input for one target.
Scenario Manager: compare named bundles of assumptions.
Data Table: calculate a grid of outputs for a range of one or two inputs.
Solver: search for the best result within constraints.
One variable: Classic volume
First build the blank model formulas: Premium contribution B9 = B13−B14, income B11 = (B5−B6)*B7+B8*B9−B10, and hours B12 = B7*0.1+B8*0.2.
Keep Premium at 200, Classic variable cost at $12, and fixed costs at $4,000. Track both profit and labor.
Build it in Excel · Bonus 1V
- E5 links to profit (
=B11); F5 links to hours (=B12). Test quantities are in D6:D10. - Select
D5:F10. Data → What-If Analysis → Data Table. - Set Column input cell to
B7. Leave Row input cell blank. - Read the profit and hours together. Highlight rows exceeding 90 labor hours.
Two variables: volume and unit cost
Keep Premium at 200. Costs across the top; quantities down the side. Each intersection shows operating income.
† Requires more than 90 labor hours. The output is mathematically valid but exceeds current capacity.
Build it in Excel · Bonus 2V
- D5 links to profit (
=B11). Classic variable costs run across E5:H5; Classic volumes run down D6:D10. - Select
D5:H10. Data → What-If Analysis → Data Table. - Row input cell:
B6(Classic variable cost). Column input cell:B7(Classic quantity). - Interpret the sensitivity and check capacity. A Data Table does not optimize or enforce constraints.
Instructor solution · Bonus tables
One-variable quantities 200 / 300 / 400 / 500 / 600 produce income $0 / $800 / $1,600 / $2,400 / $3,200 and labor 60 / 70 / 80 / 90 / 100 hours. The last row is infeasible.
Two-variable check: 400 Classic at $13 variable cost produces 400 × $7 + 200 × $12 − $4,000 = $1,200. All 600-Classic rows exceed current capacity with Premium fixed at 200.