# RCM Waterfall + Office Financial Health Score — Methodology

**Project:** RCM-WOHS (Waterfall + Office Health Score)
**Date:** 2026-05-27
**Owners:** Scott Guest (build), Melanie Kosier @ A&M (weight calibration), Myles McAllister (sponsor), Michelle Kosier (Gen4 sign-off), Derrek Gafford (finance)

---

## 1. Scope

Per-office, per-region, per-entity reconciliation of Gross Production → Cash Collections with leakage attribution by bucket, plus a 90-day Office Financial Health Score.

All measures aggregate at office or higher. **No patient-level data.** Phase A HIPAA boundary.

---

## 2. Data Source

**Power BI dataset:** `sga_master_OP_FINAL` — id `9fe377a5-90a7-4c59-8ea7-b7389ee3eae9`, workspace `47eba405-7935-481b-ad05-2842a729f3dc` (SGA Production).

This is the canonical Sage-tied operations model. The previous default (`SGA_Master_OP - Copy`, e3fcdf32) is a subset and does not contain the full RCM measure set.

Bridge: SGA Power BI Bridge at VPS:3050 (`/opt/powerbi-bridge/server`), static token auth. Refresh cadence: nightly at 04:00 CT.

---

## 3. Canonical Measures (Bucket Picks)

| Bucket | Measure | Rationale |
|---|---|---|
| Gross Production | `'_Measures'[Gross Production]` | Validated TTM, bridge tie-out. |
| Total Adjustments | `'_Measures'[Production Adjustments]` | Drives PBI's own `Waterfall Net Collections Rate`. |
| Contractual + Insurance | `'_Measures'[Contractual/Policy Production Adjustments]` and `'_Measures'[Unavoidable (Contractual) Production Adjustments]` | Split bucket. |
| Policy | `'_Measures'[Unavoidable (Policy) Production Adjustments]` | Sub-bucket detail in drill rail. |
| Courtesy-Promo | `'_Measures'[Unavoidable (Courtesy-Promo) Production Adjustments]` | Sub-bucket. |
| OTC Variance | `'_Measures'[OTC Collections Difference Patient Collections]` | Expected vs actual collected from patient. |
| OTC Rate | `'_Measures'[OTC Collections Rate]` | Per-office quality of patient collection. |
| Bad Debt | `'_Measures'[Bad Debt Adjustments Total]` and `[Bad Debt Adjustments % of Gross Production]` | |
| Bad Debt (no double-count) | `'_Measures'[Production Adjustments (Less Bad Debt)]` | Isolates adjustment band from bad debt for outlier checks. |
| Net Collections | `'_Measures'[Net Collections]` | Canonical. **Stored as negative in PBI**; dashboard normalizes to absolute. |
| Net Collection Rate | `'_Measures'[Net Collections Rate]` | Composite for Health Score. |
| Adjusted Net Coll Rate | `'_Measures'[Adjusted Net Collections Rate]` | Refunds/contractual variant for diagnostic depth. |
| Sage Tie | `'_Measures'[Net Practice Revenue]` | Sage-tied. Validation only — not in waterfall display. SGA Legacy only. |

---

## 4. Sage Tie-Out

Method: abs(`[Net Collections]`) vs `[Net Practice Revenue]`, TTM. Threshold: |Δ| < 3% (loose; tighten when month-end-aligned).

Current state (2026-05-27 snapshot):

| Entity | abs Net Collections | Net Practice Revenue | Δ | Status |
|---|---|---|---|---|
| SGA Legacy | $288,907,300 | $296,136,058 | −2.44% | Within 3% tolerance |
| Gen4 | $290,700,535 | $0 (not in PBI) | n/a | Pending finance Sage figure |
| Modis | included in Net Coll | $0 (not in PBI) | n/a | Pending finance Sage figure |
| All | $650,744,731 | $296,136,058 (SGA only) | n/a | Out-of-scope comparison |

**Action:** Derrek's finance team must produce Sage Net Practice Revenue for Gen4 + Modis TTM Apr-26 month-end. Until then, dashboard shows pending badge on Gen4/Modis aggregates.

---

## 5. Office Health Score Components

v0 ships with 9 of 12 components active. Components 10-12 gated on Phase 3-4 data work.

| # | Component | Weight | Direction | Source | Status |
|---|---|---|---|---|---|
| 1 | Net Collections % | 15 | higher better | `[Net Collections Rate]` | Live |
| 2 | OTC Collection % | 12 | higher better | `[OTC Collections Rate]` | Live |
| 3 | Bad Debt % | 12 | lower better | `[Bad Debt Adjustments % of Gross Production]` | Live |
| 4 | Adjustment % vs Band | 10 | lower better (deviation) | `[Production Adjustments % of Gross Production]` vs network band [−15.4%, −14.1%] | Live |
| 5 | Visit→Cash | 8 | higher better | `[Net Collections] / [Visits]` | Live |
| 6 | Production Volatility | 8 | lower better | stddev(Net Production) T90 (network proxy — per-office T90 pending) | Live (proxy) |
| 7 | Case Acceptance | 0 | higher better | `[Case Starts]` / scheduled treatment | **Inactive v0** — PBI measure is specialty-only |
| 8 | Hygiene Reappt % | 10 | higher better | `[Patients Recare %]` | Live |
| 9 | New Patient Recare % | 5 | higher better | `[New Patients Recare %]` | Live |
| 10 | AR >90 % | 5 | lower better | Aging table | **Inactive** — Phase 3 |
| 11 | Insurance Aging | 3 | lower better | Aging table | **Inactive** — Phase 3 |
| 12 | Claim Denial Rate | 2 | lower better | Claim adjudication fact | **Inactive** — Phase 4 |

**Live weight sum:** 80 of 100 (case acceptance reserved at 10 + Phase 2 components 10).

### Score math

Per office, per component:
1. Compute the raw value from PBI.
2. Convert to a percentile rank within the network distribution (0–100). For `lower_better` metrics, invert.
3. Weight component scores per the table.

Composite = weighted average of component scores. Mean composite ≈ 50 by construction.

**Tier thresholds (composite):**
- **A:** ≥60
- **B:** 50–59
- **C:** 40–49
- **D:** <40

Quartile-anchored. Mean composite is near 50 because we use percentile ranks, so an A is meaningfully top-quartile.

**Weight recalibration** is owned by Melanie + A&M. `config/health-score-weights.yaml` is version-controlled and can be edited without code changes.

---

## 6. EBITDA Opportunity

Bad-debt reduction at 25 / 50 / 100 bps of Gross Production:
- 25 bps × Gross = $/year
- 50 bps × Gross = $/year
- 100 bps × Gross = $/year

Adjustment band recapture: for offices with adjustment % below network low band (−15.4% of Gross), opportunity = (band low − office adj%) × Gross.

---

## 7. Known Gaps

| Gap | Phase | Notes |
|---|---|---|
| AR aging $ buckets (Insurance / Patient / Legacy / Disputed / Agency) | Phase 3 | PBI lacks aging fact table. PMS-direct extract per PMS family or new aging fact in PBI. |
| Claim denial rate by payor / office | Phase 4 | No claim adjudication fact available. ERA file ingestion required. |
| Pre-collection rate | Phase 4 | Collection agency flag not surfaced in PBI. |
| Disputed claims | Phase 4 | Same source as denial. |
| Gen4 + Modis Sage tie-out | Phase 0 follow-on | Derrek's finance team to produce Sage Net Practice Revenue figures. |
| Office-level production volatility | Phase 1.5 | Currently network proxy; per-office T90 stddev pending separate snapshot query. |

---

## 8. Refresh Cadence

| Job | When | Owner |
|---|---|---|
| Nightly snapshot + validation | 04:00 CT | Task Scheduler `nightly.ps1` |
| Friday weekly summary | 14:00 CT Friday | Same scheduler entry, weekly trigger |
| Quarterly weight recalibration | first week of Apr / Jul / Oct / Jan | Melanie + Scott |
| 90-day Office Health review | every 90 days | Myles + ROD |

---

## 9. Trust & Audit

Every aggregate using Gen4 or Modis data shows a "Sage tie-out pending finance" badge until Phase-0 resolves. Every dashboard view exposes the snapshot timestamp and the Sage delta. Adjustments outside the network band are flagged in the bucket-detail view with z-score.

---

## 10. Reference

- Source code: `personal/SGA/rcm-project/`
- Live dashboard: https://sga-rcm-v2.pages.dev
- Snapshot history: `data/snapshots/<date>/`
- Validation history: `pbi-validation/reports/rcm-<date>/`
- Daybreak handoff: `personal/SGA/daybreak/handoffs/rcm-waterfall.md`
- PRD: `personal/SGA/rcm-project/PRD.md`