# Allocating overhead hours with a spreadsheet.

Allocating overhead hours with a spreadsheet

An electronic spreadsheet can simplify workload recording and generate data that are more useful because they are timelier.1 A particularly valuable function of the spreadsheet is allocation of lab overhead hours to each section's workload, a lengthy and tedious task when attempted manually.

The College of American Pathologists notes that some personnel perform support services for one or more laboratory sections-- administration, secretaries, and glassware washers, for example.2 Their hours must be equitably assigned to individual workload reporting sections based on the extent of support provided. Often, a variety of methods are necessary to allocate the different types of services.

The electronic spreadsheet can automatically perform all the calculations used to break down the overhead hours and add them to each section's monthly hours. In addition, it can calculate the number of paid and worked hours of each support service. Finally, with an electronic spreadsheet, it's easy to modify the methods of computing and allocating hours.

We use VisiCalc spreadsheet software (VisiCorp, San Jose, Calif.) and an Apple IIe micro-computer to calculate and produce the monthly overhead allocation report. Many other spreadsheet software and microcomputer hardware combinations are available.

Our laboratory has six identifiable support services--administrative, transcription, clerical, billing, dishroom, and the 11-to-7 shift--whose hours are allocated to 12 reporting laboratory sections. These support services and laboratory sections are shown on the spreadsheet in Figure I. The CRT screen displays letters labeling the columns and numbers labeling the rows. This method indexes each cell, or space for data, on the spreadsheet. When the overhead allocation report is printed out, the index letters and numbers do not appear.

The first data input line of the spreadsheet in Figure I provides spaces to enter the month and year (cell B4) as well as two key variables --the number of weekdays (cell E4) and weekend days (cell H4) in that month. With these variables, other input data, and formulas previously entered into specific cells throughout the rest of the spreadsheet, the paid and worked hours of each support service are automatically computed.

The chief administrative technologist's paid hours (cell D7) are derived from the number of weekdays times 8 hours. So cell D7 has the formula E4 8. When the number of weekdays are entered at cell E4 in the first data input line, D7 is instantly calculated.

Since the chief administrative technologist position is exempt from overtime pay, it is not necessary to calculate additional paid hours. If the administrative technologist used one week of vacation during this period, 40 would be input by keyboard in cell E7 under the S/V/H heading, which represents sick, vacation, and holiday hours--time paid but not worked. Then worked hours would automatically be computed in cell F7 as the difference between paid hours and S/V/H hours (D7 - E7).

Similarly, clerical time is computed as the number of weekdays multiplied by regularly scheduled hours (four full-time employees or 32 hours per day in our laboratory), plus any "XTRA' hours such as unscheduled overtime or additional part-time hours paid in this period, plus the number of weekend days multiplied by the regularly scheduled weekend hours of clerical support. The formula for calculating total clerical hours in cell E15 is (E4 32) C15 (H4 16).

Once formulas are in place, they are transparent on the display, keeping the spreadsheet uncluttered for the user. If the scheduled weekday clerical hours change from 4 to 3 1/2 full-time equivalents, the formula for cell E15 can be simply altered to (E4 28) C15 (H4 16).

The lower portion of the spreadsheet on Figure I computes the allocated hours. Each reporting laboratory section submits its paid and worked hours for the period and these are input under the corresponding headings across rows 24 and 25. The paid and worked hours for each support service, calculated in the upper worksheet area, are automatically allocated by the spreadsheet among sections in accordance with predetermined formulas. This takes place in rows 26 to 37.

Administrative hours are allocated according to the section's percentage of total laboratory FTEs. Clerical services not directly attributed to laboratory sections are first divided into billing hours and other hours and then each is allocated separately. Billing hours are apportioned according to the percentage of each section's billable test volume; other clerical hours are distributed according to the section's percentage of weighted workload units.

Formulas for these methods of allocation are entered into appropriate cells. Each month, as the sections' direct paid and worked hours are entered, the overhead hours will be computed, and added to the sections' hours, and the total paid and worked hours will be indicated at the bottom of the worksheet. A data input sheet (Figure II) may be used to conveniently assemble the necessary information at the close of every month.

The measure of productivity is a principal aim of the CAP workload recording method, and laboratory sections are understandably concerned about the accuracy of allocated hours. Use of an electronic spreadsheet protects against the transcribing mistakes and mathematical errors that can plague manual calculations. In addition, the formulas loaded into spreadsheet cells are easy to alter. The allocated hours spreadsheet is instantly adaptable to changing circumstances.

1. Whitehead, R.L, Workload recording with an

1. Whitehead, R.L. Workload recording with an 1984.

2. College of American Pathologists, Workload Recording Committee. "Manual for Laboratory Workload Recording Method.' Skokie, Ill., CAP, 1984.

Table: Figure I Workload spreadsheet: Small input, extensive calculations

Letters across the top and numbers down the left side appear on the screen display but not on printouts. They are indices used to locate cells, or spaces for data, on the spreadsheet. Boxed data are input by keyboard, and the computer then performs calculations to fill out the rest of the report. Abbreviations here include S/V/H for sick, vacation, and holiday hours; CAT for chief administrative technologist; LD TRAN for lead transcriptionist; and TRANSCR for transcription.

Table: Figure II Using a data sheet to guide input

An electronic spreadsheet can simplify workload recording and generate data that are more useful because they are timelier.1 A particularly valuable function of the spreadsheet is allocation of lab overhead hours to each section's workload, a lengthy and tedious task when attempted manually.

The College of American Pathologists notes that some personnel perform support services for one or more laboratory sections-- administration, secretaries, and glassware washers, for example.2 Their hours must be equitably assigned to individual workload reporting sections based on the extent of support provided. Often, a variety of methods are necessary to allocate the different types of services.

The electronic spreadsheet can automatically perform all the calculations used to break down the overhead hours and add them to each section's monthly hours. In addition, it can calculate the number of paid and worked hours of each support service. Finally, with an electronic spreadsheet, it's easy to modify the methods of computing and allocating hours.

We use VisiCalc spreadsheet software (VisiCorp, San Jose, Calif.) and an Apple IIe micro-computer to calculate and produce the monthly overhead allocation report. Many other spreadsheet software and microcomputer hardware combinations are available.

Our laboratory has six identifiable support services--administrative, transcription, clerical, billing, dishroom, and the 11-to-7 shift--whose hours are allocated to 12 reporting laboratory sections. These support services and laboratory sections are shown on the spreadsheet in Figure I. The CRT screen displays letters labeling the columns and numbers labeling the rows. This method indexes each cell, or space for data, on the spreadsheet. When the overhead allocation report is printed out, the index letters and numbers do not appear.

The first data input line of the spreadsheet in Figure I provides spaces to enter the month and year (cell B4) as well as two key variables --the number of weekdays (cell E4) and weekend days (cell H4) in that month. With these variables, other input data, and formulas previously entered into specific cells throughout the rest of the spreadsheet, the paid and worked hours of each support service are automatically computed.

The chief administrative technologist's paid hours (cell D7) are derived from the number of weekdays times 8 hours. So cell D7 has the formula E4 8. When the number of weekdays are entered at cell E4 in the first data input line, D7 is instantly calculated.

Since the chief administrative technologist position is exempt from overtime pay, it is not necessary to calculate additional paid hours. If the administrative technologist used one week of vacation during this period, 40 would be input by keyboard in cell E7 under the S/V/H heading, which represents sick, vacation, and holiday hours--time paid but not worked. Then worked hours would automatically be computed in cell F7 as the difference between paid hours and S/V/H hours (D7 - E7).

Similarly, clerical time is computed as the number of weekdays multiplied by regularly scheduled hours (four full-time employees or 32 hours per day in our laboratory), plus any "XTRA' hours such as unscheduled overtime or additional part-time hours paid in this period, plus the number of weekend days multiplied by the regularly scheduled weekend hours of clerical support. The formula for calculating total clerical hours in cell E15 is (E4 32) C15 (H4 16).

Once formulas are in place, they are transparent on the display, keeping the spreadsheet uncluttered for the user. If the scheduled weekday clerical hours change from 4 to 3 1/2 full-time equivalents, the formula for cell E15 can be simply altered to (E4 28) C15 (H4 16).

The lower portion of the spreadsheet on Figure I computes the allocated hours. Each reporting laboratory section submits its paid and worked hours for the period and these are input under the corresponding headings across rows 24 and 25. The paid and worked hours for each support service, calculated in the upper worksheet area, are automatically allocated by the spreadsheet among sections in accordance with predetermined formulas. This takes place in rows 26 to 37.

Administrative hours are allocated according to the section's percentage of total laboratory FTEs. Clerical services not directly attributed to laboratory sections are first divided into billing hours and other hours and then each is allocated separately. Billing hours are apportioned according to the percentage of each section's billable test volume; other clerical hours are distributed according to the section's percentage of weighted workload units.

Formulas for these methods of allocation are entered into appropriate cells. Each month, as the sections' direct paid and worked hours are entered, the overhead hours will be computed, and added to the sections' hours, and the total paid and worked hours will be indicated at the bottom of the worksheet. A data input sheet (Figure II) may be used to conveniently assemble the necessary information at the close of every month.

The measure of productivity is a principal aim of the CAP workload recording method, and laboratory sections are understandably concerned about the accuracy of allocated hours. Use of an electronic spreadsheet protects against the transcribing mistakes and mathematical errors that can plague manual calculations. In addition, the formulas loaded into spreadsheet cells are easy to alter. The allocated hours spreadsheet is instantly adaptable to changing circumstances.

1. Whitehead, R.L, Workload recording with an

1. Whitehead, R.L. Workload recording with an 1984.

2. College of American Pathologists, Workload Recording Committee. "Manual for Laboratory Workload Recording Method.' Skokie, Ill., CAP, 1984.

Table: Figure I Workload spreadsheet: Small input, extensive calculations

Letters across the top and numbers down the left side appear on the screen display but not on printouts. They are indices used to locate cells, or spaces for data, on the spreadsheet. Boxed data are input by keyboard, and the computer then performs calculations to fill out the rest of the report. Abbreviations here include S/V/H for sick, vacation, and holiday hours; CAT for chief administrative technologist; LD TRAN for lead transcriptionist; and TRANSCR for transcription.

Table: Figure II Using a data sheet to guide input

Printer friendly Cite/link Email Feedback | |

Title Annotation: | Computer Dialog |
---|---|

Author: | Lollich, Michael C. |

Publication: | Medical Laboratory Observer |

Date: | Dec 1, 1984 |

Words: | 1006 |

Previous Article: | Job satisfaction: the possible dream. |

Next Article: | Changes in the new AABB standards. |

Topics: |