Back

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

  1. 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.
  2. Define metrics: six capacity metrics (see Metrics).
  3. 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.
  4. 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.
  5. Insights: six insights in the OBIPR format (Observation, Business Impact, Isolation, Prioritization, Recommendation), ranked High or Medium.
  6. 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

  • 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.

Hospital capacity dashboard

Rebuilt from the same data as the Tableau version.

Months

Admission type

Admissions

5,000

Avg stay (days)

4.5

Admissions per bed

2.04

Busiest branch

Jakarta Selatan

905 admissions

Admissions by month

Drag across the chart to pick months

Length of stay

Admissions by department

Admissions by branch

and admissions per bed

#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
MonthAdmissions
Jan 2024107
Feb 202487
Mar 202484
Apr 2024108
May 202499
Jun 2024110
Jul 202480
Aug 2024106
Sep 202493
Oct 202494
Nov 202485
Dec 202493
Jan 2025336
Feb 2025293
Mar 2025327
Apr 2025303
May 2025341
Jun 2025293
Jul 2025317
Aug 2025336
Sep 2025331
Oct 2025331
Nov 2025304
Dec 2025342

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
BranchAdmissionsAdmissions per bedAvg length of stay (days)
Jakarta Selatan9052.324.58
Jakarta Pusat8091.934.60
Surabaya7652.134.56
Bandung6352.054.34
Semarang5571.994.34
Yogyakarta4862.034.44
Malang4371.904.20
Cirebon4061.854.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
DepartmentAdmissions
Internal Medicine970
Emergency863
Orthopedics583
Pediatrics561
Obstetrics & Gynecology539

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
MonthCumulative admissions
Jan 2024107
Feb 2024194
Mar 2024278
Apr 2024386
May 2024485
Jun 2024595
Jul 2024675
Aug 2024781
Sep 2024874
Oct 2024968
Nov 20241,053
Dec 20241,146
Jan 20251,482
Feb 20251,775
Mar 20252,102
Apr 20252,405
May 20252,746
Jun 20253,039
Jul 20253,356
Aug 20253,692
Sep 20254,023
Oct 20254,354
Nov 20254,658
Dec 20255,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 hospitalAdmissions
1244
2474
3843
41,071
51,011
6673
7389
8199
968
1022
116

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
DepartmentAdmissions
Internal Medicine970
Emergency863
Orthopedics583
Pediatrics561
Obstetrics & Gynecology539

All six insights (OBIPR)

  • 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)

  1. Reset staffing baselines to 2025 levels and investigate what drove the surge. (High)
  2. 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)
  3. Prioritize staffing and resources for Internal Medicine and Emergency. (High)
  4. Forecast capacity against the steeper 2025 trend. (High)
  5. Review (not force) 7+ day stays to confirm they're clinically needed. (Medium)
  6. 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)