Healthcare dashboard: operating metrics that drive action

A healthcare dashboard compares facility utilization, staffed capacity, and service volumes with operating targets over the same dates. Its job is to show where an operations manager needs to investigate a capacity change, a volume gap, or an incomplete export. Keep the business review separate from clinical monitoring and patient risk assessment.

Start with the decisions and the units

Choose the questions before arranging the page: did available capacity change, did activity meet the approved operating plan, and which facility needs a follow-up? A high occupancy rate can reflect more occupied beds, fewer staffed beds, or both. The dashboard needs both counts beside the percentage.

The American Hospital Association's hospital statistics distinguish staffed beds from admissions. Keep that distinction in your workbook: capacity is a stock available at a point in time, while admissions and visits count activity over a period. An admission count is not an occupied-bed count.

For the example below, use occupied beds at the same daily census cutoff and staffed beds available at that cutoff. Adding the daily counts creates occupied and staffed census bed-days for this operating comparison. It does not measure every hour of bed use or every person served. Document your organization's census convention before comparing facilities.

Use the broader operational dashboard guide for the overall review structure. This page concentrates on the facility denominators that make the healthcare figures comparable.

Prepare one row per facility and date

Riverbend Care, an illustrative US hospital operator, combines an approved daily census export with outpatient activity summaries. Its two hospitals provide both inpatient and outpatient services. The rows below cover October 19 and October 20, 2026; they contain facility totals rather than patient records.

Put these columns in A through G, with the header in row 1. Staffed and occupied columns are daily census counts; their period sums are bed-days. The visit target and planned beds are internal operating assumptions, not external standards.

Keep each export's reporting period and approval status with the workbook. Confirm that the facility names map consistently and that the facility-date key is unique in each input. Combine summaries only after checking those keys; joining a daily capacity row to several service categories could repeat the capacity count.

The general KPI examples page covers measure selection across teams. Here, keep a small set with a visible source and a named owner.

Calculate the measures in Excel

First add a validation flag in H2 and fill down through H5:

=IF(AND(COUNT(C2:G2)=5,MIN(C2:G2)>=0,D2<=C2),"Ready","Review")

This catches missing or nonnumeric values, negative counts, and occupied beds above staffed capacity. A zero is still a real input and should represent a confirmed zero, rather than a blank replaced during import. Investigate a failed row before calculating its rate.

Daily occupancy in I2, filled down, is:

=IF(AND(H2="Ready",C2>0),D2/C2,"")

Daily visit target attainment in J2 is:

=IF(AND(H2="Ready",F2>0),E2/F2,"")

Capacity variance in K2 is:

=IF(H2="Ready",C2-G2,"")

Format I and J as percentages. Report a blank rate as unavailable, not as zero performance. The period occupancy formula checks all source rows and the combined denominator:

=IF(AND(COUNTIF(H2:H5,"<>Ready")=0,SUM(C2:C5)>0),SUM(D2:D5)/SUM(C2:C5),"Review source rows")

For a facility slice, after those checks pass, use summed counts. For example, West occupancy is:

=SUMIFS(D2:D5,B2:B5,"West")/SUMIFS(C2:C5,B2:B5,"West")

Microsoft documents SUMIFS as summing values that meet multiple criteria. Add date criteria when the workbook contains more periods, and keep every sum range and criteria range aligned. Covirage is not affiliated with Microsoft or Google.

Work the dashboard from the same rows

The following summary adds each facility's daily counts before dividing. Percentages are rounded to one decimal place; calculations use unrounded values.

The group occupancy is 251 / 290 = 86.6%. Averaging the facility percentages gives (85.0% + 90.0%) / 2 = 87.5%, which gives the smaller facility the same weight as the larger one. Use the ratio of totals instead.

West's October 20 capacity is 40 against a plan of 50: (40 - 50) / 50 = -20.0%. Its occupied count is also lower than the previous day's, yet daily occupancy remains 36 / 40 = 90.0%. That is a capacity question for the operations lead, not evidence of rising demand.

The same rows show why the period total needs a daily exception view. East completed 180 visits against 190 on the first day and 200 against 190 on the second. Its period attainment is 380 / 380 = 100.0%, but that total hides the first day's shortfall.

Prove the totals and assign the next action

The census owner should confirm that East's 170 occupied bed-days plus West's 81 equal the source total of 251. The capacity owner confirms 200 + 90 = 290 staffed bed-days, and the activity owner confirms 380 + 240 = 620 completed visits. The approved plan contributes 200 + 100 = 300 staffed bed-days, making the capacity gap 290 - 300 = -10.

Put the exception, source date, owner, and next review date beside the summary. Ask West's operations lead why staffed capacity fell and ask East's outpatient manager whether the daily visit pattern was expected. A measure identifies a question; the responsible manager establishes the cause.

For a wider set of measures, see healthcare KPIs. The existing healthcare and med-tech analytics overview covers the broader industry context.

Where it goes wrong

• Licensed beds used as staffed capacity. A fixed licensed count can conceal the actual operating constraint. Label the basis and use it consistently.
• Mixed census cutoffs. Morning occupied beds divided by evening staffed beds describe different moments. Align the cutoff before comparison.
• Canceled appointments counted as completed visits. Keep completed activity separate from scheduled demand and use the same status definition as the target.
• Partial periods presented as a trend. A missing facility or late export can look like a volume decline. Display completeness before interpreting the total.
• One threshold applied to every service. Capacity conventions and workloads differ. Have operations approve targets for each comparable service group.

Compare your own facility files in Covirage

Bring your approved facility census, capacity, and activity file or Google Sheet, with the operating targets identified. Covirage maps the columns, its tools compute and check the figures, and the external AI model explains the comparisons with every figure traced to its rows. Create a free Covirage account, set up your business, and bring a sample of your own export.

See Covirage for healthcare for the supporting industry context.