What-If Analysis in One Line Each
- Goal Seek: "What input gives me exactly this output?" (one variable, one target).
- Data Table: "How does the output move across a range of inputs?" (sensitivity grid).
- Scenario Manager: "What do Best / Base / Worst cases look like?" (named input sets).
- Solver: "What's the optimal mix under constraints?" (multi-variable optimization).
Goal Seek: Break-Even in 60 Seconds
- Model profit as a formula:
=(Price-VarCost)*Units - Fixed. - Data → What-If Analysis → Goal Seek.
- Set cell = profit cell, To value =
0, By changing = units cell → OK. - Excel reports the break-even units. Screenshot the dialog + result for your appendix.
Classic prompts: "What discount still hits $50k profit?", "What growth rate reaches 1M users by December?", "What price covers costs at 2,000 units?" — all one Goal Seek each.
Data Tables: Sensitivity Grids That Impress
Put outputs at the top row/left column of a grid, select the grid, Data → What-If → Data Table, point Row/Column input cells at your drivers (price, growth). Add 3-color conditional formatting and you have the sensitivity exhibit every finance rubric rewards.
Solver: Constrained Optimization (Enable It First)
File → Options → Add-ins → Solver Add-in → OK. Then Data → Solver. The standard assignment pattern:
Objective: Maximize Total_Profit (or Minimize Cost)
By changing: product mix cells (integers ≥ 0)
Subject to: machine_hours_used ≤ hours_available
material_used ≤ material_available
demand_min ≤ production ≤ demand_max
Method: Simplex LP (for linear models)
Always tick "Make Unconstrained Variables Non-Negative", solve, and keep the Answer Report (right-click the tab → it documents constraints for your write-up).
Write-Up Lines That Score
- State the question as a question: "How many units must we sell to break even at a $19 price?"
- Record inputs, method, and result in one table — reproducible beats clever.
- Note limitations: single-variable (Goal Seek), linearity assumption (Simplex), or binding constraints (Solver).
Optimization case due? We set up Goal Seek, Data Tables, and Solver models with documented constraints and reports.
