An Excel Solver Model For Personnel Task Scheduling Problem: Application Of Medium Voltage Fuse Production

Year 2020, Volume: 12 Issue: 2, 729 - 744, 30.06.2020

Mehmet Pınarbaşı , Hacı Mehmet Alakaş

https://doi.org/10.29137/umagd.721797

Cited By: 1

Abstract

Along with increasing labor costs, labor-intensive firms tend to use the labor force more effective and balanced. For this reason, firms want to employ their personnel as to possible as maximum tasks. The personnel task scheduling problem is defined as the problem of assigning tasks to the personnel while one or more objectives are optimized. In this study, a mathematical model has been proposed for personnel task scheduling problem. The model is solved by using the Excel spreadsheet and Solver tool. The real-life data obtained from a firm that produces the medium voltage fuses are used in problem-solving. The proposed mathematical model is solved by using this real data. In order to measure the effectiveness of the Excel solver model, mathematical model results are compared with the solver’s performance. Thanks to the proposed worksheet solution, a significant contribution has been made to the efficient and productive use of the workforce in the firm.

Keywords

Personnel task scheduling problem, Workforce balancing

Personel Görev Çizelgeleme Problemi İçin Bir Excel Çözücü Modeli: Orta Gerilim Sigorta Üretimi Uygulaması

Abstract

Artan işçilik maliyetleri ile birlikte, işçi yoğun çalışan işletmeler işgücünü daha etkin ve dengeli olarak kullanmaya yönelmektedir. Bu sebeple işletmeler ellerinde bulundurdukları personeli mümkün olduğu kadar maksimum görevde kullanmaya çalışmak istemektedirler. Bir veya birden fazla amacı en iyileyecek şekilde personellere görevlerin atanması problemi personel görev çizelgeleme problemi olarak tanımlanmaktadır. Bu çalışmada personel görev çizelgeleme problemi için bir matematiksel model önerilmiştir. Model Excel çalışma sayfası ve Çözücü aracı kullanılarak çözülmüştür. Problem çözümünde orta gerilim sigorta üretimi yapan bir işletmeden elde edilen gerçek veriler kullanılmıştır. Bu gerçek veriler kullanılarak önerilen matematiksel model çözülmüştür. Excel çözücü modelinin etkinliğini ölçmek için matematiksel model sonuçları ile çözücü performansı karşılaştırmalı olarak verilmiştir. Önerilen çalışma sayfası çözümü sayesinde işletmede işgücünün daha etkin ve verimli kullanılmasına önemli ölçüde katkı sağlanmıştır.

Keywords

Personel görev çizelgeleme problemi, İşgücü dengeleme, Matematiksel modelleme

References

• Ağralı, S., Taşkın, Z.C., & Ünal, A.T. (2017). Employee scheduling in service industries with flexible employee availability and demand. Omega, 66, 159-169.
• Al-Yakoob, S.M., & Sherali H.D. (2007). Mixed-integer programming models for an employee scheduling problem with multiple shifts and work locations. Annals of Operations Research, 155, 119–142.
• Ashley, D.W. (1995). A spreadsheet optimization system for library staff scheduling. Computers & Operations Research, 22(6), 615-624.
• Bhulai, S., Koole, G., & Pot, A. (2008). Simple methods for shift scheduling in multiskill call centers. Manufacturing & Service Operations Management, 10(3), 411-420.
• Boute, R.N., & Lambrecht, M.R. (2009). Exploring the bullwhip effect by means of spreadsheet simulation. INFORMS Transactions on Education, 10(1), 1-9.
• Boyer, V., Gendron, B., & Rousseau, L.M. (2014). A branch-and-price algorithm for the multi-activity multi-task shift scheduling problem. Journal of Scheduling, 17(2), 185-197.
• Brezulianu, A., Fira, M., & Fira, L. (2009). A genetic algorithm approach for a constrained employee scheduling problem as applied to employees at mall type shops. Proceedings of the 2009 International Conference on Hybrid Information Technology, Dong Seoul University, Korea, ACM.
• Brunner, J.O., Bard, J.F., & Kolisch, R. (2009). Flexible shift scheduling of physicians. Health Care Management Science, 12(3), 285-305.
• Cuevas, R., Ferrer, J.C., Klapp, M., & Muñoz, J.C. (2016). A mixed integer programming approach to multi-skilled workforce scheduling. Journal of Scheduling, 19(1), 91-106.
• Cunha, C.B., & Fernando, M. (2007). A spreadsheet-based optimization model for the integrated problem of producing and distributing a major weekly newsmagazine. European Journal of Operational Research, 176(2), 925-940.
• Drezet, L.E., & Billaut, J.C. (2008). Employee scheduling in an IT company. Resource‐Constrained Project Scheduling: Models, Algorithms, Extensions and Applications, 243-255.
• Erdoğan, G. (2017). An open source spreadsheet solver for vehicle routing problems. Computers & Operations Research, 84, 62-72.
• Fırat, M., & Hurkens, C.A.J. (2012). An improved MIP-based approach for a multi-skill workforce scheduling problem., Journal of Scheduling, 15(3), 363-380.
• Günther, M., & Nissen, V. (2010). Particle swarm optimization and an agent-based algorithm for a problem of staff scheduling. European Conference on the Applications of Evolutionary Computation. Berlin: Springer-Heidelberg.
• Hojati, M. (2018). A greedy heuristic for shift minimization personnel task scheduling problem. Computers & Operations Research, 100, 66-76.
• Krishnamoorthy, M., Ernst, A.T., & Baatar, D. (2012). Algorithms for large scale shift minimisation personnel task scheduling problems. European Journal of Operational Research, 219(1), 34-48.
• Kumar, S., & Kropp, J. (2006). Studying the operational efficiencies of a multi-product supply chain using excel spreadsheet model. Technovation, 26(10), 1186-1200.
• La Trobe-Bateman, J., & Wild, D. (2003). Design for manufacturing: use of a spreadsheet model of manufacturability to optimize product design and development. Research in Engineering Design, 14(2), 107-117.
• Laesanklang, W., Pinheiro, R.L., Algethami, H., & Landa-Silva, D. (2015). Extended decomposition for mixed integer programming to solve a workforce scheduling and routing problem. International Conference on Operations Research and Enterprise Systems, Lisbon, Portugal.
• Lapègue, T., Odile, B.M., & Damien, P. (2013). A constraint-based approach for the shift design personnel task scheduling problem with equity. Computers & Operations Research, 40(10), 2450-2465.
• LeBlanc, L.J., Randels, D., & Swann, T.K. (2000). Heery International's spreadsheet optimization model for assigning managers to construction projects. Interfaces, 30(6), 95-106.
• Liao, S., Van Delft, C., & Vial, J.P. (2013). Distributionally robust workforce scheduling in call centres with uncertain arrival rates. Optimization Methods and Software, 28(3), 501-522.
• Louly, M.A.O. (2013). A goal programming model for staff scheduling at a telecommunications center. Journal of Mathematical Modelling and Algorithms in Operations Research, 12(2), 167-178.
• Omar, E.R., Garaix, T., Augusto, V., & Xie, X. (2015). A stochastic optimization model for shift scheduling in emergency departments. Health Care Management Science, 18(3), 289-302.
• Ovchinnikov, A., & Milner, J. (2008). Spreadsheet model helps to assign medical residents at the University of Vermont's College of Medicine. Interfaces, 38(4), 311-323.
• Parisio, A., & Jones, C.N. (2015). A two-stage stochastic programming approach to employee scheduling in retail outlets with uncertain demand. Omega, 53, 97-103.
• Powell, S.G., & Baker, K.R. (2014). Management science: The art of modeling with spreadsheets. New Jersey: Wiley
• Prot, D., Lapègue, T., & Bellenguez-Morineau, O. (2015). A two-phase method for the shift design and personnel task scheduling problem with equity objective. International Journal of Production Research, 53(24), 7286-7298.
• Rocha, M., Oliveira, J.F., & Carravilla, M.A. (2014). A constructive heuristic for staff scheduling in the glass industry. Annals of Operations Research, 217(1), 463-478.
• Sakalli, U.S., & Birgoren, B. (2009). A spreadsheet-based decision support tool for blending problems in brass casting industry. Computers & Industrial Engineering, 56(2), 724-735.
• Salvagnin, D., & Walsh, T. (2012). A hybrid MIP/CP approach for multi-activity shift scheduling, Principles and practice of constraint programming. Berlin: Springer-Heidelberg.
• Shetty, D., Ali, A., & Cummings, R. (2010). Survey-based spreadsheet model on lean implementation. International Journal of Lean Six Sigma, 1(4), 310-334.
• Smet, P., Wauters, T., Mihaylov, M., & Berghe, G.V. (2014). The shift minimisation personnel task scheduling problem: A new hybrid approach and computational insights. Omega, 46, 64-73.
• Solos, I.P., Tassopoulos, I.X., & Beligiannis, G.N. (2016). Optimizing shift scheduling for tank trucks using an effective stochastic variable neighbourhood approach. Int. J. Artif. Intell, 14(1), 1-26.
• Soukour, A.A., Devendeville, L., Lucet, C., & Moukrim , A. (2012). Staff scheduling in airport security service. IFAC Proceedings Volumes, 45(6), 1413-1418.
• Topaloglu, S. (2009). A shift scheduling model for employees with different seniority levels and an application in healthcare. European Journal of Operational Research, 198(3), 943-957.
• Trautmann, N., & Gnägi, M. (2015). On an application of Microsoft Excel's evolutionary solver to the resource-constrained project scheduling problem RCPSP. Industrial Engineering and Engineering Management (IEEM) (pp. 646-650).
• Valls, V., Pérez, Á., & Quintanilla, S. (2009). Skilled workforce scheduling in service centres. European Journal of Operational Research, 193(3), 791-804.
• Zolfaghari, S., Quan, V., El-Bouri, A., & Khashayardoust, M. (2009). Application of a genetic algorithm to staff scheduling in retail sector. International Journal of Industrial and Systems Engineering, 5(1), 20-47.