Optimising School Options Columns

This page describes a simple Excel tool for optimising a school’s subject options columns. The tool can be downloaded for free at https://www.rhydlewis.eu/resources/SubOpsCols.zip. A video tutorial on how to use this tool is available at https://youtu.be/M1SN4Ryz0Ns. This software is also the subject of the following publication in the Journal of Learning Analytics:

· Lewis, R., T. Anderson, and F. Carroll (2020) 'Can School Enrolment and Performance be Improved by Maximizing Students’ Sense of Choice in Elective Subjects?' Journal of Learning Analytics, vol. 7(1), pp. 75-87.

The article can be accessed here (pdf).

What is this software for?

In many countries, students entering their final few years of study will be asked to choose a small set of elective subjects that they want to specialise in. In places such as the United States (APs), Germany (Arbiturs), England and Wales (GCSEs and A-levels), and Australia (HSEs), these choices will be given in a set of “options columns” (sometimes also known as “blocking structures”). They may look like this:

Please select one subject from four of the five columns
(1)	(2)	(3)	(4)	(5)
Maths	Further Maths	Art	Chemistry	Biology
Media Studies	Geography	History	Computing	French
	Physics	P.E.		Spanish

Here we see that the options available to each student are dependent on the way that the subjects are split into columns. In the example above, students cannot study Further Maths and Physics because they appear in the same column. On the other hand, they are free to study both Maths and Art, despite this often being an unpopular combination

This Excel tool helps teachers arrange their schools’ subjects into columns so that the number of students whose preferred options are met is maximised.

Why are subjects put into columns?

The main reason is that it assists the timetabling process because subjects from the same columns can be taught at the same time. Using the example above, we could have a timetable like this:

Monday 09:00-10:00	Monday 10:00-11:00	Monday 11:15-12:15	Etc...
Subjects from Column 1 (Maths and Media Studies)	Subjects from Column 2 (Further Maths, Geography, Physics)	Subjects from Column 3 (Art, History, P.E.)	…

How do we split subjects into columns correctly?

In the above example, the thirteen subjects are split into five columns. The total number of ways of doing this (the total number of solutions that we might have used) is 10,306,752. Things get even more complicated for larger numbers. For example, with 30 subjects split into five columns, there will be more than seven quintillion possible solutions. The following graph shows the number of subjects and the resultant number of solutions using five columns.

This Excel tool enables you to identify the best possible solution for your school from among these many options. Teachers simply find out what their prospective students want to study, and the software uses this information to produce a solution that maximises the number of students whose preferences are met.

Why are students’ subject options important?

The way that subjects are split into options columns is important for schools and students for several reasons.

· Our experience in the education sector indicates that in localities where schools compete for numbers, students are more likely to choose institutions that allow them to study their favoured options. Indeed, schools will often publicize their options columns online and during open days to entice prospective students. More students bring increased revenues.

· Allowing students to select their favoured options is known to be beneficial for their studies. Being interested in a topic is a mental resource that enhances learning, which then leads to better performance and achievement. From a school’s perspective, this is beneficial because it can bring higher rates of successful outcomes, which in turn boost outcome-related revenues. Allowing students to study their favoured subjects can also improve student behaviour, attendance, and engagement, as well as reducing occurrences of students wanting to change their subject options mid-semester.

· Maximizing the number of students whose choices are satisfied by the options columns reduces the need for schools to be forced into creating additional classes (and incur the associated financial costs to accommodate students whose choices are not met).

How does the process work?

One of the main benefits of this tool is that it requires minimal investment of time. No staff training is needed, and only a basic knowledge of spreadsheets is required. Ultimately, the only information needed to produce your customised solution is a list giving the preferred subject options of each student. This information should be placed into the first worksheet in the spreadsheet.

A	B	C	D	E	F
Student #1	Maths	Further Maths	Physics	Chemistry
Student #2	Physics	English	Maths	Further Maths
Student #3	English	Physics	Maths	Further Maths
Student #4	Chemistry	French	English
Student #5	French	German	English Lit.	Latin

The example above shows example input for five students, one per row. We see that the first student wants to study Maths, Further Maths, Physics and Chemistry (first to fourth options respectively). Similarly, Student #2 wants to study Physics, English, Maths and Further Maths. This tool uses specialised algorithms to process this information and produce a customised solution that maximises the number of students whose options are satisfied.

Other factors to consider

Every school is different, and it is usual for individual schools to have additional needs.

· Some subjects may always need to be in separate columns, such as those taught in the same room or by the same teacher.

· Some subjects may need to be in the same column (e.g., pairs of subjects for which a student can take a maximum of one).

· Subjects usually have a maximum class size and a limited number of qualified teaching staff.

· Some schools also choose to prioritise students’ first and second subject options over others.

· Some subjects are offered as double subjects and therefore span two columns.

These requirements can also be specified within this software.

What do I do now?

Simply download the program for free, unzip it, open it in Excel, and follow the onscreen instructions. A video tutorial is also available at https://youtu.be/M1SN4Ryz0Ns.

R. Lewis, T. Anderson, and F. Carroll

Cardiff University / Bishop of Llandaff Church in Wales High School / Cardiff Metropolitan University

Last Updated, Tuesday, 08 February 2022