Spreadsheet Model Approaches for Generating Student Class Schedules

1. Spreadsheet Model Approaches for Generating Student Class Schedules Robert L. Armacost Director, University Analysis and Planning Support Magdy Helal Research Associate, University Analysis and Planning Support Dianne Adams Coordinator of Computer Applications, University Analysis and Planning Support University of Central Florida 2005 SAIR Conference October 24, 2005 Presentation available at http://uaps.ucf.edu

2. Goals for the Presentation • Challenges in addressing graduation rates • Introduction to Grad On Track solution • Challenges in meeting guarantees • Challenges developing student class schedules • Optimization approach using Solver Add-in • Limited enumeration approach • Stand-alone Visual Basic implementation • Comparison of approaches Spreadsheet Models for Generating Student Class Schedules

3. The University of Central Florida Stands for Opportunity • Established in 1963 in Orlando Florida (first classes in 1968), Metropolitan Research University • Grown from 1,948 to 45,000 students in 37 years • 38,000 undergraduates and 7,000+ graduates • 12 regional campus instructional sites • 9th largest public university • Doctoral intensive • 92 Bachelors, 94 Masters, 3 Specialist, and 25 PhD programs • Largest undergraduate enrollment in state • Approximately 1,200+ faculty and 3,100 staff • Nine colleges • Arts and Sciences, Biomedical Sciences, Business Administration, Education, Engineering and Computer Science, Health and Public Affairs, Honors, Hospitality Management, and Optics and Photonics Spreadsheet Models for Generating Student Class Schedules

4. Graduation is the Goal Large Metropolitan Research University 6-year Graduation Rates—IPEDS 97 Spreadsheet Models for Generating Student Class Schedules

5. Graduation is the Goal Large Metropolitan Research University 4-year Graduation Rates—IPEDS 99 Spreadsheet Models for Generating Student Class Schedules

6. Graduation is the Goal UCF Time to Degree —2003-04 Baccalaureate Graduates Average time to graduation = 11.83 semesters 67.9% graduated in 12 semesters or less (Semester count includes summer, fall, and spring) Spreadsheet Models for Generating Student Class Schedules

7. Graduation is the Goal • BUT • Florida SUS 4-year graduation rate = 33% • University of Florida 4-year graduation rate = 52% • University of Central Florida 4-year graduation rate = 29.5% • Differences in institutional mission • Time to degree • 1998 FTIC cohort—46-51% of graduates graduate in 4 years or less (81-90% in 5 years or less) • 1998 FTIC cohort—41-47% dropped out • 2003-04 graduates—average time to degree was 11.83 semesters Spreadsheet Models for Generating Student Class Schedules

8. Improve Graduation Rate • Delayed graduation represents significant lifetime losses • Perception: UCF = U Can’t Finish • Class availability • Unplanned delays • System inefficiency—excess hours • Solution: • New program that guarantees class availability • Grad On Track (GOT) was born Spreadsheet Models for Generating Student Class Schedules

9. Grad On Track http://gradontrack.sdes.ucf.edu Spreadsheet Models for Generating Student Class Schedules

10. Grad On Track Requirements • FTIC student • Declares single major on entry • Has appropriate math and chemistry placements • Agrees to follow a restricted schedule for the major • Limited electives • Generally 15 hours per semester • Maintains satisfactory performance • UCF • Guarantees a seat in every class that is needed • Guarantees graduation in four years Spreadsheet Models for Generating Student Class Schedules

11. Biology GOT Schedule Spreadsheet Models for Generating Student Class Schedules

12. How to Guarantee Classes? • Biology BS • BSC 2010: 3 lecture sections, 22 lab sections • CHM 2045: 16 lecture sections • ENC 1101: 110 lecture sections • MAC 2311: 22 lecture sections • 2,555,520 combinations!!! • Potential conflicts with times that classes are offered • Technical challenge • Construct a feasible class schedule that identifies which sections of which courses need seats to be reserved • Administrative challenge • How to actually reserve those seats Spreadsheet Models for Generating Student Class Schedules

13. Scheduling Approaches • Foundation is first obtaining scheduled dates and times for all classes offered • Objective is to identify a feasible schedule for a given program • Problem reduces to finding a class schedule for one week • Alternative scheduling approaches • Optimization-based approach—find a feasible solution for a particular “set-covering” 0-1 integer program • Enumeration-based approach—develop a feasible schedule by constructing a schedule adding one class at a time Spreadsheet Models for Generating Student Class Schedules

14. System Guidelines • Scheduling tool should • Read input from the university database (class data) • Classes open in the semester • Number of sections of each class • Meeting dates for each section • Meeting start and end times • Read input from academic departments (program data) • List of courses required for the program in that semester • Manipulate these inputs • Generate feasible schedules for the classes • Implementation should • Be accessible on a desktop or on the Web • Spreadsheet based (initially) Spreadsheet Models for Generating Student Class Schedules

15. Structure Spreadsheet Models for Generating Student Class Schedules

16. Class Data Preparation • Download from PeopleSoft to Excel • Used as is • Import to Access database • Filter then format and code meeting times • Generate Excel sheets for input to scheduler Spreadsheet Models for Generating Student Class Schedules

17. Program Requirement Data • Obtained for each program with GOT students • Use shown template Spreadsheet Models for Generating Student Class Schedules

18. Optimization-based Scheduler • Finding student class schedule is a “set-covering” problem • Find the class sections that will “cover” the “set” of program requirements (courses) • Constraints • No two sections can be scheduled at the same time • Exactly one section of each course must be scheduled during a week • Maximum of five hours of classes may be scheduled in a given day • Objective • No particular objective is required—only looking for a feasible solution • Objective could be changed to generate different feasible solutions Spreadsheet Models for Generating Student Class Schedules

19. Excel Setup • Columns correspond to class sections offered at different times • Rows correspond to half-hour time periods for each day of the week • Cell values = 1 if class section is offered at that time or = 0 if section is not offered at that time • Decision variable row cells = 1 if that section of the course is scheduled and = 0 otherwise • SOLVER Add-in • Tools > Solver (go to Tools > Add-ins and check “Solver Add-in” if not loaded) • “Target cell” is the objective to be optimized • “Changing cells” are the decision variables • “Constraints” are the conditions to be satisfied Spreadsheet Models for Generating Student Class Schedules

20. Solver Setup Spreadsheet Models for Generating Student Class Schedules

21. Solver Spreadsheet Models for Generating Student Class Schedules

22. User Output Spreadsheet Models for Generating Student Class Schedules

23. The Scheduler • Used VBA to integrate data preparation and generate multiple schedules Spreadsheet Models for Generating Student Class Schedules

24. Demo Spreadsheet Models for Generating Student Class Schedules

25. Solver Pros and Cons • Pros • Generates feasible solutions • Modifiable to add other constraints (e.g., minimum time between classes) • Relatively easy to customize output • Cons • Requires mathematical understanding to set up • Requires careful mapping of class schedule data • Relatively long execution times • Potential automation connection problems • Need to “trick” the set up to generate alternate schedules Spreadsheet Models for Generating Student Class Schedules

26. Enumeration Approach • Potential for reducing processing time • Use existing data structure with class and program information • Constructive generation of student class schedule • Apply heuristic scheduling rule • Schedule most restrictive class first • Add next most restrictive class while satisfying time conflict constraints • Number of feasible schedules is limited by the amount of time to be spent or number specified in advance • Output format is same as for Solver Spreadsheet Models for Generating Student Class Schedules

27. Enumeration Approach Spreadsheet Models for Generating Student Class Schedules

28. Demo Spreadsheet Models for Generating Student Class Schedules

29. Enumeration Pros and Cons • Pros • Easier to set up than Solver • Computer processing time efficient • Less automation connection problems • Cons • Rigid structure—must be recoded for customized results • Must be run until finished to get any solutions • Limited number of feasible solutions as coded Spreadsheet Models for Generating Student Class Schedules

30. Stand-Alone VB Application • Based on the constructive enumerative approach Spreadsheet Models for Generating Student Class Schedules

31. Demo Spreadsheet Models for Generating Student Class Schedules

32. VB Application Pros and Cons • Pros • Offers more control and user-friendly capabilities than Solver or Enumeration • No need to deal with underlying Excel or database files • Faster, at least 10 times faster when using Solver DLL • Extendable to connect directly to the internet, SQL servers, and university database • Scalable • No language limitations as with VBA • Cons • Advanced programming skills • Special software and/or add-ins needed • Difficulty in using automation and data objects • Time consuming to develop • Sensitive to software releases and upgrades Spreadsheet Models for Generating Student Class Schedules

33. Use of Student Schedules • Output includes a relatively large (50-60) set of schedules • Replaces previous trial and error generation approach • GOT first year advisor • Selects a schedule and registers the student • Uses the set of feasible schedules to help student identify alternatives • Uses to advise registration for second year • Program advisors • Set of schedules provide input for advisors for third and fourth years Spreadsheet Models for Generating Student Class Schedules

34. Benefits of GOT Initiative • Programs required to prepare four-year schedule • Led to full year registration • Highlighted schedule conflicts • Publication of planned offerings of courses • Facilitates better advising and planning by students • Program issues • Attractive to uncertain students with uncertain parents • 15 hour requirement is a potential barrier • Self-directed student do not need or desire the program • Identified potential benefits of having a tool to generate a four year program of study tailored to individual desires and planned course offerings Spreadsheet Models for Generating Student Class Schedules

35. Future Technical Extensions • Stand-alone VB application • Real-time connection to university database • Local SQL/database servers for advisors and students • Student web access with advisor approval • Allow online registration • Link to academic departments to dynamically update program data Spreadsheet Models for Generating Student Class Schedules

36. Questions ??? Dr. Robert L. Armacost Director, University Analysis and Planning Support University of Central Florida 12424 Research Parkway, Suite 215 Orlando, FL 32826-3207 407-882-0286 armacost@mail.ucf.edu http://uaps.ucf.edu Mr. Magdy Helal Research Associate, University Analysis and Planning Support University of Central Florida 12424 Research Parkway, Suite 215 Orlando, FL 32826-3207 407-882-0285 mhelal@mail.ucf.edu http://uaps.ucf.edu Spreadsheet Models for Generating Student Class Schedules