Financial model review

Scenario and sensitivity checks in Excel

Test scenarios and sensitivities in a financial model with worked examples. Check that changed assumptions reach the intended profit and cash outputs.

Three forecast paths beside sensitivity sliders

A scenario selector can change its label without changing the financial model. A sensitivity grid can contain plausible numbers while referencing the wrong input. Test the connection from an assumption to an output, rather than accepting the presence of a Base, Downside and Upside dropdown as evidence that it works.

Start with a small calculation whose expected result you can determine by hand. The invented model below uses a single product and one period; it is a review exercise, not a forecast or a recommendation about commercial assumptions.

Write down the base case first

Use these assumptions:

  • Units sold: 1,000.
  • Price per unit: EUR 50.
  • Variable cost per unit: EUR 30.
  • Fixed operating costs: EUR 12,000.

For this simplified model, operating profit before any other charges is:

Units × (Price − Variable cost) − Fixed costs
1,000 × (50 − 30) − 12,000 = EUR 8,000

Record which cells hold the four inputs and which cell reports that output. Exclude interest, taxes, depreciation and working capital explicitly; adding them would change what the result measures. A test is useful only when its expected result and scope are clear.

Change one input before changing a whole scenario

In a separate copy, reduce units from 1,000 to 900 while holding the other inputs fixed. Expected operating profit is EUR 6,000, a EUR 2,000 reduction. Each lost unit removes the EUR 20 contribution between price and variable cost.

Restore units to 1,000, then change price from EUR 50 to EUR 52. Expected profit becomes EUR 10,000. Restore price, then increase variable cost to EUR 32: profit should become EUR 6,000.

These simple movements test different connections. If changing price has no effect, the output may reference an old price, a typed revenue total or a different scenario input. An unexpected result is a reason to trace the calculation, not immediate proof of one particular defect.

In a fuller model, capacity limits, commissions or other linked assumptions may change these relationships. Predict the movement from the actual model's stated logic instead of applying the simple example mechanically.

Build a downside case from explicit assumptions

Suppose the intended downside combines 900 units, EUR 48 price, EUR 31 variable cost and EUR 12,000 fixed costs. The expected profit is:

900 × (48 − 31) − 12,000 = EUR 3,300

The reduction from the base case is EUR 4,700. Do not compare that with the volume-only reduction of EUR 2,000 and assume the difference is wrong: this case changed three assumptions together.

Check that switching to Downside updates all intended input cells and leaves unrelated assumptions unchanged. Inspect the actual cell values, not only the selector label. Return to Base and confirm EUR 8,000 again. Test switching in both directions so that an old scenario's inputs cannot remain unnoticed.

Check the scenario tool's output date

Excel's desktop Scenario Manager can store and switch sets of input values. Where available, find it under Data > What-If Analysis > Scenario Manager; menus vary by version. Existing scenario summary reports do not automatically update after changes to their scenarios, so generate a new summary when comparing revised cases. Microsoft explains this behavior in its scenario guide.

A manually built dropdown and an Excel Scenario Manager scenario are different implementations. Identify which the workbook uses before following instructions. For either one, compare the live calculation with the assumptions that are actually selected.

Verify a sensitivity grid against individual calculations

A sensitivity explores several values of one or two inputs. Excel's What-If Data Table can do this; a two-variable table maps its row and column values to two input cells. See Microsoft's data-table guide.

For the simplified model, keep variable cost EUR 30 and fixed costs EUR 12,000. At 900 units and EUR 50 price, profit is EUR 6,000. At 1,000 units and EUR 52 price, profit is EUR 10,000. Check these two grid intersections against individual input changes in the working copy.

If the grid disagrees, inspect its linked output and input mapping. Confirm whether it is a genuine Excel Data Table, ordinary formulas or pasted values. A colored heatmap does not establish any of those connections.

Calculation settings also matter. Excel supports manual calculation and an automatic mode that excludes data tables; a visible grid may therefore need a deliberate recalculation in Excel. Microsoft's calculation guide explains the options. Do not infer that every displayed value reflects your latest edit.

Keep an evidence-based scenario checklist

  1. Name the output and list the assumptions that should affect it.
  2. Calculate one small baseline independently.
  3. Test individual inputs and explain the direction of each movement.
  4. Test the combined scenario and inspect its selected input values.
  5. Compare a few sensitivity intersections with individual recalculations.
  6. Restore the approved base case and save the review copy clearly.

FinModelCheck does not execute scenario switches, recalculate data tables, test sensitivities or assess forecast plausibility. It inspects supported saved Excel errors, broken references, external workbook links and embedded numeric coefficients. Its provisional score is not a financial validation of the scenarios.

Use the checker for the supported checks and the example to see the report. The complete PDF is free in the current valid owning browser session without registration. If a test gives an unexplained zero, consult the IFERROR review guide; for calculation loops, read about circular references.

Keep reviewing

Continue with a related guide.

Browse all guides