Workload recording with an electronic spreadsheet

Medical Laboratory Observer, May, 1984 by Ran L. Whitehead

An electronic spreadsheet can turn a microcomputer into a mathematical wizard. It's a program for formatting data that holds a desired series of equations and triggers instant calculations once variables are entered.

Since much information needed by management derives from a few month-to-month or year-to-year changes in activity, even filling in three or four unknowns for the computer may lead to a lengthy printout of new data that would take hours to arrive at with a hand-held calculator.

A spreadsheet can be set up in a variety of tabular formats. We use it in conjunction with the CAP workload recording method. Monthly test volumes are entered into the microcomputer, and we immediately see how many workload units each department and the lab as a whole accounted for.

Other valuable analyses of workload patterns require only a few extra keystrokes. We can also look ahead--project a different volume a year from now, revise staffing, change the case mix, or alter prices--and the microcomputer will tell us how that affects laboratory operations.

The electronic spreadsheet that we use with our Apple II Plus microcomputer is called VisiCalc (VisiCorp, Inc., San Jose, Calif.). Other commercial programs provide similar capabilities, but we found this one easiest to operate for its level of sophistication.

Every department in our laboratory designed a template, or form, listing each procedure that it routinely records for CAP workload purposes, as well as the procedure's assigned CAP workload unit (WLU) value. The cytology department's template is shown in the upper portion of Figure I. It includes spaces for the number of times each procedure is performed and for the product of that total multiplied by the corresponding WLU value.

This multiplication formula is part of the electronic spreadsheet's mathematical ability, and is programmed by the user to automatically perform the calculation. It gives the department the total workload in units for any procedure, as shown in the completed portion of Figure I.

Monthly WLUs for each procedure are added by the microcomputer to get the department's total workload, and totals for departments are tallied to generate a workload figure for the entire laboratory. Figure II shows the master spreadsheet for the laboratory before and after June workloads have been entered. When the departmental WLUs are in the June column, the electronic spreadsheet automatically recalculates year-to-date departmental and lab WLUs, as well as productivity.

Simplified workload recording and the immediate results encourage technologists to enter their data on a timely schedule. And lab management stays more current on productivity trends.

Once entering workload data into the spreadsheet becomes part of a lab's routine, it can perform other useful analyses. For example, one question the electronic spreadsheet answers best is "What if?" With data stored in a format like that in Figure II, you can manipulate the numbers to produce a projected outcome from a set of varying assumptions. We use this application in our lab.

Suppose a microbiology department wants to know how many tests for ova and parasites in stool it can perform with existing staff. From the master spreadsheet (top of Figure III), the department knows its overall workload and productivity for the most recent month (highlighted). These numbers reflect subtotals taken from the department's monthly template (see excerpt at top of Figure IV), including 14 ova and parasite or O&P tests.

Following entry of a hypothetical number of additional tests, perhaps based on forecasts for an upcoming month, the microcomputer calculates the new amount of WLUs allotted to O&P tests (bottom of Figure IV).

Simple keyboard commands then transfer the new WLU total to the laboratory's master electronic spreadsheet. Compare the productivity of the microbiology department when it performed 14 O&P tests with that of the hypothetical month in which it performed 100 O&P tests (bottom of Figure III). The program automatically recalculates productivity, and the section supervisor quickly sees that 86 more O&P procedures would increase productivity by 0.93 units per paid employee-hour.

Other useful programmable ratios for automatic calculation include procedures per technologist, procedures per square foot, revenue per procedure, and expenses per procedure. Further insights are provided by the relationship between monthly budget variances and CAP workload data. By reviewing all significant operational parameters in the laboratory, such as peak hours and changing testing patterns, a lab manager may be able to fine-tune revenue and expense expectations over the short and long terms.

Most electronic spreadsheets can be integrated with other software packages. Once you enter data in the desired format, they can be used in word processing, graphic, statistical, telecommunication, and data base management systems. While electronic spreadsheets are valuable for entering and storing data, a laboratory won't realize the full power of computers until it can transfer that data to other locations in other formats. Through integration with other software, the laboratory can analyze and document workload in presentations that best suit the circumstances--such as a report to the hospital administration.