About

Project Overview We need a senior Excel modeler with deep US pain-management/insurance expertise to build a multi-location Master Calculator viewed through a referral funnel lens that executives can grasp instantly. If we turn on the faucet (increase referrals), how does that flow through locations → revenue drivers (procedures, urines/UDS, injections, E/M, etc.) to produce visits, revenue, cost, and profit—given payer mix and capacity? The workbook must feature a high-level, executive control sheet (simple levers, clear outputs) with supporting tabs that feed into each other for deeper detail (assumptions, capacity, payer rates, costs). What to Build (Excel-only) ● Executive Control Panel (high level): inputs for referral volume, contact/qualification, schedule and show rates, conversion to new patient, payer mix, reimbursement assumptions, and location capacity. Outputs: revenue, contribution margin, net profit, break-even, and required referrals to hit the target. ● Referral Funnel Engine (by location): Referral → Contacted → Scheduled → Show → New Patient → Procedure pipelines (e.g., ESIs, RFA, SIJ, PRP if applicable) →follow-ups. Adjustable levers by location/provider. ● Revenue Drivers: procedures, urines/UDS, E/M, ancillary; volumes × allowed amounts. ● Payer Mix & Reimbursement: Medicare/Medicaid/commercial mixes, allowed amounts by CPT family, prior auth impact, denial rates, AR timing/aging. ● Capacity & Throughput: provider templates, rooms, clinic hours, staff; enforce bottlenecks to keep outputs realistic. ● Costs: direct labor, supplies, rent/overhead allocation, marketing—rolling to location P&L and consolidated view. ● Scenario / Goal-Seek: Solver/Goal Seek, Data Tables, and sensitivity (tornado) on show rate, payer mix, reimbursement, denials, capacity, and referral volume. ● Dashboards: clean executive charts for revenue, margin, profit by location/month; capacity utilization; bottleneck flags. Deliverables 1. Excel Master Calculator (.xlsx) with protected formulas, named ranges, data validation, and error checks (no external BI tools). 2. Assumptions & Data Dictionary (plain English). 3. Quick SOP for monthly updates and scenario use. 4. Optional: one-page Executive Dashboard inside the workbook. Required Expertise ● US-based (required). ● 5+ years in healthcare analytics; pain management / MSK / PM&R strongly preferred. ● Deep understanding of US insurance & revenue cycle: payer mix, fee schedules/allowed vs billed, prior auth, denials/appeals, no-show dynamics, AR aging. ● Excel power user: Dynamic Arrays, XLOOKUP/XMATCH, INDEX/MATCH, LET/LAMBDA, PivotTables/slicers, Power Query/Power Pivot, Solver/Scenario Manager/Data Tables, robust charting. ● Able to build models that are simple for executives yet auditable and granular under the hood. Nice to Have ● Familiarity with the Las Vegas pain management market (referral patterns, payer behavior, competitive procedures). ● Experience modeling multi-location clinics and provider capacity. What We’ll Provide ● Historic referral funnel metrics by location (referral → contacted → scheduled → show → new patient → procedure). ● Payer mix and typical allowed amounts by major CPT families; denial rates; AR timing. ● Staffing templates, clinic hours, cost assumptions, target goals. Engagement ● Start: ASAP • Project-based with potential ongoing support • United States only (confirm city/state). How to Apply 1. Share 1–3 screenshots/redacted examples of Excel calculators/models you built for clinics (ideally pain management/MSK). 2. Brief approach outline: proposed tabs, key formulas (e.g., LAMBDA usage), and how you’ll keep the exec control panel simple while feeding it from granular tabs. 3. Your experience with payer mix, prior auth, denials, and AR in pain management. 4. Confirm you will deliver Excel-only (no third-party BI). 5. Estimated timeline and budget/rate. 6. Confirm US location and availability for Pacific/Mountain-time check-ins. We’ll prioritize proposals that demonstrate industry fluency and Excel modeling depth, with an executive-simple / analyst-powerful design centered on the referral funnel.