Computer Specialist in Spreadsheet & Database Level 1: Goal Seek Says $61.20; the Residence Hall Contract Says Otherwise

Goal Seek Says $61.20; the Residence Hall Contract Says Otherwise

The conference services office at a college models summer accommodation revenue in a single worksheet. Three room types - Standard, Suite and Apartment - have nightly fees in B4:B6 ($89, $129, $165), variable costs per occupied night in B14:B16 ($58, $81, $104) and modeled room-nights in B9:B11. Room-nights are not independent of price: each is computed as base nights multiplied by (1 − elasticity × (fee − current fee) / current fee), so raising a fee reduces demand.

Fixed costs of $182,000 sit in B19 and net surplus is B24, currently +$14,300.

The director wants the lowest fee structure that breaks even. An analyst runs Goal Seek, setting B24 to 0 by changing B4 only. Goal Seek reports success at $61.20. At that fee the model shows 9,850 Standard room-nights, up from 6,400, and a Standard contribution of $3.20 per night. The residence hall contract caps total room-nights at 7,200 and carries a $40,000 penalty above the cap; the model does not currently include the penalty.

1. Explain why $61.20 is not a usable break-even fee, and identify what Goal Seek did not consider.

Read the worked answer

Goal Seek did exactly what it was asked and nothing more: it changed one cell (B4) until one other cell (B24) reached zero. Because it treats the worksheet as a black box, it did honour the demand formula - at $61.20 the model as written really does net to zero. The problem is that the model is incomplete and the solution point is infeasible.

Capacity is ignored. 9,850 room-nights breaches the 7,200 cap. Add the $40,000 penalty and the true surplus at $61.20 is −$40,000, not zero.
Contribution is too thin to be a decision. At $3.20 a night, a 6% error in the $58 variable cost, or one extra cleaning shift per stay, flips the result. A break-even recommendation should carry a margin constraint, not sit on the knife edge.
Only one fee moved. Suite and Apartment pricing are decision variables too; the lowest break-even structure is a combination, not a single price.
The equation is non-linear in fee. Several fee levels can produce net = 0 - a very low fee that fills the building, a high fee with low occupancy - and Goal Seek returns whichever one its starting value walks toward, with no way to compare them or to know it found the best.
No floor is applied. $61.20 may sit below the competitor rate and below the level the college is willing to be seen charging.

Goal Seek is the right tool for a one-input, one-output, unconstrained question. This is a constrained optimisation across three inputs.

2. Specify the Solver model you would build instead: objective, changing cells, constraints, engine and the checks after solving.

Read the worked answer

Use Solver with an explicit objective, explicit constraints and the correct engine.

Setup:

Set Objective: a helper cell computing the weighted average nightly fee, =SUMPRODUCT(B4:B6,B9:B11)/SUM(B9:B11), To: Min.
By Changing Variable Cells: $B$4:$B$6.
Subject to:
$B$24 >= 0 - break-even.
$B$9+$B$10+$B$11 <= 7200 - contract cap. Constraining total nights is cleaner than modelling the penalty, because Solver then never explores the region where the $40,000 applies.
$B$4 >= $B$14*1.25, and the same for B5/B14 and B6/B16 - a minimum contribution margin per room type.
$B$4 <= 93, $B$5 <= 140, $B$6 <= 180 - competitor ceilings.
$B$4:$B$6 >= 0.
Solving Method: GRG Nonlinear. The elasticity term multiplies fee by demand, so the model is not linear in the decision variables, and the cap creates a discontinuity.
Options: tick Use Multistart (the surplus surface is non-convex and a single start can land in a poor local optimum), set Convergence to 0.0001, and tick Make Unconstrained Variables Non-Negative.

After solving: independently recompute B24 from the returned fees by hand before accepting; read the Answer and Sensitivity reports for binding constraints - a binding fee ceiling means break-even cannot be reached without attacking fixed costs or the cap; then re-run once with the penalty term built in as a cross-check. The two runs should agree, and if they do not, the cap constraint is the reason.

3. The board also wants to see net surplus for Standard fees from $55 to $95 in $5 steps, with the other two fees unchanged. Which tool produces that, and what must you warn them about when reading it?

Read the worked answer

A one-variable Data Table (Data ▸ What-If Analysis ▸ Data Table).

Put the candidate fees 55, 60, 65 … 95 in F3:F11.
In G2:H2 reference the outputs to watch: =B24 (net surplus) and =B9+B10+B11 (total room-nights), so the capacity effect is visible next to the money.
Select F2:H11 and set Column input cell to $B$4.

Warnings for the board pack:

The table recalculates the entire model per row, so the 7,200-night cap shows up as a kink: below it, cutting the fee raises surplus; above it, the $40,000 penalty pulls the line down sharply. Read the table for shape and threshold, not as a menu of interchangeable options.
It is a static snapshot. Change the fixed cost or an elasticity and the old values sit there looking current until the table is recalculated; individual cells cannot be edited.
Every row holds Suite and Apartment at their current fees. The table answers "what if only Standard moves?", and the row that looks best may be invalid once the other two fees are optimised jointly.
The table varies one input. As soon as the recommendation touches all three fees, present Scenarios (Scenario Manager) with a full fee set per scenario, so the board compares coherent packages rather than isolated values.
A Data Table is volatile in a large model; if recalculation becomes slow, paste the results as values for the pack and keep the live table in a working copy.