In the MBA program at a prestigious university in the Pacific Northwest,students bid for electives in the second year of their program.Each student has 100 points to bid (total)and must take two electives.There are four electives available: Management Science,Finance,Operations Management,and Marketing.Each class is limited to 5 students.The bids submitted for each of the 10 students are shown in the table below. (a)Formulate this problem as an assignment problem by constructing an appropriate cost table.(b)Reformulate this assignment problem as an equivalent transportation problem by constructing the appropriate parameter table.(c)Formulate and solve a spreadsheet model for this problem.d)Does the resulting solution in part (c)seem like a fair assignment? (e)Which alternative objectives might lead to a fairer assignment?

Question

In the MBA program at a prestigious university in the Pacific Northwest,students bid for electives in the second year of their program.Each student has 100 points to bid (total)and must take two electives.There are four electives available: Management Science,Finance,Operations Management,and Marketing.Each class is limited to 5 students.The bids submitted for each of the 10 students are shown in the table below.  (a)Formulate this problem as an assignment problem by constructing an appropriate cost table.(b)Reformulate this assignment problem as an equivalent transportation problem by constructing the appropriate parameter table.(c)Formulate and solve a spreadsheet model for this problem.d)Does the resulting solution in part (c)seem like a fair assignment? (e)Which alternative objectives might lead to a fairer assignment?

Quizplus · Accepted Answer

The Answer is (a)This is a variant of an assignment problem where the assignees are the students and the tasks are the classes.However,each student is to be assigned to two classes rather than just one and each class is to be assigned five students.Therefore,each student needs to be split into two assignees and each class needs to be split into five tasks.Furthermore,the objective here involves maximization rather than minimization of costs as assumed by the assignment problem.To convert to an equivalent problem involving minimization,we can multiply each bid by -1.To avoid negative costs,we then can add the largest bid (70)to each of these negative bids.(A number larger than 70 also could have been added to each of these negative bids.)Before splitting each student and each class into multiple assignees and multiple tasks,we now have the following cost table.Cost

(a)This is a variant of an assignment problem where the assignees are the students and the tasks are the classes.However,each student is to be assigned to two classes rather than just one and each class is to be assigned five students.Therefore,each student needs to be split into two assignees and each class needs to be split into five tasks.Furthermore,the objective here involves maximization rather than minimization of costs as assumed by the assignment problem.To convert to an equivalent problem involving minimization,we can multiply each bid by -1.To avoid negative costs,we then can add the largest bid (70)to each of these negative bids.(A number larger than 70 also could have been added to each of these negative bids.)Before splitting each student and each class into multiple assignees and multiple tasks,we now have the following cost table.Cost After next splitting the students and classes into multiple assignees and tasks,we now have an appropriate cost table shown below for formulating this problem as an assignment problem. (b)The formulation of the equivalent transportation problem begins in just the same way as in part (a)by converting the table of bids into the equivalent table of costs shown in the first table in part (a).However,it is no longer necessary to split the students and classes into multiple assignees and tasks because the supplies and demands in the parameter table provide the same information.Therefore,the appropriate parameter table is the one shown below. (c)This is a variant of an assignment problem.The assignees are the students and the tasks are the classes.Each student is assigned to take two classes.Each class has a limit of five students.Start by entering the data.The data for this problem are the bid points for each student for each class,where the corresponding data cells are assigned a range name of Points (C5:F14). The decisions to be made in this problem are whether or not to assign each student to each class.Therefore,a table of changing cells is created for each student and class combination in C18:F27,and given a range name of Assignment.The values in Assignment (C18:F27)will eventually be determined by the Solver.For now,arbitrary values of 0 and 1 are entered. The goal is to maximize the total bid points of the assignments.Total Bid Points = SUMPRODUCT(Points,Assignment).This formula is entered into cell I29. The functional constraints in this problem are that each student must be assigned to two classes,each class is limited to five students,and each student can take each class at most once.The number of classes a student is assigned to is just the sum of the row of changing cells for each student.For example,for George it is =SUM(C18:F18).This formula is copied into G18:G27.The number of students assigned to a class is the sum of the column of changing cells for each class.For example,for Management Science it is =SUM(C18:C27).This formula is copied into C28:F28. The Solver information and solved spreadsheet are shown below. Thus,the 1's in Assignment (C18:F27)show the assignments that should be made,achieving a total of 705 points.(d)No.For example,Eric did not get into Management Science despite bidding 50 points,while Ann got in with only 45 points.Also,Eric got into classes worth only 45 total bid points to him while Liz got classes worth 100 bid points to her.(e)Perhaps maximizing the minimum total number of bid points achieved by each student.

After next splitting the students and classes into multiple assignees and tasks,we now have an appropriate cost table shown below for formulating this problem as an assignment problem.

(b)The formulation of the equivalent transportation problem begins in just the same way as in part (a)by converting the table of bids into the equivalent table of costs shown in the first table in part (a).However,it is no longer necessary to split the students and classes into multiple assignees and tasks because the supplies and demands in the parameter table provide the same information.Therefore,the appropriate parameter table is the one shown below.

(c)This is a variant of an assignment problem.The assignees are the students and the tasks are the classes.Each student is assigned to take two classes.Each class has a limit of five students.Start by entering the data.The data for this problem are the bid points for each student for each class,where the corresponding data cells are assigned a range name of Points (C5:F14).

The decisions to be made in this problem are whether or not to assign each student to each class.Therefore,a table of changing cells is created for each student and class combination in C18:F27,and given a range name of Assignment.The values in Assignment (C18:F27)will eventually be determined by the Solver.For now,arbitrary values of 0 and 1 are entered.

The goal is to maximize the total bid points of the assignments.Total Bid Points = SUMPRODUCT(Points,Assignment).This formula is entered into cell I29.

The functional constraints in this problem are that each student must be assigned to two classes,each class is limited to five students,and each student can take each class at most once.The number of classes a student is assigned to is just the sum of the row of changing cells for each student.For example,for George it is =SUM(C18:F18).This formula is copied into G18:G27.The number of students assigned to a class is the sum of the column of changing cells for each class.For example,for Management Science it is =SUM(C18:C27).This formula is copied into C28:F28.

The Solver information and solved spreadsheet are shown below.

Thus,the 1's in Assignment (C18:F27)show the assignments that should be made,achieving a total of 705 points.(d)No.For example,Eric did not get into Management Science despite bidding 50 points,while Ann got in with only 45 points.Also,Eric got into classes worth only 45 total bid points to him while Liz got classes worth 100 bid points to her.(e)Perhaps maximizing the minimum total number of bid points achieved by each student.

In the MBA Program at a Prestigious University in the Pacific