Reference
Worked example: Halvorsen Industrial Supply
Part of the Value Driver Modeling skill · loaded on demand from SKILL.md
Contents
- The situation and the question
- Stage 1: root metric
- Stage 2: baseline reconstruction and reconciliation
- Stage 3: the driver tree
- Stage 4: envelope sizing
- Stage 5: model specification and hygiene
- Stage 6: tornado
- Stage 7: scenarios and value at stake
- Stage 8: the drivers that matter
- What was handed to whom
All figures in $M unless stated. Fiscal year 2025 (FY2025) is the baseline year. Numbers are rounded to one decimal in tables and reconcile at that precision; the underlying model carries full precision.
1. The situation and the question
Halvorsen Industrial Supply distributes maintenance, repair and operations (MRO) products to around 6,250 active industrial and construction accounts through 14 branches in the upper Midwest. Revenue is $400.0M. A private-equity sponsor acquired the business 18 months ago at 8.5x EBITDA. EBITA has been flat at $20M for two years; the sponsor's investment case assumed $28M by year three. The board's question, as framed by problem-framing: "Where is the value in Halvorsen, and which two or three things should management concentrate on in the next 24 months to reach it?"
The problem statement specified: decision-maker the board (sponsor's deal partner and the CEO); success criterion a value plan the management team owns; accuracy sufficient to rank drivers and size the prize to within 20%; constraint no acquisitions in the horizon.
2. Stage 1: root metric
Root metric: economic profit, FY2025 actual, steady-state annual, capitalised to enterprise value with the key value driver formula at 3% long-run growth and a 9.0% WACC.
Why economic profit rather than EBITDA, which is what the sponsor's model tracks: Halvorsen carries $84.8M of working capital on $400M of revenue, and its branches have historically grown EBITDA by adding range and extending terms, both of which consume capital. An EBITDA tree would reward the behaviour that got the business to a 12% ROIC on a 9% WACC. A tree rooted in economic profit shows the capital cost of every branch of growth.
Partner checkpoint applied: "If management maximised economic profit and nothing else, would the sponsor be happy?" Yes, provided the horizon is long enough that they do not starve growth; the 24-month window and the 3% growth assumption cover that.
3. Stage 2: baseline reconstruction and reconciliation
Reported to normalised P&L, FY2025:
| Line | Reported | Adjustment | Normalised | Note |
|---|---|---|---|---|
| Revenue | 400.0 | 0.0 | 400.0 | No partial-year items |
| Cost of goods sold | 304.0 | 0.0 | 304.0 | Net of supplier rebates of 6.0 |
| Gross profit | 96.0 | 0.0 | 96.0 | 24.0% margin |
| Variable operating cost | 40.0 | 0.0 | 40.0 | Warehouse, delivery, sales commissions; 10.0% of revenue |
| Fixed operating cost | 31.4 | (1.4) | 30.0 | Strip 1.8 restructuring charge; add back 0.4 disposal gain that was netted here |
| EBITDA | 24.6 | 1.4 | 26.0 | |
| Depreciation and amortisation | 7.1 | (1.1) | 6.0 | Strip 1.1 amortisation of acquired intangibles (purchase accounting) |
| EBITA | 17.5 | 2.5 | 20.0 | 5.0% margin |
| Operating tax at 25% | 5.0 | Effective cash rate; reported charge distorted by deal interest | ||
| NOPAT | 15.0 |
Reported to operating invested capital, FY2025 year-end (average not available at branch level; year-end used and stated; the December stock count was normal):
| Line | Reported | Treatment | Operating IC | Note |
|---|---|---|---|---|
| Trade receivables | 54.8 | Include | 54.8 | 50 days of revenue |
| Inventory | 60.0 | Include | 60.0 | 72 days of COGS |
| Trade payables | (30.0) | Include | (30.0) | 36 days of COGS |
| Operating working capital | 84.8 | |||
| Net PP&E | 37.4 | Include | 37.4 | Branches, fleet, racking |
| Capitalised ERP software | 2.8 | Include | 2.8 | Supports operations |
| Invested capital (operating) | 125.0 | |||
| Goodwill and acquired intangibles | 88.0 | Exclude for management view | Shown separately for the sponsor view | |
| Cash | 9.4 | Exclude | Non-operating |
Returns and spread:
- ROIC (operating) = 15.0 / 125.0 = 12.0%
- DuPont: NOPAT margin 3.75% x capital turnover 3.2x = 12.0%. A thin-margin, high-turnover business: a point of margin is a third of NOPAT.
- WACC = 9.0% (sponsor's target structure, 45% debt at 6.5% pre-tax, equity at 12.5%; stated as an assumption on the sheet)
- Spread = 3.0 points
- Capital charge = 125.0 x 9.0% = 11.25
- Economic profit = 15.0 - 11.25 = 3.75. Cross-check: 125.0 x (12.0% - 9.0%) = 3.75.
- Value (key value driver formula, g = 3%) = 15.0 x (1 - 0.03/0.12) / (0.09 - 0.03) = 15.0 x 0.75 / 0.06 = 187.5. That is 9.4x EBITA and 7.2x normalised EBITDA, against the 8.5x paid.
The sponsor view: ROIC including the $88.0M of goodwill and acquired intangibles is 15.0 / 213.0 = 7.0%, below WACC. At current performance the acquisition is not earning its cost of capital. This is not a management driver (goodwill cannot be reduced by operating action) but it is the sentence that gets the board's attention, and it is why the plan has to move economic profit, not EBITDA.
4. Stage 3: the driver tree
Identity layer (every node reproduces its parent arithmetically; values FY2025 normalised):
Economic profit 3.75
├── NOPAT 15.0
│ ├── EBITA 20.0
│ │ ├── Gross profit 96.0
│ │ │ ├── Revenue 400.0
│ │ │ │ ├── Active accounts 6,250
│ │ │ │ └── Revenue per account $64,000
│ │ │ │ ├── Orders per account per year 25
│ │ │ │ └── Average order value $2,560
│ │ │ └── Gross margin 24.0%
│ │ │ ├── Invoice margin 22.5%
│ │ │ └── Supplier rebates 1.5% of revenue (6.0)
│ │ ├── Variable operating cost (40.0) = 10.0% of revenue
│ │ │ ├── Warehouse and delivery (29.0) = 156,250 orders x $185.60 per order
│ │ │ └── Sales commissions (11.0) = 2.75% of revenue
│ │ ├── Fixed operating cost (30.0)
│ │ │ ├── Branch fixed cost (19.6) = 14 branches x 1.4
│ │ │ └── Central cost (10.4)
│ │ └── Depreciation (6.0)
│ └── Operating tax (5.0) = 25% x 20.0
└── Capital charge (11.25) = 125.0 x 9.0%
├── Operating working capital 84.8
│ ├── Receivables 54.8 = 400.0 x 50 / 365
│ ├── Inventory 60.0 = 304.0 x 72 / 365
│ └── Payables (30.0) = 304.0 x 36 / 365
├── Net PP&E 37.4
└── Capitalised software 2.8
Checks: 6,250 x $64,000 = $400.0M. 25 x $2,560 = $64,000. 6,250 x 25 = 156,250 orders; 156,250 x $185.6 = $29.0M. 22.5% + 1.5% = 24.0%. 96.0 - 40.0 - 30.0 = 26.0; 26.0 - 6.0 = 20.0; 20.0 x 0.75 = 15.0. 54.8 + 60.0 - 30.0 = 84.8; 84.8 + 37.4 + 2.8 = 125.0. 15.0 - 11.25 = 3.75.
Causal layer (hypotheses, hung below the identity leaves, each with a test):
| Identity leaf | Driven by (causal) | Proposed test | Owner |
|---|---|---|---|
| Invoice margin 22.5% | Discount depth from list, by rep and by branch | Pocket-price waterfall on the top 500 SKUs by branch; correlation of branch margin with rep tenure and with discount authority | Commercial director |
| Invoice margin 22.5% | Product mix (private label 11% of sales at 34% margin vs branded 22%) | Mix shift analysis by account segment | Category manager |
| Supplier rebates 1.5% | Rebate-tier attainment across 40 supplier programmes | Tier-shortfall analysis; 6 programmes missed the next tier by under 8% of volume in FY2025 | Purchasing director |
| Orders per account 25 | Fill rate (currently 91.5% line fill) and range coverage | Regression of order frequency on fill rate across branches; interviews via primary-research-synthesis | Supply-chain director |
| Active accounts 6,250 | Rep coverage and churn (11% of accounts went dormant in FY2025) | Cohort of dormant accounts by last-order reason | Commercial director |
| Inventory days 72 | Range breadth (41,000 SKUs, 18% with no sale in 12 months) and branch-level safety stock | SKU velocity analysis; branch dispersion 55 to 91 days | Supply-chain director |
| Receivable days 50 | Terms granted (net 45 standard, net 60 for 30% of accounts) and collection cadence | Ageing by terms bucket; branch dispersion 44 to 58 days | CFO |
Partner checkpoint applied: "Change inventory days by 10%. Trace the root." 72 to 79.2 days: inventory 304.0 x 79.2 / 365 = 66.0, up 6.0; capital charge up 0.54; economic profit down 0.54 to 3.21. Traced.
5. Stage 4: envelope sizing
Before building the model, each lever on two lines. The envelope rule: since value = invested capital + economic profit / (WACC - g), the value of a permanent change is the change in annual economic profit divided by (WACC - g), here 0.06, and any capital released or consumed in the transition is already inside that number.
| Lever | Envelope calculation | Economic profit effect per year | Envelope value | Model result |
|---|---|---|---|---|
| 1 point of gross margin | 400.0 x 1% = 4.0 EBITA; x 0.75 = 3.0 NOPAT | +3.0 | 3.0 / 0.06 = 50 | 50.6 (50.2 steady state plus 0.4 released, as COGS-based inventory and payables shrink) |
| 10 days of inventory | 304.0 / 365 x 10 = 8.3 capital released once; x 9% | +0.75 | 0.75 / 0.06 = 12.5 | 12.5 (4.2 steady state plus 8.3 released) |
| 5 days of receivables | 400.0 / 365 x 5 = 5.5 capital released once; x 9% | +0.49 | 8.2 | 8.2 |
| $2.5M of fixed cost | 2.5 x 0.75 = 1.9 NOPAT | +1.9 | 31 | 31.3 |
| 3% volume at constant margin | Contribution 14% x 12.0 = 1.7 EBITA; 1.3 NOPAT; working capital consumed 21.2% x 12.0 = 2.5; charge 0.23 | +1.0 | 17 | 17.2 (19.7 steady state less 2.5 invested) |
| 1 point of WACC | 125.0 x 1% = 1.25 of charge, and (WACC - g) itself moves | -1.25 | Roughly 15% of value, 25 to 30 | 26.8 |
Reading: gross margin is roughly three times any other lever, and its range (branch dispersion of 2.8 points) is wider than any other lever's. The ranking is already clear. The model is still needed because the sponsor will scrutinise the number, the levers interact (margin on grown revenue; days on grown COGS), and kpi-architecture needs the tree as a file. Build it, at annual granularity, one sheet each for assumptions, calculations, outputs and checks.
The single number that changes the answer if wrong by a factor of two: the achievable gross margin uplift. The first hour of diligence goes to the branch margin data.
6. Stage 5: model specification and hygiene
Assumptions sheet, abridged (full sheet has 23 rows):
| ID | Input | Base | Unit | Source | Date | Owner | Low | High | Range source |
|---|---|---|---|---|---|---|---|---|---|
| A01 | Revenue | 400.0 | $M | FY2025 audited | Mar 2026 | CFO | 384.0 | 424.0 | FY2023 to FY2025 range, market forecast |
| A02 | Gross margin | 24.0% | % of revenue | FY2025 normalised | Mar 2026 | Commercial director | 23.0% | 25.5% | Branch dispersion: worst three 22.8%, best three 25.6% |
| A03 | Variable operating cost ratio | 10.0% | % of revenue | FY2025 cost allocation | Mar 2026 | CFO | held | held | Structural |
| A04 | Fixed operating cost | 30.0 | $M | FY2025 normalised | Mar 2026 | CFO | 32.0 | 27.5 | Inflation drift vs central cost review |
| A05 | Depreciation | 6.0 | $M | FY2025 | Mar 2026 | CFO | held | held | |
| A06 | Tax rate | 25% | % of EBITA | Effective cash rate | Mar 2026 | CFO | 27% | 24% | State apportionment range |
| A07 | Receivable days | 50 | days of revenue | FY2025 year-end | Mar 2026 | CFO | 55 | 44 | Branch dispersion 44 to 58 |
| A08 | Inventory days | 72 | days of COGS | FY2025 year-end | Mar 2026 | Supply-chain director | 80 | 60 | Branch dispersion 55 to 91; peer median 61 |
| A09 | Payable days | 36 | days of COGS | FY2025 year-end | Mar 2026 | Purchasing director | 33 | 40 | Supplier terms range |
| A10 | Net PP&E plus software | 40.2 | $M | FY2025 year-end | Mar 2026 | CFO | 43.0 | 38.0 | Fleet replacement vs deferral |
| A11 | WACC | 9.0% | % | Sponsor target structure | Mar 2026 | Deal partner | 10.0% | 8.5% | Rate scenarios |
| A12 | Long-run growth | 3.0% | % | Assumed: regional industrial output | Mar 2026 | Deal partner | held | held | Flagged as assumed |
Calculation sheet: 19 rows, one per identity node, formulas only, in tree order. Outputs sheet: baseline column, five scenario columns, tornado table. Checks row: five checks (NOPAT via EBITA route equals NOPAT via EBITDA minus D&A route; tree leaves sum to root; working capital components sum; every tornado input brackets base; scenario switch in valid range). Checks cell reads OK.
Hygiene checklist result: passed after two fixes found on review. Fix one: the payable-days row was initially computed on revenue rather than COGS (a 4.2 error in payables). Fix two: the variable cost ratio had been typed as 0.1 in a calculation formula rather than linked to A03.
7. Stage 6: tornado
Economic profit, one input at a time, all others at base. Ranges oriented so that "high" is the favourable end. Value column uses the key value driver formula with ROIC recomputed at each point.
| Rank | Input | Base | Low | High | EP at low | EP at high | Swing | Value at low | Value at high | Range source |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Gross margin | 24.0% | 23.0% | 25.5% | 0.72 | 8.31 | 7.59 | 137.3 | 262.8 | Branch dispersion |
| 2 | Revenue | 400.0 | 384.0 | 424.0 | 2.38 | 5.81 | 3.44 | 161.2 | 227.0 | History and market forecast |
| 3 | Fixed operating cost | 30.0 | 32.0 | 27.5 | 2.25 | 5.63 | 3.38 | 162.5 | 218.8 | Drift vs review |
| 4 | WACC | 9.0% | 10.0% | 8.5% | 2.50 | 4.38 | 1.87 | 160.7 | 204.6 | Rate scenarios |
| 5 | Inventory days | 72 | 80 | 60 | 3.15 | 4.65 | 1.50 | 184.2 | 192.5 | Branch dispersion, peers |
| 6 | Receivable days | 50 | 55 | 44 | 3.26 | 4.34 | 1.08 | 184.8 | 190.8 | Branch dispersion |
| 7 | Tax rate | 25% | 27% | 24% | 3.35 | 3.95 | 0.60 | 180.8 | 190.8 | State apportionment |
| 8 | Payable days | 36 | 33 | 40 | 3.53 | 4.05 | 0.52 | 186.3 | 189.2 | Supplier terms |
| 9 | Net PP&E and software | 40.2 | 43.0 | 38.0 | 3.50 | 3.95 | 0.45 | 186.1 | 188.6 | Fleet plan |
Total swing across all nine inputs: 20.43. The top three account for 14.41, or 71%. Gross margin alone is 37% of the total and exceeds the next two combined (6.82).
Bracket check: every row has base 3.75 between EP at low and EP at high. Passed.
Reading, as written for the board: "Gross margin is the driver. Across the range Halvorsen's own branches already achieve, it moves economic profit by $7.6M and value by $125M, more than the next two drivers together. Volume and fixed cost each move economic profit by about $3.4M, but the volume range comes from the market rather than from anything management controls in 24 months. Working-capital days matter less to annual economic profit than their prominence in the sponsor's model suggests, though they release cash once, which the annual view does not show. WACC is outside management's control. Tax, payables and PP&E do not change the answer."
Interaction noted: margin and volume together produce 7.91 in the commercial scenario, against 6.79 for margin alone and 4.78 for volume alone (a combined uplift of 4.16 against 3.04 + 1.03 = 4.07 if simply added); the extra 0.09 is the extra point of margin earned on the additional $12.0M of revenue, after tax. Correlated inputs noted: a downturn would hit revenue, margin and receivable days together, which the downside scenario models.
8. Stage 7: scenarios and value at stake
| Case | Assumptions changed from base | Revenue | EBITA | NOPAT | Invested capital | ROIC | Economic profit | Steady-state value | One-off capital release | Total value | Delta vs base |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Base | None | 400.0 | 20.0 | 15.0 | 125.0 | 12.0% | 3.75 | 187.5 | 0.0 | 187.5 | 0.0 |
| Commercial excellence | Gross margin 25.0%; revenue 412.0 | 412.0 | 25.8 | 19.35 | 127.1 | 15.2% | 7.91 | 258.9 | (2.1) | 256.8 | +69.3 |
| Working capital | Inventory 62 days; receivables 46; payables 38 | 400.0 | 20.0 | 15.0 | 110.6 | 13.6% | 5.05 | 194.7 | 14.4 | 209.1 | +21.6 |
| Combined plan | Both of the above | 412.0 | 25.8 | 19.35 | 112.4 | 17.2% | 9.23 | 266.3 | 12.5 | 278.8 | +91.3 |
| Downside | Gross margin 23.2%; revenue 388.0 | 388.0 | 15.2 | 11.4 | 122.7 | 9.3% | 0.37 | 128.8 | 2.3 | 131.1 | (56.4) |
Arithmetic checks on the combined plan: gross profit 412.0 x 25.0% = 103.0; variable cost 41.2; fixed 30.0; EBITDA 31.8; EBITA 25.8; NOPAT 19.35. COGS 309.0; receivables 412.0 x 46 / 365 = 51.9; inventory 309.0 x 62 / 365 = 52.5; payables 309.0 x 38 / 365 = 32.2; working capital 72.2; plus 40.2 = 112.4. ROIC 19.35 / 112.4 = 17.2%. Charge 10.12; economic profit 9.23. Value 19.35 x (1 - 0.03 / 0.172) / 0.06 = 19.35 x 0.826 / 0.06 = 266.3. Release 125.0 - 112.4 = 12.5 (sign: capital falls, cash comes out; in the commercial case capital rises 2.1 with revenue, so the release is negative).
Value at stake: $91M (from $187.5M to $278.8M), of which $69M is commercial and $22M is working capital. Downside: a loss of $56M if margin slips 0.8 points and revenue falls 3%. Range for the board: minus $56M to plus $91M, with the base at zero.
The three assumptions that carry the combined case: (1) gross margin reaches 25.0%, which is below the best three branches' current 25.6%; (2) revenue grows 3% in a flat market, which requires holding accounts and lifting order frequency; (3) inventory falls to 62 days, which is the peer median and above the best branch's 55.
Partner checkpoint applied: "If the downside happens, is the recommendation still right?" Yes: the margin programme is the right first move in the downside too, because discount discipline is more valuable when volume is scarce. The working-capital programme should be sequenced after the margin programme, not alongside, so that it does not compete for branch management attention. That sequencing note went to execution-planning.
9. Stage 8: the drivers that matter
Filter: large (top of tornado), controllable within 24 months, evidenced range.
| Driver | Tornado rank | Controllable | Evidenced | Verdict |
|---|---|---|---|---|
| Gross margin | 1 | Yes: discount authority, mix, rebate tiers | Yes: branch dispersion | In |
| Revenue | 2 | Partly: retention and frequency, not market | Range is market-based | Out as a headline driver; retention becomes a sub-lever of margin programme |
| Fixed operating cost | 3 | Yes: central cost review | Partly | In, as a hold-flat discipline rather than a cut |
| WACC | 4 | No | Out | |
| Inventory days | 5 | Yes: range rationalisation | Yes: branch dispersion, peers | In, for cash rather than annual economic profit |
| Receivable days | 6 | Yes | Yes | Folded into the working-capital programme |
The drivers that matter, as written for the board:
- Gross margin from 24.0% to 25.0% through discount discipline: cap rep discount authority at 8% from list without branch-manager approval, and lift the six supplier programmes currently short of their next rebate tier. Worth $3.0M of NOPAT a year and about $50M of value. Owner: commercial director. Evidence: the best three branches already run 25.6% with the same product range and the same customers.
- Inventory from 72 to 62 days through range rationalisation at the four largest branches, which hold 61% of stock: delist the 7,400 SKUs with no sale in 12 months and cut safety stock on C-class items. Worth $8.3M of cash released once and $0.75M of economic profit a year. Owner: supply-chain director. Evidence: branch range is 55 to 91 days; peer median 61.
- Fixed operating cost held at $30.0M while revenue grows 3%, rather than drifting with inflation to $30.9M. Worth $0.9M of EBITA and $0.7M of NOPAT a year. Owner: CFO. Evidence: central cost grew 4.1% a year over the last three years with no change in branch count.
Headline: "Halvorsen is worth about $190M today, earning a 3-point spread over its cost of capital on operating capital and below its cost of capital once goodwill is counted. A margin programme built on what its own best branches already do, followed by a working-capital programme, takes it to about $280M. The plan rests on one assumption above all others: that discount discipline can move the average branch to where the best three already are."
10. What was handed to whom
executive-communication: the headline, the three driver sentences, the tornado chart and the scenario table.hypothesis-stress-testing: the causal link "discount authority drives invoice margin", with the branch-level correlation as the first test and a two-branch pilot as the disproving analysis.execution-planning: three driver-target-owner triples and the sequencing note (margin first, working capital second).kpi-architecture: the identity tree with baselines and targets, to become the value ledger.- Pre-mortem: the three assumptions that carry the combined plan.
- Pricing design: the pocket-price waterfall and discount-authority design, since that is a pricing question.
xlsx: the assumptions sheet, calculation structure, checks row and output layout for the working file.