WIXLIB

Rita KumarA Spreadsheet-based Scheduling Model to Create Individual Graduation RoadmapsA Spreadsheet-based Scheduling Model to CreateIndividual Graduation RoadmapsRita Kumar*California State Polytechnic University, Pomona, California, USAWe address the problem of creating individual graduation roadmaps for students on a dynamicbasis. Graduation roadmaps show suggested courses to take each term, and can be valuable tostudents in planning their coursework. The problem is modeled using integer programming withthe objective of minimizing the time to degree completion, and a simplified version is solved usingthe Analytic Solver Platform in Microsoft Excel. This research makes two contributions to theliterature: it presents a prototype of a new tool to aid students with planning their path to degreecompletion; and it provides an example of a real-life problem that can be used in the classroom todemonstrate an application of optimization/prescriptive analytics using spreadsheet software.* Corresponding Author. E-mail address: adkumar@cpp.edutimely completion of a degree are graduationroadmaps. These provide an overview of theEnsuring timely completion of a college requirements for degree completion, along withdegree is a high priority for Colleges and a way to meet the requirements in a timelyUniversities across the nation, as evidenced by manner (typically four years for first-timethe continued focus on graduation rates. For freshmen and two years for transfer students).example, The California State University (CSU) However, these roadmaps are static, andlaunched its Graduation Initiative 2025 in generic. Further, they assume that students willJanuary 2015 with a clear goal: to increase typically take a full load of courses and that thegraduation rates for its 475,000 students across courses recommended to be taken in aall 23 campuses (CSU Office of the Chancellor, particular term will in fact be offered during2016). As of September 2016, the initiative that term. The current low four-year graduationestablished a series of objectives, including rates for first-time freshmen and two-yearincreasing the four-year graduation rate for graduation rates for transfer students suggestfirst-time freshmen from the current 17% to that the roadmaps may not be realistic for all40%, and the corresponding six-year rate from students to follow. Thus there is a need to createindividual roadmaps on a dynamic (term-by57% to 70% (CSU System Plan, 2016).As universities focus on graduation term) basis, which is the motivation for thisrates (and correspondingly the times to degree research.Other tools for degree planning havecompletion), several tools are deployed to aid inthe degree planning process. According to a also been rolled out at several campuses of thesurvey of advisors, technology is widely used to CSU, including Cal Poly Pomona. Two of theseprovide access to resources for course and are My Planner (Cal Poly Pomona My Planner,degree planning (Pasquini and Steele, 2016). 2016) and Schedule Builder (Cal Poly PomonaAmong the advising tools available to facilitate Schedule Builder, 2016). While both areI. INTRODUCTIONJournal of Supply Chain and Operations Management, Volume 15, Number 2, December 2017165

Rita KumarA Spreadsheet-based Scheduling Model to Create Individual Graduation Roadmapsdesigned to help students plan their coursework,they have limitations. My Planner does not haveany optimization or constraint checkingcapabilities, and Schedule Builder has a shortterm focus on creating a detailed schedule forone term. This research considers both anoptimization perspective as well as a multi-termoutlook, and develops a prototype tool tosupport coursework planning for degreecompletion.The specific context is one departmentin one of the campuses of the CSU system. Thescheduling (course planning) problem facing astudent each term is modeled as an integerlinear programming problem, with theobjective of minimizing the number ofacademic terms to degree completion (anacademic term in the context of this paper is aquarter, however, it could apply to semesters aswell). Constraints include hard constraints, forexample: All courses/units required must becompleted, a course may be taken only ifprerequisites are satisfied, a course may betaken in a particular term and timeslot only if itis offered during that term and timeslot, and twocourses may not be taken in the same term ifthey have overlapping timeslots. In addition tothe hard constraints, there are soft constraints,for example, the maximum number ofcourses/units that may be taken in a term tobalance work and school requirements, andbalancing the load between different types ofclasses. A simplified version of the problem isthen solved using the Analytic Solver Platformin Microsoft Excel.The purpose of this research is two-fold:first, as a prototype of a new tool to supplementadvising resources available to aid studentswith planning their path to degree completion;and second, as an example that could be used inthe classroom to demonstrate an application et software, with the context of a reallife problem that students can relate to.The remainder of the paper is organizedas follows. Section II provides an overview ofthe relevant literature. The problem is describedin further detail in Section III. The integer linearprogramming model is presented in Section IV.Section V discusses the use of a spreadsheetapproach to solve a simplified version of themodel. Analytical results of the model,including solutions corresponding to differentscenarios, are presented in Section VI. SectionVII concludes with contributions, limitations,and suggestions for future enhancements.II.LITERATURE REVIEWThis research approaches the degreeplanning problem from a constrainedoptimization perspective, modeling it is amathematical programming problem with theobjective of minimizing the time to degreecompletion. While mathematical programmingis a widely used approach to modeloptimization problems, including in universitycontexts (e.g., Babaei, Karimpour, and Hadidi,2015; Schimmelpfeng and Helber, 2007), thedegree planning problem does not appear tohave been widely addressed in the modelingliterature. Scheduling problems in g/course scheduling issues involvingscheduling classes, instructors, timeslots, androoms for a single academic term at a time (e.g.,Kassa, 2015; Rudova, Miller, and Murray,2011).Babaei et al (2015) present a survey ofapproaches for the university coursetimetabling problem, and identify the followingcategories of approaches to the problem:operations research (e.g., mathematicalprogramming), metaheuristics (e.g., geneticalgorithms), multi-criteria approaches, ce). Schimmelpfeng and Helber (2007)describe a mathematical programmingapproach to create a timetable of all courses fora term. They model the problem as anassignmentproblem,andincorporateconstraints including core and elective courses,Journal of Supply Chain and Operations Management, Volume 15, Number 2, December 2017166

Rita KumarA Spreadsheet-based Scheduling Model to Create Individual Graduation Roadmapsas well as teacher preferences. Their model issolved using CPLEX.Kassa (2015) describes a multi-stageinteger programming approach to the coursescheduling problem. The first stage determinesthe optimal assignment of instructors to coursesections, while subsequent stages focus on theassignments of rooms and times. The model issolved using an AMPL-Gurobi package.Rudova et al (2011) describe an iterativeforward search algorithm for the coursescheduling problem, focusing on hardconstraints first, and adding soft ty settings have primarily focused oncourses, instructors, timeslots, and rooms, therehas been some research on student scheduling.For example, Head and Shaban (2007) addressthe problem of scheduling students in a firstyear program. In their context, all courses arerequired of all students, and there are noelectives. Their approach is to build theschedule and place the students into classessimultaneously. The model focuses onsatisfying all the hard constraints andminimizing the violations of the soft constraints.The system uses Visual Basic with embeddedSQL. Causmaecker, Demeester, and Berghe(2009) use a metaheuristic procedure whereconstraints are solved one by one rather than allat once. They consider student groups ratherthan individual students, where students aregrouped according to the required courses thatthey need to take.To summarize, course scheduling hasbeen widely researched in the literature, andseveral solution procedures developed.However, these are typically focused ondeveloping detailed schedules and assignmentsfor a single term, and do not consider the pathto degree planning from the perspective ofindividual students.Research on specific models forindividualized degree planning spanningmultiple terms is limited. Wermus and Pope(1999) draw an analogy between courseworkplanning and Material Requirements Planning,wherein individual courses are analogous to thecomponents that go toward the final product(the degree). However, it is more of aconceptual paper and does not present anyspecific model. Chen, Wang, Chen, and Luo(2014) present an integer programmingapproach to planning coursework from anindividual student’s perspective, taking intoaccount student preferences for timeslots andelective courses, however, their model onlyconsiders a single term.Dechter (2007, 2009) considersindividualized degree planning across multipleterms. He models the degree planning problemusing constraint programming as well asmathematical programming, with the objectiveof minimizing degree completion time. Themodel incorporates core and elective courses,prerequisites, and a maximum course load perterm. However, the model does not considerspecific timeslots for courses, planned courseofferings, or student time availabilities. Theunderlying assumption is that courses includedin the model solution will in fact be offeredduring the terms scheduled by the model, thatthere will not be any time overlaps for multiplecourses scheduled in a particular term, and thatthere are no constraints on the student’savailabilities during different times/days.This paper draws on the research byDechter. It includes the main elements of hismodel: degree planning spanning multipleterms; and the consideration of prerequisites,core and elective courses, and a maximumcourse load each term. Further, it extends themodel by incorporating all three considerationsnot included in Dechter’s research: timeslots forcourses, planned course offerings over theplanning horizon, and student availability bytimeslots and terms. Dechter’s solutionapproaches for a simplified version of theproblem are ILOG for the constraintprogramming model, and CPLEX for themathematical programming model. This paperJournal of Supply Chain and Operations Management, Volume 15, Number 2, December 2017167