Printer Friendly

A spreadsheet system for managing workload data.

Managing the compiling, computing, and reporting of workload data is an important internal function of the medical laboratory. The value of such information increases as the economics of health care transforms the practice and work environment of the lab. A more management-oriented approach to contemporary laboratory operations is clearly the established trend and will continue.

Solving the problems posed by the demand for management analyses and reports requires the use of sophisticated application software for the PC or laboratory information system. Spreadsheet software is a powerful and practical solution. It provides an essential tool for managing the kinds of information typically collected by workload recording methods.

In an attempt to simplify the clerical work necessary to compile and report workload data, I designed a spreadsheet model capable of supporting data entry, calculation, and reporting of sectional workload information. The application performs all functions within a single worksheet. The model, Lab Analyst Portfolio, is a useful tool for crunching the ever-present tallies of tests performed. It increases flexibility in reporting workload findings.

LA Portfolio is a spreadsheet application developed from Better Working Spreadsheet (Spinnaker Software Corp., Cambridge, Mass.) and from Quattro and Quattro Pro (Borland International, Scotts Valley, Calif.). The system consists of two files. One is the actual worksheet data processor; the other is a co-file used to generate reports. Designed primarily for the Lotus 1-2-3 environment (Lotus Development Corp., Cambridge, Mass.), the model is functional and easy to use. The application, based on the Lotus WK1 format, is adaptable across several software platforms.

Three separate reports are produced simultaneously as data are entered in the worksheet. These reports can be printed from either dot matrix or laser printers, depending on the support provided by the software program being used. Although the ability to rotate the printout by 90 degrees is required (a Sideways or Landscape orientation), the report templates of the worksheet will fit on standard paper (8.5 x 11 inches) when small or condensed character fonts are used.

The model will support data from all lab sections for three shifts. The application's functionality, as described in this article, is based on operating the 282KB worksheet file within Lotus 1-2-3, version 2.01 (or macrocompatible software), running on a PC/XT (IBM and compatibles) with 640K RAM and a 20-megabyte hard drive.

* Problem: Complexity. In a completely manual system, workload data are recorded in every section of the lab on department-specific sheets. These records in turn are compiled into several reports. The amount of time involved in organizing, computing totals, revising, and transcribing the final data onto report sheets is considerable. Analysis of the data is limited to a few reports, mainly because of the large amount of transcription time involved to produce them. More detailed report types that include graphical analysis are simply out of the question for the typical nonenhanced workload system.

* Answer: Spreadsheet. A PC-based spreadsheet system facilitates data handling and makes it possible to generate reports for workload recording swiftly. Users can enter data, review the results on screen, perform a variety of analyses, and print reports. An automated worksheet can significantly improve the task of collating and reporting workload data. Incorporating a convenient user interface and macro programming enhancements, the model is an efficient statistics manager.

* How it works. My primary objective in building this spreadsheet model was to enhance data entry, calculations, and report generation. All worksheet commands that control the application are listed for the user (Figure I). Help screens explain certain features and general worksheet operations. The user simply retrieves and initializes the workfile (sets the worksheet to manual calculation for immediate data entry response), edits a few ID fields, and enters sectional data.

CAP workload procedures are listed in rows. Titles for the general workload types (inpat[ient], outpat[ient], cont/stds [controls/standards], repeats, and so on) are listed by column (Figure II). Title-locking and window functions can be employed to facilitate data entry. These features allow the user to move easily through the worksheet, with more than 2,600 rows and a matrix of 26,000 + cells, without losing track of the row or column being used.

Input accuracy is aided by "protecting" rows between procedure listings. If an unintentional attempt to record data outside a test row occurs, the user is instructed through a prompt that the entry is not allowed.

The user can move to any section of the worksheet quickly by invoking a GOTO function, typing the first three letters of the section name and the shift number, and pressing <ENTER>.

With all entries in the sectional arrays complete, the user can analyze the data and print reports (Figure III). Utilizing the graphics capabilities of most professional spreadsheet software packages provides a more vivid picture of workload statistics. By graphically illustrating a group of values within the worksheet, you'll show reviewers what the data mean. Graphs often communicate important information that might otherwise be overlooked. Graphical presentations of workload data can be the solution for management concerns regarding test volumes, test percentages, productivity, and trend analysis.

Reports are easily obtained, since the worksheet is composed of dynamically linked cell references and formulas. At our lab, three report types are issued each month. These report templates have been combined with the worksheet layout and are filled in as data are entered in the sectional arrays. Using "sideways" print capabilities, the user can quickly define the range (an area of the worksheet) of the report to be printed and execute the print. If a larger spreadsheet can be supported by a PC's current RAM configuration and by 3-D or "memory-management" software features, additional report templates can be created and linked into the model. The base report sections of LA Portfolio can be modified to suit various requirements. The worksheet can be edited and managed for weekly, monthly, and quarterly reports.

The modular layout of the worksheet permits lab departments to enter their own data, if desired. Sections of the worksheet can be "cut" and saved to a hard drive or floppy disk as a separate file if the spreadsheet program provides this type of editing and file-combine function. These subsections can be retrieved, loaded with data, and recombined with the parent file. This feature provides useful flexibility for distributing the data collection process. The accessible single-worksheet application simplifies computing for a multi-user lab scenario.

* Benefits. Spreadsheet management is helpful in a number of ways. A properly designed worksheet model operating in the proper software environment can provide substantial savings in clerical work. The study of workload data is remarkably enhanced by powerful analytical and presentation tools resident in several spreadsheet programs available today. Increased clerical productivity, exceptional analysis, and improved reporting are good reasons to use the PC and suitable application software for enhancing workload recording in the clinical lab.

Readers interested in the LA Portfolio application can inquire about obtaining the model files with documentation by writing to the author at P.O. Box 2353, Springfield, OH 45502, or by calling (513) 322-0158 from 10 a.m. to 2 p.m. Eastern time, Monday through Friday. A model designed for Lotus 1-2-3 (version 2.01) or 1-2-3 macro compatible software will be shared at no charge for a limited time.

Additional versions of this application are being developed. A stand-alone DOS program is being planned. The author will answer inquiries about this spreadsheet system at the address above.

In addition, the program is available on Med TechNet, a lab-oriented computerized bulleting board headquartered at the Department of Medical Technology, SUNY at Buffalo. The modern number is now (716) 688-1552. Set your communications program to 8 data bits, 1 stop bit, no parity. Start by requesting the text file LAPRTFLO.TXT in file area #3, Medical/Clinical Files. Full instructions for downloading a file from Med TechNet to a PC were published in this column in November 1989.
COPYRIGHT 1991 Nelson Publishing
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 1991 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Title Annotation:Computer Dialog
Author:Mitchell, Steven E.
Publication:Medical Laboratory Observer
Date:Dec 1, 1991
Previous Article:How to earn perfect scores from your JCAHO surveyor.
Next Article:If we're professionals, let's act the part.

Related Articles
Putting the magic to work.
Workload recording with an electronic spreadsheet.
The microcomputer as management tool.
Allocating overhead hours with a spreadsheet.
Developing software for management applications.
Workload recording with Lotus 1-2-3.
Use of a spreadsheet for method evaluation statistics.
A PC buyer's primer.
How to link to Web data.
Show-and-tell in real time: link a spreadsheet to a PowerPoint slide for up-to-the-minute visuals.

Terms of use | Copyright © 2018 Farlex, Inc. | Feedback | For webmasters