An interactive scenario planning dashboard that models call center cost-per-appointment under three simultaneous trajectories — enabling leadership to test 20+ operational variables and forecast financial outcomes in real time, entirely outside Power BI.
A healthcare call center operation managing millions of outbound dials per month needed to forecast 2025 cost-per-appointment under different operational assumptions. The team was relying on disconnected spreadsheets that broke whenever an input changed — making it impossible to answer questions like "What if LCPH improves 10% but cancellation rates also rise?" in real time.
This project rebuilds the Power BI scenario model as a standalone web application. Three independent scenarios — Forecast, Scenario 1 (+6% LCPH), and Scenario 2 (+10% LCPH, -3% cancel rate) — run simultaneously through the same cost calculation chain, with every variable adjustable and every chart showing all three trajectories side by side.
Every variable feeds into a multi-step chain. Adjust any input and the entire chain recalculates across all three scenarios simultaneously:
All charts in this case study display three trajectories simultaneously so you can visually compare how different assumptions change outcomes. The scenarios are:
Under the base forecast, cost per completed appointment starts at $28.50 in January and climbs to $34.80 by December — driven by rising cancellation rates (25% → 39%) and increasing level-of-effort (more dials per appointment as the year progresses). This 22% cost escalation through the year is the central challenge for budget planning.
Scenario 1 (6% LCPH improvement) flattens the curve noticeably, peaking at $32.40 in December. But only Scenario 2 — combining a 10% LCPH gain with a 3-point reduction in cancellation rates — keeps the weighted average under the $30 target across the full year.
Key insight: Improving LCPH alone (Scenario 1) reduces costs by ~$2/appt on average but isn't enough to hit the $30 target. You need both an efficiency gain and a cancellation rate improvement (Scenario 2) to cross the threshold — the model quantifies exactly how much of each.
A 1-percentage-point increase in cancellation rate adds approximately $0.45 to cost per appointment. Over the base forecast, cancellation rates rise from 25% in January to 39% by December — a 14-point swing that alone accounts for over $6 of the Q4 cost escalation.
Scenario 2's 3-point cancellation reduction saves approximately $1.35/appt across every month, compounding with the LCPH improvement to produce significant savings by year end. This makes cancellation rate reduction the highest-ROI operational lever available.
LCPH (Lives Changed Per Hour) is the core productivity metric — how many completed appointments each agent produces per billed hour. In the base forecast, LCPH ranges from 2.08 to 2.30 across the year. Scenario 2 pushes this to 2.29–2.53, meaning each agent handles roughly 0.2 more appointments per hour.
That seemingly small efficiency gain, compounded across 884K annual appointments, reduces total billed hours by approximately 7,000 — translating to roughly $260K in annual production cost savings.
At 5% monthly attrition, a 215-FTE operation replaces roughly 10–11 agents per month. Each new hire requires 40 hours of training at a 50% cost uplift, with 30% of trainees leaving during training itself — meaning the operation effectively trains 14–15 people to net 10–11 replacements.
This training overhead adds $1.80–$2.20 per completed appointment — a cost that's invisible in simple spreadsheet models but becomes clearly visible when the full cost chain is calculated.
Key insight: Reducing monthly attrition from 5% to 3% would save approximately $180K annually in training costs alone — making retention programs a potentially higher-ROI investment than productivity improvements.
The model's primary question: "What combination of variables gets us to a $30 weighted average cost per appointment?" After testing dozens of combinations, the answer is specific:
Neither lever alone is sufficient. A 10% LCPH gain without cancellation improvement yields $30.85 (Scenario 1). A 3-point cancellation reduction without LCPH improvement yields $31.10. Only the combination (Scenario 2) breaks through the $30 barrier — and the model shows this instantly.
Adjust the key variables below and watch all three scenarios recalculate in real time. Every chart shows Forecast, Scenario 1, and Scenario 2 simultaneously.
All charts render Forecast, Scenario 1, and Scenario 2 as overlaid lines/bars — making divergence and convergence points immediately visible without any toggling.
A single dropdown reframes the main trend chart between Cost/Appt, LCPH, Cancel Rate, Completed Appts, and Level of Effort — each with all 3 scenario lines.
LCPH boost percentages, cancellation reduction, billing rates, FTE hours, utilization, attrition, and rebadge offset — all feeding directly into the cost chain.
Shows all 3 scenarios' cost per appointment side-by-side with shading to indicate which scenario wins each month.
Red dashed line shows actual Jan–Nov performance against the forecast, making model accuracy immediately visible.
Stacked bar chart decomposes monthly cost into production, training, IT overhead, and rebadge offset — revealing hidden cost drivers.
Dual-axis chart tracking FTE requirements and billed hours across scenarios, highlighting the staffing implications of productivity changes.
Single HTML file, no build tools, no server, no database, no BI subscription. Static hosting only.
The dashboard models the full chain of variables that drive cost per appointment:
| Variable | Type | What It Controls |
|---|---|---|
| Monthly Inputs | ||
| LCPH | Monthly | Agent productivity — completed appointments per billed hour |
| Completed Appointments | Monthly | Volume of successfully completed appointments after cancellations |
| Cancellation Rate | Monthly | % of scheduled appointments canceled (25%→39% through the year) |
| Level of Effort | Monthly | Dials required per scheduled appointment |
| Structural Constants | ||
| Productive Utilization | Constant | % of agent time on productive work (default: 84%) |
| Billing Rate | Constant | Blended hourly rate across L1/L2/L3 tiers ($37.25 avg) |
| FTE Hours | Constant | Monthly billable hours per full-time equivalent (160) |
| Monthly Attrition | Constant | Agent turnover rate driving training cost (5%) |
| IT Cost / FTE | Constant | Per-seat technology allocation ($150) |
| Rebadge Offset | Constant | Revenue offset from rebadged staff billing ($12K/mo) |
The interactive model enables real-time scenario testing that previously required hours of spreadsheet manipulation. Key outcomes:
Time to model a new scenario (previously hours)
Identified exact variable combination to hit goal
Replaced 4+ disconnected spreadsheet models
Work With Us
Book a free 20-minute diagnostic and let's explore what's possible with your data.