Day 22: Scenario Analysis & What-If Tools

Goal Seek (Data → What-If Analysis → Goal Seek) finds the input value needed to achieve a specific target output. Example: your profit formula in C1 references price in A1 and cost in B1. To find what price achieves exactly ₹10 lakh profit: set "Set cell" to C1, "To value" to 1000000, "By changing cell" to A1. Excel iterates to find the answer. Goal Seek only works with one variable. For multiple variables, use Solver.

A What-If Data Table (Data → What-If Analysis → Data Table) calculates how a formula's output changes across a range of input values. A one-variable table shows output for many values of one input. A two-variable table shows output for combinations of two inputs. Example: a pricing table showing profit at 10 different price points and 5 different cost levels — 50 scenarios calculated automatically with no manual work. The results update whenever the formula or input values change.

Scenario Manager (Data → What-If Analysis → Scenario Manager) saves and switches between named sets of input values. You define a "Best Case" scenario (high growth, low cost), a "Base Case", and a "Worst Case" — each with specific values for the changing cells. Click a scenario name to instantly apply that set of inputs to your model and see the results. The Scenario Summary report produces a comparison table of all scenarios side by side.

Solver (Data → Solver, must enable via Add-ins) is an optimisation tool — it finds input values that maximise, minimise, or set equal to a target while satisfying constraints. Example: "maximise revenue by choosing the optimal product mix given production capacity constraints." Goal Seek handles one variable with one target. Solver handles multiple variables with multiple constraints. Used in supply chain, operations, and financial modelling roles.

In a well-built financial model, all input assumptions are in a dedicated Inputs section with named ranges (GrowthRate, TaxRate, DiscountRate). All calculation cells reference named ranges, not cell addresses: =Revenue * (1+GrowthRate) instead of =B5*(1+$D$3). This makes formulas readable, reduces errors from wrong cell references, and makes scenario switching simpler — change the input once and every calculation updates. Group input assumptions separately from outputs and protect the calculation sheet.