The Analyst's Path

Phase 2 · Financial statement analysis and quality of earnings · free

What-If & Optimization

EX2.02 · 11,408 words

Every model an analyst builds is a machine that turns assumptions into answers: put in a discount rate, a growth rate, a set of margins, and out comes an enterprise value, a payback period, a profit. But the single answer is never the point.

Read the whole module in the app All Phase 2 modules

Learning objectives

By the end you can:

  1. Use Goal Seek to solve a model for the single input that drives a chosen output cell to a target value, and state the two structural conditions it requires, one changing cell, connected to the target by a live formula chain.
  2. Build a one-variable Data Table and a two-variable Data Table, placing the driving formula in the correct corner and mapping the row-input and column-input cells to the right model inputs without swapping them.
  3. Construct the canonical DCF sensitivity grid (enterprise value across a matrix of WACC and terminal-growth assumptions) and read it the way an investment committee does.
  4. Store and switch between named scenarios (base / bull / bear) with Scenario Manager, and produce a scenario summary that compares their outputs side by side.
  5. Formulate a resource-allocation problem as a linear program (objective, decision variables, constraints) enable and drive Solver to the optimum, and read the binding constraints.
  6. Explain why the Solver optimum sits where it does (at a vertex of the feasible region where constraints bind), and identify the binding versus slack constraints from the solution.
  7. Turn on iterative calculation deliberately to resolve an intentional circular reference (interest-on-revolver), compute the fixed point by hand, and articulate the danger of leaving iteration enabled globally.
  8. Diagnose the five classic what-if failures, swapped Data Table input cells, the formula in the wrong corner, a Goal Seek pointed at a hardcoded cell, Solver disabled or given the wrong constraint sense, and iterative calc masking a real circular-reference bug.

Prerequisites & connections

Builds on. EX0.02 (Formulas I, relative/absolute/mixed references and the F4 toggle) and EX1.01 (Formulas II, the aggregation family), because a Data Table only works when your model is built from live formulas that trace back to a small number of clearly separated input cells; a workbook full of hardcoded numbers cannot be sensitised. You also lean on EX1.02 (Lookups Mastery): the row/column intersection you learned to fetch with INDEX(matrix, MATCH(row), MATCH(col)) is the manual version of exactly what a two-variable Data Table automates, the Data Table fills that whole matrix for you by re-running the model at each cell. The discipline of separating inputs (assumption cells you colour differently and never bury inside a formula) from calculations is the single habit that makes everything in this module possible.

Feeds forward. This is the beating heart of financial modelling. On the analyst track, M3.08's three-statement model uses Scenario Manager to flip between operating cases, and its revolver plug depends on the iterative-calculation switch you meet here. M2.05's valuation work is the DCF sensitivity grid of Worked example 1, and the capital-allocation and product-mix decisions of the strategy modules are Solver problems. EX2.03 (Modelling & Forecasting) turns these one-off tools into a repeatable model architecture, and EX6.xx's Monte-Carlo work generalises the Data Table's "run the model many times" idea from a deterministic grid to a random sample. Master the mechanics here and you will recognise them, dressed in domain clothing, in every model you ever touch.

Goal Seek: solve one input backwards

A spreadsheet formula is a one-way function: inputs on the left, an answer on the right. =A1*A2 takes A1 and A2 and produces a value; you cannot type into the result and have Excel work out what A1 must have been. Goal Seek is Excel's tool for running that function backwards, for the special case of one unknown input. You tell it three things: the cell whose value you want to control (the Set cell, which must contain a formula), the To value you want that cell to reach, and the single By changing cell, an input the Set cell depends on. Goal Seek then searches numerically (a root-finder under the hood) for the input value that drives the output to your target.

The mental model is a thermostat. You do not set the furnace's gas flow directly; you set the temperature you want and the system adjusts the flow until the room matches. Goal Seek is that controller for one cell of a model.

This page is an excerpt

The full module runs to 11,408 words and carries the worked examples, the tables, the quiz that gates the next module and the spaced-repetition deck built from it. All of it is free and none of it needs an account.

Read the whole module All 228 modules

Terms this module defines

Previous: Data Tools & Integrity Next: Financial & Statistical Functions