A fictional B2B service provider wants to screen five counties for a service sold to medical-office employer establishments. Your task is to define the addressable segment precisely, establish a sourced count and show how an annual opportunity range changes when eligibility, adoption and price assumptions change. You will use ChatGPT to inspect files and calculate; you do not need to write code.
The useful result is a transparent scenario that a client can challenge. Census counts are observations. Eligibility, adoption and the USD 12,000 annual fee are teaching assumptions. The resulting amounts are calculations, not measured demand, industry revenue, a sales forecast or a verified total addressable market.
What you will learn and produce
- Define a buyer unit and combine mutually exclusive county-by-industry counts without adding hierarchy totals twice.
- Preserve original fields and unavailable values, verify source lineage and enrich labels with joins that do not change row counts.
- Build a simple Excel model with centralized assumptions and live formulas, and test valid zero, blank and unavailable inputs.
- Produce a scenario exhibit, a sensitivity grid, two narrower practice cases and a concise client scope note.
Lesson 3 provides useful background on joins, but this lesson includes everything required to work independently. Familiarity with Excel filters, formulas and percentages is enough. The blueprint estimates 55 minutes; this has not been measured in a learner session. Allow additional time for reading, downloading and checking outputs.
Prepare your files
- Download and extract L13_Complete_Publication_Package.zip. Start with the learner lesson and prompts in output/. The nested L13_Learner_Practice_Files.zip contains staged input folders without the instructor results. Preserve an untouched copy of every source file.
- Open a fresh ChatGPT conversation with file-analysis capability. Record the actual model label shown, regular or Temporary mode, and your Excel platform/version in L13_Usability_Test_Log.txt. A plan or subscription name alone is not a model identifier. Interface labels may vary.
- Use the attachment control to upload the three Stage 1 files below and wait until all appear attached. Copy Prompt 1 exactly from L13_Exact_Prompts.txt. Continue in the same conversation, adding only the named files at each stage. Save each returned file before moving on.
- Do not open and re-save the original CSV in Excel before uploading. That can change text, zeros and bytes. The full Census parent has more rows than a standard Excel worksheet can hold; the prepared 15-row file is the learner input. Ask ChatGPT to calculate actual checks, not merely describe how it would check.
| Stage | Attach these files | Purpose |
|---|---|---|
| 1 | Frozen CSV; method card PDF; input manifest JSON | Check identity and profile |
| 2 | Source-row ledger; selected parent lines; county lookup; industry lookup; scope policy JSON | Verify lineage, joins and observed counts |
| 3 | Assumption register CSV | Build live scenario workbook |
| 4 | No new inputs | Sensitivity and industry practice |
| 5 | Reference workbook; scenario, sensitivity and practice CSVs; validation report | Compare after saving your own answers |
In the complete ZIP, raw inputs and lookups are in source/, the policy and assumptions in policies/, and the reference calculations in results/. The method card, prompts, reference workbook and this lesson are in output/. The learner-only ZIP uses stage01/, stage02/ and stage03/ folders with the same exact filenames. Do not rename or resave the input files.
Understand the scope before you multiply
Each source row is one county and one four-digit medical-office industry, across all establishment sizes. It is not an individual office record. Use five FIPS codes: 34003 Bergen County NJ, 34017 Hudson County NJ, 36059 Nassau County NY, 36103 Suffolk County NY and 36119 Westchester County NY. The exact slash-padded industry strings are 6211//, 6212// and 6213//. The 2023 release uses 2017 NAICS definitions.
Use total est once per cell. Physicians, dentists and other health practitioners form three separate four-digit groups. Do not add their parent sector total or their more detailed children. Size fields are another view of the same establishments; adding them to total est double counts. n1000 also overlaps its four n1000_* subcategories.
An establishment is a business location. A firm can own several establishments and may buy centrally. CBP covers employer activity, not all solo practitioners or all health-care activity. Keep the scope in every chart and client conclusion. A historical count does not establish current reachability, eligibility or willingness to pay.
N means not available or not comparable. Leave N, other special codes and blanks unavailable in derived fields, retaining the raw value. Do not call every N a proven suppression. A known zero is different. Missing size detail does not invalidate a known all-size total; an unknown required total does block the affected model.
Starting and follow up prompts
Prompt 1 Inspect The Frozen Source
Attach: cbp2023_five_counties_medical_offices.csv; L13_Data_Dictionary_and_Method_Card.pdf; L13_Input_Manifest.json.
Act as a data-analysis partner for a management consultant who uses Excel but does not program. Use your analysis tools to inspect the actual files. Calculate the source CSV SHA256, byte size, row count and field count. The registered controls are 15 rows, 25 fields, 1,678 bytes and SHA256 f3a31b32faf20154c9bb9989c3ec64955f49fa65fccff0eaefc1fc4741a17a57. Stop if identity differs; do not replace the file or resample records.
Read every source field as text and preserve it unchanged. Inspect the five county FIPS 34003,34017,36059,36103,36119 crossed with exact NAICS strings 6211//,6212//,6213//. Each row is a county-industry statistical cell, not an individual establishment. Verify one row per combination. Keep leading zeros in fipscty and keep the slash-padded source industry codes. Explain the 2023 data year and 2017 NAICS classification basis.
Profile est, every establishment-size field, and emp_nf/qp1_nf/ap_nf. Distinguish a known numeric zero from blank, N or another special value. N means not available or not comparable; do not label every N as proven suppression. Do not back-solve unavailable cells. Explain why n1000 and its four n1000_* components overlap. Do not sum bins or payroll/employment as the market denominator. Save an inspection table and list any problems. Stop before scenario calculations.
Prompt 2 Verify Lineage Joins And Observed Counts
Attach now: L13_Source_Row_Ledger.csv; L13_Selected_Parent_Lines.txt; L13_Official_County_Lookup.csv; L13_Official_Industry_Lookup.csv; L13_Scope_and_Metric_Policy.json.
Verify these files against the input manifest. Confirm every one of the frozen CSV's 25 fields matches its ledger record. The original parent contains 23 fields; county_name and county_fips were added in the frozen preparation. Compare the 23 source strings to the selected parent lines. Retain the existing parent-bound source_record_id and parent data-row/physical-line references; do not call this an operational establishment ID. Keep original and official county labels in separate fields.
Check lookup key types and uniqueness before two many-to-one left joins on county_fips and exact source NAICS. Report input/output rows, unique keys, null/unmatched keys and conflicting labels; never silently multiply or drop a row. Use the supplied policy and source definitions to confirm the 15 disjoint county-industry cells and all establishment sizes. Do not add 62/621 totals or detailed children to these four-digit groups.
Derive a numeric est field separately. The core total is the sum of known nonnegative integer est values, once per cell. Any missing, unavailable or invalid required count makes the affected total unavailable, with its reason; it is not zero. Report county totals, industry totals and the full total, and reconcile both grouping routes. Preserve all original strings, unavailable size bins and quality flags in a complete analytical ledger. Call the base observed in-scope employer establishments, reserving modeled eligible establishments for the later eligibility assumption.
Prompt 3 Build The Scenario Model
Attach now: L13_Assumption_Register.csv.
Verify the assumption register against the input manifest and keep assumptions in a separate table. Low/base/high eligible fractions are 50%/65%/80% of observed in-scope establishments. Adoption fractions are 2%/5%/8% of modeled eligible establishments. Annual fee is USD 12,000 per adopting establishment in all three cases. These are fictional teaching assumptions, not Census observations or an approved business plan.
Calculate modeled eligible establishments = observed establishments × eligible fraction; modeled adopting establishments = modeled eligible establishments × adoption fraction; and scenario annual opportunity = modeled adopting establishments × annual fee. Keep fractional modeled establishments and unrounded values in every calculation. Round only for display. Show all numerator/base fields, units and assumptions for the full scope, each county and each industry. Do not call modeled adopters actual customers or interpret the output as measured demand, industry revenue, a sales forecast or verified TAM.
Create a simple Excel workbook with centralized editable assumptions, source/derived fields visibly separated, live formulas and a source trail. Unknown counts or blank/invalid assumptions must produce an unavailable result and an explicit status, never a healthy zero. Known zero counts or zero valid fractions may legitimately produce zero. Preserve the baseline workbook and test fee doubling, a valid fraction change, zero versus blank, N in a disposable source-count copy, and an out-of-range percentage. Restore all baseline inputs. Record which calculations actually ran; do not claim native Excel execution unless it happened. Export the scenario tables and workbook.
Prompt 4 Test Sensitivity And The Practice Scopes
Use the unchanged source population and policy. For the full scope, hold annual fee at USD 12,000 and calculate the declared 5-by-5 sensitivity grid: eligible fractions 40%,50%,65%,80%,90% and adoption fractions 1%,2%,5%,8%,10%. The grid is an additional teaching assumption, not a confidence interval or distribution of probable outcomes. Keep both axis denominators explicit. Highlight the base 65%/5% cell and verify that it equals the base scenario.
Repeat the low/base/high scenarios for physicians' offices (6211//) only and then dentists' offices (6212//) only. Keep the five counties, fee and fraction definitions unchanged. Reconcile the two changed observed denominators to their source rows; do not mix the two scopes or add them to the full scope again. Show all six practice answers and explain why common fee, eligibility and adoption assumptions would need evidence before applying them to different industries in a client engagement.
Create a scenario PNG exhibit, a sensitivity PNG exhibit and their unrounded chart-value tables. Explain why common positive multipliers preserve the county ordering from establishment counts and why this does not establish which county is commercially best. Keep establishment-size segmentation optional. Show unavailable cells and overlapping bins rather than deriving a complete size result by subtraction.
Prompt 5 Verify And Write The Client Note
First save your original outputs. Then attach: L13_Market_Sizing_Workbook.xlsx; L13_Scenario_Results.csv; L13_Sensitivity_Values.csv; L13_Practice_Answers.csv; L13_Validation_Report.txt.
Compare file identities, scope, years, industry definitions and schemas before comparing numbers. Recalculate the observed totals directly from the frozen CSV and scenario amounts from the declared assumptions; do not use the reference workbook's results as inputs to their own check. Compare every scenario, sensitivity cell and practice answer. Separate data/arithmetic errors from alternative explicit business assumptions. Do not silently tune assumptions to reproduce an answer.
Write a concise client scope and findings note for a fictional B2B provider screening these five counties. Lead with the observed establishment base and the calculated low/base/high annual scenario range. Clearly separate published source observations, calculated results and teaching assumptions. Identify the operational/customer research needed to substantiate eligibility, decision authority across multi-location firms, adoption, willingness to pay, service costs and current market coverage. No Census or human approval is implied.
Report exactly which source, calculation, formula-recalculation, artifact, isolated prompt, native Excel and human usability checks were performed. Record failures, fixes and affected retests. The blueprint's 55-minute estimate is unvalidated. Do not invent findings, approvals or tests. Preserve all raw inputs and baseline formulas for review.
Verify the reference workbook in Excel
Save your own output before opening the reference. The workbook deliberately has four sheets: Scenarios, Assumptions, Sensitivity and Source. Source preserves the original text and separates parsed counts. Prepared source lineage and official lookups are supplied; the workbook does not download the parent, regenerate hashes or determine business assumptions for you. The simple workbook displays main and county scenarios plus the two practice industries. All three industry scenario sets are retained in L13_Scenario_Results.csv.
- Check the original 25 fields in Source!A8:Y23 and the separate derived layer A28:J43. All 15 required total counts must have a Known state. Confirm official lookup names and exact FIPS/NAICS strings; do not infer validity from a total alone.
- Recalculate counts independently from a separately imported, text-preserved frozen CSV. Compare county and industry sums with Scenarios!B5, A16:F21 and A27:C30. Keep any unknown outside a sum visible; a SUM function alone can ignore blanks and text.
- Inspect the live formulas in Scenarios!H9:H11. Change Base assumptions in Assumptions!B10:D10 on a disposable copy, test zero versus blank, then restore. Use L13_Excel_Verification_Instructions.txt for exact cells, expected outcomes and safe change-and-restore steps.
- Check Sensitivity!D12 against the restored Base scenario and practice results in Scenarios!A36:H42. The displayed modeled adopter count is rounded; formula calculations retain fractional establishments. Report the actual application used for testing, and keep the untouched released workbook.
Practice without changing the method
Restrict the observed base to physicians’ offices only, then dentists’ offices only, keeping all five counties and the same three assumption sets. Produce six annual scenario amounts, each with its establishment denominator, eligibility and adoption fractions, annual fee and units. Explain how the denominator changed and what remained constant. Compare with instructor answers only after saving your own work.
Write three questions a commercial team must answer before using the same assumptions across the two industries. For example, is the buying decision made at establishment or firm level; what share has a problem this service can solve; and what evidence supports willingness to pay and adoption? Do not tune assumptions to make one industry appear more attractive.
Use the result in a client discussion
State the source, year, geography, industry codes and buyer unit before showing the range. Explain how much of the range comes from assumptions and how a common positive multiplier preserves the county ordering of establishment counts. A large county is not necessarily the best entry market. Local competition, buying authority, acquisition cost, delivery capacity and current establishment status require additional evidence.
For a client project, agree the scope and assumption owners, document evidence for each input, preserve source snapshots, and test the workbook with unknown and zero cases. Use a versioned assumption register to show alternatives. An optional size-band extension must retain unavailable cells and cannot infer suppressed values by subtraction.
What has and has not been tested
Source reconstruction, selection, joins, calculation reconciliations and the exported reference workbook were executed and checked. Workbook testing used the authoring calculation runtime and independent exported-file inspection, including assumption mutations and baseline restoration. Rendered artifacts were reviewed. The package validation report records the separate isolated numerical prompt replay and its limits. No native Microsoft Excel session or human fresh-chat usability session was completed, and no human or Census approval is claimed.
Sources and definitions
Observed data: U.S. Census Bureau, 2023 County Business Patterns county release, https://www2.census.gov/programs-surveys/cbp/datasets/2023/cbp23co.zip. The frozen 15-row extract is byte-identical to the registered SHA256 after reconstruction from the exact parent. The recipe and case lookup are reconstructed, not recovered originals.
Definitions and limitations: https://www.census.gov/programs-surveys/cbp/about/glossary.html and https://www.census.gov/programs-surveys/cbp/technical-documentation/methodology.html. Use the applicable 2020–2023 county record layout and the 2017 NAICS reference retained in the package.
Reuse: https://www2.census.gov/foia/ds_policies/ds027.pdf. Census employee-created works are generally not subject to U.S. copyright, subject to the policy qualifications; no Census endorsement is implied. Official source access checked October 25, 2026 UTC.
Calculated values and checks: results/L13_Scenario_Results.csv, L13_Sensitivity_Values.csv and L13_Practice_Answers.csv; qa/independent and qa/workbook. The source/reuse notice provides complete reference URLs and identities.
