Sharpening the Image: Data Visualization for Clinic Workflow (Clinical Staffing Workload Tool Evolution)

Matching staffing to workload is crucial to efficient operations. At a major academic medical center, the journey to optimize staffing in the ambulatory care setting began in 2017 with a workgroup of administrators and analysts to develop a system to accurately track staffing. Focusing in on nurses and medical assistants (MA), the workgroup electronic medical record (EMR) for patient messaging by ambulatory care contracted full-time equivalent (cFTE) nurse visits, nurse/MA-assisted procedures, patient per sessions, and forms encounters. This data was then compiled in a spreadsheet format.
The spreadsheet was rolled out to the clinics by geographical area in 2018. Each clinic was provided with a customizable staffing workload too kit to begin the process. A limitation of the early tool was the labor-intensive manual input of the data. In 2020, the spreadsheet was move from Excel to data visualization tool Power BI to allow results to automatically populate in a standardized model. Clinic specific models, such as an infusion specific model, were added as well as a rooming stetting feature. This tool became a step in the position request process.
A 2021 update added improved formatting, the ability to specify roles for activities, and the optional projected adjustment of cFTEs and/or visit encounter. The most up-to-date version implemented in 2022 allows greater customization. Clinics may upload outside images or specify the max daily average number of nurses needed to support operations. A page was added detailing historical encounter responsibility and another to list the calculated ambulatory care cFTE and available hours of providers for each clinic. It features a seven-step process to complete the tool, with data sourced from the EMR in three-month increments. Each panel allows users to enter data on expected workflows or utilize existing EMR data. After completion of the seven panels, users can select to complete the calculation, leading to a summary page. This summary page is based on input from seven panels which provides users with the estimated historical and projected FTE to estimate volume of needed cFTEs. This page provides a table below the projections with an actual FTE for the selected clinic for each role. By utilizing this model, the estimated and actual staffing needs become more aligned. New pages summarize breakdowns of each captured activity. A percentage is assigned to the role completing the work. For the RN workload, the percentage is 1 RN/LVN per 4 cFTE providers. The final page shows historical data by role. Users can hover over each of those activities and see all percentages together with the daily average volume for each activity.
In the 90-day date range from January 7, to April 5, 2022, the tool was viewed 453 times by 54 unique viewers. The view rank is 51/7,394. This rank shows the relative popularity of the report in comparison to other PowerBi reports in the organization that had a least one view in the 90-day period, illustrating the usefulness of the tool for a wide range of users.