An Excel Based Tool Development for Scheduling Optimization

Scheduling can be seen in many areas such as chemical processing, logistic, supply chain, and class. In this study, two different cases of scheduling problems are addressed which are batch reactor process scheduling and University Class Scheduling (UCS) problems. The aim for the batch reactor process scheduling is to minimize the total discrepancies between the size of the assigned reactors and their corresponding assigned capacities or reactor sizes. The approach taken into consideration includes some constraints such as desired products, the production capacities, and the reactor capacities. Meanwhile, for the UCS problem, the concern is on the number of needed courses assign to certain classrooms while at the same time noting the constraints such as the size of the classroom and the number students. The UCS problem also seeks to optimize the distribution of courses remarkably to classrooms refers to the ratio of classroom capacity to course enrolment. These issues will be resolved using Integer Linear Programming (ILP) in the form of Excel-based software. The ILP model tool for both scheduling problems, therefore, is developed and resolved using the Excel solver utilizing Visual Basic Applications (VBA) and Macro. Article History: Received 17 Nov 2020 Revised 19 Jan 2021 Accepted 12 Feb 2021 Available online 26 Feb 2021 ____________________ Keyword: Batch process scheduling, University Class Scheduling, Optimization, Integer Linear Programming (ILP) ASEAN Journal of Science and Engineering Education Journal homepage: https://ejournal.upi.edu/index.php/AJSEE/ ASEAN Journal of Science and Engineering Education 1(1) (2021) 7-14 © 2021 Universitas Pendidikan Indonesia Muhammad Afwan Mohamad, et.al. An Excel Based Tool Development for ... | 8 DOI: http://dx.doi.org/10.xxxxx/AJSEE.v1i1 pISSN 2528-1410 eISSN 2527-8045


A B S T R A C T S A R T I C L E I N F O
Scheduling can be seen in many areas such as chemical processing, logistic, supply chain, and class. In this study, two different cases of scheduling problems are addressed which are batch reactor process scheduling and University Class Scheduling (UCS) problems. The aim for the batch reactor process scheduling is to minimize the total discrepancies between the size of the assigned reactors and their corresponding assigned capacities or reactor sizes. The approach taken into consideration includes some constraints such as desired products, the production capacities, and the reactor capacities. Meanwhile, for the UCS problem, the concern is on the number of needed courses assign to certain classrooms while at the same time noting the constraints such as the size of the classroom and the number students. The UCS problem also seeks to optimize the distribution of courses remarkably to classrooms refers to the ratio of classroom capacity to course enrolment. These issues will be resolved using Integer Linear Programming (ILP) in the form of Excel-based software. The ILP model tool for both scheduling problems, therefore, is developed and resolved using the Excel solver utilizing Visual Basic Applications (VBA) and Macro.

INTRODUCTION
The latest trend in the chemical process industry according to Grossmann (2004) is that the plants producing high-value products in a small quantity such as the biotechnology, nanotechnology, specialty chemicals, and pharmaceutical sectors will be growing in demand. Due to this trend, the batch plant process flexibilities will be a major advantage and a significant research area. Thus, more and more research work have been focusing on the batch process scheduling (Foo et. al. 2007).
In terms of definition, scheduling can be defined as a method to distribute a valuable resource to a designated process. In addition, scheduling is done to prioritize a set of rules and balancing the load on the system to ensure equal distribution of resources. In terms of batch scheduling, the objective is to minimize the allocation of production capacity towards the reactor volume or vessel size. This research will improve the efficiency of production capacity distribution to the reactor volume in a fairly manner.
Meanwhile, University Course Scheduling (UCS) problem almost draws some similarities in their planning and scheduling. It is focused on allocating courses to classrooms taking into account constraints such as classroom sizes and university rules and regulations (Wasfy and Aloul, 2007). Course scheduling or timetabling problem can be subdivided into three categories according to Saltzman (2009) which are school, university course, and exam timetabling problem.
Conventionally, scheduling process is being conducted manually. This process often takes a long time since there are several constraints associated with the problems. In addition, complicated human factors and resources limitation involved causing the process to be more time consuming. The scheduling problem is an interesting and well researched version of the timetabling problem that is tackled by educational institutions on a continuous basis (Dahiya, 2015).
Nowadays, the use of computers to perform scheduling process through methods such as mathematical modeling with the integration of programming can consolidate the preferences despite large numbers of constraints involved. Through computational and automated approach, a lot of time will be saved (Chen and Shih, 2013).
In this work, an ILP program will be developed capable to solve simple batch process scheduling as well as class scheduling problem. The mathematical formulation will be as general as possible since both cases have a same concept in terms of solution approaches.

LITERATURE REVIEW
The main purpose of process scheduling approaches is to find the most effective method in producing a certain product which is limited in resources and processing recipes. The tasks to be scheduled mostly being done in multiproduct and multipurpose plants. For a period of time, these kinds of plants have been employed to manufacture high added-values or frequently changing process parameters of chemicals. Due to its flexibility in the operation, a good production schedules can give a great advantages and benefits the operation process (Floudas and Lin, 2005).
From the study done by Floudas and Lin (2005), various researches related to process scheduling with respect to chemical engineering and the operations research literature have been conducted. In 1996, Reklaitis (1996) discussed the planning and operation scheduling of batch processes by focusing on the basic components of the scheduling problems and the available solutions. Meanwhile, Rippin encapsulated the progress of batch operation system which focuses on the scheduling, design, planning, and uncertainties (Ripin, 1993). In 1996, Grossman et. al. (1996 provided an overview on mixed-integer optimization techniques for the batch chemical processes scheduling and design. The recent developments for mixedinteger linear and nonlinear programming problems and solution methods were being the focus of those paper as well as issues in modeling and reformulation (Grossman et. al., 1996).
Scheduling technologies such as randomized or constraint guided search, rule-based methods, simulation-based strategies, and also mathematical programming approaches were being reviewed (Pekny and Reklaitis, 1998). Other than that, issues regarding the development and use of scheduling software were being addressed by Applequist et. al., (1997).
In operational research, the UCS is considered as a crucial class for the problems of optimization. Nowadays, UCS has been known to be a difficult problem faced by educational institutions. In short definition, with the increasing number of courses and student intakes, while at the same time limited number of classrooms, the aim is to allocate courses to classrooms while obeying the constraints and optimizing the utilization of existing classroom facilities efficiently and effectively (Wasfy and Aloul, 2007).
In order to solve UCS, various algorithms and formulations have been proposed (Wasfy and Aloul, 2007). According to Saltzman (2009), some researchers, such as Shih and Sullivan (1997) used a binary integer programming model to assign courses to lecturers which then allocate courses to time slots, in several terms. Deris et. al., (2000) have fixated on heuristic algorithms while Foulds and Johnson (2000) constructed a decision support platform to help experienced course scheduler by addressing scheduling conflicts, proposing alternatives, and presenting a numbers of reports. Dimopoulou and Miliotis (2001) addressed the same problems with a two-stage approach. Yet, in their course scheduling stage, courses to time slots and rooms are assigned, but instructor's preferences are not included.
According to Daskalaki et. al., (2004), there are several rules that need to be adhered when constructing a timetable. Rules which are important and could never be violated are known as hard constraints. The other constraint is called soft constraints which most of the time will be considered after all hard constraints are satisfied. Typically, soft constraints will be addressed for a better solution approach and further improvement.
In this paper, the use of advanced ILP solvers approach is used in solving the batch process and UCS problem. The mathematical optimization formulation for both integer and linear is developed in Excel using few simple codes in VBA. The problem is then solved using the LP solver in Excel Solver.

MATERIALS AND METHODS
To illustrate this approach, consider a factory with n processes and m reactors. In general, the number of reactors m is larger than or equal to the number of processes n. For each process i, we define m variables as follows: xij = 1 if process i is assigned to reactor j, 0 otherwise. Three sets of constraints are described as follows: -Each process must be assigned to one reactor. This can be expressed using the following constraint in Equation 1: (1) -Each reactor can fit up to one process to avoid allocating two processes in the same reactor. This can be described using the following constraint in Equation 2: (2) -Each reactor capacity must be equal to or larger than the processes production capacity. This can be described using the following constraint in equation 3: Where T is the set of reactors which capacity is less than the number of production capacity in reactor i.
The above three constraints will satisfy the batch process constraints and ensure that each process gets assigned to a reactor with a larger (or equal) capacity than the process production. However, these three equations are not enough to make sure that each reactor is used or allocated appropriately. It is still possible that small process production will be assigned to other large reactors. In order to avoid that, the following objective function in Equation 4 is implemented: Where cij is equal to the reactor unit capacity j divided by the number of production capacity in reactor i. Since objective of the function is to minimize the value, hence the Excel solver will choose the ratio of j divided by i that produced the nearest value to number one and subjected to the other three constraints earlier. This Equation 4 serves to impose that the size of any assigned reactor or class to be as close as possible to the size of the production batch or the group of students. In order to develop a user friendly tool that will be used to solve a scheduling problem, the hard constraints that need to be use for the problems need to be identified. Table 1 shows the hard constraints that are being used in this research. Table 1. Descriptions of hard constraints

Collision is not permitted
A collision will occur when two or more processes are allocated at the same period in the same reactor and the other way around.
When two or more lectures/courses are allocated at the same period by the same lecturer, for the same group of students or in the same classroom. Furthermore, when two or more classrooms are assigned to the same course and to the same group of students is also considered as collision.

The timetable must be completed.
Batch process schedule is complete when all types of products planned for the respective reactor are shown in the timetable, with the corresponding duration for every processes and every portion of each processes.
A timetable is said to be complete when all courses planned for every group of students is shown in the timetable, with the corresponding course duration for every course and every portion of each course. Once the hard constraints are identified, the ILP formulation based on those constraints will be developed as shown in Equation 1, 2 and 3. Due to limitation of the Excel solver that can only use 200 cells assigned constraints, a small scale case study will be solved for this research.
Once the Solver in MS Excel generates the solution on the problem, an easy-to-use interface will be developed for the data input. Next, an automated table will be constructed in order for the results to be displayed in a better representation and easier to be understood. Three Excel functions were being used in developing the table which are 'IF', 'SEARCH' and 'ISNUMBER' function.
To further optimize and improve the Excel file, an automated 'SOLVE' and 'RESET' buttons are added. These buttons are equipped with Macro and VBA codes in Excel. With the addition of these buttons, user will only need to key in the data input followed by the click of the 'SOLVE' button; the Excel software will generate the solution and displays the result on the table automatically. The 'RESET' button will reset back all the data so that there is no need to delete the manually one by one. This will become a friendly-user tool for the scheduling problem.

RESULTS AND DISCUSSION
In this example, consider a paint production plant with the following data in Table 2 and 3. Table 2 shows 4 types of paint production processes with different numbers of production capacity for 3 different types of paint colours.   All the data from Table 2 and 3 were inserted inside the Excel Data input section. Then all the constraints were checked in order to ensure all constraints were satisfied with the example. The 'SOLVE' button was then clicked and Excel Solver generated the solution instantly. Figure 1 below shows the computational result in Excel.

Figure 1. ILP computational result in Excel
From the figure above, the cells that consist of number one (1) and highlighted with blue colour indicates that the respected process for certain types of product is being assigned to the respected reactor on certain day. For example, paint that will undergo process A which is Lily white colour will be assigned to reactor W on Wednesday. The numbers denote the period, e.g.; W1=Reactor W on MONDAY.
Once the Excel solver generates the results, another table was constructed in order for the results to be displayed in a better representative and easier to be understood. There are two tables with different data arrangement that can be generated automatically according to solver result as shown in Figure 2.  Table 2 is using the Excel 'ISNUMBER' along with 'SEARCH' function. The 'ISNUMBER' function returns TRUE when a cell contains a number, and FALSE if not. However, since the cell contains text and not numbers, 'SEARCH' function can be used for this purpose. Either of these two tables can be shown at the data input interface based on one's preferences.

Figure 2. Automated result tables
To make the tool more user friendly, some additional buttons are added. A table for data input and data reset were developed. The 'SOLVER' and 'RESET' buttons were added by using the Macro and VBA function in the Excel. A macro is a series of commands that can be used to automate a repeated task, and can be run when perform the task. The data input, 'SOLVER' and 'RESET' button representation is shown in Figure 3.

CONCLUSION
To summarize the whole approach, an ILP formulation for the scheduling problems has been shown. A user friendly Excel tool for the scheduling process has been developed. The Excel tool has been tested in a simple case study and proved to work. For future recommendations and continuation of the project, since the Solver function in Microsoft Excel can only cater a limit of 200 decision variables for both linear and nonlinear, an adjustment is necessary. This adjustment requires the integration of Excel (as the user interaction platform) with GAMS (General Algebraic Modeling System) software. Then, larger systems can be solved.

ACKNOWLEDGEMENT
The authors thank Yayasan Universiti Teknologi PETRONAS for providing the research grant (Grant number: 0153AA-H03). Authors also appreciate the support from Chemical Engineering Department of Universiti Teknologi PETRONAS.