Computers ind. Engng Vol. 19, Nos 1-4, pp. 77-80, 1990 Printed in Great Britain. All rights reserved
0360-8352/90 $3.00 + 0.00 Copyright © 1990 Pergamon Press plc
A COMPUTER ASSISTED SCHEDULING SYSTEM FOR RETAIL ORGANIZATIONS Susan A. Johnsen and Sandra C. Parker Department of Industrial Engineering University of Arkansas Fayetteville, Arkansas
ABSTRACT a measure of resource allocation. With the exception of cases involving only a handful of employees, the mathematical programming approach has not proven promising [Glover, McMillain & Glover, 1984]. Mathematical models become complex, to the point of unsolvable, as significant constraints are added to the model. The heuristic programming approach more closely follows the methods and procedures a personnel manager uses when creating a schedule. Logic programming offers a powerful medium in which to represent the heuristics involved in the employee scheduling problem.
A knowledge-based system is developed to produce work schedules for personnel in large retail settings. The system considers partand full-time employment, specific and general assignments, minimum and maximum hours per work period and per week, absences due to vacation and illness, and other constraints. The major objective is to schedule employees in relation to sales activity over the year without exceeding a certain budgetary allocation for sales labor. This allocation is defined as a percentage of sales. The system is developed in Arity/Prolog and includes a forecasting module which forecasts sales by week on the basis of the previous three years of data and activity by hour and day on the basis of several weeks of aggregated data. Schedules are produced for periods of one week but the user may override the schedule as circumstances warrant.
LOGIC PROGRAMMING Prolog (PROgramming in LOGic) is one of the more prominent declarative computer languages. It is best suited to problems which require symbolic computation. Prolog has been proven to be well-suited to the development of expert systems, natural-language interfaces, databases, and rapid prototyping of applications [Clocksin, 1987 ]. Prolog exhibits many features which make it the language of choice for the development of expert system type programs. It is a declarative, rule-based language which supports top-down design, forward and backward chaining, and multiple solutions [Lucas, 1988, pg. 47]. Objects and their relationships are defined by facts, rules and the use of queries [Clocksin & Mellish, 1987, pg. 2]. Prolog is an ideal language in which to represent the scheduling procedure.
INTRODUCTION Personnel scheduling is defined as the "assignment of available personnel to handle job demands that vary over time" [Burgess & Busby, 1982]. The process of creating work schedules for retail sales personnel is complicated by conditions such as full- and part-time employment, established departments for some employees, and the large size of the standard workforce. The desire of management to keep labor as a percentage of sales is usually considered after a schedule has been executed. The scheduling process is typically performed manually by a personnel director, who with considerable time and effort arrives at a possible solution. Personnel scheduling has been modeled empirically, mathematically and heuristically. The empirical model is not a suitable model for the scheduling of retail employees when sales data is used as
PROBLEM DOMAIN The scheduling problem considered here is the determination of work schedules for retail sales personnel where the workforce is often large (i00+) and scheduling is complex due to a variety of factors. The constraints or rules for scheduling employees include: 77
Proceedings of the 12th Annual Conference on Computers & Industrial Engineering
Employees each have a minimum and a maximum number of hours to work in the period of one week. Restrictions on working hours may exist for employees due to seniority or part-time employment. A direct relationship between the number of employees scheduled and the required work activities at the retail outlet must exist. Each employee must work a minimum number of hours each working period. The extent to which workers are interchangeable between different departments and functions must be considered. Achieving an overall minimum level of experience is preferred. Scheduled and unscheduled absences due to vacations and illness must be acknowledged by the scheduler. Staffing in relation to sales activity over the year without exceeding the budgetary allocation for sales labor is desired. SYSTEM DESIGN
The developed scheduling system links a forecasting and scheduling program written in Prolog to employee data originally contained in a relational database management system (RDBMS). Use of data from a RDBMS offers two major advantages; a correctly designed system can eliminate the need to duplicate data within the
organization, and it augments the data storage capabilities of Prolog. In order for the scheduling program (written in Prolog) to efficiently access the employee data contained in the relational database, the database design and linkage must be correctly modeled to suit the scheduling problem. There are two basic approaches for system design when linking a relational database with a language; tightly-coupled, where the language interacts dynamically with the database to acquire more data, and loosely-coupled, a static situation where the entire database is loaded into the program at one time [Lucas, 1988]. The system designed here employs a loosely-coupled approach. The relational model was chosen because it is employed in most retail environments in the form of a relational database management system (RDBMS) which performs payroll, inventory and other functions. The scheduling program requires the RDBMS to have the ability to export the employee database tables into files in dBase III format. These files are then read and stored internally by the scheduling program. The dBase format is a standard format for export of database tables. Any RDBMS which has this ability will work with the developed scheduling program. The program design is illustrated in Figure I. A pull-down menu system is employed which directs the user in selecting appropriate functions. A forecast for each hour of
Arity/Prolog Scheduling Program
Figure i. Program Design
Johnsen and Parker: Scheduling Systems for Retail Organizations
the workweek is generated by the forecasting module. The user is prompted to enter actual sales data for the week corresponding to previous year(s) actual sales figures. These figures are used to generate a weekly forecast made by the methodology described below. The hourly forecast is based on several weeks of aggregated data. This forecast is stored internally and used by the scheduling module. The chosen forecasting model was presented in Industrial Enaineerina by McLean and Wortham . This method uses the Taylor Series to represent the forecasting function. This methodology assumes that the first few derivatives are the most important components of the expansion and the underlying function is assumed to be "continuous and well behaved insofar as the derivatives are concerned" [McLean & Wortham, 1988 p. 18]. The program was translated by the author from BASIC to Prolog. It determines the available revenue and therefore the number of employees to schedule in a given time period. The model assumes the workload is directly proportional to sales. Most retail stores incorporate some type of forecasting in one or more facets of their organization. The program contains an override function which allows the user to employ a sales forecast created outside the scheduling environment rather than the model used in the program. Before scheduling can commence, employee data must be loaded into the program. The data must be supplied to the program in dBase III format. This information includes employee number, the department(s) the employee may work in, the employee's hourly wage, whether the employee is full or part-time, and other pertinent information. Simple heuristics are used to create the schedule. They emulate the manner in which a manager would proceed to generate a schedule. For example, the typical procedure is to manually fill in a schedule with full-time employees first, department by department, within individual constraints, and schedule part-time personnel to fill any gaps. The scheduling module follows a similar procedure while accounting for labor as a percentage of sales. CONCLUSION The knowledge-based System presented here produces work schedules for personnel in large retail settings. It uses a forecasted sales
figure for the week to generate hourly sales guidelines for each hour of the week. Employees are scheduled based on these guidelines and other constraints.
[1987a]. The Arity/Proloq Lanauaue Reference Manual. concord, Massachusetts: Arity Corporation. Arity [1987b]. Usina the Aritv/Prolog compiler and Interpreter. Concord, Massachusetts: Arity Corporation. Bechtold, Stephen and Michael J. Showalter . "Simple Manpower Scheduling Methods for Managers." Production and Inventory ManaGement. Vol. 26, Iss. 3, pp. 116-133. Burgess, William J. and Robert E. Busby . "Personnel Scheduling." Handbook of Industrial Enqineering. Gavriel Salvendy Ed., New York: John Wiley & Sons. Clocksin, W. F. . "A Prolog Primer." Byte. Vol. 12, No. 9, pp. 147-158. Clocksin, W. F. and C. S. Mellish . Proaramminu in Prolog. 3rd ed. Berlin: SpringerVerlag. Garavaglia, Susan . Proloa Prourammina Techniaues and Applications. New York: Harper & Row. Glover, Fred, Claude McMillian and Randy Glover . "A Heuristic Programming Approach to the Employee Scheduling Problem And Some Thoughts On "Managerial Robots." Journal of Operations Manaaement. Vol. 4, No. 2, Feb. 1984. pp. 113128. Lazarev, Gregory L. . Why Proloa? Justifvinu Loaic Proarammina for Practical Applications. Englewood Cliffs, New Jersey: Prentice Hall. Lucas, Robert . Database ADDlications Usina Prolo a. New York: Halsted Press. McLean, Lawrence B. and A. William Wortham . "Methodology Aids Forecasting with Limited Amounts of Data." Industrial Enaineerina. Vol. 20, No. 2, 1988, pp. 18-22. Rich, Elaine . Artificial Intelliaence. New York: McGraw-Hill, Inc.
Proceedings of the 12th Annual Conference on Computers & Industrial Engineering
BIOSKETCH Dr. Sandra C. Parker is an Associate Professor of Industrial Engineering at the University of Arkansas, Fayetteville. Her research interests include operations research and artifical intelligence/expert systems. She is a Professional Engineer, registered in Arkansas and is a senior member of IIE. S u s a n A. J o h n s s n is a degree candidate for the M.S. in Industrial Engineering at the University of Arkansas, Fayetteville, where she received her B.S.I.E. in May, 1988. Her research interests include applications of logic programming and expert systems. She has accepted a position with Andersen Consulting in Dallas, Texas.