1GROUP6ASSIGNINGSTUDENTSTOSCHOOLS1218

nts to SchoolsDec. 18, 2008徐立平 (Leon)沈山丹 (Lar) 李硕仁 (Lee Seog In)孙显燮 (Son Hyun Sub)Case 5-4Programming & Decision making by Dr. Chen LihuaIMBA2 Group 6Contents 1. Case Overview2. A linear programming model on a Spreadsheet & The Sensitivity report3. Assumption 1 for Problem4. Assumption 2 for Problem 5. Recommendations1. Case OverviewThe Springfield School Board has made the decision to close one of its middle schools at the end of this school year and reassign all of next years middle school students to the three remaining middle schools. The school district provides busing for all middle school students who must travel more than approximately a mile, so the school board wants a plan for reassigning the students that will minimize the total busing cost.The annual cost per students for busing from each of the six residential areas of the city to each of the schools is shown in the table. In term of busing cost per student field, 0 indicates that busing is not needed and a dash indicates an infeasible assignment.The School board also has imposed the restriction that each grade must constitute between 30 and 36 percent of each schools population. The above table shows the percentage of each areas middle school population for next year that falls into each of the three grades.2. A linear programming model and the sensitivity report Before showing model for this problem on spreadsheet, there are two issues for solving this Case with related to Case 3-5. One thing is that we can set a linear programming model if there is no additional restriction that each grade must constitute between 30 and 36 percent of each schools population. The other thing is that we can establish this problem as a nonlinear programming model if the additional restriction of the school board is adapted to problem. l A linear programming model Target Cell Total Cost Adjustable Cells Assignment Constraints Area 2 to School 1 = NAArea 5 to School 2 = NAArea 4 to School 3 = NA Total Assignment = Area Population Total Assigned <= School Capacity Option Assume Linear Model andAssume Non-Negative In order to solve from c to i at Case 5-4 and generate the sensitivity report, we will use this linear programming model. In addition, we will recommend to the school board as a conclusion after we consider all constraints including another restriction with respect to constitution of each grade in terms of schools populations percentage. l The sensitivity report (linear programming model)l Nonlinear programming model(including another restriction of the school board) Above nonlinear programming model includes another restriction of the school board that each grade must constitute between 30 and 36 percent of each schools population. Target Cell Total Cost Adjustable Cells Assignment Constraints Area 2 to School 1 = NAArea 5 to School 2 = NAArea 4 to School 3 = NA Total Assignment = Area Population Total Assigned <= School Capacity Grade percentage <= Less than (36%) Grade percentage >= Larger than (30%) Option Assume Non-Negativel The sensitivity report (nonlinear programming model)3. Assumption 1 for ProblemOne concern of the school board is the ongoing road construction in area 6. Theses construction projects have been delaying traffic considerably and are likely to affect the cost of busing students from area 6, perhaps increasing costs as much as 10 percent.l The busing cost from area 6 to school 1 How much the busing cost from area 6 to school 1 can increase (assuming no change in the cost for the other schools) before the current optimal solution would no longer be optimal? The figure below shows the relevant part of the sensitivity report for this problem.Adjustable CellsFinalReducedObjectiveAllowableAllowableCellNameValueCostCoefficientIncreaseDecrease$D$29Area6 School 10 500 5001E+30500$E$29Area6 School 20 300 3001E+30300 $F$29Area6 School 3450 0 03001E+30As shown in the yellow part, the number of student assigned from Area 6 to school 1 is 0 and the allowable increase is infinitive. So, it doesnt matter how much the cost of busing increases before the current optimal solution would no longer be optimal. The busing cost from area 6 to school 1 can increase infinitely. The current solution could be optimal whatever the road construction proceed between area 6 and school 1 because the number of student assigned from area 6 to school is 0.l The busing cost from area 6 to school 2As s