Market sizing

Count lines by state, price them, bridge to the top down figure and cut to what a contractor can win. Every input sourced or marked as an assumption

Customize this Agent

XLSXPPTX15 steps

The problem

Lines needing replacement, on EPA's projections: 9.2 million in 2023, 4 million in 2025

The count drives the market, and it moved when utility inventories replaced EPA's survey sample: Florida's projected lines went from 1.16 million to 51,707. The 2025 total of 4 million already includes a million lines of unknown material that EPA predicts are lead, and counts systems that reported nothing as having none. Multiply an unchecked count by an unchecked price and the answer can move more than threefold

The request

Size the US lead service line replacement market for our Project Keystone diligence: 2025, each year to EPA's 2037 deadline, and what's left after, which a buyer at our 2031 exit will price. Total spend first, then what Keystone can win: contracted work in the states its job history covers, net of utilities' own crews. Count lines bottom up by state and system size from 2025 inventories, unknowns separate. Price a full replacement, both sides, from EPA's rule, the AWWA study and city programs, checked against Keystone's job history, attached. Bridge to EPA's 2023 estimate of $50 to $80 billion. Show drivers, including federal funding and the court challenge. Model and short deck, every input sourced or labeled an assumption.

Keystone job history.xlsx

How Axe works

ApprovedChanges asked

Your reviewApprove, or say what to change

Approved or changes

Model and deckModel, deck and check report

Final reviewApprove the revised files

Revised model and deckRevised files and check report

Step

Count lines by state

Prompt

Sent to the model at this step

Build the count of lines to replace, state by state and by the system size bands in EPA's needs survey. Start from the service line inventories utilities reported to EPA and the state projections in EPA's 2025 update to the 7th Drinking Water Infrastructure Needs Survey and Assessment. For each state and band, record total lines, lead, galvanized requiring replacement, unknown material and non lead, and the systems and population served that reported nothing. Keep three groups apart: lines reported as lead or galvanized requiring replacement, lines of unknown material, and systems with no report. EPA predicts the lead share of unknown lines from each state's known ratio and treats systems with no report as having no lead. Show EPA's figure for each, then your own base, low and high with the reasoning. Set your lead share of unknown lines from excavation results utilities have published for unknown lines they dug up, cited to the utility's report; where no published result covers a state, use EPA's state ratio and mark it as an assumption. Put the 2023 survey count beside each state, noting that it also counted lines with only a lead connector, and explain every state that moved by more than half, starting with Florida. Check that the national total matches the 3,956,383 lead and galvanized requiring replacement lines in EPA's 2025 projections before changing anything. Cite every figure to the file, table and row it came from.

Uses

Market definition

Produces

Line counts
Count notes

What you get back

XLSXMarket modelCounts by state and size, unit costs, contractor share, the bridge, forecast and assumptions

PPTXSizing deckThe answer first, then the count, price, contractor's share, bridge, drivers and scenarios

Check reportEach input and total marked checked, recalculated, assumption or open

Make it yours

Point it at your own market, change the boundary or the segments, or swap in the price sources your team trusts. Every step and every prompt can be edited

Customize this Agent

Opens a private copy of this Agent in your workspace, ready to adapt to your own sources

More agent examples

Strategy consultantsCompetitive benchmarkingPPTXXLSX14 steps

Capital markets analystsBroker opinion of valuePPTXXLSX16 steps

Financial planning analystsBudget variance analysisXLSXDOCX13 steps

Credit research analystsCapital structure analysisXLSX12 steps

Life sciences analystsClinical trial landscapeXLSXDOCX14 steps