Formulate a LP model usinf excel
IA2 - Teaching at Aston Individual Assignment: Task 2 BNM860 Decision Models CASE STUDY: Teaching at Aston At Aston University, four instructors teach Foundations of Business Analytics – a large compulsory module for more than 600 first year undergraduate students. The Aston timetabling team have split the students into seven groups and have scheduled module webinars into the following time slots: Slot 1: Monday 10 am – 11 am Slot 2: Monday 11 am – 12 noon Slot 3: Monday 12 noon – 1 pm Slot 4: Monday 2 pm – 3 pm Slot 5: Monday 3 pm – 4 pm Slot 6: Tuesday 2 pm – 3 pm Slot 7: Friday 1 pm – 2 pm 9 AM 10 AM 11 AM 12 PM 1 PM 2 PM 3 PM 4 PM Mon Slot 1 Slot 2 Slot 3 Slot 4 Slot 5 Tue Slot 6 Wed Thu Fri Slot 7 The four instructors teaching the module are Josh, Liv, Kim and Dan. Josh is the module leader, and he was responsible to make a fair distribution of teaching duties among four of them. He already knows some of the conditions that need to be satisfied: 1. Each of the seven weekly webinar slots need to be covered by two instructors exactly. 2. Each instructor will cover four slots except for Josh himself, who will cover two slots only. 3. He and Kim are the most experienced instructors and so he wants his two slots to be: one with Liv, one with Dan, and none with Kim. Josh now needed to find out preferences of each instructor for each time slot. So, he asked them all to use the scale {-2, -1, 0, 1, 2} to express their preferences, where -2 stands for ‘very much against’ and 2 stands for ‘very much preferred’. Zero stands for ‘neutral’. The following preference table emerged: Slot 1 Slot 2 Slot 3 Slot 4 Slot 5 Slot 6 Slot 7 Two slots in a row Three slots in a row Liv -1 0 0 -2 -2 -2 0 0 -2 Kim -2 1 2 2 2 -1 -2 2 -2 Dan 0 1 2 2 2 2 0 -2 -2 Josh 0 0 0 0 0 -2 -2 -1 -2 ‘Slots in a row’ refer to slots that have no gap in time between them. For example, slots 2 and 3 are ‘slots in a row’ but slots 3 and 4 are not. Josh now has enough data to try to make a fair allocation of webinars. For such a small problem, a trial-and-error approach would work perfectly fine. However, since similar problems need to be frequently solved for other courses, Josh decided to use an optimisation approach to find a fair allocation. He is entrusting this task to you: the apprentice decision analyst. Your most important task is to: • Create an LP-based model that will produce a fair allocation of webinars to the four instructors. The model should work for any set of preferences specified by instructors, and it should be generic enough so that the same modelling principles/elements could be applied for more complex problems. You need to pay special attention to the objective function, where you may want to maximise the overall satisfaction score of the four instructors, or to maximise the minimum of the four scores, or some kind of combination of the two, etc. The objective measurement of ‘fairness’ is further complicated by the fact that instructors used different sets of scores to voice their preferences (e.g., the sums of scores provided by Liv, Kim, Dan, and Josh are -9, +2, +5, and -7, respectively). This may influence your choice of using absolute versus relative satisfaction scores for individual instructors. Your additional tasks are therefore to: • Explore, evaluate, and discuss various ways of defining ‘fairness’ through different objective functions. Justify your final choice of the objective function used in your model. • Suggest an alternative approach of eliciting instructors’ preferences that could potentially lead to a better ‘fairness’ model. Discuss advantages and disadvantages of the suggested approach relative to the one used by Josh.