A Manpower Planning Decision Support System
for MQM Meat Services
Quality Management (MQM) is an arm of the Ministry of Agriculture and Fisheries (MAF) providing policy advice to the New Zealand Government on matters relating to agriculture and fisheries, as well as performing regulatory functions. These functions include developing and setting standards for agricultural security, quality assurance, pest and disease management, and animal welfare. MQM also delivers a range of services for government and clients in the agricultural, fishing and food industries. One of its services is Meat Services (MS), which provides inspection, audit, and verification services to the meat and game industries to meet certification and market access requirements. MS also ensures that quality systems are in place to meet the specifications of MAF Regulatory Authority and importing countries.
Currently, MS is the sole statutory provider of inspection services to the meat industry. In November 1994, the consultancy firm Price Waterhouse released a report on future options for New Zealand's meat hygiene programme. The report recommended that contestability be introduced into the meat industry. This means MS's role as the statutory provider of meat inspection services is about to change. With statutory changes regarding meat inspection likely to be introduced in late 1997, MS are now moving into a competitive environment where they will be required to compete with other providers of meat inspection services. Contestability will mean that meat plants will be free to choose the meat inspection service provider of their choice.
Meat inspection services are tendered for in advance on an annual basis. MS need to price their services competitively if they are to succeed in winning meat inspection contracts. To do this, MS need to know with greater accuracy the number of permanent, relief, and seasonal staff to employ to ensure MS is able to meet service requirements. If MS overprice their inspection services, they will find it more difficult to compete with suppliers whose services are more competitively priced. The variations in meat inspection load and in staff leave exacerbate the problem. Currently, MS make all their MS manpower capacity planning decisions in an ad hoc manner. This results in MS having a large pool of relief staff even though during some periods they are not required. MS desired a system that would enable them to reduce the cost of their operations while coping with the variations.
This paper reports on a decision support system (DSS) that was developed for MS to enable them to plan their manpower requirements, competitively, on an annual basis. The following section presents a review of the relevant literature. Then we describe the problem and the modelling options that were considered. Next we discuss the DSS and the implementation issues. Finally, we present some concluding remarks.
Bowey (1977) has defined manpower planning as a "strategy for matching future manpower numbers and skills with organisational activities". Many researchers have published work in this area. Gregory (1983) presented a manpower-planning model based on the idea of career development and job assignment according to skills attained. This followed along the same lines as Thomson (1979) who introduced a transportation linear programming model to be used for studying the design of a manpower system. Thomson's (1979) work was notable for its application of linear programming to manpower planning problems. Golver et al (1979) introduced a model whereby jobs are assigned to individuals based on the costs associated with each individual doing a particular job. Although these models are useful for manpower planning, they are more concerned with matching employee's skills with different jobs.
Another line of manpower research is to focus on the transitions between various stages of employees' careers. Typically, a Markov process is used as an underlying theoretical model. Venema and Wessels (1988) describe a DSS that follows this approach and may be used for promotion, hiring, and firing decisions. Verbeek (1991) presents a DSS for strategic manpower planning of airline pilots. Here long-term fleet plans, pilot seniority and pilot bidding for promotion are incorporated in a DSS for hiring, training, and promotion of pilots. Instead of a Markov model, this DSS uses a number of optimisation models to accomplish various tasks in decision making. The system permits representation, manipulation, evaluation, and generation of manpower plans.
Many authors have worked on the shorter-term problem of rostering and scheduling of personnel. Airline crew rostering is a prime example of this research. Ryan (1992) presents an efficient strategy for solving the massive integer programming formulations generated by this problem. Gopalakrishnan et al. (1993) report on a DSS developed to support planning and scheduling part-time workers at a newspaper company. They used a rule base to generate the requirements of workers, and then a heuristic to find a near-optimal allocation of the workers over weekly periods. Buffa (1976) provides an earlier example of this line of work where a computer system to schedule operators in a telephone company is described. This system uses forecasting and queueing models to forecast weekly workloads. Shifts are then designed so that the divergence of actual operator hours from the workload is minimised. Finally, operators are assigned to the shifts. Obviously, rostering takes a short term approach where available employees are allocated work, sometimes on overtime basis, as needed. Worker equity is often more important than cost considerations.
More applicable to the problem at hand are aggregate planning models. Traditionally, aggregate planning principles have been more widely applied in the manufacturing industry than in the service industry. Khumawala et al (1986) defined aggregate service planning as the "function of setting the overall level of service output and identifying resources and service rates required to support the marketing and business plans in a manner consistent with company objectives". The aggregate service plan is a general plan on how an organisation expects to respond to forecasted demand. Linear programming can be used to solve deterministic aggregate planning problems. An example is provided in Chase and Aquilano (1992). Another deterministic planning model is presented by Dijkstra et al. (1991 and 1994) who developed a DSS for the aircraft maintenance department of the Dutch national airline KLM. This DSS assisted the aircraft maintenance department in matching the manpower needs of regular between-flight short inspections of aircrafts with the available engineers who held various licenses to carry out the inspection. The DSS consisted of a database module, including several sub-databases, an analysis module (optimisation model), and a user interface. The DSS is used to evaluate the impact of new time-tables, effect of engineer licensing policies, estimate the workforce required, and in contracting inspections for other airlines. The uniqueness of the DSS is in handling various aircraft/skill licensing combinations of engineers.
Fait et al. (1991) bridges the gap between aggregate planning models and rostering models by using a multistage approach where in the first stage an aggregate plan of personnel capacity is prepared. This plan is then elaborated first into a weekly work plan for each employee and then into a daily work plan for each employee. Their model focussed on matching demand by varying work hours, overtime, number of full time, and the number of part time employees.
For our purposes, however, we needed to be able to cope with stochastic variations of the demand for services, and in provision of the services. The stochastic approach is presented by the likes of El Agizy (1971) and Wild and Schneewei (1993). El Agizy (1971) proposed a system for manpower planning in the face of uncertain requirements. El Agizy's system consisted of two parts: first a statistical procedure for estimating workload distribution by skill and by time period, and second, a multistage stochastic programming model to determine the optimal manpower plan by minimising costs over the planning horizon. The inputs to El Agizy's model were future workloads characterised by their statistical distributions. The outputs were full-time employee levels and the associated retraining and hiring programmes, temporary employee levels, planned overtime, and subcontracted work.
Wild and Schneewei (1993) present a hierarchical approach to manpower capacity planning. Their approach recognises stochasticity by using probabilities of scenarios of workload and absenteeism. The search for solution is done at three levels: the first level searches over the set of workforce sizes, percentage of floats (multiskilled people who can work across departments), and work shifts. For each option at this level, the second level uses dynamic programming to select work time of the personnel at each department in each month. At level three, the cost of each option at level two is calculated by using simulation of workload and absenteeism. Within the simulation, the amount of overtime, floaters, and temporary workers is determined by non-linear integer programming. Obviously, this is a very complex stochastic model that can be used to find optimal answers, but can be used practically only for a very limited range of options.
Although many approaches to manpower capacity planning exist, the practicality of such approaches depends on how easily such models can be implemented, and the ease with which models can be tailored to the situation at hand. We needed a stochastic approach since the inspection workload and employee leave could vary significantly. However the stochastic models available in the existing literature are very complex, and therefore, in our view, not suitable for the task in hand since the acceptance of a DSS depends highly on the extent to which the user can relate to the solution presented by a DSS. We wanted a model that would be possible to implement in a spreadsheet package since these packages are readily usable by most users, and since these packages provide ready made interfaces which are familiar to most users. At the same time we sought a model that could be readily understandable (and thus acceptable) to the user. We did not find such a model in the extant literature.
The Decision Scenario
MS provides an independent inspection and certification service to the meat, seafood and game industries to ensure products are acceptable to the market place. Most of the staff are located at slaughtering and processing premises throughout New Zealand. The meat industry is very susceptible to fluctuations in the demand for inspection services due to the large number of environmental factors effecting the number of animals killed at any time during a year. The meat industry is also a seasonal industry with peak manpower requirements occurring during the period November through to June.
Each meat plant has a fixed number of permanent meat inspectors and a meat veterinarian employed at any one time. During the operation of a meat processing plant, a meat veterinarian must be present at all times. In addition, a meat inspector must inspect every carcass. Each plant has individual characteristics with respect to the animal species they process, the average inspection rate for each animal, and plant layout, which determines the total number of MS staff required. To meet fluctuations in service requirements, a number of relief and seasonal staff are employed by MS. Relief staff are deployed to plants on a daily basis as needed. Seasonal staff may be used for inspection. Seasonal staff are identical to permanent staff except they are hired on a contract basis. The minium term of a contract is 3 months with the maximum term being 9 months. MS staff are paid overtime - time and a half for the first two hours per day and double time for every hour following; time and a half for the first four hours on a Saturday with double time for every hour following; and double time all day Sunday.
Planned and unplanned absenteeism exacerbate the random fluctuations in the demand for inspection services. MS management desires to offer a competitively priced service in their bid to become the "preferred supplier" of inspection services to the meat industry. To accomplish this, they want to ascertain the most cost-efficient deployment of the permanent, relief, and seasonal staff.
In this section we offer some alternative modelling perspectives on this decision scenario. The simplest model to tackle this decision would be a linear programming formulation.
i = index of planning periods (1 .. p)
j = index of facilities ( 1 .. f)
k = index of staff type (1 .. 3: 1 = permanent, 2 = seasonal, 3 = relief)
Xijk = Number of meat inspectors of type k available in the period i, assigned to facility j
sk = Salary of meat inspectors of type k per period
Yijk = Overtime hours of meat inspectors of type k in period i, at facility j
ok = Overtime pay per hour for staff of type k
Uijk = Number of newly hired meat inspectors of type k (k = 1 ... 2), in period i, facility j
Vi = Number of newly hired relief meat inspectors in period i
hk = Hiring cost for meat inspectors of type k
Wijk = Number of fired meat inspectors of type k (k = 1 ... 2) in the beginning of period i, at facility j
Zi = Number of fired relief meat inspectors in the beginning of period i
fk = Firing cost for meat inspectors of type k
li = Expected number of hours of leave taken by one meat inspector in period i
Iij = Total forecast expected inspection workload, in hours, in period i, facility j
h = Number of regular (not overtime) hours per period per meat inspector
ot = Number of hours of overtime allowed per meat inspector per peiod
With this notation, the linear program may be written:
Xijk 0 and integer (7)
Expression 1 is the total cost of regular time, over time, hiring, and firing of all employees at all facilities for all periods in the planning horizon. Equation 2 stipulates that total hours of work at each facility for each period should at least match the total forecasted workload, allowing for expected employee leave. Equation 3 ensures that the number of permanent and seasonal personnel accords with number of hirings and firings. Equation 4 does the same for relief personnel. This constraint is needed since relief meat inspectors belong to a pool, and can be readily reassigned to different facilities. Equation 5 enforces the condition that the seasonal personnel have to be hired for at least three months. Equation 6 makes sure that overtime hours are within the limits permitted by the number of personnel. Note that these equations need to be slightly modified for the first two periods (i.e. i = 1 ... 2) for initial data.
This is an integer linear programming formulation of the aggregate manpower planning problem. This model can be solved to optimality. One problem with this model is that it uses average leave and workload values. Given the variability that exists in these variables, a manpower plan based on this model may prove inadequate in practice, causing the management to hire seasonal personnel uneconomically, or worse still, to be unable to provide inspection service when the client needs it. Another problem is the integer requirement - this requirement causes the computer to take a long time for solving the model, causing frustration on the part of the manager and possible eventual rejection of the model.
Chance constrained programming
Considering the variability of the workload and the number of personnel on leave, the manager needs to be provided a high degree of assurance that the manpower plan will meet the inspection workload requirements. This can be accomplished by a chance constrained programming (CCP) model. In place of a given constraint in the LP model, a CCP model inserts the constraint that the probability of achieving that constraint statement is above a given value. In the current problem, we seek to be assured that the probability of meeting the workload, allowing for the variability of personnel on leave, is above a user specified level, say .
Assuming normal distribution, this probability will be achieved by replacing constraint (2) in the above LP model by constraint (2A) below.
where Z is the standard normal variate corresponding to the user specified probability level , is the coefficient of variation of leave, and is the coefficient of variation of workload.
This CCP model represents the problem faced by the decision maker very closely, but the computational complexity is much more than the integer LP model because of the non-linearity in constraint (2A). This model is solvable by using non-linear integer programming packages, but we did not pursue this path in view of the computation time that would be needed, which would impinge on the acceptability of the model. We believed a much more simpler approach was needed that would be quicker and understandable and thus acceptable to the decision maker.
Approximate chance constrained programming model
An approximation to the above CCP model is to sum up the standard deviation of the random variables rather than summing up their variances. Considering that an accurate estimate of the variation is not available, and the variation is comparatively small, this approximation should still give reasonably close answers. This will modify constraint (2) further:
Further, for the sake of computational efficiency, if the integrality constraints (7) are dropped, the problem is converted into a simple LP problem which can be solved within very reasonable time frame.
One criticism of this model is that although this LP model assures that each constraint is satisfied to the desired degree of probability, the probability that all the constraints are satisfied will be smaller. Further, as in any chance-constrained model, we assume that the probabilities are independent, which cannot be said in this multi-period model where there is a continuation of the level of personnel employment. Still another problem with this approximation is that the sum is used to represent the square root of sum of the squares of standard deviation. This means that the variation will always be overestimated, and the optimal solution will provide more coverage than that desired by the decision maker.
The optimum solution, as calculated above, provides cost estimates that are not realistic since the optimal manpower plan will meet contingencies of higher demands than one would normally expect. The objective function reflects the inflated workload, and thus the solution to the CCP model does not provide us with the likely or the expected cost for budgeting purposes.
Monte Carlo simulation
To the decision maker, a good measure of customer service is the overall probability of meeting the workload. That is, the decision maker needs to know what proportion of the many periods and facilities will have adequate manpower to cover the variable demand?. The decision maker seeks to balance this customer service level against the total cost of the manpower plan. There is no analytical models available to calculate this measure of the customer service level. However, a Monte Carlo simulation can provide a reasonable estimate of this probability. Since the simulation does not need to be a complex discrete event simulation, computation can be done within reasonable time. In this simulation model, given the employment levels of permanent, relief, and seasonal personnel, the simulation model uses overtime as necessary (using the least cost permanent personnel first) to meet the workload. A tally of workload that could not be met is kept, and after sufficient replication, the probability of meeting the workload is indicated to the user together with the expected cost of the plan.
Besides estimating the customer service level for a given manpower plan, the Monte Carlo simulation model is also able to provide an estimate of the expected costs for the plan for budgeting purposes. This estimate is better than the cost estimated by the optimisation model, since the cost of the optimisation model is a cost of the contingency, as pointed out earlier.
Decision Support System
The analytical models presented above optimise (minimise) the total cost of the manpower plan. The decision maker, however, usually has multiple objectives to juggle. Equity, not just the cost, is a prime consideration in dealing with personnel matters. Other considerations are the labour contracts, laws and regulations, and the personnel policy of the organisation. The problem with optimisation models is that they provide the user with one optimal answer, and if the user cannot use the anser for some reason, the user is not provided other options. We decided, in consultation with MS, to develop a decision support system in which the optimisation model provides a suggested solution, but lets the user dabble with other decisions. The Monte Carlo model would provide feedback to the user by providing estimates of total annual cost and of customer service level. This way the power of the computer is used to quickly evaluate a number of solutions.
In consultation with the users, we established the following requirements:
1. Provide an estimate of future workload
2. Provide an estimate of future leave of MS staff
3. Optimise the manpower level for a set customer service level
4. Estimate the customer service level for a manpower scenario proposed by the user.
Design of the DSS
We implemented the DSS in Microsoft Excel which is a popular spreadsheet package. The reason for selecting this package is the familiarity of the user with this package, and the programming and optimising features provided by the package. The DSS consists of database module, a model base, and a user interface (Turban 1995). An overall view of the DSS is featured in Figure 1.
A database of kill figures for each meat plant is maintained. MS currently use an Excel based spreadsheet system for recording the kill numbers at each meat plant for invoicing purposes. An interface was designed that added a menu bar to this system that allows the DSS kill database to be updated automatically. The kill database currently holds records for selected meat plants for the period May 1994 to the present.
Leave statistics for all meat plants are also available in the DSS. The database was based on a report run from MQM's current leave system. The leave database currently holds data for the period 1 July 1995 to 1 July 1996.
Another database holds information about charge-out rates and other parameters such as maximum number of overtime hours and monthly workdays. Like both the databases above, this database is stored in an Excel sheet within the DSS.
An optimisation model based on the approximate CCP model discussed above was implemented using a SOLVER from within the EXCEL spreadsheet. This model determines the optimal manpower levels that would meet a customer service level selected by the user.
A Monte Carlo simulation program was created in VISUAL BASIC within the EXCEL spreadsheet to simulate the workload of MS. This was intended to answer any "what-if" question posed by the user. The user can create a manpower plan, and the simulation model estimates the expected cost and service level achieved by the plan.
The appearance of the DSS is determined by the User System Interface (USI). After discussing application preferences with the end user of the system, the USI selected was in the form of a mouse driven point-and-click graphical user interface (GUI). The aim of the GUI was to make the spreadsheet that housed the model transparent to the user. The GUI provided ease of training and of use. The start-up screen is shown below in Figure 2.
The DSS can also be used to estimate the future leave of MS Staff based on historical data. Again, the DSS presents historical leave information and trends which the end user can use to base their decisions upon. The historical data presented by the DSS can assist not only in manpower capacity planning, but also in employment policy development. For example, MQM may make it a term of employment that MS staff are unable to take planned leave during certain periods of the year.
We presented a decision situation and a decision support system to assist the decision maker. This DSS consisted of a Data base, Model base, and an User interface. The salient feature of this DSS is the use of a spreadsheet environment. This allows presenting the relationships and the data in a simple format that is understandable for the manager making the decisions. This simplicity and the familiar interface fostered user confidence and acceptance. It was also essential for the acceptability of the DSS to use a model that was accessible to the decision maker, and would provide outputs quickly in order to examine various decision alternatives. This meant using the approximate solution of a non-integer linear model in place of the exact solution. Nevertheless the model did capture the peculiarities of the situation, such as the reserve meat inspectors who belong to a pool, and the seasonal inspectors who have to be hired for at least three months. The DSS approach (as against an optimisation approach) as followed here permits the user to get an understanding of the situation, and to examine decision alternatives created by the user. Thus the DSS aids the user rather than supplanting them.
Bowey, A. M. (1977). Corporate Manpower Planning. Management Decisions, 15, 421-469.
Buffa, E.S. (1976). An integrated work shift scheduling system. Decision Sciences, 7, 620-630.
Chase, R. B. & Aquilano, N. J. (1992). Production and Operations Management: A Life Cycle Approach. Homewood IL: Irwin.
Dijkstra, M. C., Kroon, L. G., van Nuen, J. A. E. E., and Salomon, M. (1991). A DSS for capacity planning of aircraft maintenance personnel. International Journal of Production Economics, 23, 69-78.
Dijkstra, M. C., Kroon, L. G., Salomon, M., van Nuen, J. A. E. E., and Van Wassenhove, L. N. (1994). Planning the Size and Organization of KLM's Aircraft Maintenance Personnel. Interfaces, 24: 6, 47-58.
El Agizy, M. (1971). A Stochastic Programming Model for Manpower Planning. In D. J. Bartholomew and A. R. Smith, Manpower and Management Science. London: The English Universities Press Ltd.
Fait, J., Schneewei, C., and Wolf, F. (1991). A hierarchically structured decision-support-system for demand oriented shift planning. International Journal of Production Economics, 23, 89-98.
Golver, F., Karney, D., & Klingman, D. (1979). A study of Alternative Relaxation Approaches for a Manpower Planning Problem. In Yuji Ijiri and Andrew Whinston, Quantitative Planning and Control. New York: Academic Press.
Gopalakrishnan, M., Gopalakrishnan, S., and Miller, D.M. (1993). A decision support system for scheduling personnel in a newspaper publishing environment. Interfaces, 23, 104-115.
Gregory, G. (1983). Mathematical Methods in Management. Bath, Avon: John Wiley & Sons.
Khumawala, B. M., Hixon, C., & Law, J. S. (1986). MRP II in the Service Industries. Production and Inventory Management, 27(2): 57-63.
Price Waterhouse Management Consultants (1994). A Report on the Future Options for New Zealand's Meat Hygiene Programme.
Ryan, D.M. (1992). The solution of massive generalized set partitioning problems in aircrew rostering. Journal of Operational Research Society, 43 (5), 459-467.
Thomson, G. L. (1979). A Network Transshipment Model for Manpower Planning and Design. In Yuji Ijiri and Andrew Whinston, Quantitative Planning and Control. New York: Academic Press.
Turban, E. (1995). Decision Support and Expert Systems. New Jersey: Prentice Hall.
Verbeek, P.J. (1991). Decision support systems - an application in strategic manpower planning of airline pilots. European Journal of Operational Research, 55, 368-381.
Venema, W.Z. and Wessels, J. (1988). Systematic modeling and model handling for manpower planning systems. Decision Support Systems, 4, 503-508.
Wild, B. & Schneewei, C. (1993). Manpower capacity planning - A hierarchical approach. International Journal of Production Economics, 30-31, 95-106.