A world‑class market‑sizing function does not start from a blank workbook each time a new question lands. It deploys pre‑built skeletons, standardized macros, and style guides that slash cycle time, enforce best practice, and reduce error risk. This chapter curates the essential assets—Excel models, Python notebooks, geospatial dashboards, and QA scripts—that practitioners can download, customize, and plug directly into their workflow. Think of it as the “starter kit” that turns the Playbook’s concepts into day‑one productivity.
12.1 Excel Model Skeletons (Top‑Down, Bottom‑Up, Hybrid)
The firm maintains three flagship Excel templates—one for each primary methodology. All share a common architecture, color convention, and macro toolkit, ensuring that analysts can shift between models without cognitive re‑boot and that reviewers always know where to look.
Common Architecture Across Templates
- 0_Scope – Project charter: boundary, currency basis, precision target, version ID.
- 1_Inputs – Raw external figures; blue‑font input cells only.
- 2_Lookups – Currency rates, inflation indices, unit conversions; locked formulas.
- 3_Calc – Core calculations; black‑font formulas.
- 4_Growth_Scenarios – Driver tables for pessimistic, reference, optimistic cases; green‑font toggles.
- 5_Sensitivity_MC – Tornado and Monte‑Carlo engine (native VBA, no add‑in dependency).
- 6_Dashboard – Auto‑refresh charts conforming to visualization standards (Chapter 11).
- 7_QA_Log – Auto‑generated sheet logging hard‑coded constants and circular references.
- 8_Source_Log – Publication name, page, URL, reliability score.
- 9_Change_Log – Time‑stamped edits with user ID.
All templates ship with a ribbon add‑in that runs linting macros, regenerates the QA Log, and pushes updated figures to linked Think‑Cell charts.
Top‑Down Skeleton Highlights
- Ratio Cascade Builder. A guided wizard walks users through defining the base metric and up to four sequential filters. The wizard auto‑creates the cascade table and formulas, minimizing manual range errors.
- Cross‑Check Module. Imports public company revenue, OECD macro series, and capacity statistics via PowerQuery to triangulate the headline figure.
- Scenario Fan Auto‑Chart. Converts Monte‑Carlo outputs into a fan chart on the Dashboard with optional projection cones up to ten years.
Bottom‑Up Skeleton Highlights
- Unit Census Importer. PowerQuery script for Dun & Bradstreet/Orbis or custom CSV uploads. De‑duplicates by tax ID or URL and assigns confidence scores.
- Segment Pivot Engine. Drag‑and‑drop slicers let analysts rebuild segmentation hierarchies without rewriting formulas.
- Adoption & Usage Matrix. Separate input blocks for baseline penetration, curve parameters, and usage intensity—each linked to a colored toggle panel for scenario testing.
- Churn & Upsell Loop. Pre‑written cohort retention tables feed LTV/CAC calculations; flexible to subscription or transaction models.
Hybrid Skeleton Highlights
- Lens Tabs. Dedicated “TopDown” and “BottomUp” calc sheets run in parallel, feeding a “Reconcile” tab that applies the Three‑Lens weighting logic (Chapter 7).
- Supply‑Side Overlay. Optional module for capacity and BOM inputs; VLOOKUP keys align plant IDs with demand segments for leakage reconciliation.
- Dynamic Consistency Checks. Red flags appear on the Dashboard if top‑down and bottom‑up totals diverge by >15 percent or if supply exceeds demand by >10 percent.
Color and Style Guide (Enforced by Macro)
- Blue = editable input
- Black = standard formula
- Green = scenario driver or dropdown
- Orange fill = QC flag (requires resolution before gate)
Macros prohibit saving if any orange‑flag cell persists.
Automation and Extensibility
- API Hooks. Templates include sample Python scripts (callable via xlwings) that pull fresh FX, CPI, or commodity prices on opening.
- Add‑in the Library. Buttons for: rebuild tornado, rerun Monte‑Carlo, export Dashboard as PNG, generate Think‑Cell chart data file.
- Git Integration. A lightweight VBA macro exports worksheets as CSV to a local folder, making diff‑tracking in Git straightforward and avoiding binary file headaches.
Usage Workflow
- Clone template from the internal portal or playbook site.
- Run Scope Wizard—auto‑populates 0_Scope and locks version ID.
- Load Inputs via Importer scripts; validate blue cells turn black after formulas propagate.
- Complete QA‑Precheck—run linter macro; resolve orange flags.
- Refresh Dashboard and link slides via Think‑Cell.
- Commit to Git/SharePoint; push to peer reviewer ahead of gate.
Support Resources
- Video walk‑throughs (5–10 minutes each) embedded in the portal.
- Cheat‑sheet PDF summarizing ribbon commands and color codes.
- Slack channel #market‑sizing‑toolkit staffed by data engineers for live troubleshooting.
- Quarterly template releases—version notes highlight new macros, bug fixes, and updated standards.
Quick‑Action Checklist
- Template version latest? (Check 0_Scope)
- Scope wizard completed and locked?
- All inputs in blue cells only?
- QA Log shows zero critical flags?
- Dashboard charts populate without errors?
- Workbook saved under version control?
With these Excel skeletons, analysts spend their week interrogating strategic questions rather than debugging column references—freeing time for higher‑order thinking and ensuring every new market‑sizing effort starts on a foundation of proven structure and code.
12.2 Interview Guides and Survey Question Banks
Primary research fills the gaps that syndicated reports and public filings leave behind. Done well, it converts anecdotes into quantifiable insight; done poorly, it generates noise, bias, and rework. This toolkit provides ready‑made interview guides and modular survey question banks that map directly to the high‑impact, high‑uncertainty nodes highlighted in Chapter 2.4. Each module is purpose‑built—adoption drivers, price sensitivity, capacity bottlenecks—so teams can assemble a research instrument in hours rather than days, while maintaining methodological rigor and comparability across projects.
1 Expert Interview Guide Templates
Structure and Timing
- 45 minutes total
- 5 min rapport, NDA confirmation
- 10 min market mechanics walk‑through
- 15 min quant estimations (ranges, percentages)
- 10 min forward‑looking triggers and risks
- 5 min closing, permission to cite anonymized quotes
Core Question Modules
Module | Objective | Sample Questions | Response Format |
Market Mechanics | Clarify value chain, purchase process, and key players | “Walk me through the end‑to‑end workflow for sourcing electrolyzers.” | Narrative |
Size & Growth | Obtain directional estimates for top‑down anchors | “What share of new industrial projects include PEM systems today?” | % ranges: <10 %, 10–30 %, >30 % |
Adoption Drivers | Surface pain points, trigger events | “Which technology milestone would double adoption in your org?” | Ranked list; ask for top 3 |
Pricing Landscape | Capture net price vs. list, discount ladders | “On a typical 10 MW order, what is the negotiated discount off list?” | % range; min–mode–max |
Capacity & Supply | Validate plant ramp‑up, bottlenecks | “How long is the current lead time for H100‑class GPUs?” | Weeks/months slider |
Risk & Scenario | Stress‑test upside/downside triggers | “What single regulation could delay adoption by two years?” | Open‑ended; flag for scenario grid |
Interview Tips
- Triangulate numbers. Always ask for min, most likely, and max; record confidence level (1–5).
- Avoid leading questions. Use neutral phrasing—replace “How severe is iridium scarcity?” with “How do raw material constraints affect supply?”
- Confirm citability. Close by confirming whether the expert may be cited anonymously and whether key numbers can enter the model.
2 Survey Question Bank Modules
Each module is written at 7th‑grade readability, field‑tested for completion times, and aligned with common online platforms (Qualtrics, SurveyMonkey). Mix and match based on project needs; target total survey length ≤10 minutes to minimize drop‑off.
A. Screening & Firmographics
- Q1: “Which of the following best describes your organization?” (radio)
- Manufacturer / Service Provider / Government / Academic / Other
- Q2: “How many full‑time employees does your organization have?” (slider, 1–10 000)
B. Adoption Status
- Q3: “Which statement best reflects your organization’s current use of [technology]?”
- Not evaluating / Evaluating / Pilot testing / Partly deployed / Fully deployed
- Q4: “In what year did you first run a pilot or deploy?” (dropdown years)
C. Future Adoption Intent
- Q5: Likert 0–10: “Rate the likelihood your organization will deploy at scale within three years.”
- Q6: Multiple choice (rank top 3): “What are the primary barriers to deployment?”
- CapEx cost / Opex cost / Regulatory uncertainty / Talent shortage / Supply chain / Performance doubts / Other
D. Price Sensitivity
- Q7: Van Westendorp set
- “At what per‑unit price would you consider the product good value?”
- “At what price would it start to feel expensive?”
- “At what price would it be too expensive to consider?”
- “At what price would you question quality?”
E. Usage Intensity
- Q8: “Estimate the average monthly consumption per unit once fully deployed.” (numeric entry + unit selector)
- Q9: “What utilization factor (%) do you target for installed capacity?” (slider 0–100 %)
F. Trigger Events & Scenarios
- Q10: Binary: “If [trigger event, e.g., $50/MWh renewable PPA] occurs, will you accelerate adoption?” Yes/No
- Q11: “Rank the following potential regulations by their impact on your adoption timeline.” (drag‑and‑drop)
G. Budget Allocation
- Q12: “What percentage of your annual capital budget is earmarked for [category] over the next two years?” (slider 0–100 %)
Measurement Scales & Logic
- Use forced‑choice over free text when possible; improves comparability.
- Randomize answer orders to reduce primacy bias, except when logical order is required (e.g., price scales).
- Employ display logic—show price questions only if the respondent is buyer or budget owner.
3 Survey Design Best Practices
- Sample sizing rule of thumb: For a ±5 ppt margin at 95 % confidence among a population of 20 000, aim for 377 completes. Oversample if key sub‑segments require subgroup analysis.
- Incentives: Offer a $25 gift card or executive summary; avoid conflict‑of‑interest inducements.
- Field timing: Avoid end‑of‑quarter or major industry event weeks when response rates dip.
- Data cleaning: Flag speeders (completion ≤1/3 median time), straight‑liners, and inconsistent numerical answers; remove before analysis.
- Weighting: Post‑stratify responses by known population parameters (industry, firm size) to correct sampling bias.
4 Ethics and Compliance Checklist
- Obtain IRB exemption or equivalent internal review of surveying customers or patients.
- Include GDPR‑compliant consent language for EU participants; store data in approved servers.
- Do not record interviews without explicit permission.
- For NDA‑bound experts, share only anonymized, aggregated outputs.
5 Integration with the Market‑Sizing Model
- Coding schema: Map each survey question number to driver‑tree node ID. Example: Q7 feeds “Price Sensitivity – ASP Range” leaf.
- Confidence weighting: Assign higher weight to survey inputs when sample size >100 and confidence interval <10 %.
- Data repository: Store raw .csv and interview transcripts in the project folder under /primary_data; update Source Log with file path and access rights.
- Refresh cadence: Flag questions likely to change with market conditions; schedule follow‑up pulse surveys quarterly.
Quick‑Action Checklist
- Select modules aligned to high‑impact, high‑uncertainty drivers.
- Program survey with forced logic and randomization; pilot with 10 users.
- Secure expert NDAs and schedule interviews; record and transcribe.
- Clean and code data within 48 hours of field close.
- Update driver tree inputs; document in Assumption Register.
- Archive raw data; confirm GDPR/CCPA compliance.
- Circle back to respondents with high‑level findings (ethical reciprocity).
Armed with these interview guides and survey question banks, teams can launch primary research that is fast, replicable, and laser‑focused on the uncertainties that matter—transforming conversations and questionnaires into the quantitative backbone of high‑confidence market‑sizing models.
12.3 Data‑Source Catalogue and Vendor Comparison Sheet
No model is stronger than its weakest input, which means smart market‑sizing starts with smart data procurement. This section provides two assets:
- A curated catalogue of data‑source categories with representative vendors, typical use cases, and watch‑outs.
- A vendor‑comparison sheet template that scores potential sources across the five dimensions that matter most: transparency, timeliness, taxonomy fit, triangulability, and total cost.
Together they institutionalize data due‑diligence, ensuring analysts spend their budget on information that moves the forecast needle rather than on glossy PDFs that gather dust.
1 Data‑Source Categories and Representative Vendors
Industry Syndicates and Analyst Firms
- Use cases: Segment splits, ASP trends, competitive share.
- Examples: Gartner, IDC, Omdia, Canalys for tech; Wood Mackenzie for energy; Euromonitor for consumer.
- Watch‑outs: Methodologies often proprietary; clarify sample frame and refresh cadence before purchase.
Government and Multilateral Databases
- Use cases: Baseline macro statistics, trade flows, labor metrics.
- Examples: U.S. Census, Eurostat, UN Comtrade, IEA, FAO.
- Watch‑outs: Data lag (12–24 months). Always read footnotes for re‑baselined series.
Point‑of‑Sale and Transaction Feeds
- Use cases: Real‑time demand proxies, price elasticity, geographic penetration.
- Examples: NielsenIQ, Circana, Visa Business Insights, Stripe Atlas aggregated spend.
- Watch‑outs: Panel coverage biases—e‑commerce often under‑represented in legacy retail scanners.
Mobility and Geospatial Providers
- Use cases: Foot‑traffic heatmaps, catchment analysis, logistics routing.
- Examples: SafeGraph, Placer.ai, Google Mobility Reports, TomTom O/D data.
- Watch‑outs: Privacy regulations may restrict granularity; rural coverage thin.
Raw Commodity and Spot‑Market Feeds
- Use cases: Cost‑curve modeling, supply‑driven price forecasts.
- Examples: S&P Global Platts, LME, BloombergNEF Metals, Argus Media.
- Watch‑outs: Subscription tiers limit historical depth; confirm license covers redistribution in board materials.
Patent and R&D Analytics Platforms
- Use cases: Innovation velocity, technology road‑mapping.
- Examples: PatSnap, Lens.org, Derwent World Patents Index.
- Watch‑outs: Disambiguation of assignees can be poor; supplement with manual QC for marquee players.
Job‑Posting and Talent Databases
- Use cases: Emerging skill demand, regional adoption proxies.
- Examples: Lightcast (Emsi Burning Glass), LinkedIn Talent Insights, Revelio Labs.
- Watch‑outs: Titles vary by company; build synonyms list before keyword pulls.
Social and Web‑Scraped Signals
- Use cases: Product sentiment, feature adoption cues, open‑source project traction.
- Examples: Brandwatch, Reddit API, GitHub Archive.
- Watch‑outs: Bot noise, sentiment sarcasm; require NLP cleaning.
Private Market and VC Funding Data
- Use cases: Early‑stage capital flow, startup headcount, runway signal.
- Examples: PitchBook, CB Insights, Crunchbase.
- Watch‑outs: Self‑reported valuations inflate headline numbers; triangulate with SEC Form D filings.
2 Vendor‑Comparison Sheet Template
The template is a one‑tab workbook that analysts populate during the Data‑Reconnaissance Week (see Section 10.2). Columns are pre‑scored 1–5; weighted totals surface the best fit.
Syndicate – Wood Mac
- Transparency: 4
- Timeliness: 3
- Taxonomy Fit: 5
- Triangulability: 4
- Cost: $$
- Weighted Score: 4.1
- Notes: Method clear; 6‑month lag
POS Data – NielsenIQ
- Transparency: 3
- Timeliness: 4
- Taxonomy Fit: 4
- Triangulability: 3
- Cost: $$$
- Weighted Score: 3.6
- Notes: E‑commerce gap; needs weighting
Geospatial – SafeGraph
- Transparency: 4
- Timeliness: 5
- Taxonomy Fit: 3
- Triangulability: 3
- Cost: $
- Weighted Score: 4.0
- Notes: Good for urban foot‑traffic
Patent – PatSnap
- Transparency: 2
- Timeliness: 5
- Taxonomy Fit: 4
- Triangulability: 4
- Cost: $$
- Weighted Score: 3.8
- Notes: UI intuitive; license query sent
Scoring Rubric (default weights)
- Transparency (25 %): methodology disclosure, raw‑data access.
- Timeliness (20 %): data age vs. market clock‑speed.
- Taxonomy Fit (20 %): alignment to boundary and segment codes.
- Triangulability (15 %): presence of orthogonal sources for cross‑check.
- Cost (20 %): price tier vs. budget, including redistribution rights.
Weights can be adjusted; default settings favor methodological integrity and data freshness over raw cost—reflecting the firm’s philosophy that $5 000 of high‑quality data beats $50 000 of shiny but opaque output.
Vendor Negotiation Best Practices
- Scope‑alignment preview. Request sample pages or API pulls targeting your exact boundary to avoid paying for irrelevant segments.
- Multi‑year discounts. Most vendors offer 10–15 % off for two‑year contracts and another 5 % if auto‑renew enabled.
- Redistribution rights. Secure board‑book sharing rights upfront; legal wrangling later delays gate approvals.
- API vs. PDF. API access may cost more but saves analyst hours; calculate ROI on automation.
- Kill‑or‑cure clause. For custom studies, negotiate milestone payments contingent on delivering data that passes QA checks.
Integration and Maintenance
- Source Log link. Each vendor row auto‑generates a Source‑Log entry stub—analysts fill page/table refs once data arrives.
- Quarterly vendor review. Analytics lead reviews sheet, drops stale sources, and adds newcomers; keeps the catalogue living.
- Feedback loop. After each project, analysts rate vendors on usability and accuracy; averages feed back into the weighted score column.
Quick‑Action Checklist
- Populate comparison sheet before committing budget.
- Weights reflect project priorities (speed vs. depth vs. cost).
- Sample data reviewed and QA‑approved.
- License covers model distribution and archival.
- Source Log stubs created and linked to cells.
- Vendor performance feedback logged post‑project.
With a structured catalogue and comparison sheet, data procurement shifts from ad‑hoc vendor pitches to a disciplined sourcing strategy—one that aligns spending with analytical need, keeps methodological integrity front and center, and continuously improves as new providers enter the market.
12.4 Market‑Sizing Readiness Self‑Assessment Checklist
No toolkit, template, or playbook can substitute for an honest look in the mirror. Before you accept a new sizing mandate—or before executives sign off on a multimillion‑dollar investment—run a structured self‑assessment. The goal is not to generate a vanity score but to surface blind spots early, allocate resources intelligently, and set expectations on accuracy, timeline, and risk. The checklist below distills the best practice elements covered throughout this book into a single diagnostic you can complete in under an hour.
How to Use the Checklist
- Assemble the core team—Engagement Lead, Methodology Architect, QA Reviewer, and Finance Partner.
- Read each statement aloud; rate your readiness on a scale of 1 – 5
- 1 = Not started / no evidence
- 3 = In progress / partial coverage
- 5 = Fully in place and documented
- Tally each section to identify red zones (<3) requiring immediate action.
- Record owners and deadlines for closing gaps; revisit weekly until all critical items score ≥4.
Readiness Domains and Diagnostic Prompts
Scope & Governance
- We have a written charter that defines the market boundary, precision target, and decision context.
- RACI roles are assigned, and stage‑gate dates are locked on the calendar.
- The Executive Sponsor has reviewed and approved the charter.
Methodology & Data Strategy
- A driver tree is drafted and peer‑reviewed for completeness and parsimony.
- The chosen sizing approach (top‑down, bottom‑up, hybrid) is justified and documented.
- All high‑impact ratios or unit counts have at least two credible data sources identified.
Data Procurement & Quality Assurance
- The vendor comparison scorecard is complete, and preferred datasets are budgeted.
- QA protocols (30‑point checklist) are embedded in the workplan with named reviewers.
- Data ingestion scripts and version‑control repositories are set up and tested.
Model Infrastructure
- The correct Excel skeleton (top‑down, bottom‑up, hybrid) is cloned with version ID locked.
- Macros for linting, Monte‑Carlo, and dashboard refresh run without errors.
- Source Log, Change Log, and Assumption Register tabs are populated with stubs.
Primary Research Readiness
- Expert interview target list is drafted; NDAs and scheduling logistics confirmed.
- Survey questionnaire modules are selected, programmed, and piloted.
- Incentives, GDPR/CCPA compliance, and data‑storage locations are approved by legal.
Scenario & Sensitivity Planning
- Tornado sensitivity drivers are identified and mapped to input cells.
- Trigger events (regulatory, technology) have probability distributions and linkage formulas.
- Monte‑Carlo parameters (iterations, distributions, correlations) are set and tested.
Visualization & Storylining
- Slide template with approved color palette is loaded into Think‑Cell or equivalent tool.
- Headline charts (waterfall, fan, tornado) populate automatically from the model.
- Storyline pyramid and slide map are drafted, with each slide title phrased as a conclusion.
Embedding & Decision Integration
- Model outputs link directly to valuation or budget spreadsheets—no manual copy‑paste.
- Confidence‑weighted discount rates or budget haircuts are specified in finance models.
- Refresh cadence is scheduled to update valuations when new market data drops.
Quick‑Action Gap‑Closure Checklist
- Any statement scoring 1 – 2: assign an owner and a 48‑hour action plan.
- Items scoring 3: schedule resources within the next sprint.
- All 4 – 5 items: verify documentation is archived and accessible to reviewers.
- Summarize results in a one‑page Heat Gauge—green (≥4), yellow (3), red (≤2)—and share with the Sponsor before Gate 1.
Completing this readiness assessment forces the team to confront uncertainties that slide decks often gloss over—turning hidden risks into managed tasks and transforming the market‑sizing
