An interactive Power BI dashboard that models call center cost-per-appointment under multiple scenarios, enabling leadership to test operational assumptions and forecast financial outcomes in real time.
A healthcare call center operation was planning its 2025 budget with no reliable way to forecast cost-per-appointment under different operational scenarios. The team managed hundreds of thousands of outbound dials per month, resulting in 60,000–100,000+ scheduled appointments — but the cost to produce each completed appointment depended on a web of interconnected variables: agent productivity (LCPH), cancellation rates, level of effort (dials per scheduled appointment), staffing utilization, attrition, IT costs, and rebadge billing.
Leadership needed to answer questions like: "If we improve LCPH by 10%, what happens to cost per appointment? What if cancellation rates rise at the same time? What combination of variables gets us to a $30 target?" These questions were being answered with one-off spreadsheets that broke every time an assumption changed.
I designed and built a scenario planning model in Power BI that treats each operational variable as an adjustable input. The model calculates cost-per-appointment in real time as users toggle variables on or off and adjust monthly values. Two independent scenarios can be configured and compared against the base forecast simultaneously, with trend lines and weighted averages updating instantly.
Every variable in the dashboard feeds into a multi-step chain. Adjust any input and the entire chain recalculates in real time.
Explore the live dashboard below. Try adjusting variables, switching between KPI views, and comparing scenarios.
Compare up to 3 trajectories simultaneously: the base forecast plus two independently configurable scenarios, each with its own variable assumptions. Toggle comparison on or off with a single click.
A single dropdown swaps the entire dashboard between All-in Cost per Appt, LCPH, Cancel Rate, Completed Appts, Level of Effort, and Productive Utilization — each with its own weighted average and scenario lines.
When actual data becomes available, the dashboard overlays realized performance against the forecast line, making it easy to spot where the model was accurate and where reality diverged.
LCPH, completed appointments, cancellation rate, and level of effort can each be toggled on/off and set to custom values per month with individual on/off switches, allowing granular what-if analysis.
Worksheet-level constants include FTE hours, multi-tier billing rates (Level 1/2/3 + training), new hire training hours, training cost increases, and training attrition — all feeding directly into the cost model.
An always-visible weighted average card shows the annual result for the selected KPI, recalculating instantly as scenario variables change. Weighted by monthly appointment volume, not a simple average.
Variables are organized into two tabs: Monthly Avg Variables (single values applied across the year) and Worksheet Variables (structural constants like billing rates and FTE hours). Each tab holds a different class of assumption.
Line chart with data labels showing month-over-month progression and divergence between scenarios. Cost curves steepening into Q4 (driven by rising cancellation rates and LOE) are immediately visible.
The dashboard models the full chain of variables that drive cost per appointment in a healthcare call center operation:
| Variable | Type | What It Controls |
|---|---|---|
| Monthly Inputs (per-month adjustable) | ||
| LCPH (Lives Changed Per Hour) | Monthly toggle | Agent productivity — the core efficiency metric billed to the client |
| Completed Appointments | Monthly toggle | Volume of successfully completed appointments after cancellations |
| Cancellation Rate | Monthly toggle | Percentage of scheduled appointments canceled — ranges from 25% (Jan) to 39% (Dec) |
| Level of Effort | Monthly toggle | Dials required per scheduled appointment — higher = less efficient outreach |
| Monthly Average Variables | ||
| Productive Utilization | Constant | Percentage of agent time on productive work vs. idle/admin (default: 84%) |
| Attrition % | Constant | Annual agent turnover rate — drives training cost and capacity gaps |
| IT Cost | Constant | Per-seat technology cost allocated to the program |
| Other (Downtime) | Constant | Non-productive costs from system downtime or transition periods |
| Rebadge Billing | Constant | Revenue offset from rebadged staff billing to adjust net cost |
| Contacts % of Dials | Constant | Conversion rate from dials to actual human contacts |
| Call % of Dials | Constant | Percentage of dials that result in answered calls |
| Contact % of Calls | Constant | Conversion from answered calls to meaningful contacts |
| IB % of Appts | Constant | Inbound appointment share as percentage of total |
| Worksheet Variables (billing & training structure) | ||
| FTE Hours | Constant | Monthly billable hours per full-time equivalent agent (default: 160) |
| Level 1 Billing Rate | Constant | Hourly billing rate for standard production agents ($37.50) |
| Level 2 Billing Rate | Constant | Hourly rate for mid-tier or specialized agents ($37.25) |
| Level 3 Billing Rate | Constant | Hourly rate for senior or offshore agents ($37.00) |
| Training Billing Rate | Constant | Hourly rate charged during agent training periods ($37.50) |
| New Hire Training | Constant | Hours required to train each new hire before production (40 hours) |
| Training $$ Increase | Constant | Percentage uplift on training costs above base rate (50%) |
| Training Attrition | Constant | Percentage of trainees who leave during training period (30%) |
Data foundation: The model is built on 11 months of historical operational data (Jan–Nov 2024) including total dials (1.8M–5.7M per month), scheduled appointments, completed appointments, cancellation rates, and staffing metrics. This historical data provides the baseline forecast trajectory and serves as the “Actual” overlay when comparing against projected scenarios.
Scenario engine: Each scenario (Scenario 1, Scenario 2) applies a different set of assumptions to the same calculation framework. For example, Scenario 1 might use the client’s provided staffing matrix inputs, while Scenario 2 asks: “What if we increase LCPH by 6% — can we hit the $30 cost target?” Both run simultaneously with independent variable values. The “Compare Scenarios” toggle switches between single-line and multi-line comparison views.
KPI-switchable view: The entire dashboard reframes around whichever KPI is selected from the dropdown: All-in Cost per Appt, LCPH, Cancel Rate, Completed Appts, Level of Effort, or Productive Utilization. The weighted average card, trend lines, and scenario comparisons all recalculate for the selected metric — effectively giving leadership 6 dashboards in one.
Cost calculation chain: The model computes cost per appointment through a multi-step chain: dials → contacts → scheduled appointments → net appointments (after cancellations) → billed staff hours (at tier-specific billing rates) → FTE required → production invoice → training cost (driven by attrition rate, training hours, and training attrition) → total cost → cost per completed appointment → adjusted cost (after rebadge billing offset). Every step is transparent and adjustable.
Real-time calculation: All DAX measures recalculate instantly when any variable is adjusted. The weighted average, trend lines, and data labels all respond in real time, making it possible to run dozens of scenarios in a single meeting.
The scenario model became the primary tool for 2025 budget planning discussions between operations leadership and the client. Instead of debating assumptions in static spreadsheets, the team could adjust variables live during meetings and immediately see the cost impact. Key outcomes:
Time to model a new scenario (previously hours in Excel)
Identified the exact variable combination to hit cost goal
Replaced 4+ disconnected spreadsheet models