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:
| Line | Reported | Adjustment | Normalised | Note |
|---|---|---|---|---|
| Revenue | ||||
| Cost of goods sold | ||||
| Gross profit | margin % | |||
| Variable operating cost | % of revenue; what is in it | |||
| Fixed operating cost | ||||
| EBITDA | ||||
| Depreciation and amortisation | strip acquired-intangible amortisation | |||
| EBITA | margin % | |||
| Operating tax at <rate> | effective cash rate, not reported charge | |||
| NOPAT |
Operating invested capital:
| Line | Reported | Treatment (include / exclude) | Operating IC | Basis (days and base where relevant) |
|---|---|---|---|---|
| Trade receivables | days of revenue | |||
| Inventory | days of COGS | |||
| Trade payables | days 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 intangibles | Exclude for management view; show for owner view | |||
| Cash and non-operating assets | Exclude |
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 leaf | Driven by (causal claim) | Proposed test | Evidence so far | Owner | Hand-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).
| Lever | Two-line calculation | Economic profit effect per year | Envelope value | Range 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:
| ID | Input | Base | Unit | Source | Date | Owner | Low | High | Range source | Scenario A | Scenario 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:
| Check | Formula in words | Tolerance | Status |
|---|---|---|---|
| NOPAT two routes | EBITA x (1 - t) = (EBITDA - D&A) x (1 - t) | 0.01 | |
| Tree sums | Sum of leaves at each level = parent | 0.01 | |
| Working capital | Receivables + inventory - payables + other = operating working capital | 0.01 | |
| Capital | Working capital + fixed + intangibles = invested capital | 0.01 | |
| Economic profit two routes | NOPAT - IC x WACC = IC x (ROIC - WACC) | 0.01 | |
| Tornado brackets | For each input, root at low <= base <= root at high | exact | |
| Scenario switch | Switch value within valid list | exact | |
| WACC > g | WACC - g > 0 | exact | |
| Overall | All above pass | OK / FAIL: <list> |
6. Model-hygiene checklist (Stage 5, run before anyone else opens the file)
| # | Test | Pass / Fail | Fix applied |
|---|---|---|---|
| 1 | Every input lives on the assumptions sheet with unit, source, date and owner | ||
| 2 | No typed number in any calculation formula except labelled structural constants (365, 12, 100) | ||
| 3 | Every row has a unit in a dedicated column; percentages stored as decimals | ||
| 4 | One sign convention declared and held; payables shown as a reduction of working capital | ||
| 5 | Working-capital days on the right base (receivables on revenue; inventory and payables on COGS), base named on the row | ||
| 6 | Checks sheet exists, covers the minimum set, and the overall cell reads OK | ||
| 7 | Time explicit: one column per period, fiscal year stated, average versus year-end capital stated | ||
| 8 | Scenarios are switches on the assumptions sheet, not copied tabs | ||
| 9 | Colour convention applied (inputs blue, formulas black, links green) | ||
| 10 | Baseline column reconciles to reported accounts on the reconciliation sheet, every adjustment named | ||
| 11 | A colleague changed one input and the root moved as expected | ||
| 12 | Model headline agrees with the envelope within a factor of 1.5, or the difference is explained |
7. Tornado table and chart specification (Stage 6)
| Rank | Input | Base | Low | High | Root at low | Root at high | Swing | Value at low | Value at high | Range 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)
| Case | Assumptions changed from base | Revenue | EBITA | NOPAT | Invested capital | ROIC | Economic profit | Steady-state value | One-off capital release (consumed) | Total value | Delta vs base |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Base | None | 0 | 0 | ||||||||
| <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:
| Driver | Tornado rank | Controllable 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
| To | What | Format |
|---|---|---|
executive-communication | Headline, three driver sentences, tornado chart, scenario table | Text plus chart data |
hypothesis-stress-testing | Each causal link with its proposed test and the swing it drives | Causal-link register rows |
execution-planning | Driver, baseline, target, value, owner for each driver that matters; sequencing note | Table |
kpi-architecture | Identity tree with baselines and targets | Tree plus assumptions sheet |
| Pre-mortem (separate exercise) | The three assumptions that carry the plan | List |
strategic-choice | One scenario column per option, with the driver assumptions behind each | Scenario table |
war-gaming | Downside assumptions that depend on competitor moves | List |
| Pricing design | Price-realisation leaf, baseline and range, when it becomes a pricing-design question | Leaf plus register row |
xlsx | Sheet layout, assumptions sheet, calculation order, checks, output layout, colour convention | This file's sections 5 and 6 |
11. Partner review checklist (before the client sees anything)
| # | Test | Result |
|---|---|---|
| 1 | Recompute the root from the leaves by hand; reproduces baseline to stated precision | |
| 2 | Three random baseline numbers traced to accounts or numbered assumptions | |
| 3 | Invested capital branch present with a cost attached, or its absence justified in line one | |
| 4 | Every tornado range has a source; "assumed" ranges flagged in the reading | |
| 5 | Every tornado input brackets the base | |
| 6 | Model headline within 1.5x of the envelope, or the difference explained | |
| 7 | Five random calculation cells: no typed numbers; checks cell reads OK | |
| 8 | Each driver that matters has an owner, a target and evidence; a CEO could act on it | |
| 9 | Value at stake stated as a range, downside included | |
| 10 | Baseline reconciles to reported EBITDA less D&A with every adjustment listed | |
| 11 | Growth shown with the capital it consumes and the return it earns | |
| 12 | One-off capital releases counted once, not capitalised as annual |