Hospital Capex Signals

Description

A derived slice of Provider Change Events: every capex_jump event — a hospital whose cost report shows major movable equipment purchases of at least $1M and at least 2× the prior fiscal year — joined back to CMS HCRIS Worksheet A-7 Part I for the complete purchase breakdown by asset class in the trigger year, plus the prior-year comparison and the year-over-year multiple. One row per (hospital, trigger fiscal year).

Worksheet A-7 Part I is the hospital's own reconciliation of capital asset balances, filed with CMS: annual purchases in dollars split across land, land improvements, buildings & fixtures, building improvements, fixed equipment, major movable equipment, and HIT-designated assets. The framing is observational: the hospital reported a step-up in equipment purchases on its cost report — who they bought from, and why, is not in the data.

  • Source: CMS HCRIS HOSP10 (form 2552-10) Worksheet A-7 Part I, numeric files from downloads.cms.gov; hospital identity from the filed cost report header.
  • Refresh cadence: quarterly, on the HCRIS release cycle.
  • Row count: 4,277 across 3,138 hospitals (2026-09-08 export). Trigger fiscal years 2019–2025: 7 · 422 · 920 · 833 · 831 · 782 · 482. FY2019 is thin because the loaded A-7 history starts there and a jump needs a prior year.
  • Two bases, recorded per row in prior_year_basis: positive (3,638 rows: prior-year movable line > 0 and this year ≥ 2× it) and no_movable_filed (639 rows: no prior-year movable line — CMS files never contain a zero, so absent means zero or unfiled — while other capital purchases were filed that year: a first equipment purchase after a year without one). Hospital years whose prior A-7 schedule was blank, or whose prior line was negative (a reclassification), are excluded.
  • Correction 2026-09-08: the rule originally read any missing or negative prior line as zero and flagged 958 rows against it; those were removed, the rule split into the two bases above, and the 639 no_movable_filed rows reinstated with that label.
  • Vintage: 2026-07-22 (A-7 ingest run).
  • Jump rule: movable_equipment_purchases >= 1,000,000 AND ( (prior FY movable > 0 AND movable >= 2 × prior FY movable) OR (prior FY movable absent AND prior FY total_purchases > 0)) (thresholds recorded per event in the underlying events table).

Table: hospital_capex_signals

Snowflake: HEALTHPARSE_DATA.HOSPITAL_CAPEX_SIGNALS.HOSPITAL_CAPEX_SIGNALS

Column Type Description Notes
ccn VARCHAR CMS Certification Number of the hospital. Joins to HCRIS Hospital Financials (beds, revenues, margins) and Facility Quality.
hospital_name VARCHAR Provider name from the filed cost report. —
address / city / state / zip VARCHAR Hospital location from the cost report. —
fy_end_year INTEGER The trigger fiscal year (year the reporting FY ends). —
event_date DATE The fiscal-year-end date of the trigger year's report (Dec 31 of fy_end_year when the exact FY end wasn't on file). —
prior_year_basis VARCHAR positive or no_movable_filed — see Description. Filter to positive for a strict 2× screen.
land_purchases DOUBLE A-7 Part I line 1, purchases column: land bought in the FY, dollars. Any A-7 amount can be null (line not filed) or, rarely, negative (filed reclassification/correction) — take coalesce(x, 0) before aggregating.
land_improvement_purchases DOUBLE Line 2: land improvements. —
building_purchases DOUBLE Line 3: buildings & fixtures. —
building_improvement_purchases DOUBLE Line 4: building improvements. —
fixed_equipment_purchases DOUBLE Line 5: fixed (built-in) equipment. —
movable_equipment_purchases DOUBLE Line 6: major movable equipment — the trigger class: imaging, surgical, lab, and other relocatable capital equipment. ≥ $1M by construction of the slice.
hit_asset_purchases DOUBLE Line 7: health-IT designated assets. Sparsely filed; null-heavy.
total_purchases DOUBLE Line 8 (subtotal of lines 1–7): all capital purchases in the FY. —
prior_year_movable_equipment_purchases DOUBLE Line 6 purchases from the same hospital's prior-FY report. > 0 where prior_year_basis = positive; null where no_movable_filed.
prior_year_total_purchases DOUBLE Line 8 purchases from the prior FY. —
movable_equipment_yoy_multiple DOUBLE movable_equipment_purchases / prior_year_movable_equipment_purchases, rounded to 2 places. Null where prior_year_basis = no_movable_filed (no base to divide by).
total_beginning_balance DOUBLE Line 8, beginning-balance column: total capital assets at FY start. With total_ending_balance, sizes the jump against the hospital's asset base.
total_ending_balance DOUBLE Line 8, ending-balance column. —
_dataset / _vintage / _exported_at VARCHAR See README. —

Example queries (Snowflake)

-- 1. "Hospital just bought equipment": biggest movable-equipment jumps in
--    the most recent trigger year, with the YoY multiple.
SELECT "ccn", "hospital_name", "city", "state",
       "movable_equipment_purchases", "prior_year_movable_equipment_purchases",
       "movable_equipment_yoy_multiple"
FROM HEALTHPARSE_DATA.HOSPITAL_CAPEX_SIGNALS.HOSPITAL_CAPEX_SIGNALS
WHERE "fy_end_year" = 2025
ORDER BY "movable_equipment_purchases" DESC
LIMIT 25;

-- 2. Territory planning for an imaging/device vendor: California signals,
--    $5M+ movable-equipment years, newest first.
SELECT "event_date", "hospital_name", "city",
       "movable_equipment_purchases", "total_purchases"
FROM HEALTHPARSE_DATA.HOSPITAL_CAPEX_SIGNALS.HOSPITAL_CAPEX_SIGNALS
WHERE "state" = 'CA'
  AND "movable_equipment_purchases" >= 5000000
ORDER BY "event_date" DESC;

-- 3. Construction-adjacent: hospitals whose building + building-improvement
--    purchases ALSO exceeded $10M in a movable-equipment trigger year
--    (equipment jump riding a build-out).
SELECT "ccn", "hospital_name", "state", "fy_end_year",
       "building_purchases" + coalesce("building_improvement_purchases", 0) AS building_spend,
       "movable_equipment_purchases"
FROM HEALTHPARSE_DATA.HOSPITAL_CAPEX_SIGNALS.HOSPITAL_CAPEX_SIGNALS
WHERE coalesce("building_purchases", 0) + coalesce("building_improvement_purchases", 0) >= 10000000
ORDER BY building_spend DESC;