FACILITY FACTS

• 2 OR rooms available (ASC + OBL). Currently operating 1 OR, evaluating 2.
• Days per week: 4 or 5 (Friday is optional).
• Wednesday operates out of OBL with only 4 pre/post-op bays. All other days have 6 bays.
• Patients arrive 90 minutes before their procedure and occupy a bay for their full journey: pre-op (90 min) + OR procedure time + recovery.
• Average OR procedure time (including turnover): 44 minutes (derived: 4 days × 480 min ÷ 44 cases = 43.6 min).
• Average recovery time across 76 procedure types: 27 minutes (weighted average from recovery times data).
• Operating hours per day: 8 hours.

HISTORICAL CASE SCHEDULE (actual cases per day, 1 OR)

The 2-OR case counts must be calculated — not estimated. Use the bay constraint formula:

• 6-bay days: FLOOR(6 × 480 / (90 + OR_time + recovery), 1) = 17 cases/day
• Wednesday (4 bays): FLOOR(4 × 480 / (90 + OR_time + recovery), 1) = 11 cases/day

So: 2 OR × 4 days = 62 cases/week. 2 OR × 5 days = 79 cases/week.

CONVERSION & VISIT RATES (from eCW data, Jun 2025–Apr 2026)

• Order → scheduled OR case conversion rate: 78.7%
• NP visit-to-order rate: 34.1%
• FU visit-to-order rate: dropdown — 10.6% / 20% / 30% (current measured = 10.6%; target = 30%)
• Referral → NP appointment conversion rate: 70.3%
• Staging multiplier (avg procedures per patient): 1.29× (958 procedures ÷ 740 unique patients; captures both staged procedures and internal referrals flowing through ASC ordering)
• Current tracked orders/week: 20
• FU clinic visits/week (default input): 77 (current actual ≈ 66; at 30% rate, 77 visits achieves the historical 41.4% FU share)

REFERRAL DATA (2026 actuals, editable)

Non-referral new patient sources (monthly avg): WOM 16, Google 10.7, Dr WOM 9.7, walk-in 1, print/other 1 = ~36/month. Referral share ≈ 74%.

INTERNAL REFERRAL PIPELINE (INT referrals — patients identified for additional conditions)

Category breakdown (Feb+Mar avg): INT PAIN 35.5, INT IR 15.5, INT CARD 8.5, INT VEIN 7.5, INT WC 5.5, INT POD 5.5, INT ARTERIAL 4. These are already partially captured in the 1.29× multiplier. As INT volume grows, consider raising multiplier to 1.35–1.40×.

CANCELLATION RATES (from Retro. Clinic & OBL Schedule Report)

2026 YTD actuals:

Monthly OBL cancel trend: Jan 12.8%, Feb 10.4%, Mar 10.4% (trending toward 8% goal). Reschedule rate ~25% — patients return, not permanently lost.

Goals: OBL cancel 8%, NP cancel 10%, FU cancel 5%.

MODEL STRUCTURE TO BUILD

Section 1 — Inputs & Dropdowns

• Days/week dropdown (5 or 4), ORs/day dropdown (1 or 2) — top of sheet
• All rate inputs in blue (editable): conv rate, FU visits/week, NP order rate, FU order rate (dropdown), current tracked orders, staging multiplier

Section 2 — OR Cases Reference Table

• Rows for Mon–Fri: 1-OR cases (B col, hardcoded from historical), 2-OR max cases (C col, formula-driven from bay constraint), E col formula: =IF($D$3=1,$B{r},$C{r})*IF(A{r}=”Friday”,IF($B$3=5,1,0),1)
• F22 = =SUM(E15:E19) — total OR cases/week

Section 3 — Weekly Summary Cascade formulas:

• F22 = OR cases/week
• F23 = F22 / conv_rate (total orders needed)
• F25 = FU_visits × FU_rate (FU orders)
• F24 = MAX(F23 − F25, 0) (NP orders)
• F26 = F24 / (NP_rate × staging_multiplier) (NP visits/week)
• F27 = FU visits/week (= B7)
• F28 = F26 + F27 (total clinic visits/week)

Section 4 — Referral Pipeline Monthly actuals table, non-referral sources table, then:

• Referrals needed/month = ROUND(NP_visits/week × 4.33 × (ref_share / (ref_share + nonref_share)) / ref_conv_rate, 0)
• Gap vs current average, estimated current OR cases/week at current volume

Section 5 — Internal Referral Pipeline Table with monthly INT referral data and category breakdown. Note explaining relationship to 1.29× multiplier.

Section 6 — Bay Capacity Analysis Inputs: bays (6), pre-op time (90 min), avg recovery (27 min), OR hours (8), avg OR procedure time (44 min), Wednesday bays (4).

Per-day table showing: max cases/day at each bay count, 1-OR current schedule, 2-OR bay-limited max, bay utilization %. 4-day total (62 cases/wk) and 5-day total (79 cases/wk).

Section 7 — Cancellation Analysis & Schedule Targets Monthly trend table (Jan–Mar 2026) with color-coding vs goal. Editable attrition rate inputs with goal column. Schedule targets table showing: Seen Target/week → Must Schedule (current rates) → Must Schedule (at goal), for OR cases, NP appts, FU appts, and total clinic.

Formula: Must Schedule = ROUNDUP(Seen_Target / (1 − cancel% − noshow%), 0)

FORMATTING

• Blue text = editable hardcoded inputs
• Black text = formulas
• Section headers in soft blue (#D6E4F0)
• Input rows in light blue (#EBF5FB)
• Green = at/below goal, yellow = slightly over, red = materially over goal
• Dropdowns via hidden Lists sheet (days: 5/4; ORs: 2/1; FU rate: 10.6%/20%/30%)
• Zero formula errors required before delivery

That prompt will get you back to this exact model in one shot. If you want to make it even tighter, you can attach the three source files (ASC Recovery Times, Internal Referrals, and the Retro. Clinic & OBL Schedule Report) and add a line at the top saying “The attached files are the source data — use them to verify the inputs below rather than taking my word for the numbers.”