Use of spreadsheet software for short-range forecasting of pharmacy management data

The use of a personal-computer spreadsheet program (Lotus 1-2-3) for forecasting pharmacy management data is described. Exponential smoothing is used in the forecasting model; historical data are used to predict subsequent values. Future performance is assumed to be more closely related to recent performance than to older performance data, so the model gives greater weight to more recent data. The smoothing weight controls the magnitude of error correction; a mean squared error table in the spreadsheet is used to determine the weighting. Two versions of the forecasting model are described. Version A, for a service or product for which data are expected to remain fairly stable, is used to predict values for the next time period. Version B, for data in which a substantial upward or downward trend exists, can be used to predict values for several time periods in the future. Version B differs from version A in that the forecast in version B is an estimate of the overall average of the data (weighted toward the most recent data) plus the estimated change per time period (weighted toward the most recent changes). Data from a university hospital for 1986-87 are used to illustrate the spreadsheet's tabular and graphic output; version A is used to predict the number of outpatient prescriptions for the next month, and data for the hospital's semiannual expenditures on i.v. solutions and sets are used to illustrate version B's forecast. Pharmacy managers can use these spreadsheet forecasts to quantify drug-use and personnel information for presentation to hospital administration.