Excel Goal Seek and Data Tables: How to Build Financial Sensitivity Analysis

Goal Seek finds the value of one input needed to reach a target output. A Data Table calculates an output across alternative values of one or two inputs. Use them to expose the assumptions behind a financial result, not to manufacture a desired valuation. Both tools depend on a correctly linked underlying model.

Build a model that can be solved

Consider a hypothetical product with a selling price of ₹500, variable cost of ₹300 per unit and fixed annual costs of ₹4,00,000. Let B2 contain price, B3 variable cost, B4 volume and B5 fixed costs. Enter =(B2-B3)*B4-B5 in B6 for operating profit.

At 3,000 units, profit is ₹2,00,000. Break-even volume is Fixed costs/(Price-Variable cost), or 2,000 units. The closed-form calculation is a useful independent check before asking Excel to solve the same relationship.

The assumed relationship is intentionally simple. It excludes capacity limits, step-fixed costs, discounts, taxes and working-capital needs. If those features are material, add them explicitly and recognise that profit break-even may differ from cash break-even.

Use Goal Seek for one unknown

On desktop Excel, open Data, What-If Analysis, then Goal Seek. Set the formula cell B6 to the target zero by changing volume cell B4. The formula cell must depend on the changing cell. Excel should find 2,000 for this linear illustration.

Microsoft's What-If Analysis guide distinguishes Goal Seek's one-input approach from tools that vary several inputs. If a problem needs several decisions plus constraints, Solver may be more appropriate. That does not mean it is required for a straightforward one-variable calculation.

Check the proposed answer against business feasibility. A fractional number of customers, negative volume or a price below contractual limits may solve the equation without being operationally possible. Round only according to a justified business rule, then recalculate the output to see whether the target still holds.

Create a one-variable Data Table

To test profit at different volumes, leave the model inputs in B2:B5 and profit in B6. In D1 enter a link to the output, =B6. List trial volumes in C2:C6, such as 1,000, 1,500, 2,000, 2,500 and 3,000.

Select C1:D6, open Data Table and use B4 as the Column input cell because trial volumes run down a column. Excel substitutes each listed volume into B4 and records the resulting linked output. Check the 2,000-unit row against the independently calculated break-even point.

Do not confuse a What-If Data Table with an ordinary formatted Excel Table. They serve different purposes despite similar names. Keep the input units visible, and label the output as annual operating profit in rupees rather than simply “result”.

Extend to two inputs and interpret the range

For a two-variable illustration, place =B6 in E1. Put trial prices across F1:H1, and trial volumes down E2:E6. Select E1:H6 and create the Data Table. Set the Row input cell to price B2 and the Column input cell to volume B4.

The orientation follows where the trial values are positioned, not where the original model inputs happen to sit. Confirm a corner scenario manually: price ₹450 and volume 1,000 produce (450-300)*1000-400000, or a ₹2,50,000 loss. This catches swapped input cells.

Present a range with clearly named assumptions rather than highlighting only the most attractive cell. In a DCF, analogous inputs might be WACC and terminal growth, but combinations with growth at or above WACC are invalid for a standard growing perpetuity. A sensitivity grid should identify invalid cases instead of colouring them as opportunities.

Document calculation settings and refresh the workbook before sharing. Large Data Tables can affect recalculation time; no performance claim is made here. The workflow and formulas are documentation-based and were not executed in Excel. Validate them in the target desktop edition and retain a manually checked scenario beside the automated output.

Quick questions, clear answers

Does Goal Seek find the best decision?

It finds an input that produces a target within the model; it does not establish that the target is sensible or the decision optimal. A solution may violate commercial constraints or rely on unrealistic assumptions. Check feasibility and compare alternative scenarios before making a recommendation.

Why does my Data Table repeat one number?

The output may not depend on the chosen input, the wrong input cell may be selected, or calculation settings may prevent an update. Trace the formula links and test a manual input change first. Then confirm row-versus-column orientation and recalculate.

Is sensitivity analysis a forecast?

It shows conditional outcomes under specified assumptions. It does not assign probabilities or prove which scenario will occur. A forecast needs a reasoned base case, supporting evidence and an explanation of uncertainty; sensitivity analysis helps reveal how fragile that forecast may be.

About this guide

Aniket Thakur prepared this educational guide from secondary research checked on 22 October 2026. All numerical illustrations are hypothetical unless stated otherwise. No investment product was tested, and no individual financial circumstances were assessed. Read the cited primary material and relevant product documents before acting.