Skip to main content

Excel What-If Analysis Tools

Goal Seek and Solver are the workhorses of professional valuation modeling. While sensitivity tables show you outcomes for different inputs, these tools let you answer the inverse question: "What would revenue growth need to be for this stock to be worth $60?" This reversal of the calculation unlocks market-embedded assumptions and validates whether your pricing is reasonable.

Quick Definition​

Goal Seek is an Excel tool that finds the single input value needed to achieve a target output. Solver is a more powerful optimization engine that can adjust multiple inputs simultaneously to hit a goal, satisfy constraints, or minimize/maximize an objective. Together, they convert your three-statement DCF from a forward-looking model ("If growth is 12%, value is $48") into a reverse-engineering instrument ("If the market prices this at $65, the market is assuming 18% growth").

Key Takeaways​

  • Goal Seek solves for one unknown input to hit a specific output (intrinsic value, margin, WACC)
  • Solver handles multiple constraints and can find optimal combinations of inputs
  • Reverse-engineering market price reveals what assumptions the market is pricing in—invaluable for spotting consensus mistakes
  • Sensitivity tables (data tables) show you outcome ranges; Goal Seek + Solver answer "what input makes sense?"
  • Both tools live in Excel's Data ribbon under Data → What-If Analysis (Goal Seek) or installed separately (Solver, via Add-ins)

Goal Seek: Finding the Hidden Assumption​

How Goal Seek Works

Goal Seek takes three inputs:

  1. Set cell: The output you want to hit (e.g., intrinsic value per share)
  2. To value: Your target (e.g., $65, the current stock price)
  3. By changing cell: The input that should adjust to reach that target (e.g., revenue growth rate)

Goal Seek iteratively adjusts the input until the output matches your target.

Practical Example: What Growth Does the Market Assume?

Assume your DCF model shows intrinsic value in cell B45. The stock currently trades at $65. You want to know: What revenue growth rate would justify this market price?

Steps:

  1. Open Data tab → What-If Analysis → Goal Seek
  2. Set cell: B45 (intrinsic value per share)
  3. To value: 65 (current market price)
  4. By changing cell: B8 (revenue growth rate)
  5. Click OK

Goal Seek adjusts B8 until B45 equals 65. Suppose it lands on 16% growth. This means the market is pricing in 16% revenue growth. If you think the company will grow at 12%, the stock is overvalued. If you think 20% growth is achievable, it's undervalued.

Common Reverse-Engineering Scenarios:

QuestionSet CellTo ValueBy Changing Cell
What growth justifies current price?Intrinsic valueMarket priceRevenue growth rate
What WACC implies current price?Intrinsic valueMarket priceWACC
What terminal margin is priced in?Intrinsic valueMarket priceYear 10 EBIT margin
What multiple is the market using?Terminal valueMarket capExit EV/EBITDA
What downside risk is safe?Intrinsic valueEntry price (margin of safety)Margin compression %

Solver: Multi-Constraint Optimization​

What Solver Does

Solver is more sophisticated. It can:

  • Optimize a target cell (maximize profit, minimize cost, hit intrinsic value)
  • Subject to multiple constraints (revenue growth ≤ 15%, margin ≥ 20%, WACC ≤ 10%)
  • By changing multiple input cells simultaneously

Solver uses algorithms (simplex, evolutionary, GRG nonlinear) to find optimal solutions.

Installing Solver

Solver is a built-in add-in:

  1. File → Options → Add-ins
  2. Manage: Excel Add-ins → Go
  3. Check "Solver Add-in" → OK
  4. Now appears in Data tab

Practical Example: Optimized Valuation Scenario

You're modeling a mature industrial company. You want to find the valuation scenario that's most realistic—one where all inputs move together sensibly.

Setup:

Cells to change:

  • B10: Revenue growth (constrained 2–8%)
  • B11: EBIT margin (constrained 12–18%)
  • B12: CapEx as % of revenue (constrained 3–6%)

Constraints:

  • When revenue growth ≤ 4%, margin drops 1% (slower growth + competitive pressure)
  • WACC rises 50 bps if capital intensity (CapEx) exceeds 5%
  • Working capital change correlates with growth: high growth requires more cash

Objective:

  • Intrinsic value (set to maximize? minimize? or equal to market price $45?)

Steps in Solver:

  1. Data tab → Solver
  2. Set Objective: $B$50 (intrinsic value per share)
  3. To: Value Of: 45 (we want to find inputs consistent with $45 fair value)
  4. By Changing Cells: $B$10:$B$12
  5. Subject to Constraints:
    • $B$10 >= 0.02, $B$10 <= 0.08 (revenue growth bounds)
    • $B$11 >= 0.12, $B$11 <= 0.18 (margin bounds)
    • $B$12 >= 0.03, $B$12 <= 0.06 (CapEx bounds)
    • $B$50 >= 44, $B$50 <= 46 (tolerance on intrinsic value)
  6. Select solving method: GRG Nonlinear (for smooth, continuous functions)
  7. Click Solve

Solver returns the realistic combination of growth, margin, and CapEx that produces a fair value near $45.

Advanced Use: Reverse-Engineering Market Assumptions​

Scenario: The Market is Pricing in Something You Don't Understand

The stock trades at $100, but your DCF base case gives $65. Two possibilities:

  1. You're wrong (margin expansion, faster growth, lower risk than you think)
  2. The market is wrong (overvaluation)

Use Solver to find what combination of assumptions would justify $100:

Setup:

  • Objective: Intrinsic value = $100
  • Changing cells: Revenue growth, terminal margin, WACC
  • Constraints: Growth ≤ 25% (industry realistic), margin ≤ 30% (peer max), WACC ≥ 6% (reasonable minimum)

Run Solver. It returns the set of assumptions baked into that $100 price. If Solver says "25% growth + 30% margin + 6% WACC," and you think the company will grow 15%, compress to 22%, and faces 8% WACC, you've found your edge: the stock is overvalued.

Diagram: The Reverse-Engineering Workflow

Sensitivity Table vs. Goal Seek vs. Solver​

Each tool answers a different question:

ToolQuestionDirectionUse Case
Data Table (Sensitivity)"If growth is 10%, 12%, 14%, what's intrinsic value?"ForwardExplore outcome ranges
Goal Seek"If I want intrinsic value = $65, what growth rate?"Reverse, single inputQuick market assumption check
Solver"Given constraints on growth, margin, WACC, what combo gives $65?"Reverse, multiple inputsRealistic stress scenario

Common Mistakes​

Goal Seek Overshooting If your model has discontinuities or jumps (e.g., piecewise logic, IF statements), Goal Seek may fail or give nonsensical results. Smooth your model; use linear assumptions where possible.

Forgetting Feasibility Constraints Solver may find a mathematically perfect answer that's economically impossible (200% WACC, negative margins). Add logical constraints: "If growth > 20%, add 100 bps to WACC (reflects higher risk)."

Interpreting Solver Results as Prediction Solver finds an optimal input combo, not a forecast. It answers "What scenario is internally consistent and hits your target?" not "What will actually happen." Use it for sanity-checking, not prophecy.

Not Documenting the Reversal When you reverse-engineer $100 price and find it assumes 25% growth, document that finding. Write it down. Compare it to your base case. A month later, you'll forget what you discovered.

Confusing Solver Constraints with Reality You can tell Solver "WACC ≥ 6%," but that doesn't make 6% reasonable. Use constraints to reflect real bounds (margins can't exceed 40% given industry competition, growth can't exceed industry rate), not arbitrary limits.

Step-by-Step: Reverse-Engineering Your Stock​

  1. Price the market: Note current stock price (e.g., $78)
  2. Set up Goal Seek: Intrinsic value = $78, change one key input (usually revenue growth or terminal margin)
  3. Interpret: "Market assumes 14% growth." Write it down.
  4. Stress with Solver: "If growth is only 11% but everything else adjusts optimally, what's fair value?" (Constraints: margin max 25%, WACC min 8%)
  5. Compare scenarios:
    • Market scenario (14% growth): $78
    • Conservative scenario (11% growth): $62
    • Bull scenario (17% growth): $95
  6. Decide: Is the margin between market price and conservative scenario large enough? If $78 vs. $62 conservative = $16 upside if wrong, that's your risk/reward.

FAQ​

Q: Can Goal Seek handle multiple inputs at once? A: No; Goal Seek changes one cell. Use Solver for multiple inputs.

Q: Should I use Solver to optimize profit (maximize) or match price (constraint)? A: For valuation, match price using a constraint. Don't "maximize" intrinsic value—that's mathematically meaningless. Constrain it to market price (or target price) and solve for realistic inputs.

Q: What if Solver doesn't converge? A: Your problem may be non-convex or have no solution given constraints. Relax one constraint and rerun. Or simplify your model (too many feedback loops confuse solvers).

Q: Is Goal Seek accurate? A: For linear models, exact. For complex models with many formulas, Solver is more reliable. Goal Seek's iterative approach can miss edge cases.

Q: Can I use these tools on Google Sheets? A: Goal Seek is available in Sheets (Tools → Goal Seek). Solver requires third-party add-ins (e.g., MaxMind). Excel remains superior for advanced modeling.

Q: How do I set reasonable constraint bounds? A: Use historical data and peer analysis. If peers' revenue growth ranges 8–16%, set your constraint bounds at 5–20% (slightly wider to allow for company-specific outperformance or underperformance).

  • Sensitivity Analysis: Single-variable outcome ranges; often precedes Goal Seek
  • Scenario Analysis: Discrete Bull/Base/Bear cases; can validate Solver outputs
  • Constraint Programming: Mathematical framework underlying Solver
  • Optimization: Broader field of finding best solutions; Solver is one tool
  • Reverse Valuation: Extracting market assumptions from market prices

Summary​

Goal Seek and Solver convert your DCF from a mechanical calculator into a reasoning engine. They let you ask the right questions: "What does the market assume?" and "Is that realistic?" By reverse-engineering market price and running constrained optimizations, you surface consensus mistakes and validate whether the market's embedded assumptions are grounded in reality or fantasy.

Use Goal Seek for quick sanity checks (current price assumes X growth—do I agree?). Use Solver for nuanced stress-testing (what realistic inputs justify this valuation given real-world constraints?). Both tools separate investors who understand what they're buying from those who just chase price momentum.

Next Steps​

Your models now forecast forward and reverse-engineer backward. The final layer is discipline: Documenting Your Model ensures assumptions are transparent, repeatable, and defensible. A model you can explain to a skeptic is a model you should trust.