The B school operates four colleges and provides education to high school students in grades 6, 7 and 8. Now, the group decide to close one of the 4 colleges in summer of 2021, and has to redistribute its students to the other 3 colleges. A policy of the Group is that the Group will provide transportation/bus service to students who live far away from the colleges (more than 1 km). Thus, the Group is going to make a plan that can minimize the total transportation expense when redistributing students.

The transportation expenses (annual) between each district and each college are list below. Here, zero means that transportation is not required, and a dash means that the assignment is infeasible. The capacities of colleges (the number of students that a college can accommodate) are also shown in the table.

              Amount of students   

District	Grade-6	Grade-7	Grade-8
1	144	171	270
2	222	168	420
3	165	176	418
4	98	140	224
5	195	170	270
6	153	156	324

       

    

Transportation expense ($/Student)

District	College 1	College 2	College 3
1	$1500	0	$3500
2	-	$2000	$2500
3	$3000	$1500	$1000
4	$1000	$2500	-
5	$0	-	$2000
6	$2500	$1500	$0
Capacity	900	1100	1000

The Group requires that

• in each college, the percentage of each grade's students to the total number of students must be between 30% ~ 36%.

In the second table above, the numbers of students for each grade and each district are shown (for the academic year 2021/2022). When redistributing students, assume that any student and be assigned to any one of the 3 colleges. (This is made possible by modifying the college attendance region.) As a graduate from university, you will help the Group management decide redistributing students: assigning students in each district to the 3 colleges, how?

 

a. Formulate a linear programming model (algebraic model) for this problem, and then use Excel to solve it. What is your resulting recommendation to the Group? (You are required to explain the algebraic model concisely in the "answerbook". To simplify ignore the consideration that "student number must be an integer" for this question.)   

 

b. The Group is thinking about removing some transportation services to cut expenses. They think that it is well acceptable for students to go the college on their own as well, if the traveling distance is between 1 and1.5 km; that is, those transportation services with an expense of $1000 can be cut. Please revise part-a model to fit this "cut expense" option, and solve using Excel. (You are NOT required to present and explain the algebraic model again, but must include the spreadsheet for this question.) Please summarize the key results and information for the Group management (in "answerbook"). Suppose you are the management. What decision would you make? Why?                                 

 

c. Return to the model formulated in part a. Now, the Group notices the heavy traffic congestion in district 6. This may increase the transportation expense for students from district 6, and the increment could be up to 10%. Please use the sensitivity report to find out how much the transportation expense can increase, for students from district 6 to college 1, before the current optimal solution changes. (Assume the transportation expenses to the other 2 colleges remain unchanged.) In case the allowable increase is within 10%, please solve for the new optimal solution with a 10% increase.      

 

d. Continue part c. Next, suppose the transportation expense from district 6 would increase by the same percentage for all the colleges. Use the sensitivity report to determine how large this percentage can be before the current optimal solution might no longer be optimal. If the allowable increase is less than 10%, use Solver to find the new optimal solution with 10% increase.                                                                               

e. Return to the model formulated in part a. The Group now considers using movable rooms to add capacity to colleges 1 and/or 2 in the next academic year. A movable room can accommodate 20 students, but will incur an additional annual cost of $12,500. For the colleges 1 and 2 respectively, please use the corresponding shadow price to answer whether it helps save cost by using the movable room; and when it is worth doing so, use the sensitivity report to find out how many movable rooms could be added before the shadow price would no longer be valid.                     

                                 

f. The Group now feels that it is not desirable to divide a district for different colleges, and would like to assign all students from the same district to a single college. (The current policy is still in force; that is, transportation must be provided to students who need to travel more than 1 km.) Please Formulate a BIP model (algebraic model) for this problem, and then use Excel to solve it. (You are required to explain the algebraic model concisely in the "answerbook".) How much expense is increased due to this new consideration ("no dividing" requirement)?