Reference

Worked example: Halvorsen Industrial Supply

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

Contents

  1. The situation and the question
  2. Stage 1: root metric
  3. Stage 2: baseline reconstruction and reconciliation
  4. Stage 3: the driver tree
  5. Stage 4: envelope sizing
  6. Stage 5: model specification and hygiene
  7. Stage 6: tornado
  8. Stage 7: scenarios and value at stake
  9. Stage 8: the drivers that matter
  10. 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:

LineReportedAdjustmentNormalisedNote
Revenue400.00.0400.0No partial-year items
Cost of goods sold304.00.0304.0Net of supplier rebates of 6.0
Gross profit96.00.096.024.0% margin
Variable operating cost40.00.040.0Warehouse, delivery, sales commissions; 10.0% of revenue
Fixed operating cost31.4(1.4)30.0Strip 1.8 restructuring charge; add back 0.4 disposal gain that was netted here
EBITDA24.61.426.0
Depreciation and amortisation7.1(1.1)6.0Strip 1.1 amortisation of acquired intangibles (purchase accounting)
EBITA17.52.520.05.0% margin
Operating tax at 25%5.0Effective cash rate; reported charge distorted by deal interest
NOPAT15.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):

LineReportedTreatmentOperating ICNote
Trade receivables54.8Include54.850 days of revenue
Inventory60.0Include60.072 days of COGS
Trade payables(30.0)Include(30.0)36 days of COGS
Operating working capital84.8
Net PP&E37.4Include37.4Branches, fleet, racking
Capitalised ERP software2.8Include2.8Supports operations
Invested capital (operating)125.0
Goodwill and acquired intangibles88.0Exclude for management viewShown separately for the sponsor view
Cash9.4ExcludeNon-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 leafDriven by (causal)Proposed testOwner
Invoice margin 22.5%Discount depth from list, by rep and by branchPocket-price waterfall on the top 500 SKUs by branch; correlation of branch margin with rep tenure and with discount authorityCommercial director
Invoice margin 22.5%Product mix (private label 11% of sales at 34% margin vs branded 22%)Mix shift analysis by account segmentCategory manager
Supplier rebates 1.5%Rebate-tier attainment across 40 supplier programmesTier-shortfall analysis; 6 programmes missed the next tier by under 8% of volume in FY2025Purchasing director
Orders per account 25Fill rate (currently 91.5% line fill) and range coverageRegression of order frequency on fill rate across branches; interviews via primary-research-synthesisSupply-chain director
Active accounts 6,250Rep coverage and churn (11% of accounts went dormant in FY2025)Cohort of dormant accounts by last-order reasonCommercial director
Inventory days 72Range breadth (41,000 SKUs, 18% with no sale in 12 months) and branch-level safety stockSKU velocity analysis; branch dispersion 55 to 91 daysSupply-chain director
Receivable days 50Terms granted (net 45 standard, net 60 for 30% of accounts) and collection cadenceAgeing by terms bucket; branch dispersion 44 to 58 daysCFO

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.

LeverEnvelope calculationEconomic profit effect per yearEnvelope valueModel result
1 point of gross margin400.0 x 1% = 4.0 EBITA; x 0.75 = 3.0 NOPAT+3.03.0 / 0.06 = 5050.6 (50.2 steady state plus 0.4 released, as COGS-based inventory and payables shrink)
10 days of inventory304.0 / 365 x 10 = 8.3 capital released once; x 9%+0.750.75 / 0.06 = 12.512.5 (4.2 steady state plus 8.3 released)
5 days of receivables400.0 / 365 x 5 = 5.5 capital released once; x 9%+0.498.28.2
$2.5M of fixed cost2.5 x 0.75 = 1.9 NOPAT+1.93131.3
3% volume at constant marginContribution 14% x 12.0 = 1.7 EBITA; 1.3 NOPAT; working capital consumed 21.2% x 12.0 = 2.5; charge 0.23+1.01717.2 (19.7 steady state less 2.5 invested)
1 point of WACC125.0 x 1% = 1.25 of charge, and (WACC - g) itself moves-1.25Roughly 15% of value, 25 to 3026.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):

IDInputBaseUnitSourceDateOwnerLowHighRange source
A01Revenue400.0$MFY2025 auditedMar 2026CFO384.0424.0FY2023 to FY2025 range, market forecast
A02Gross margin24.0%% of revenueFY2025 normalisedMar 2026Commercial director23.0%25.5%Branch dispersion: worst three 22.8%, best three 25.6%
A03Variable operating cost ratio10.0%% of revenueFY2025 cost allocationMar 2026CFOheldheldStructural
A04Fixed operating cost30.0$MFY2025 normalisedMar 2026CFO32.027.5Inflation drift vs central cost review
A05Depreciation6.0$MFY2025Mar 2026CFOheldheld
A06Tax rate25%% of EBITAEffective cash rateMar 2026CFO27%24%State apportionment range
A07Receivable days50days of revenueFY2025 year-endMar 2026CFO5544Branch dispersion 44 to 58
A08Inventory days72days of COGSFY2025 year-endMar 2026Supply-chain director8060Branch dispersion 55 to 91; peer median 61
A09Payable days36days of COGSFY2025 year-endMar 2026Purchasing director3340Supplier terms range
A10Net PP&E plus software40.2$MFY2025 year-endMar 2026CFO43.038.0Fleet replacement vs deferral
A11WACC9.0%%Sponsor target structureMar 2026Deal partner10.0%8.5%Rate scenarios
A12Long-run growth3.0%%Assumed: regional industrial outputMar 2026Deal partnerheldheldFlagged 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.

RankInputBaseLowHighEP at lowEP at highSwingValue at lowValue at highRange source
1Gross margin24.0%23.0%25.5%0.728.317.59137.3262.8Branch dispersion
2Revenue400.0384.0424.02.385.813.44161.2227.0History and market forecast
3Fixed operating cost30.032.027.52.255.633.38162.5218.8Drift vs review
4WACC9.0%10.0%8.5%2.504.381.87160.7204.6Rate scenarios
5Inventory days7280603.154.651.50184.2192.5Branch dispersion, peers
6Receivable days5055443.264.341.08184.8190.8Branch dispersion
7Tax rate25%27%24%3.353.950.60180.8190.8State apportionment
8Payable days3633403.534.050.52186.3189.2Supplier terms
9Net PP&E and software40.243.038.03.503.950.45186.1188.6Fleet 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

CaseAssumptions changed from baseRevenueEBITANOPATInvested capitalROICEconomic profitSteady-state valueOne-off capital releaseTotal valueDelta vs base
BaseNone400.020.015.0125.012.0%3.75187.50.0187.50.0
Commercial excellenceGross margin 25.0%; revenue 412.0412.025.819.35127.115.2%7.91258.9(2.1)256.8+69.3
Working capitalInventory 62 days; receivables 46; payables 38400.020.015.0110.613.6%5.05194.714.4209.1+21.6
Combined planBoth of the above412.025.819.35112.417.2%9.23266.312.5278.8+91.3
DownsideGross margin 23.2%; revenue 388.0388.015.211.4122.79.3%0.37128.82.3131.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.

DriverTornado rankControllableEvidencedVerdict
Gross margin1Yes: discount authority, mix, rebate tiersYes: branch dispersionIn
Revenue2Partly: retention and frequency, not marketRange is market-basedOut as a headline driver; retention becomes a sub-lever of margin programme
Fixed operating cost3Yes: central cost reviewPartlyIn, as a hold-flat discipline rather than a cut
WACC4NoOut
Inventory days5Yes: range rationalisationYes: branch dispersion, peersIn, for cash rather than annual economic profit
Receivable days6YesYesFolded into the working-capital programme

The drivers that matter, as written for the board:

  1. 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.
  2. 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.
  3. 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.