An Application of Solver in Microsoft Excel for Solving Job Scheduling Problem: A Case Study of a Hospital in Maha Sarakham

Abstract

This research aims to apply Solver in Microsoft Excel to solve the job scheduling problem in the hospital’s department. The job scheduling problem is challenging to resolve because it has to consider various conditions and constraints for determining the job scheduling problem solution. This study focuses as a case study on the job scheduling problem for nurses who work in a hospital’s department. Generally, the nurse work schedule is arranged by the heads of the nurses based on their experience of scheduler and competence. This would likely cause mistakes and take long time, unnecessarily, to solve the nurse job scheduling problem. A mathematic model has been developed, and a solver program based on Microsoft Excel is proposed in this study for effectively computing the nurse job scheduling. The results have shown that there are three nurse leaders and six nurses in the selected department. Every week, the head nurses ask all nurses for their availability to come to work to finalize the weekly schedules. The heads have to make the nurse schedules by considering the number of nurses required for each shifts three times a day, including morning, afternoon, and night shifts. There are five important conditions that need to be considered when making nurse scheduling in the department. Therefore, to find a solution for nurse scheduling difficulties, the conditions and constraints are designed for a mathematical model by considering the requirement of nurse number for each shift in a day. Then, the Solver program in Microsoft Excel was used to calculate the best solution for the nurse scheduling problems. The Solver used only 10 minutes for finding the solution, while the current method used about 3 hours. The results showed that the solver can solve the nurse scheduling problem and find an efficient and exact solution under the terms and conditions of nurse job scheduling (100%).