Problem:
A school has three teachers. On a particular day, six subjects are scheduled to be taught. A subject does not need more than one teacher. The cost for each teacher to do each subject is shown below.
|
Teacher
|
Subject 1
|
Subject 2
|
Subject 3
|
Subject 4
|
Subject 5
|
Subject 6
|
|
A
|
3
|
2
|
2
|
6
|
4
|
6
|
|
B
|
4
|
3
|
7
|
5
|
3
|
3
|
|
C
|
9
|
9
|
7
|
9
|
7
|
6
|
For each of the following questions, formulate a linear mathematical model in a standard format and implement it in Excel to find the optimal answer. Clearly define your decision variables, objective function and constraints in your formulation and provide snapshots of your Excel solutions. You can define the decision variables only once but you need to have a complete formulation for each question.
Q1. What is the minimum cost assignment when each teacher can do 2 subjects? Provide both the formulation and the Excel solution
Q2. What is the minimum cost assignment to do any three subjects when each teacher can do only one subject? Provide both the formulation and the Excel solution
Q3. What is the minimum cost assignment when each teacher can do any number of subjects given that all classes must be done? Provide both the formulation and the Excel solution
Q4. What is the minimum cost assignment if every subject must be performed by a teacher but a teacher can do zero, one, or more subjects along with the following conditions:
• If teacher A does Subject 2, then teacher A cannot do Subject 1.
• If Subject 1 is assigned to teacher A, then teacher B cannot do Subject 5.
• Subject 4, 5, and 6 must be done by teachers A or B (or a combination of both) Provide both the formulation and the Excel solution for part (d) for all of the conditions in one model (i.e., do not create different models for the different conditions)
• The model must remain linear, and therefore the use of IF-THEN or similar functions is NOT permitted in the model