An Excel Based Tool Development for Scheduling Optimization

Muhammad Afwan Mohamad, Zulfan Adi Putra, M Roil Bilad, Nik Abdul Hadi Md Nordin, M Dzul Hakim Wirzal


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.


Batch process scheduling; University Class Scheduling; Optimization; Integer Linear Programming (ILP)

Full Text:



Applequist, G., Samikoglu, O., Pekny, J., & Reklaitis, V. (1997). Issues in the Use, Design and Evolution of Process Scheduling and Planning Systems. ISA Transactions, 36(2), 81-121.

Chen, R. M., & Shih, H. F. (2013). Solving University Course Timetabling Problems Using Constriction Particle Swarm Optimization with Local Search. Algorithms, 6(2013), 228-244.

Dahiya, S. (2015). Course Scheduling With Preference Optimization. MSc Thesis. Computer Science Program. Pennsylvania, The Pennsylvania State University.

Daskalaki, S., Birbas, T., & Housos, E. (2004). An integer programming formulation for a case study in university timetabling. European Journal of Operational Research, 153(1), 117-135.

Deris, S., Omatu, S., & Ohta, H. (2000). Timetable Planning Using the Constraint-Based Reasoning. Computers and Operations Research, 27(9), 819-840.

Dimopoulou, M., & Miliotis, P. (2001). Implementation of a university course and examination timetabling system. European Journal of Operational Research, 130(1), 202-213.

Floudas, C. A., & Lin, X. (2005). Mixed Integer Linear Programming in Process Scheduling: Modeling, Algorithms, and Applications. Annals of Operations Research, 139(2005), 131-162.

Foo, D. C., Hallale, N., & Tan, R. (2007). Pinch Analysis Approach to Short-Term Scheduling of Batch Reactors in Multi-Purpose Plants. International Journal of Chemical Reactor Engineering, 5(1), 1-14.

Foulds, L., & Johnson, D. (2000). SlotManager: a microcomputer-based decision support system for university timetabling. Decision Support Systems, 27(4), 367–381.

Grossman, I. E. (2003). Challenges in the new millennium: Product discovery and design, enterprise and supply chain optimization, global life cycle assessment. Computer Aided Chemical Engineering, 15(2003), 28-47

Grossmann, I. E., Quesada, I., Raman, R., & Voudouris, V. T. (1996). Mixed-Integer Optimization Techniques for the Design and Scheduling of Batch Processes. Batch Processing Systems Engineering, 143(1996), 451-494.

Pekny, J., & Reklaitis, G. (1998). Towards the Convergence of Theory and Practice: A Technology Guide for Scheduling/Planning Methodology. Proceedings of the Third International Conference on Foundations of Computer-Aided Process Operations, (1998), 91-111.

Reklaitis, G. V. (1996). Overview of Scheduling and Planning of Batch Process Operations. Batch Processing Systems Engineering, 143(1996), 660-705.

Rippin, D. (1993). Batch process systems engineering: A retrospective and prospective review. Computers & Chemical Engineering, 17(1), 1-13.

Saltzman, R. (2009). An Optimization Model for Scheduling Classes in a Business School Department. California Journal of Operations Management, 7(1), 84-92.

Shih, W., & Sullivan, J. A. (1977). Dynamic course scheduling for college faculty via zero-one programming. Decision Sciences, 8(4), 711-721.

Wasfy, A., & Aloul, F. A. (2007). Solving the University Class Scheduling Problem Using Advanced ILP Techniques. Department of Computer Engineering, American University of Sharjah (AUS). Paper presented at the 4th IEEE GCC Conference.



  • There are currently no refbacks.

Copyright (c) 2021 Universitas Pendidikan Indonesia

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

View My Stats
ASEAN Journal of Science and Engineering Education (AJSEE) is published by Universitas Pendidikan Indonesia (UPI)