Spotting the Overloaded Branches in an 8-Hospital Network
Sep 2026 · 8 min read
Monthly admissions jumped from about 100 to around 300 in early 2025 and stayed there. I built an interactive Tableau dashboard that shows a hospital operations lead where the load is landing, by branch, department and month, so they can plan against the new normal.
- Role
- Data Analyst / Dashboard Developer, solo project
- Industry
- Healthcare
- Tools
- Tableau (LOD, ranking, running total, map, filters), Google Slides
- Timeline
- 1 week in Sep 2026
- Admissions
- 5,000
- Days average stay
- 4.5
- Admissions per bed
- 2.04
- 2025 vs 2024
- 3.4×
#TL;DR
- Problem
- A hospital operations lead needs to spot branch overload early and rebalance before capacity problems build.
- What I did
- Defined six capacity metrics, built an interactive Tableau dashboard (KPIs, department ranking, map of Java, trend, length of stay, filters), and wrote six prioritized insights in the OBIPR format.
- What I found
- Monthly admissions jumped from about 80 to 110 in 2024 to about 290 to 340 in 2025, 3.4 times more for the year, and stayed there. For its size, Jakarta Selatan is the most stretched branch, and Internal Medicine and Emergency carry the most patients. Plans based on 2024 levels would badly under-provision.
Admissions more than tripled in January 2025 and stayed there.
#1.My role & scope
- Role: Data Analyst / Dashboard Developer, solo project
- What I owned: choosing the angle, defining the metrics, mapping the data relationships, every chart and calculation, dashboard layout and interactivity, insights and the report deck
- Timeline: 1 week in Sep 2026
- Deliverables: Google Slides report + published Tableau Public dashboard
#2.Background
- Context: A hospital network with 8 branches across Java, 8 departments and 64 doctors. 5,000 admissions over 24 months (January 2024 to December 2025).
- Problem: Admissions and length of stay put uneven pressure on each branch and department, and there was no single view to spot overload early.
- Business question: How can the Hospital Operations Lead monitor admission volume and length of stay across all eight branches to spot overload early and rebalance before capacity problems build? (I framed this question myself.)
- Audience / decision maker: Hospital Operations Lead
- Angle chosen: an operational capacity story, because it leads to clear decisions on staffing, beds and patient flow.
#3.Method / Strategy
- Understand the data: mapped the table relationships (admissions to branches, departments and patients, all many-to-one) and set the grain at one row per admission before building anything. 5,000 admissions come from 1,800 patients, so a patient admitted twice counts twice.
- Define metrics: six capacity metrics (see Metrics).
- Core charts: KPI scorecard, branch comparison, trend over time and length of stay distribution, each with a short reason for the chart type and a weaker alternative I ruled out.
- Advanced build:
- LOD calculation: each branch's share of total network admissions, with the network total fixed so it stays correct under any filter:
COUNTD([Admission Id]) / SUM({ FIXED : COUNTD([Admission Id]) }) - Top 5 department ranking
- Running total: cumulative admissions across the 24 months
- Map of the 8 branches across Java, sized by admissions
- Combined into one interactive dashboard with 3 filters applied to every chart: Month (date range), Department and Admission Type. Built in Tableau Desktop, published to Tableau Public.
- LOD calculation: each branch's share of total network admissions, with the network total fixed so it stays correct under any filter:
- Insights: six insights in the OBIPR format (Observation, Business Impact, Isolation, Prioritization, Recommendation), ranked High or Medium.
- Keeping claims honest: after a review round, each insight only claims what the data proves. "Admissions per bed" is a load ratio, not an occupancy rate. A long length of stay is a signal to review, not proof of slow discharges. I also checked the busiest branches against admissions per bed, not only total admissions (insight 2).
#4.Metrics
| Metric | Definition | Why it matters |
|---|---|---|
| Admissions per Branch | Distinct count of admissions by branch | Shows where patient volume lands |
| Admissions per Department | Admissions by department / service line | Shows which clinical areas carry the load |
| Room Class Mix | Share of admissions across Standard, Deluxe and Executive | Shows what kind of beds are in demand |
| Admissions per Bed | Admissions ÷ bed capacity, by branch | Compares load fairly across branches of different sizes (a load ratio, not occupancy) |
| Average Length of Stay | Mean days per stay, by branch | Longer stays tie up beds |
| Monthly Admissions Trend | Admissions per month across 24 months | Shows whether load is growing, and when |
Admissions per Branch
- Definition
- Distinct count of admissions by branch
- Why it matters
- Shows where patient volume lands
Admissions per Department
- Definition
- Admissions by department / service line
- Why it matters
- Shows which clinical areas carry the load
Room Class Mix
- Definition
- Share of admissions across Standard, Deluxe and Executive
- Why it matters
- Shows what kind of beds are in demand
Admissions per Bed
- Definition
- Admissions ÷ bed capacity, by branch
- Why it matters
- Compares load fairly across branches of different sizes (a load ratio, not occupancy)
Average Length of Stay
- Definition
- Mean days per stay, by branch
- Why it matters
- Longer stays tie up beds
Monthly Admissions Trend
- Definition
- Admissions per month across 24 months
- Why it matters
- Shows whether load is growing, and when
#5.Solution
- Dashboard: View dashboard (opens in a new tab)
- Report deck: View deck (opens in a new tab)
- What's on the live dashboard: KPI scorecard (5,000 admissions, 4.5 day average stay, 2.04 admissions per bed), Top 5 departments, map of admissions across Java, monthly admissions trend, length of stay distribution. Filters for month, department and admission type apply to every chart.
- Also built (shown in the report deck): LOD branch share of total, running total of admissions. The running total chart on this page is rebuilt in code from the monthly counts.
#6.Result / Output
Headline: Monthly admissions went from about 80 to 110 a month in 2024 to about 290 to 340 a month in 2025: 1,146 admissions in 2024 vs 3,854 in 2025, 3.4 times more, and it never dropped back. That resets the capacity baseline the Operations Lead has to plan against.
Key insights (OBIPR)
Insight 1 · High
Admissions more than tripled in January 2025 and stayed there.
Monthly admissions jumped from about 80 to 110 in 2024 to about 290 to 340 from January 2025, and stayed high. 2025 is a structural shift, not noise: every month of 2025 is at least 2.6 times the busiest month of 2024.
Show data
| Month | Admissions |
|---|---|
| Jan 2024 | 107 |
| Feb 2024 | 87 |
| Mar 2024 | 84 |
| Apr 2024 | 108 |
| May 2024 | 99 |
| Jun 2024 | 110 |
| Jul 2024 | 80 |
| Aug 2024 | 106 |
| Sep 2024 | 93 |
| Oct 2024 | 94 |
| Nov 2024 | 85 |
| Dec 2024 | 93 |
| Jan 2025 | 336 |
| Feb 2025 | 293 |
| Mar 2025 | 327 |
| Apr 2025 | 303 |
| May 2025 | 341 |
| Jun 2025 | 293 |
| Jul 2025 | 317 |
| Aug 2025 | 336 |
| Sep 2025 | 331 |
| Oct 2025 | 331 |
| Nov 2025 | 304 |
| Dec 2025 | 342 |
Insight 2 · High
Jakarta Selatan is the busiest branch, and the most stretched for its size.
Jakarta Selatan has the most admissions (905) and the highest admissions per bed (2.32 vs 2.04 for the network). Jakarta Pusat is 2nd on volume (809) but below average per bed (1.93), because it has the most beds. Surabaya is 2nd per bed (2.13).
Show data
| Branch | Admissions | Admissions per bed | Avg length of stay (days) |
|---|---|---|---|
| Jakarta Selatan | 905 | 2.32 | 4.58 |
| Jakarta Pusat | 809 | 1.93 | 4.60 |
| Surabaya | 765 | 2.13 | 4.56 |
| Bandung | 635 | 2.05 | 4.34 |
| Semarang | 557 | 1.99 | 4.34 |
| Yogyakarta | 486 | 2.03 | 4.44 |
| Malang | 437 | 1.90 | 4.20 |
| Cirebon | 406 | 1.85 | 4.39 |
Insight 3 · High
Internal Medicine and Emergency carry the most patients.
Internal Medicine (970) and Emergency (863) top all departments. Load concentrates in two clinical areas.
Show data
| Department | Admissions |
|---|---|
| Internal Medicine | 970 |
| Emergency | 863 |
| Orthopedics | 583 |
| Pediatrics | 561 |
| Obstetrics & Gynecology | 539 |
Insight 4 · High
It took a year to reach 1,146 admissions, and one more year to reach 5,000.
Cumulative admissions steepen sharply from 2025: it took 12 months to reach 1,146, and 12 more to reach 5,000. The surge is sustained, not a one-month spike.
Show data
| Month | Cumulative admissions |
|---|---|
| Jan 2024 | 107 |
| Feb 2024 | 194 |
| Mar 2024 | 278 |
| Apr 2024 | 386 |
| May 2024 | 485 |
| Jun 2024 | 595 |
| Jul 2024 | 675 |
| Aug 2024 | 781 |
| Sep 2024 | 874 |
| Oct 2024 | 968 |
| Nov 2024 | 1,053 |
| Dec 2024 | 1,146 |
| Jan 2025 | 1,482 |
| Feb 2025 | 1,775 |
| Mar 2025 | 2,102 |
| Apr 2025 | 2,405 |
| May 2025 | 2,746 |
| Jun 2025 | 3,039 |
| Jul 2025 | 3,356 |
| Aug 2025 | 3,692 |
| Sep 2025 | 4,023 |
| Oct 2025 | 4,354 |
| Nov 2025 | 4,658 |
| Dec 2025 | 5,000 |
Insight 5 · Medium
Most stays are short, but a small tail holds beds much longer.
Most stays are short (stays of 2 to 5 days hold most patients), with a small tail of 7+ day stays. A few long stays quietly use up capacity.
Show data
| Days in hospital | Admissions |
|---|---|
| 1 | 244 |
| 2 | 474 |
| 3 | 843 |
| 4 | 1,071 |
| 5 | 1,011 |
| 6 | 673 |
| 7 | 389 |
| 8 | 199 |
| 9 | 68 |
| 10 | 22 |
| 11 | 6 |
Insight 6 · Medium
Emergency is the 2nd busiest department, and its admissions can't be scheduled.
Emergency is the 2nd busiest department (863), and its admissions can't be scheduled. Buffers matter most where demand can't be smoothed.
Show data
| Department | Admissions |
|---|---|
| Internal Medicine | 970 |
| Emergency | 863 |
| Orthopedics | 583 |
| Pediatrics | 561 |
| Obstetrics & Gynecology | 539 |
All six insights (OBIPR)
| # | Observation | Business impact | Isolation | Priority | Recommendation |
|---|---|---|---|---|---|
| 1 | Monthly admissions jumped from about 80 to 110 in 2024 to about 290 to 340 from January 2025, and stayed high | Planning based on 2024 would badly under-provision | 2025 is a structural shift, not noise: every month of 2025 is at least 2.6 times the busiest month of 2024 | High | Reset staffing to 2025 levels and investigate what caused the surge |
| 2 | Jakarta Selatan has the most admissions (905) and the highest admissions per bed (2.32 vs 2.04 for the network). Jakarta Pusat is 2nd on volume (809) but below average per bed (1.93), because it has the most beds. Surabaya is 2nd per bed (2.13) | Jakarta Selatan is the branch most stretched for its size. Jakarta Pusat's high volume is mostly explained by its size | The strain sits at Jakarta Selatan, with Surabaya next, not across "Jakarta" as a whole | High | Add staffing and bed capacity at Jakarta Selatan first and watch Surabaya. Test moving some planned (non-emergency) admissions to nearby Jakarta Pusat, which has room per bed |
| 3 | Internal Medicine (970) and Emergency (863) top all departments | These two departments take the most patients | Load concentrates in two clinical areas | High | Prioritize staffing and resources for Internal Medicine and Emergency |
| 4 | Cumulative admissions steepen sharply from 2025: it took 12 months to reach 1,146, and 12 more to reach 5,000 | The surge is sustained, not a one-month spike | Capacity pressure is building faster | High | Forecast capacity against the steeper 2025 trend |
| 5 | Most stays are short (stays of 2 to 5 days hold most patients), with a small tail of 7+ day stays | Long stays tie up beds more than their share | A few long stays quietly use up capacity | Medium | Review (not force) 7+ day stays to confirm they're clinically needed |
| 6 | Emergency is the 2nd busiest department (863), and its admissions can't be scheduled | Emergency load is high and unpredictable | Buffers matter most where demand can't be smoothed | Medium | Keep buffer capacity and flexible staffing for Emergency instead of running it at full use |
Insight 1
- Observation
- Monthly admissions jumped from about 80 to 110 in 2024 to about 290 to 340 from January 2025, and stayed high
- Business impact
- Planning based on 2024 would badly under-provision
- Isolation
- 2025 is a structural shift, not noise: every month of 2025 is at least 2.6 times the busiest month of 2024
- Priority
- High
- Recommendation
- Reset staffing to 2025 levels and investigate what caused the surge
Insight 2
- Observation
- Jakarta Selatan has the most admissions (905) and the highest admissions per bed (2.32 vs 2.04 for the network). Jakarta Pusat is 2nd on volume (809) but below average per bed (1.93), because it has the most beds. Surabaya is 2nd per bed (2.13)
- Business impact
- Jakarta Selatan is the branch most stretched for its size. Jakarta Pusat's high volume is mostly explained by its size
- Isolation
- The strain sits at Jakarta Selatan, with Surabaya next, not across "Jakarta" as a whole
- Priority
- High
- Recommendation
- Add staffing and bed capacity at Jakarta Selatan first and watch Surabaya. Test moving some planned (non-emergency) admissions to nearby Jakarta Pusat, which has room per bed
Insight 3
- Observation
- Internal Medicine (970) and Emergency (863) top all departments
- Business impact
- These two departments take the most patients
- Isolation
- Load concentrates in two clinical areas
- Priority
- High
- Recommendation
- Prioritize staffing and resources for Internal Medicine and Emergency
Insight 4
- Observation
- Cumulative admissions steepen sharply from 2025: it took 12 months to reach 1,146, and 12 more to reach 5,000
- Business impact
- The surge is sustained, not a one-month spike
- Isolation
- Capacity pressure is building faster
- Priority
- High
- Recommendation
- Forecast capacity against the steeper 2025 trend
Insight 5
- Observation
- Most stays are short (stays of 2 to 5 days hold most patients), with a small tail of 7+ day stays
- Business impact
- Long stays tie up beds more than their share
- Isolation
- A few long stays quietly use up capacity
- Priority
- Medium
- Recommendation
- Review (not force) 7+ day stays to confirm they're clinically needed
Insight 6
- Observation
- Emergency is the 2nd busiest department (863), and its admissions can't be scheduled
- Business impact
- Emergency load is high and unpredictable
- Isolation
- Buffers matter most where demand can't be smoothed
- Priority
- Medium
- Recommendation
- Keep buffer capacity and flexible staffing for Emergency instead of running it at full use
Recommendations for the Operations Lead (in priority order)
- Reset staffing baselines to 2025 levels and investigate what drove the surge. (High)
- Add staffing and bed capacity at Jakarta Selatan first, and keep an eye on Surabaya. Test moving some planned admissions to Jakarta Pusat, which has spare room per bed. (High)
- Prioritize staffing and resources for Internal Medicine and Emergency. (High)
- Forecast capacity against the steeper 2025 trend. (High)
- Review (not force) 7+ day stays to confirm they're clinically needed. (Medium)
- Keep buffer capacity and flexible staffing for Emergency. (Medium)
#7.Reflection
- The biggest lesson was about wording. A metric can look like it proves more than it does. Calling a load ratio "occupancy" would have been a wrong claim, so I wrote every insight to match exactly what the data supports.
- Admissions per bed shows load over two years, not daily peaks. It tells which branches carry the most for their size. Daily bed data would show when each branch actually runs full, so that's what I'd add next.
Data note: Built on a synthetic dataset of a hospital network across Java: 5,000 admissions, 1,800 patients, 8 branches, 8 departments, 64 doctors, January 2024 to December 2025.
View dashboard (opens in a new tab)View deck (opens in a new tab)