What-If & Optimization

Excel Mastery Track · Green belt · ~6–8 hours · Microsoft 365 on Windows assumed

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. The questions that actually get asked in a meeting run backwards and sideways. "What rate would we have to assume to justify that price?" "How much does the valuation move if growth is a point lower?" The third is a capacity question. "Given our factory can only run so many hours, what's the most profitable thing to build?" Those are what-if and optimization questions. Excel has a dedicated toolkit for each, and most analysts use it badly or not at all, reaching instead for the crude method of retyping an input, squinting at the result, retyping again.

Five tools cover all of it. Goal Seek solves a model backwards. You name the answer you want and it finds the one input that produces it. Data Tables run a model sideways, dozens or hundreds of what-ifs at once, laid out as a grid, and that grid is how a DCF sensitivity table (WACC × terminal growth) gets built. Scenario Manager stores whole named worlds (base, bull, bear) and switches between them on demand. Solver answers the hardest question of all, which is what's best. It treats your model as a linear program and finds the allocation that maximises profit subject to real constraints. Then there's iterative calculation, the switch that lets a model resolve a deliberate loop such as interest on a revolver that depends on the interest. You'll also see why leaving that switch on by accident is one of the most dangerous things you can do to a workbook.

Underneath all five is a change in how you hold a model. A beginner drives it forwards, one keystroke at a time, and reads whatever falls out. An analyst interrogates it. Which input hits the target? How does the answer look across a whole range of assumptions, and what's the best decision under a constraint? They get those answers structurally, from tools that update themselves when the model changes.

One version note, stated once. Everything here works in Microsoft 365 on Windows. Goal Seek, Data Tables, Scenario Manager and iterative calculation are built in to every modern Excel. Solver is an add-in you have to enable once, and we'll show you how. None of it needs VBA.

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). 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, and you can't sensitise a workbook full of hardcoded numbers. You'll also lean on EX1.02 (Lookups Mastery). Remember fetching a row and column intersection with INDEX(matrix, MATCH(row), MATCH(col))? That's the manual version of what a two-variable Data Table automates. A Data Table fills the whole matrix for you by re-running the model at each cell. One habit makes everything that follows possible: keep inputs, the assumption cells you colour differently and never bury inside a formula, apart from calculations.

Feeds forward. This is the core 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. Solver problems turn up in the capital-allocation and product-mix decisions of the strategy modules. EX2.03 (Modelling & Forecasting) turns these one-off tools into a repeatable model architecture. 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. Get the mechanics down here and you'll recognise them, dressed in domain clothing, in every model you ever touch.

Core teaching content

Scenario Manager

A Data Table varies one or two inputs continuously. Sometimes, though, the alternatives you care about are whole coherent stories, and a sweep of WACC from 10% to 12% won't capture them. Picture a bull case where growth is strong, margins expand and the discount rate is low. In the bear case all three move against you, and the base case falls in between. Each story changes several inputs at once, and you want to save them, name them and flip between them. That's the job of Scenario Manager.

Start at Data ▸ What-If Analysis ▸ Scenario Manager (Alt, A, W, S). Click Add, name the scenario ("Base"), and nominate the Changing cells. These are the handful of input cells this scenario overrides, say the WACC cell, the terminal-growth cell and the year-5 cash-flow driver. Type the values those cells take under this scenario, then repeat for "Bull" and "Bear". Now Show any scenario to push its stored values into the model, and watch every dependent formula recompute. Summary builds a clean side-by-side table of the changing cells and any result cells you nominate, such as your EV cell, so the three worlds appear in one comparison block.

Use Scenario Manager when a few named, discrete worlds each move several inputs together and you want to store and audit them. Use a Data Table when you want a continuous grid over one or two inputs. The two combine well, and many teams run a Data Table within each scenario.

One practical caution. Scenario Manager keeps its values inside the workbook's definitions, out of sight on the grid, so an inherited workbook can hide surprises. Document your scenarios on a visible sheet as well. It also caps changing cells at 32 per scenario. That's plenty for a driver set, and one more reason to sensitise the drivers and leave every other cell alone.

Common mistakes & how experts think differently

1. Swapping the Data Table's row and column input cells, the signature error to watch for. The rule is mechanical and unforgiving. Values across the top row feed the Row input cell, and values down the left column feed the Column input cell. Swap them and Excel won't complain. It dutifully substitutes WACC values into the growth input and growth values into the WACC input, and fills the grid with confident, wrong numbers that look like a valid sensitivity table. A beginner eyeballs the grid and trusts it. An expert spot-checks one cell by hand against the model before believing any Data Table. They recompute a single (WACC, g) corner manually, as Worked example 1 does, and only then read the grid. If the corner disagrees, the input cells are almost always crossed.
2. Putting the driving formula in the wrong corner. In a two-variable table the output reference must go in the top-left corner (where the row and column headers meet). In a one-variable column it goes one row up and one column right. Beginners put the formula above their input column, or inside the body, or forget it entirely. They get #REF!, blanks, or a table that never fills. An expert lays the skeleton out on purpose. Input strips go in first, then the formula in the corner. Select the whole rectangle including the corner and both strips, and only then open the dialog. Get the shape right and the rest follows.
3. Pointing Goal Seek (or a Data Table) at a hardcoded cell. Goal Seek can only solve a formula cell by changing an input cell that the formula actually depends on. Beginners point Set cell at a number they typed, or By changing cell at a cell the target doesn't reference. Goal Seek spins and fails with "may not have found a solution". Before running any what-if, the expert checks the formula chain by reflex. Press Ctrl+[ (or Formulas ▸ Trace Precedents) on the target and confirm it traces back to the cell you mean to change. No live chain means no what-if. It's the same habit of keeping inputs apart from calculations that makes a model sensitisable at all.
4. Solver not enabled, or given the wrong constraint sense. Two failures wear the same face, a plain "Solver won't work." In the first, the add-in is off. Solver isn't under Data ▸ Analyze because nobody ticked it in Options, so enable it once, as above. In the second, a constraint's sense is backwards or missing. Maybe you wrote used ≥ capacity instead of ≤. Maybe you left out Make Unconstrained Variables Non-Negative, so Solver happily "produces" negative units, or left the engine on GRG Nonlinear for a linear model and it stalls. An expert enables Solver on purpose and writes each constraint as resource_used ≤ capacity, with the used side a real formula of the variables. They tick non-negativity and pick Simplex LP for a linear allocation. Then they read the binding constraints to check that the answer is where the economics say it should be, at a corner with resources exhausted.
5. Leaving iterative calculation on globally: masking a real circular-reference bug. Iteration is a workbook-wide switch. Turned on for a legitimate revolver loop, it also suppresses the circular-reference alarm for accidental loops elsewhere. A real self-referential typo then converges to garbage in silence instead of being caught. A beginner enables iteration to make a #CIRCULAR warning go away, which is the worst possible reason. An expert enables it only when a deliberate loop demands it, isolates and labels that loop with a circularity-breaker switch, and treats an unexplained iteration setting in an inherited file as a red flag to investigate. Use iteration as a scalpel for one known loop, and never as a blanket to silence warnings.

One habit ties all five together. A what-if tool is only as trustworthy as the formula chain beneath it, and every automated grid or optimum must be spot-checked against a hand calculation before it is believed. Beginners trust the tool because it produced a number. Experts trust it because they checked one cell of it by hand and understood why the answer came out where it did.

Worked examples

Worked example 1: Shakti Components DCF sensitivity and implied WACC (India)

Shakti Components Ltd. is a Pune-based auto-ancillary maker with a five-year unlevered free-cash-flow forecast, in ₹ crore, of 120, 138, 156, 174, 192. Work from a base WACC of 12% and terminal growth of 4%. Then (a) compute the base-case enterprise value, (b) build the two-variable Data Table of EV over WACC {10%, 11%, 12%} × terminal growth {3%, 4%, 5%} and check one grid cell by hand, and (c) take an acquirer's offer of ₹2,600 cr and use Goal Seek to find the WACC that offer implies. Each figure is recomputed and verified.

Step 1, the base-case EV. Discount each FCF at 12% and add the discounted Gordon terminal value.

The terminal value at the end of year 5 is TV₅ = FCF₅ × (1+g)/(WACC − g) = 192 × 1.04 / (0.12 − 0.04) = 199.68 / 0.08 = ₹2,496.00 cr. Discount it back five years, so PV(TV) = 2,496.00 / 1.12⁵ = 2,496.00 / 1.76234 = ₹1,416.30 cr. Enterprise value is then:

EV = PV(FCF) + PV(TV) = 547.72 + 1,416.30 = ₹1,964.02 cr

So the base case is ≈ ₹1,964 cr. Look at how much of it comes from the terminal value: ₹1,416 cr of ₹1,964 cr, about 72%. That share is what makes the DCF so sensitive to WACC and g, and it's why the sensitivity grid matters.

Step 2: lay out the Data Table. In the model, WACC is one input cell and g is another, and the EV cell is the formula chain above. Build the grid with WACC {10%, 11%, 12%} down the left column, g {3%, 4%, 5%} across the top row, and a reference to the EV cell (=EV_cell) in the top-left corner. Select the whole rectangle and open Data ▸ What-If Analysis ▸ Data Table (Alt, A, W, T). Growth values run across the top, so the Row input cell is the g input. WACC values run down the side, so the Column input cell is the WACC input. Solve, and the grid fills (₹ cr):

The base-case pair (12%, 4%) holds ₹1,964.02 cr, the same as Step 1, and that's the first thing to check. EV falls down each column as the discount rate rises, and rises along each row as terminal growth rises, just as the economics demand.

Step 3, verify one cell by hand (the anti-swap check). Take the top-right conservative-to-aggressive corner (WACC 12%, g 3%), which the grid shows as ₹1,794.55. WACC is still 12%, so the explicit-period PVs don't change and stay at ₹547.72. Only the terminal value moves with g. TV₅ = 192 × 1.03 / (0.12 − 0.03) = 197.76 / 0.09 = ₹2,197.33 cr and PV(TV) = 2,197.33 / 1.76234 = ₹1,246.83 cr. Then EV = 547.72 + 1,246.83 = ₹1,794.55 cr, which matches the grid to the paisa.

This is the spot-check that catches a row and column swap. If the input cells were crossed, this cell would show the value for (WACC 3%, g 12%) instead. A 3% discount rate is absurd, so you'd spot it immediately. Never trust a Data Table you haven't corner-checked once.

Step 4: Goal Seek the implied WACC. The acquirer offers ₹2,600 cr. Holding g at 4%, what WACC does that price imply? Run Data ▸ What-If Analysis ▸ Goal Seek with Set cell as the EV cell, To value as 2600 and By changing cell as the WACC input. Goal Seek returns WACC ≈ 10.10%, or 10.1003% to more places. Check it. At WACC 10.10%, EV computes to ₹2,600.0 cr.

For the committee, the message is sharp. The ₹2,600 cr offer only makes sense if the buyer's cost of capital is about 10.1%, well below our 12% base. Either the buyer is cheaper-funded, or more optimistic, or overpaying. That one backwards-solved number reframes the whole negotiation. It dropped out of the model in one dialog because the EV cell was a live formula chain back to the WACC input.

Step 5, read it. You've used three tools on one model. The DCF formula gives a point, ₹1,964 cr. The Data Table gives the range, ₹1,795 to 3,082 cr across the grid. Goal Seek gives the implied assumption behind any target price, 10.1% for ₹2,600 cr. You never typed a number twice, and if you change a single FCF all three update. That's what interrogating a model means in practice. It's also the Scenario Manager / Data Table / Goal Seek spine the mini-project asks you to build.

Worked example 2: Summit Gear product-mix optimization with Solver (US)

Summit Gear Co., a Colorado backpack maker, builds two models. The Standard pack contributes $24/unit and the Pro pack $40/unit. Each pack passes through two shared departments every week, cutting (machine hours) and sewing (labour hours). A Standard uses 1 cutting hour and 2 sewing hours. A Pro uses 2 cutting hours and 3 sewing hours. Capacity is 1,000 cutting hours and 1,700 sewing hours a week. What weekly mix maximises total contribution, and which departments are the bottleneck? Every figure is recomputed and verified, by linear program and by exhaustive integer search.

Step 1: formulate the LP. Let S be Standard units a week and P be Pro units a week.

maximize   Z = 24·S + 40·P            (total weekly contribution, $)
subject to  1·S + 2·P ≤ 1000          (cutting hours)
            2·S + 3·P ≤ 1700          (sewing hours)
            S ≥ 0,  P ≥ 0             (non-negativity)

The objective and both constraints are weighted sums of S and P, so this is a linear program, and Solver's Simplex LP engine solves it exactly.

Step 2: build it on the sheet. Make two decision cells for S and P, and start them at 0. Add an objective cell =24*S + 40*P and two constraint "used" cells, cutting =1*S + 2*P and sewing =2*S + 3*P, each next to its capacity (1000, 1700). Enable Solver if you need to (File ▸ Options ▸ Add-ins ▸ Excel Add-ins ▸ Go ▸ tick Solver). Then open Data ▸ Analyze ▸ Solver. Point Set Objective at the objective cell with Max, and set By Changing Variable Cells to S, P. Add the constraints cutting_used ≤ 1000 and sewing_used ≤ 1700. Tick Make Unconstrained Variables Non-Negative, pick the Simplex LP engine, and click Solve.

Step 3, the optimum. Solver returns:

Standard S* = 400 units    Pro P* = 300 units    Z* = $21,600 / week

Check the objective first, 24 × 400 + 40 × 300 = 9,600 + 12,000 = $21,600. Now the constraints. Cutting used = 1×400 + 2×300 = 400 + 600 = 1,000, which equals capacity, so it binds. Sewing used = 2×400 + 3×300 = 800 + 900 = 1,700, also equal to capacity and binding. Both departments are exhausted. The optimum is the vertex where the two constraint lines cross, just as LP geometry predicts.

Step 4, why this corner (check against the alternatives). The feasible region is a quadrilateral with corners (S,P) = (0,0), (850,0), (400,300) and (0,500). Evaluate the objective at each:

The mixed vertex (400, 300) beats every pure-product corner. It earns $21,600, against $20,400 for all-Standard (capped by sewing) and $20,000 for all-Pro (capped by cutting). An exhaustive integer search over every feasible (S, P) confirms the same optimum, so it isn't an artefact of the solver. Here's the LP lesson in miniature. The profit-maximising plan is a blend, found where the binding constraints intersect. Making as much of the most profitable product as possible loses to it. All-Pro feels right, since Pro earns $40 against $24, but cutting caps it at 500 units and $20,000. The blend uses both departments fully and earns $1,600 more.

Step 5, read the bottlenecks. Both constraints bind, so Summit has two bottlenecks and no slack anywhere. The Sensitivity report's shadow prices would tell Summit the extra weekly contribution from one more cutting hour against one more sewing hour. That points to where a shift or a machine should go. Relaxing a binding constraint raises profit, and here there's no slack constraint to relax for free. Knowing which constraints bind, and what an extra unit of each is worth, is the whole managerial payoff of formulating the decision as an LP. Guessing a mix gives you neither.

Practice set

Work each problem before you read its solution. Assume every model is built from live formulas that trace to clearly separated input cells. Every figure is recomputed and verified.

P1 (guided). Goal Seek a break-even. A product sells at $20/unit with $12 variable cost and $300,000 fixed cost, and profit = units × (20 − 12) − 300,000. Set up Goal Seek to find the break-even volume (profit = 0). Name the Set cell, To value and By changing cell, and give the answer.

Solution. Set cell is the profit formula cell, To value is 0, and By changing cell is the units input. Goal Seek returns 37,500 units, because units × 8 = 300,000 → units = 300,000 / 8 = 37,500. Both structural conditions hold. The Set cell is a formula that depends on the units cell, and only one cell changes. You could invert this one by hand. The point is the method, because for a tangled model you can't.

P2 (guided). One-variable Data Table. You hold WACC at 12% and want EV for the Shakti forecast as terminal growth g takes 3%, 4% and 5%. The PV of explicit FCF stays fixed at ₹547.72 cr. Describe the Data Table layout and give the three EVs.

Solution. List the g values 3%, 4%, 5% down a column and put =EV_cell one row up and one column right of the top value. Select the two-column rectangle and go to Data ▸ What-If ▸ Data Table. Fill only the Column input cell, pointing it at the g input, since the values run down a column. You get ₹1,794.55 cr at (12%, 3%), ₹1,964.02 cr at (12%, 4%) and ₹2,181.91 cr at (12%, 5%), the bottom row of Worked example 1's grid. Only the terminal value moves. The ₹547.72 cr explicit PV is common to all three.

P3 (guided). Verify a grid cell by hand. Confirm the (WACC 11%, g 4%) cell of the DCF grid, ₹2,255.60 cr, from first principles (FCF 120/138/156/174/192).

Solution. Discount each FCF at 11%. The PVs are 120/1.11 = 108.11, 138/1.11² = 112.00, 156/1.11³ = 114.07, 174/1.11⁴ = 114.62 and 192/1.11⁵ = 113.94, which sum to ≈ 562.74. Then TV₅ = 192 × 1.04 / (0.11 − 0.04) = 199.68 / 0.07 = 2,852.57 and PV(TV) = 2,852.57 / 1.11⁵ = 2,852.57 / 1.68506 = 1,692.86, so EV = 562.74 + 1,692.86 = ₹2,255.60 cr. It matches the grid. That's the corner-check that proves the Data Table's input cells aren't swapped.

P4. Goal Seek an implied assumption. Holding WACC at 12%, the board wants to know what terminal growth g would justify an EV of ₹2,200 cr. Set up the Goal Seek and give the approximate answer.

Solution. Set cell is the EV cell, To value is 2200, and By changing cell is the g input. Goal Seek returns g ≈ 5.07%. Check the direction. The base (12%, 4%) EV is ₹1,964 cr and EV rises with g, so reaching ₹2,200 cr needs g above 4%, and 5.07% makes sense. In words, an EV of ₹2,200 cr requires believing in ~5% perpetual growth, above our 4% base. That's a claim the committee can now debate in the open.

P5. Two-variable Data Table setup. You must build a grid of a monthly loan payment across interest rate {6%, 7%, 8%} and term {15, 30 years}. State which values go across the top, which go down the side, and which is the Row and which the Column input cell.

Solution. Put one input across the top and the other down the side. Say the rate {6%, 7%, 8%} goes across the top row and the term {15, 30} down the left column, with =PMT_cell in the top-left corner. Then the Row input cell is the rate input, because its values run across the row. The Column input cell is the term input, because its values run down the column. That mechanical rule is the whole game: top-row values feed the Row input cell, and left-column values feed the Column input cell. Swap them and every cell is wrong but plausible.

P6. Formulate an LP. A shop makes Chairs ($24 contribution) and Tables ($40). Those match Summit's packs, so reuse Summit's numbers. Standard earns $24 and Pro $40. Cutting takes 1 and 2 hours (cap 1,000) and sewing takes 2 and 3 hours (cap 1,700). Write the objective, decision variables and constraints formally, then name the Solver settings (objective sense, engine, the non-negativity box).

Solution. The variables are S, P ≥ 0. You maximise Z = 24S + 40P subject to 1S + 2P ≤ 1000 (cutting) and 2S + 3P ≤ 1700 (sewing). In Solver, point Set Objective at the Z cell with Max and set By Changing Variable Cells to S, P. Add both used ≤ capacity constraints, tick Make Unconstrained Variables Non-Negative, and choose the Simplex LP engine, because it's a linear model. The optimum is S = 400, P = 300, Z = $21,600, with both constraints binding.

P7. Read the binding constraints. At Summit's optimum (S = 400, P = 300), compute the cutting and sewing hours used, state which constraints bind, and explain what that means for where to invest.

Solution. Cutting used = 1×400 + 2×300 = 1,000 of 1,000, so it's binding. Sewing used = 2×400 + 3×300 = 1,700 of 1,700, so it's binding too. With both binding and zero slack, Summit has two bottlenecks at once. Adding capacity to either department can raise contribution, and the Sensitivity report's shadow price says how much per hour. There's no slack resource to exploit for free. Every extra unit of output needs more of both constrained resources.

P8. Why not all-Pro? Pro contributes $40 versus Standard's $24. A manager proposes making only Pro packs. Compute the best all-Pro plan and its contribution, and explain why the $21,600 blend beats it.

Solution. All-Pro is capped by the tighter constraint on P. Cutting allows P ≤ 1000/2 = 500 and sewing allows P ≤ 1700/3 ≈ 566. The binding cap is 500 units, set by cutting, which gives a contribution of 40 × 500 = $20,000 and leaves sewing with slack (1,500 of 1,700 used). The blend (400, 300) earns $21,600, $1,600 more, by using both departments to the last hour. Under shared constraints, the highest per-unit margin doesn't give the highest total contribution. That's the core LP insight.

P9. Circular reference by hand. A period opens with a $150 (million) cash shortfall before interest. The revolver rate is 10% and interest accrues on the drawn balance. Write the fixed-point equation, solve for the draw and the interest, and say which workbook setting lets Excel compute this without an error.

Solution. Let D be the draw. D = 150 + 0.10 × D → D(1 − 0.10) = 150 → D = 150 / 0.90 = $166.67 m, and interest = 0.10 × 166.67 = $16.67 m. Excel resolves the deliberate loop through File ▸ Options ▸ Formulas ▸ Enable iterative calculation, with Maximum Iterations and Maximum Change set. Enable it only for this intentional circularity. Left on globally, it would silence the warning for accidental circular references elsewhere.

P10. Scenario vs Data Table: choose the tool. For each, name the better tool and justify it in one line. (a) EV across every WACC from 10 to 14% in 0.5% steps. (b) A "bull" world where growth, margin and WACC all move together, saved by name. (c) Contribution across every (Standard, Pro) mix under two capacity limits. (d) EV over WACC × g as a 3×3 grid.

Solution. (a) Data Table (one-variable), a continuous sweep of a single input. (b) Scenario Manager, a named, discrete world that moves several inputs at once. (c) Solver, since this is an optimisation under constraints and a what-if sweep won't find the best mix. (d) Data Table (two-variable), a grid over two inputs. Underneath, the skill is matching tool to question. Sweeping one or two inputs calls for a Data Table, and named multi-input worlds go in Scenario Manager. Finding the best under limits is Solver's job, and solving one input to a target is Goal Seek's.

P11 (timed: 6 min). Goal Seek plus a re-solve. Summit's sewing capacity rises from 1,700 to 2,000 hours, and cutting stays at 1,000. Re-solve the LP. What's the new optimal mix and contribution, and which constraint now binds? Reason from the corners.

Solution. With sewing loosened, cutting 1S + 2P ≤ 1000 becomes the tighter constraint. Standard earns more per cutting hour ($24 against Pro's $20/cutting-hour), so push toward Standard. The optimum moves to all-Standard: S = 1,000, P = 0, with contribution 24 × 1,000 = $24,000. Cutting runs at 1,000/1,000 and binds. What about sewing? 2×1,000 = 2,000, so sewing is at 2,000/2,000 and binds as well. Both bind again at (1,000, 0), with $24,000. Loosening the bottleneck raised contribution from $21,600 to $24,000 and moved the whole mix to Standard. The optimal product depends on the constraints as well as the margins.

P12 (timed: 6 min). Full what-if reasoning. Using the Shakti base case (EV ₹1,964.02 cr at WACC 12%, g 4%), answer three questions. (a) Does EV rise or fall if WACC goes to 11%? (b) Roughly what is EV at (11%, 4%)? (c) If an offer implies WACC 10.1%, is the offer above or below base EV, and by roughly how much? Use the grid.

Solution. (a) EV rises, because a lower discount rate inflates present values, and the grid's 11% figures are higher than its 12% ones. (b) From the grid, (11%, 4%) = ₹2,255.60 cr. (c) WACC 10.1% corresponds to the ₹2,600 cr offer from Worked example 1's Goal Seek. That's above the base EV of ₹1,964 cr by about ₹636 cr (≈ 32%). The buyer is paying a large premium or assuming a materially lower cost of capital. Reading the grid and the implied WACC together is how an analyst frames an offer for the committee.

Applied mini-project

Build a what-if-driven valuation and allocation cockpit. It's one workbook that interrogates two models (a DCF valuation and a product-mix allocation) using every tool above. No number gets typed twice, and every input goes in a clearly separated, colour-coded assumptions block.

Sheet 1: DCF with a sensitivity cockpit.

1. An assumptions block: five years of unlevered FCF, a WACC cell, a terminal-growth cell. Colour the input cells distinctly, and never bury a number inside a formula.
2. A calculation block that discounts the FCFs, computes the Gordon terminal value and its PV, and totals enterprise value. Every formula should chain back to the assumption cells.
3. A two-variable Data Table of EV over WACC (down the side) × terminal growth (across the top), at least 3×3. Put the EV reference in the top-left corner. Map the Row input cell to the growth input and the Column input cell to the WACC input. Verify one grid cell by hand and note the check in a comment, as proof your input cells aren't swapped.
4. A Goal Seek that takes a target enterprise value (an offer price) and solves for the WACC it implies. Write the interpretation in words next to it.
5. Scenario Manager with Base / Bull / Bear scenarios that each move WACC, terminal growth and the year-5 FCF together. Generate a scenario summary that compares their EVs side by side.

Sheet 2: allocation with Solver.

1. Two or three products with per-unit contributions, and two shared capacity constraints such as machine hours and labour hours, all in a labelled inputs block.
2. Decision-variable cells (units per product), a linear objective cell (total contribution), and constraint "used" cells that are live formulas of the variables.
3. Enable Solver, set the objective to Max, add the used ≤ capacity constraints, tick non-negativity, choose Simplex LP, and Solve. Record the optimal mix and contribution.
4. Read the bottlenecks. Mark which constraints bind and which have slack, and write one sentence on where an extra hour of capacity is worth most.

Sheet 3, circular by design (stretch).

1. Model a one-period revolver with a cash shortfall before interest, a revolver rate, interest on the draw, and the circular loop that results. Enable iterative calculation and solve to the fixed point. Then add a circularity-breaker switch cell (1 = live loop, 0 = break to zero) so you can toggle the loop off and prove the rest of the model still computes.

Rubric (self-score; 0 = missing/wrong, 1 = partial, 2 = correct and defended):

Pass: ≥ 8/10, with no zero on any criterion. Keep the workbook. Its DCF cockpit and Solver sheet become the templates you'll clone whenever a valuation needs a sensitivity range or a decision needs an optimum under constraint.

Lab: open the What-if & DCF sensitivity workbook lab in the Practice ▸ Excel tab and pass its checks.

Reading & resources

Tool references (reach for these first):

• Microsoft Learn, the official pages for Goal Seek, Data Tables ("Calculate multiple results with a data table"), Scenario Manager, and Solver (search "define and solve a problem with Solver"). They're authoritative and name the dialog fields. [Free] [Beginner]
• Microsoft Learn. "Define and solve a problem by using Solver" and "Using Solver for capital budgeting / product mix" are the canonical worked LP write-ups from the source. [Free] [Intermediate]

Deeper practice and pattern libraries:

• ExcelJet (exceljet.net), concise entries on Data Tables and Goal Seek with clear layout diagrams; the fastest way to see the corner-formula rule in a picture. [Free] [Beginner]
• Chandoo (chandoo.org), sensitivity-analysis and scenario tutorials aimed squarely at financial models, with downloadable workbooks. [Free] [Beginner]
• Corporate Finance Institute (CFI), DCF sensitivity-table and scenario-analysis guides that put WACC × growth grids in valuation context. [Free/Paid] [Intermediate]

On modelling discipline (the why behind the tools):

• "Financial Modeling" (Simon Benninga) or the FAST/Coralis modelling standards, on separating inputs from calculations, and on handling circularity with a switch instead of blanket iteration. [Paid] [Intermediate]
• Any reputable "circular references in financial models" article. Search "revolver circularity iterative calculation Excel", and make sure you understand why the loop is real and why the global switch is dangerous before you ever enable it. [Free] [Intermediate]

Bridge forward: the deterministic grid of a Data Table becomes a random sample in Monte-Carlo simulation (EX6.xx), and the model architecture that makes all these tools trustworthy (clean inputs, live chains, isolated circularity) is the whole subject of EX2.03. Skim EX2.03's intro once your cockpit workbook runs.

The Modern Analyst's Addendum

Everything above teaches this skill from first principles, by hand, that is how you learn it, and the mastery check still tests it that way. This addendum shows how a working analyst amplifies the same skill today. It adds; it never replaces. (R1/R10)

AI-Augment this skill

{
  "skill": "Goal Seek, Data Tables, Scenario Manager, and Solver (Simplex LP and GRG Nonlinear) as numerical methods with convergence properties, not black boxes -- including the discipline of treating an optimizer's output as conditional on its starting point, not as a discovered fact",
  "use": "AI is genuinely useful for the mechanical half of this module: drafting a Solver constraint set from a plain-English description of a resource-allocation problem, or translating a Goal-Seek-by-hand bracketing routine into a root-finder call. It is dangerous for the module's central warning, because it will hand back a single confident 'optimal' answer with no mention of whether the underlying problem is even convex -- asked to solve a nonlinear pricing or allocation problem, a language model will report whichever local optimum its own reasoning happened to land near, exactly Solver's GRG Nonlinear failure mode, with the same absence of a warning label.",
  "tools": ["Coding assistants -- Claude, ChatGPT, or a notebook's inline assistant for drafting scipy.optimize calls (linprog for a formulated LP, minimize for a nonlinear objective) from a plain-English problem statement", "Spreadsheet-reading AI -- for auditing an inherited Solver model's constraint list against the stated business rules before trusting its reported optimum"],
  "prompt": "Here is a resource-allocation or pricing problem: <describe objective and constraints in plain English>. First tell me whether the objective is LINEAR in the decision variables. If it is, formulate it as a linear program and write the solver call. If it is not, say explicitly that one solve is not enough, and give me three widely spaced starting points to re-solve from before I trust any single answer.",
  "verify": "For a linear objective, confirm the structure yourself -- every constraint and the objective a plain weighted sum of the decision variables, nothing squared, multiplied together, or divided -- and if so, one solve IS the global optimum, by the vertex argument this module derives; no re-checking needed. For anything nonlinear, never accept a single solve from an assistant or from Solver: re-run from at least three widely spaced starting points yourself and compare the results, because a fluent presentation of one number is no evidence at all that a better answer does not exist elsewhere in the feasible region.",
  "diy": "The gate is unaided: you formulate a linear program by hand (objective, decision variables, constraints), state from memory why its optimum sits at a vertex, and solve a revolver circularity to its fixed point with a calculator. No tool in the room solves any of this for you."
}

Modern Data Analysis

By hand first. You solved Shakti's implied WACC by bracketing a low guess and a high guess and interpolating. You formulated Summit Gear's mix as decision variables, an objective and two capacity constraints, and trusted the answer only after checking that both constraints bound. That bracketing reflex and that constraint-checking reflex are the whole skill. Everything below runs the same two operations through a general-purpose solver instead of a spreadsheet dialog.

Today's workflow. scipy.optimize.brentq is Goal Seek generalised. It uses the identical bisect-and-interpolate idea, but it converges to machine precision where Goal Seek stops at "close enough." scipy.optimize.linprog is Solver's Simplex LP engine. For a linear objective and linear constraints it's guaranteed to find the global optimum, for the vertex reason you saw earlier. A linear objective's contours are straight lines, the feasible region is convex, and the last point of contact is always a corner. Solver's other engine, GRG Nonlinear, carries no such guarantee. It's a local, gradient-based search, and scipy.optimize.minimize is the same family of method under a different name. One rule governs both. For a linear program, one solve is an answer. For a nonlinear program, one solve is a sample.

Tools & sources (IN + US). Use scipy.optimize (brentq, linprog, minimize) for the solvers below and numpy for the vectorised DCF and Monte Carlo engines. To calibrate the inputs a real Goal Seek or Solver model would use, start with the national sources. In India you'd use CCIL and FBIL's G-sec yield curve for a WACC's risk-free leg, screener.in and a company's own annual report for the capacity and revenue figures behind a real product-mix LP, and MOSPI for the nominal-GDP series that caps a terminal-growth assumption. In the US you'd use FRED's DGS10 for the Treasury leg of a WACC, a 10-K's segment-capacity disclosures via EDGAR for real allocation constraints, and Damodaran's WACC-by-sector tables (pages.stern.nyu.edu/~adamodar) as a cross-check.

# recomputed in-session -- R3: Shakti's implied WACC and Summit Gear's LP, re-solved with
# general-purpose numerical methods and checked against the module's own worked figures.
import numpy as np
from scipy.optimize import brentq, linprog

FCF = np.array([120.0, 138.0, 156.0, 174.0, 192.0])
def ev(wacc, g=0.04):
    t = np.arange(1, 6)
    return np.sum(FCF/(1+wacc)**t) + FCF[-1]*(1+g)/(wacc-g)/(1+wacc)**5

implied_wacc = brentq(lambda w: ev(w) - 2600, 0.05, 0.115)          # Goal Seek, generalised

res = linprog(c=[-24, -40], A_ub=[[1, 2], [2, 3]], b_ub=[1000, 1700],
               bounds=[(0, None), (0, None)], method="highs")       # Solver's Simplex LP, generalised
print(f"implied WACC = {implied_wacc*100:.4f}%")
print(f"Summit optimum: S={res.x[0]:.0f} P={res.x[1]:.0f} contribution=${-res.fun:,.0f}")

Verify. brentq returns an implied WACC of 10.1003%, which matches the Goal Seek answer to four decimal places. linprog returns S=400, P=300 at a contribution of $21,600, the same as Solver's reported optimum. Worked figures earlier on the page are the ground truth, and this code is checked against them.

Quantitative lens

A linear program needs one solve. A nonlinear one needs several, and Solver won't tell you if you stopped too soon. Summit Gear's mix is safe from this because the objective is linear. The vertex argument from earlier guarantees Simplex LP finds the single global optimum whatever the starting point. Most real pricing decisions aren't linear. Here's a demonstration you can't run on paper. Take a synthetic two-segment pricing problem. A large, price-sensitive segment has a reservation price around ₹340, a smaller, price-insensitive segment has one around ₹640, and the unit cost is ₹200. The profit-versus-price curve that results has two local maxima. One is a high-volume "penetration" price and the other a low-volume "premium" price, because the two segments pull the optimum in different directions. Here's a gradient-based local optimiser, from the same family as GRG Nonlinear, run from nine different starting prices:

# recomputed in-session -- R3: identical objective, nine different starting points
from scipy.optimize import minimize
c, N1, k1, P1, N2, k2, P2 = 200.0, 8000.0, 0.045, 340.0, 2600.0, 0.012, 640.0
def Q(P): return N1/(1+np.exp(k1*(P-P1))) + N2/(1+np.exp(k2*(P-P2)))
def neg_profit(x): return -(x[0]-c)*Q(x[0])

for p0 in [220, 260, 300, 400, 500, 600, 700, 800, 850]:
    r = minimize(neg_profit, x0=[p0], method="L-BFGS-B", bounds=[(205, 880)])
    print(f"start P0={p0:4d} -> converges to P*={r.x[0]:7.2f}  profit=Rs {-r.fun:,.0f}")

Starting prices at or below ₹400 converge to P* = ₹317.73, profit ≈ ₹9,88,765. Starting prices at or above ₹500 converge to P* = ₹544.23, profit ≈ ₹6,79,917. That's a different price, a different recommendation and 45.4% less profit, from the identical spreadsheet and the identical Solver settings. Only the starting value in the changing cell differs. The basin boundary is near ₹417. In both cases Excel's Solver reports "Solver found a solution. All constraints and optimality conditions are satisfied". It has no way to know a taller hill exists elsewhere, and it doesn't say so. An analyst who typed ₹450 into the price cell "just to give Solver a sensible starting point" would have anchored the search on the worse hill without any warning, and never known it.

An optimiser's output is a claim conditional on the model and on where you started it. For a real LP like Summit Gear's, that dependence on the starting point disappears, because linearity plus convexity is a mathematical guarantee. Anything nonlinear is different, and that covers most real pricing and most capacity-and-mix problems with volume discounts or segment effects. For those, use Solver's own Multistart option (Options ▸ tick "Use Multistart," bound every changing cell). Or do it by hand, re-running from several widely spaced starting points and keeping the best, as the code above does.

Data Tables versus Monte Carlo is the identical distinction, one level up. A Data Table is an exhaustive grid, with every combination computed once and zero sampling error. But Excel's Data Table dialog has only two boxes (Row input cell and Column input cell). A three-variable Data Table can't be built at all, in any version of Excel, however coarse the grid. A grid with G points per dimension needs G^D cells. At 3 points per dimension, 2 drivers need 9 cells (Worked example 1's own grid) and 5 need 243. At 10 points per dimension, 5 drivers need 100,000 cells and 8 need 10^8. Monte Carlo swaps the exhaustive guarantee for a sampling one, which scales with the number of drivers instead of G^D:

# recomputed in-session -- R3: 3 jointly-drawn drivers on the Shakti engine -- past any Data Table's reach
rng = np.random.default_rng(20260731)
N = 200_000
scale = rng.normal(1.00, 0.08, N); wacc = rng.uniform(0.105, 0.135, N); g = rng.uniform(0.030, 0.050, N)
t = np.arange(1, 6)
pv_fcf = np.sum((FCF[None,:]*scale[:,None])/(1+wacc[:,None])**t[None,:], axis=1)
evs = pv_fcf + (FCF[-1]*scale)*(1+g)/(wacc-g)/(1+wacc)**5
print(f"mean={evs.mean():,.0f} std={evs.std():,.0f} P10={np.percentile(evs,10):,.0f} P90={np.percentile(evs,90):,.0f}")

Three jointly varied drivers already go one dimension past a Data Table's hard ceiling: a forecast-scale error of ~±8%, WACC from 10.5 to 13.5%, and terminal g from 3 to 5%. They give a mean EV of ₹1,996.74 cr against the deterministic base case of ₹1,964.02 cr, a P10 to P90 range of ₹1,629 to 2,415 cr, and a 50.0% chance of landing below the point estimate. The distribution is right-skewed, since 1/(WACC−g) blows up faster than it shrinks.

That sampled answer carries a measurable, shrinking error. Across thirty independent 100-draw replications, the sample mean's own spread was ₹22, against a theoretical σ/√N ≈ ₹31. At 100,000 draws per replication it fell to ₹1.06, against ≈ ₹0.97 in theory. Both bracket the 1/√N convergence law, and the N=100 figure is noisy simply because it averages only thirty replicates. A Data Table's 9 cells carry zero sampling error and answer a narrower question exactly. A Monte Carlo's 200,000 draws carry a shrinking but nonzero sampling error and answer a wider question approximately. Neither is "more rigorous" in the abstract. The choice depends on what question you're asking and how many dimensions it has.

Where this goes next: galaxy cross-links

• G1 · QM1.03 Time Series, Monte Carlo & ML, joint sampling, correlated draws, and the convergence theory behind the 1/√N standard-error scaling demonstrated numerically above.
• G2 · QD1.04 Portfolio Optimization in Code, the identical linear- and nonlinear-programming machinery (objective, decision variables, constraints, multistart) applied to portfolio weights instead of a product mix.
• G1 · QM1.01 Probability, Distributions & Estimation, percentile reporting, the standard error of a sample mean, and why a sampled distribution is a claim about the sample, not a certainty about the world.

Teach it back & journal

Feynman prompt. Explain the difference between the four tools to a shopkeeper who has never modelled anything, using the metaphor of a recipe. A recipe turns ingredients into a dish, which is the model running forwards. Goal Seek is asking "the cake must weigh exactly one kilo, how much flour?" and working backwards to the one ingredient. A Data Table is baking the same cake at every oven temperature and every baking time, then laying the results in a grid so you can compare them all at once. Scenario Manager is three saved recipe cards ("party cake", "diet cake", "budget cake"), each changing several ingredients, and you flip between them on demand. Solver is "given only this much flour and this much sugar, what mix of cakes and biscuits earns the most at the stall?" Save the words what-if, optimisation and constraint for your last two sentences, and attach each one to the moment in the kitchen story it describes.

Journal prompt. Find one decision you or your team make by guessing and adjusting. It might be a price you nudge until it "feels right", a budget you tweak until it balances, or a workload you split across people by gut. Write down (1) the single number you're really trying to hit, which makes it a Goal Seek. Or write down the range of assumptions you're unsure about, which is a Data Table, or the "best split under a limit" you're eyeballing, which is a Solver problem. Then rebuild one of them as a proper model with the matching tool, and note what changed the moment the answer came from a structure instead of a hunch. Finish with the one sentence you want to remember forever. Make it about why interrogating a model backwards, sideways and for the optimum beats reading whatever falls out of it the first time, and why you'll never again trust a Data Table grid you haven't checked one cell of by hand.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.