Blog · Board and management reporting · Healthcare
Build a healthcare operating dashboard from aggregate site reports. Use a reusable Excel layout to compare service volume, staffing costs and budget variances, then reconcile every total.
Healthcare dashboards in Excel compare site service volumes, staffing costs and budget variances from aggregate operating reports. A useful workbook shows the inputs behind each ratio and proves that its totals match the source reports. This layout covers operational and financial comparisons; it uses no patient records or clinical measures.
The broader healthcare dashboard guide covers the reporting questions and ownership. Here the task is narrower: build repeatable sheets, enter the formulas and check the results before the monthly review.
Use one row per site, service category and month. In this example, a fictional US outpatient organization compares completed visits for the same service category across its locations. A visit is the completed service counted by the operating report; it is not a count of distinct people.
Finance defines staffing cost as wages, employer payroll costs and agency staffing expense for that service. Actuals include the approved month-end accruals. The staffing budget has the same cost boundary. Leave revenue, claims and billing activity out of this workbook.
Write these definitions above the dashboard, together with the reporting month, source filenames and report owners. If sites count different activities, separate the categories before comparing cost per visit. A clean spreadsheet cannot make different denominators comparable.
Keep supplied values separate from calculations. This structure makes the monthly refresh easy to inspect:
| Sheet | Contents | Control |
|---|---|---|
| Inputs | Period, site, completed visits, actual staffing cost, staffing budget | One row per site and period for the selected service |
| Dashboard | Site totals, cost variances and cost per visit | Ratios calculated from the underlying totals |
| Checks | Source totals, workbook totals and differences | Every reconciliation difference must equal zero |
Paste the next table into Inputs, with headers in row 1 and site records in rows 2 through 4. Columns A through E hold supplied values. Columns F through H will hold calculations. All records cover September 2026; the table shows amounts in USD and percentages rounded to one decimal place.
| Period | Site | Completed visits | Actual staffing cost (USD) | Staffing budget (USD) | Cost variance (USD) | Cost variance | Cost per visit (USD) |
|---|---|---|---|---|---|---|---|
| September 2026 | North | 1,200 | 96,000 | 90,000 | 6,000 | 6.7% | 80.00 |
| September 2026 | South | 800 | 72,000 | 68,000 | 4,000 | 5.9% | 90.00 |
| September 2026 | West | 1,000 | 75,000 | 78,000 | -3,000 | -3.8% | 75.00 |
| Total | 3,000 | 243,000 | 236,000 | 7,000 | 3.0% | 81.00 |
Select the site records and choose Home, Format as Table, confirming that the range has headers. Microsoft documents Excel tables as a way to manage related rows, with filtering and calculated columns. Keep the displayed total outside the input records so it cannot become another site when you summarize them.
The sign convention is actual minus budget. A positive cost variance means spending above budget; a negative variance means spending below budget. Enter these formulas in the specified cells and fill down through row 4:
F2 =D2-E2
G2 =IF(E2=0,"",F2/E2)
H2 =IF(C2=0,"",D2/C2)
The labels before the equals signs identify the destination cells; enter only the formula beginning with =. Format G as a percentage and H as currency. A blank ratio signals an unavailable denominator, rather than a zero cost.
For the total row, sum the supplied amounts and divide those totals:
C5 =SUM(C2:C4)
D5 =SUM(D2:D4)
E5 =SUM(E2:E4)
F5 =D5-E5
G5 =IF(E5=0,"",F5/E5)
H5 =IF(C5=0,"",D5/C5)
For a dashboard with multiple months, use a real period date in Inputs column A. Put the selected site in Dashboard A2 and the selected period in B1. Microsoft's SUMIFS guidance explains how to sum rows meeting multiple conditions:
=SUMIFS(Inputs!$D$2:$D$1000,Inputs!$B$2:$B$1000,$A2,Inputs!$A$2:$A$1000,$B$1)
That returns actual staffing cost for the selected site and period. Use the same criteria for visits and budget, changing the sum range. Extend every range together when the workbook grows.
The organization spent $243,000 against a $236,000 budget. The difference is $7,000, or 7,000 / 236,000 = 3.0% above budget after rounding. North contributes $6,000 and South $4,000, offset by West's $3,000 underspend.
Organization cost per visit is 243,000 / 3,000 = $81.00. Calculate that from the totals, rather than averaging site ratios. South has the highest displayed cost per visit, but this table alone does not establish the cause. The operating owner should check service mix and coverage; finance should check cost allocation and accruals.
On Checks, compare the 3,000 visits with the approved volume report and $243,000 with the staffing cost report. Compare $236,000 with the approved budget version. Subtract each source control total from the matching workbook total. Also check that the site variances reconcile: 6,000 + 4,000 - 3,000 = 7,000.
For a broader discussion of files and question boundaries, see healthcare business analytics. Keep this workbook's monthly comparison tied to its documented inputs.
Archive the prior period's approved view, replace or append the new aggregate records, and confirm the selected month. Check that every expected site appears exactly once for the selected service and period. Investigate missing rows before distributing the dashboard.
Retain the budget version used for the original comparison. If finance approves a revised budget, label the version visibly so a changed variance has an explanation. Excel analysis starts with the same discipline: identify the file, its columns and the business question.
Covirage is not affiliated with Microsoft.
Bring an aggregate site report with completed volumes, actual staffing costs and the matching budget. Covirage maps the columns, its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See Covirage for healthcare businesses for the supporting industry context.
For a dashboard built from aggregate operating reports, start with completed service volume, actual staffing cost, staffing budget, dollar variance and cost per completed service. Choose one consistent service definition across sites. Separate different service types when adding their counts would obscure differences in work.
Yes. This workbook uses site and month totals from existing operating and finance reports. It needs no names, dates of birth, diagnoses or individual encounter records. Keep the comparison at the aggregate level supplied by the business report owners.
Divide actual staffing costs by completed visits for the same site, service and reporting period. For the organization total, divide summed costs by summed visits. Document which staffing costs are included, and leave the ratio unavailable when volume is zero.
No. A cost variance identifies a difference from the approved budget. Service mix, operating hours, coverage requirements, accrual timing or volume changes can explain it. Ask the site and finance owners to review the underlying reports before deciding what action is appropriate.