Ravindra BagaleCourses & study guides मराठी Track your progress

22. KPI in All Industries

22.8 Healthcare

By Last updated:

Hospital and clinic interviews ask bed occupancy, average length of stay (ALOS), and whether appointments were actually seen. Footfall on a poster is not a KPI. This is a fictional trust, not a named hospital's result.

KPI What the number means Who uses it Action it drives
Occupancy Patient-days divided by beds times days in the period. Medical superintendent High occupancy with a long stay is blocked beds, not "success".
ALOS Patient-days divided by discharges. Ward in-charge A rising ALOS on the same ward is a discharge-planning problem.
Show-up % Appointments seen divided by appointments booked. OPD manager No-shows are a call list. Do not add a doctor for patients who did not come.

Fictional sample rows. One week (7 days). Type these rows into a new sheet from cell A1, press Ctrl + T, and name the table tblWards (Table Design › Table Name).

Ward City Beds Days Patient-days Discharges Appointments Seen
General Pune 20 7 112 28 200 160
Maternity Pune 10 7 56 14 80 70
General Nashik 16 7 98 20 120 110
Children Nashik 8 7 28 10 60 40
General Kolhapur 12 7 42 8 90 50
General Nagpur 18 7 100 16 140 120

Excel formulas for the whole file (type them in empty cells below or beside the table):

KPI Excel formula Result Number format
Occupancy =SUM(tblWards[Patient-days])/SUMPRODUCT(tblWards[Beds],tblWards[Days]) 74.1% 0.0%
ALOS =SUM(tblWards[Patient-days])/SUM(tblWards[Discharges]) 4.54 days 0.00
Show-up % =SUM(tblWards[Seen])/SUM(tblWards[Appointments]) 79.7% 0.0%

Calculated columns (one result per row). Click the first empty column of the table, type the header, then the formula. The Table fills every row by itself.

New column Formula in the first row
Occupancy =[@[Patient-days]]/([@Beds]*[@Days])
ALOS =[@[Patient-days]]/[@Discharges]
Show-up % =[@Seen]/[@Appointments]

Expected result per row:

Ward Occupancy ALOS Show-up %
General (Pune) 80.0% 4.00 days 80.0%
Maternity 80.0% 4.00 days 87.5%
General (Nashik) 87.5% 4.90 days 91.7%
Children 50.0% 2.80 days 66.7%
General (Kolhapur) 50.0% 5.25 days 55.6%
General (Nagpur) 79.4% 6.25 days 85.7%

One city with SUMIFS. Occupancy for Pune only:

=SUMIFS(tblWards[Patient-days],tblWards[City],"Pune")/SUMPRODUCT((tblWards[City]="Pune")*tblWards[Beds]*tblWards[Days])

Result for Pune: 80.0%. Change "Pune" to a cell reference (for example $K$1) and add a drop-down, and the same formula answers for any city.

Good KPI Vanity metric
From the same rows Nashik children show-up 66.7% and occupancy 50.0% Pune general 200 appointments
Why Twenty of sixty Nashik children did not come. The ward is half empty. Pune's appointment card is the largest. Forty of those 200 also did not come, which the card hides.

What the manager does. The Nashik manager calls the 20 missed children's appointments before asking for more beds. The Pune superintendent does not add a general-ward bed this week: occupancy is 80% and ALOS is 4 days, which is the planned stay in this file. The 40 Pune no-shows are a phone list, not a construction plan. Kolhapur general show-up is 50 ÷ 90 = 55.6% and occupancy is 42 ÷ 84 = 50.0%. Same action, different city.

Practice workbook and guide: Hospitals dashboard workbook (Excel) · How to build the Hospitals dashboard in Excel. The workbook data is also fictional.

Ravindra Bagale's Tip

Occupancy needs patient-days divided by beds × days. SUMPRODUCT does the beds × days part for the whole table in one step.