Bottom-Up TAM, SAM, and SOM Modeling in Excel Using Public Data

Relying on arbitrary percentages from syndicated market reports triggers instant pushback from investors and grant reviewers. Here is how to construct a defensible bottom-up TAM, SAM, and SOM model in Excel using public Census establishment data and sensitivity analysis.

HumanPen Team

· 9 min read

Why Bottom-Up TAM, SAM, and SOM Models Win Investor and Grant Reviewer Trust

A bottom-up market sizing model estimates market potential by multiplying verifiable operational units by an auditable average annual contract value, rather than claiming an arbitrary percentage of a speculative top-down industry report. By establishing Total Addressable Market (TAM) through government establishment counts, filtering Serviceable Addressable Market (SAM) by customer profile criteria, and grounding Serviceable Obtainable Market (SOM) in actual sales velocity, teams produce a defensible, formula-driven projection.

Key takeaway: Investors, SBIR commercialization reviewers, and corporate committees routinely reject top-down market claims because cited industry report figures aggregate unrelated enterprise revenue. A bottom-up model links every dollar to a specific NAICS establishment band, unit price, and sales capacity constraint.

When startup founders pitch institutional venture funds or researchers apply for competitive commercialization funding—such as Small Business Innovation Research (SBIR) Phase II grants or translational accelerators—market sizing slides undergo rigorous stress testing. Reviewers immediately discount claims that take a generic fifty-billion-dollar syndicated research projection and assume a one-percent market capture without operational evidence. Instead, defensible market sizing requires transparent unit economics: demonstrating who the buyers are, where official statistical agencies count them, and why your unit pricing and sales conversion timelines match commercial realities.

Top-Down Syndicated Reports vs. Bottom-Up NAICS Modeling vs. Value-Theory Pricing

Market sizing methodologies diverge significantly in their data provenance, defensibility under investor due diligence, and sensitivity to operational execution. The U.S. Small Business Administration (`sba.gov`) emphasizes in its market research guidance that evaluating genuine customer demand requires examining both objective economic data and specific consumer segments. The following comparison table contrasts the three dominant market sizing paradigms across primary data inputs, structural formulas, validation boundaries, and audit vulnerabilities.

Sizing ParadigmPrimary Data Sources & ProvenanceStructural Calculation FormulaVerification Boundary & StrengthsPrimary Due Diligence Vulnerability
Top-Down Syndicated EstimateThird-party paid research reports, press releases, trade blogs`Market = Aggregated Global Spend × Assumed Market Share %`Quick to assemble for pitch decks; illustrates macroeconomic momentumFails diligence; combines disparate product categories; impossible to audit
Bottom-Up NAICS / Establishment CountCensus County Business Patterns (`cbp.html`), BLS, official enterprise registries`TAM = Σ (Establishments by Size Band × Annual Contract Value)`Transparent data pedigree; grounded in verified employer counts; auditableRequires explicit segmentation filtering and validated enterprise pricing tiers
Value-Theory / Economic Value to CustomerCustomer workflow interviews, ROI baselines, labor cost savings`TAM = Target Workflow Users × (Quantified Value Created × Value Capture %)`Compelling for novel product categories lacking historical NAICS codesHighly sensitive to value capture assumptions; vulnerable to buyer resistance

While top-down figures appear frequently in early pitch decks to establish high-level industry excitement, institutional investors and grant committees treat them as vanity metrics. A bottom-up architecture forces the analyst to define the customer unit: whether a buyer is an establishment, a corporate parent entity, or an individual licensed seat. Grounding that unit count in official government databases eliminates speculation and transforms market sizing into an operational roadmap.

Assembling Public Data Building Blocks: NAICS Codes, CBP Establishments, and ARPU Formulas

Building an auditable market model begins with identifying the precise North American Industry Classification System (NAICS) codes representing your primary customer base. The U.S. Census Bureau provides detailed data through its County Business Patterns (`census.gov/programs-surveys/cbp.html`) and associated developer datasets (`census.gov/data/developers/data-sets/cbp-zbp.html`). Rather than using aggregated national company figures, CBP provides granular employer establishment counts broken down by 6-digit NAICS codes, geographic territory (national, state, county, and ZIP code levels), and employment size classes.

  • Step 1: Define Target NAICS Codes and Sizing Segments: Query the official Census classification to isolate primary industries (such as `NAICS 541512` for Computer Systems Design Services or `NAICS 621111` for Offices of Physicians). Distinguish between single-location establishments and multi-unit corporate enterprises.
  • Step 2: Extract Employment Size Classes from CBP: Isolate establishment distributions across standardized employment brackets (such as 1–4, 5–9, 10–19, 20–49, 50–99, 100–499, and 500+ employees) to reflect differentiated procurement capabilities.
  • Step 3: Define Annual Contract Value (ACV/ARPU) by Tier: Map tiered pricing to establishment scale (for example, small clinics paying $3,600 annually, mid-size practices paying $12,000, and regional health systems paying $48,000).
  • Step 4: Establish Serviceable Filtering Logic (SAM): Apply verifiable inclusion criteria, such as geographic software deployment boundaries, compliance requirements, or technological maturity indices derived from BLS occupational data.
  • Step 5: Apply Capacity-Constrained Penetration (SOM): Model realistic 1- to 3-year market capture based on sales rep quotas, lead-to-close conversion rates, and onboarding pipeline velocity.

In Excel, translate these relationships into clean formulas where input parameters remain completely isolated from formula calculations. For instance, store establishment counts in column `B` (`B2:B8`) and corresponding annual contract values in column `C` (`C2:C8`). Calculate Total Addressable Market using the formula `=SUMPRODUCT(B2:B8, C2:C8)`. To calculate SAM, apply an auditable filtering percentage in cell `H2` (such as `0.35` representing cloud-ready establishments) using `=B2 $H$2` across rows, and compute SOM in cell `H50` by constraining growth to sales pipeline velocity: `=MIN(SAM_Total Target_Share, Sales_Reps * Quota_Capacity)`.

Stress-Testing Assumptions: Bear, Base, and Bull Scenarios with Tornado Sensitivity Charts

A deterministic market sizing model that yields a single static dollar figure invites skepticism during technical and financial reviews. Key operational drivers—such as enterprise contract values, client churn, sales cycle lengths, and addressable segment penetration—inevitably vary under real-world market conditions. Professional valuation and commercialization teams address this uncertainty by constructing three-tier scenario switches (Bear, Base, and Bull) and running multi-variable sensitivity analyses.

Operational Sizing VariableBear Scenario (Downside)Base Scenario (Target)Bull Scenario (Upside)Impact on 3-Year SOM ($ Millions)
Tier-1 Annual Contract Value (ACV)$18,000 (-25% discount)$24,000 (Standard list)$32,000 (+33% expansion)±$1.42M
Cloud Infrastructure Readiness (SAM %)22% of total establishments35% of total establishments50% of total establishments±$2.15M
Enterprise Sales Cycle Duration9 months (slow procurement)5 months (standard onboarding)3.5 months (accelerated pipeline)±$0.95M
Annual Customer Gross Churn Rate14% annual logo loss7% annual logo loss3% net negative churn±$0.68M
Quota-Carrying Account Executives3 enterprise representatives5 enterprise representatives8 enterprise representatives±$3.10M

To evaluate which variables exert the greatest leverage on financial outcomes, analysts summarize sensitivity results in a tornado diagram. By holding all baseline variables constant and swinging one parameter at a time across its 10th and 90th percentile bounds, the resulting bar chart visually ranks parameters from highest to lowest variance. When presenting market models in grant proposals or investor memos, pairing the Excel sensitivity table with Python or R automated chart generation ensures that stakeholders see exactly which operational bottlenecks dictate your market capture.

Building Defensible Market Sizing Workbooks and Review-Ready Memos in HumanPen Start Project

Translating raw Census datasets and pricing hypotheses into institutional-grade financial deliverables requires rigorous workflow orchestration. For founders and research teams preparing commercialization cases, HumanPen Start Project provides an end-to-end analytical environment. Attach your data and papers, describe what you need, approve the plan before work starts, and get the reviewed report, charts, and code ready to preview online or download.

  • Step 1: Ingest Census Microdata and Financial Baselines: Upload Census CBP CSV files, customer interview transcripts, and historical pricing schedules up to 2 GB. Proprietary financial databases such as Wind or industry reports must be exported before uploading.
  • Step 2: Interactive Plan Confirmation: Review and approve the structured analytical blueprint—including NAICS code mappings, tiered ARPU formulas, and scenario thresholds—before execution starts.
  • Step 3: Sandbox Computation in Python or R: An isolated sandbox executes calculations, cross-validates dynamic Excel formulas, and generates publication-grade tornado charts and sensitivity curves without manual transcription errors.
  • Step 4: Dual Verification and Package Delivery: An independent verification process audits numbers across the Excel workbook, Word commercialization memo, and PowerPoint pitch slides against a single `results.json` source of truth, delivering files ready for online preview or zip download.

Maintaining consistency across financial models, executive slide decks, and commercialization proposals prevents the data discrepancies that routinely trigger due diligence red flags. On `/projects`, the public hotel booking cancellation and financial policy analysis illustrates how a serialized `results.json` pipeline verifies that every computed metric from the analytical notebook and CSV summary tables aligns with downstream `.docx` narrative reports and `.pptx` slides without manual copy-paste drift. Furthermore, when adapting quantitative financial models for multinational investors, teams rely on dedicated workflows for translating Excel workbooks while preserving formulas, and when submitting proposals to federal agencies, they follow standard guidelines for condensing SBIR and federal grant narratives without truncating commercial milestones.

Academic integrity & diligence boundary: All delivered Excel models, code scripts, and narrative memos serve as an auditable reference foundation. Founders, principal investigators, and financial analysts must personally verify all underlying commercial assumptions against their specific pricing contracts, customer letters of intent, and funder disclosure policies before submitting materials to investors or review bodies.

Frequently Asked Questions About Bottom-Up Market Sizing

How do I find the correct NAICS codes for an emerging technology that has no dedicated classification? When pioneering an innovative software or hardware category, identify the NAICS code of the traditional industry being disrupted or the operational department absorbing the budget. For instance, an AI pathology platform maps primarily to Offices of Physicians (`NAICS 621111`) and Medical Laboratories (`NAICS 621511`). The U.S. Census Bureau County Business Patterns provides establishment counts for these buyer categories, allowing you to model customer counts accurately even when your product category is novel.

What is the difference between an establishment count and an enterprise or firm count in Census data? In Census Bureau methodology, an establishment is a single physical location where business is conducted or services are performed, whereas an enterprise or firm represents an overarching legal entity that may operate multiple establishments. When modeling market size for multi-location businesses—such as retail banks or dental chains—clarify whether your pricing applies per physical facility (establishment level) or centrally per corporate parent (firm level) to avoid inflating your addressable market.

How should early-stage startups establish defensible Average Revenue Per User (ARPU) when they lack historical sales data? When historical billing records are limited, establish unit pricing through customer willingness-to-pay interviews, published pricing pages of adjacent non-competing SaaS tools used by the same buyer persona, and quantified labor cost savings. Frame early ARPU with conservative discounts, and structure sensitivity models that test the financial impact if achieved contract values fall 30% below target projections.

Why do investors focus intensely on SOM rather than TAM when evaluating early-stage commercial viability? While TAM measures the theoretical boundary of total demand, Serviceable Obtainable Market (SOM) measures immediate commercial viability under operational constraints. Investors recognize that early-stage startups cannot serve an entire market due to finite sales staffing, onboarding capacity, and geographical focus. An auditable SOM grounded in realistic conversion funnels demonstrates that the founding team possesses an actionable go-to-market plan rather than just an academic vision.

Sources and References

The market sizing methodologies, public establishment data structures, and small business research guidelines referenced in this article are derived from verified government agencies and official statistics portals:

KEEP READING