Reference

Templates and checklists for value-driver modeling

Part of the Value Driver Modeling skill · loaded on demand from SKILL.md

Copy-ready structures. Placeholders in angle brackets are permitted here and nowhere in a worked example or client deliverable. Every template carries the column definitions the model needs to fill it without guessing.

1. Root-metric statement (Stage 1)

Root metric: <metric name>
Definition: <formula in words, e.g. NOPAT minus invested capital x WACC>
Period: <FY, actual or normalised, steady-state annual or multi-year path>
Capitalisation: <key value driver formula at g = <x>% and WACC = <y>%, or client multiple of <n>x, or none>
Why this metric and not <EBITDA / revenue / other>: <one sentence tied to the decision>
Decision it serves: <decision, decision-maker, date>
Required accuracy: <rank drivers only / size to within 20% / defend to a buyer>

2. Baseline page (Stage 2)

Reported-to-normalised P&L:

LineReportedAdjustmentNormalisedNote
Revenue
Cost of goods sold
Gross profitmargin %
Variable operating cost% of revenue; what is in it
Fixed operating cost
EBITDA
Depreciation and amortisationstrip acquired-intangible amortisation
EBITAmargin %
Operating tax at <rate>effective cash rate, not reported charge
NOPAT

Operating invested capital:

LineReportedTreatment (include / exclude)Operating ICBasis (days and base where relevant)
Trade receivablesdays of revenue
Inventorydays of COGS
Trade payablesdays of COGS, shown negative
Other operating working capital
Operating working capital
Net PP&E
Capitalised operating intangibles
Invested capital (operating)year-end or average: state which
Goodwill and acquired intangiblesExclude for management view; show for owner view
Cash and non-operating assetsExclude

Returns block:

ROIC = NOPAT / invested capital = <x>%
DuPont: NOPAT margin <a>% x capital turnover <b>x = <x>%
WACC = <y>% (source or assumption)
Spread = <x - y> points
Capital charge = invested capital x WACC = <z>
Economic profit = NOPAT - capital charge = <e>   Cross-check: invested capital x spread = <e>
Value = NOPAT x (1 - g/ROIC) / (WACC - g) = <v> at g = <g>%   (= <v / EBITA>x EBITA)
Owner view: ROIC including goodwill = <x2>%

3. Driver tree (Stage 3)

<Root metric> <value>                                        [identity]
├── <Node> <value>                                           [identity]
│   ├── <Node> <value>                                       [identity]
│   │   ├── <Leaf> <value> = <formula from children>         [identity]
│   │   │     driven by: <causal 1>; <causal 2>              [causal, see register]
│   │   └── <Leaf> <value>                                   [identity]
│   └── <Node> <value>                                       [identity]
└── <Node> <value>                                           [identity]
Arithmetic checks: <each identity written out and ticked>

Rules printed at the foot of the page:

  • Every node is labelled identity or causal.
  • Identity nodes reproduce the parent exactly; write the check.
  • Causal links hang below identity leaves and appear in the register with a test.
  • Depth stops at a metric that appears in an operating report with an owner.

Causal-link register:

Identity leafDriven by (causal claim)Proposed testEvidence so farOwnerHand-off
hypothesis-stress-testing / primary-research-synthesis / market-competitive-intelligence

4. Envelope table (Stage 4)

Rule: value of a permanent change = change in annual economic profit / (WACC - g).

LeverTwo-line calculationEconomic profit effect per yearEnvelope valueRange source

Followed by:

Ranking is clear / not clear because: <reason>
Build the model: yes / no. Level of detail: <annual steady state / 3-year path / by segment>
The one number that changes the answer if wrong by 2x: <number>. First hour of diligence: <what>.

5. Model specification (Stage 5)

Sheet layout to hand to xlsx:

Sheet 1  Assumptions   one row per input (template below); scenario switch cell at top
Sheet 2  Calculations  one row per identity node, tree order, formulas only, unit column
Sheet 3  Outputs       baseline column; one column per scenario; tornado table; chart data
Sheet 4  Checks        one row per check; a single cell that reads OK or lists failures
Sheet 5  Reconciliation  reported to normalised, P&L and capital
Colour convention: inputs blue, formulas black, links to other sheets green

Assumptions sheet:

IDInputBaseUnitSourceDateOwnerLowHighRange sourceScenario AScenario B...

Column definitions: ID is stable and referenced by formulas; Unit is one of $M, %, days, units, x, per unit; Source is the document or "assumed"; Low and High are oriented so High is favourable for the root; scenario columns hold the value under each named case and the switch selects which column feeds the calculation sheet.

Checks sheet, minimum set:

CheckFormula in wordsToleranceStatus
NOPAT two routesEBITA x (1 - t) = (EBITDA - D&A) x (1 - t)0.01
Tree sumsSum of leaves at each level = parent0.01
Working capitalReceivables + inventory - payables + other = operating working capital0.01
CapitalWorking capital + fixed + intangibles = invested capital0.01
Economic profit two routesNOPAT - IC x WACC = IC x (ROIC - WACC)0.01
Tornado bracketsFor each input, root at low <= base <= root at highexact
Scenario switchSwitch value within valid listexact
WACC > gWACC - g > 0exact
OverallAll above passOK / FAIL: <list>

6. Model-hygiene checklist (Stage 5, run before anyone else opens the file)

#TestPass / FailFix applied
1Every input lives on the assumptions sheet with unit, source, date and owner
2No typed number in any calculation formula except labelled structural constants (365, 12, 100)
3Every row has a unit in a dedicated column; percentages stored as decimals
4One sign convention declared and held; payables shown as a reduction of working capital
5Working-capital days on the right base (receivables on revenue; inventory and payables on COGS), base named on the row
6Checks sheet exists, covers the minimum set, and the overall cell reads OK
7Time explicit: one column per period, fiscal year stated, average versus year-end capital stated
8Scenarios are switches on the assumptions sheet, not copied tabs
9Colour convention applied (inputs blue, formulas black, links green)
10Baseline column reconciles to reported accounts on the reconciliation sheet, every adjustment named
11A colleague changed one input and the root moved as expected
12Model headline agrees with the envelope within a factor of 1.5, or the difference is explained

7. Tornado table and chart specification (Stage 6)

RankInputBaseLowHighRoot at lowRoot at highSwingValue at lowValue at highRange source

Chart specification for xlsx or pptx:

  • Horizontal bar chart, one bar per input, sorted by swing descending, longest at top.
  • Each bar runs from root-at-low to root-at-high; a vertical line at the base value.
  • Axis in the root metric's unit; a secondary annotation in value if the audience is owners.
  • Label each bar with the input name and its low and high values (not just the name).
  • Title is the reading, not the chart type: "Gross margin moves economic profit more than the next two drivers combined", not "Sensitivity analysis".

Reading paragraph:

Total swing across <n> inputs: <s>. Top three: <t> (<t/s>%).
<Top input> alone: <x>% of total swing, more than <next two> combined.
Controllable within horizon: <list>. Not controllable: <list>.
One-at-a-time omits: <interaction 1>, <correlated inputs>; covered in scenarios.
Bracket check: passed / failed on <input>.

8. Scenario and value-at-stake table (Stage 7)

CaseAssumptions changed from baseRevenueEBITANOPATInvested capitalROICEconomic profitSteady-state valueOne-off capital release (consumed)Total valueDelta vs base
BaseNone00
<Management case 1>
<Management case 2>
Combined plan
Downside

Column definitions: steady-state value from the key value driver formula with ROIC recomputed for the case; one-off release = base invested capital minus case invested capital (negative when capital is consumed); total = steady-state value + release.

Value-at-stake statement:

Value at stake: $<plan minus base> (from $<base> to $<plan>), of which $<a> from <programme 1> and $<b> from <programme 2>.
Downside: $<loss> if <downside assumptions>.
Range for the board: minus $<loss> to plus $<gain>, base at zero.
Carried by: (1) <assumption>; (2) <assumption>; (3) <assumption>.
If the downside happens, the recommendation <holds / needs a stage gate at <point>>.

9. Drivers-that-matter note (Stage 8, half a page)

Filter table:

DriverTornado rankControllable within horizon (how)Range evidenced (source)Verdict: in / out / folded into

Driver sentences, one each, in this shape:

<n>. <Driver> from <current> to <target> through <mechanism>. Worth $<x> of <NOPAT / economic profit> a year and about $<y> of value. Owner: <role>. Evidence: <why the target is achievable>.

Headline:

<Business> is worth about $<v> today, earning <spread> over its cost of capital [and <above / below> its cost of capital once goodwill is counted]. <Programme>, built on <evidence>, takes it to about $<v2>. The plan rests on one assumption above all others: <assumption>.

10. Hand-off packet

ToWhatFormat
executive-communicationHeadline, three driver sentences, tornado chart, scenario tableText plus chart data
hypothesis-stress-testingEach causal link with its proposed test and the swing it drivesCausal-link register rows
execution-planningDriver, baseline, target, value, owner for each driver that matters; sequencing noteTable
kpi-architectureIdentity tree with baselines and targetsTree plus assumptions sheet
Pre-mortem (separate exercise)The three assumptions that carry the planList
strategic-choiceOne scenario column per option, with the driver assumptions behind eachScenario table
war-gamingDownside assumptions that depend on competitor movesList
Pricing designPrice-realisation leaf, baseline and range, when it becomes a pricing-design questionLeaf plus register row
xlsxSheet layout, assumptions sheet, calculation order, checks, output layout, colour conventionThis file's sections 5 and 6

11. Partner review checklist (before the client sees anything)

#TestResult
1Recompute the root from the leaves by hand; reproduces baseline to stated precision
2Three random baseline numbers traced to accounts or numbered assumptions
3Invested capital branch present with a cost attached, or its absence justified in line one
4Every tornado range has a source; "assumed" ranges flagged in the reading
5Every tornado input brackets the base
6Model headline within 1.5x of the envelope, or the difference explained
7Five random calculation cells: no typed numbers; checks cell reads OK
8Each driver that matters has an owner, a target and evidence; a CEO could act on it
9Value at stake stated as a range, downside included
10Baseline reconciles to reported EBITDA less D&A with every adjustment listed
11Growth shown with the capital it consumes and the return it earns
12One-off capital releases counted once, not capitalised as annual