Allocation of Students Case Study

Introduction

The purpose of this case study is to demonstrate the solution to a student allocation problem. The problem is that at the end of an academic year, a year group of students need to attend either

  • a day long course or
  • a morning AND an afternoon course

 

The senior teacher in charge had gathered student preferences and he had entered those preferences in a spreadsheet to record those preferences: he had then used his allocations manually to create registration lists for the courses.

We were asked to suggest ways in which this work could be automated:

  • to make the work easier than it now is
to ensure no student slips through the net!

 

student allocation 1

The Original Spreadsheet

Here is an extract of the original spreadsheet we were presented with is as in the screenshot above:

 

The column headings AAA and BBB are class and group references that are important for the teacher to record for registration and attendance record purposes: they need to feature in the final solution.

  • If a subject is listed without the am or pm qualifier, it is an all day course
  • If a subject is listed as, for example, IT pm or learning game am, it is a half day course

 

The senior teacher has already hard coded a letter ‘X’ in the appropriate cell according to whether a student has selected a day long course (one ‘X’ per student) or a morning and afternoon course (two ‘X’ per student). The final column, in column M, shows that the senior teacher has entered ok when he believes that a student has been fully and properly allocated.

The Task

 

You can download the original Excel 2007 file by clicking on the link at the bottom of the page: this is your starting point.

  • We have to ensure that the allocations comply with the rules including that no student has selected two morning sessions or two afternoon sessions or even a full day and another half day course.
  • We also have to prepare registration schedules of the students that show the name, AAA and BBB for every course offering.

 

You are required to redesign the spreadsheet to optimise the process of collecting and recording the students’ preferences and to create the class registration and attendance records. You are free to make any sensible suggestions you like but you have to bear in mind that the users of your spreadsheet have to be able to use it without having to make any changes to your underlying programming.

One Suggested Solution

 

Our final solution is not being made available here: although it is available to everyone who buys the book, Excel 2007 with Excel Master. However, here are two screenshots of our revised spreadsheet. Firstly, the revised worksheet on which student preferences are recorded:

student allocations 2

Secondly, here is a view of the Pivot Table that now accompanies this revised spreadsheet: this screenshot shows a ready made class list for the combined Geography and Learning Game morning and afternoon combination.

student allocations 3

© Duncan Williamson
August 2009

 

Download this page in PDF Format

Download the original Excel 2007 file on which this case study is based