OPERATIONS RESEARCH

USE EXCEL SOLVER

PLEASE PROVIDE ME OUTPUTS

300 700 500 200 6 1 Casestudy The education department of Gauteng (EG) has to place learners into schools. They always have a problem with grade one and grade eight learners. They have to place learners into the three available schools. The department provides transport for all learners who must travel more than approximately a kilometre, so the department wants a plan for reassigning the students that will minimize the total transportation cost. The monthly cost per student for transport from each of the six residential areas of the city to each of the schools is shown in the following table along with other basic data for next year), where 0 indicates that transport is not needed and a dash indicates an infeasible assignment. Transportation Cost per Student Area Number in School 1 School 2 School 3 in 1 st Grade 145 222 400 165 600 300 98 200 500 195 0 400 153 500 300 0 School capacity: 1 400 1 600 2 000 The education department also has imposed the restriction that each grade must constitute between 17 and 23 percent of each school population. The above table shows the mumber of learners in each area that falls into each of the two grades. You have been hired as a decision analyst/operations research consultant to assist the board in deciding how many students in each area should be assigned to each school. 1. Formulate and solve a linear program for this problem. Please provide all your model formulations and solutions. 2. What is your resulting recommendation to the school board? Prepare a managerial report to the EG. 3. Do sensitivity analysis, please include your sensitivity analysis, and answer the following ques- tions: (a) Use the sensitivity report to check how much the transport from area 2 to school 2 can increase assuming no other cost increases) before the current optimal solution would no longer be optimal. If the allowable increase is less or equal to 50 percent use solver to find the new solution with a 55 percent increase. (b) What is the reduced cost of xll, what does this mean? (c) How many more than the 17 percent of learners in school 1 are grade ones? (row 8) (d) The school board has the option of adding portable classrooms to increase the capacity of one or more schools. Each portable classroom holds 20 students and has a lease cost of R2 500 per month. For each school use the corresponding shadow price from the sensitivity report to determine whether it would be worthwhile to add any portable classrooms. After seeing your recommendation, the school board expresses concern about all the splitting of learners from residential areas among different schools. They indi- cate that they would like to keep each neighborhood together. They relax their rule that about the 17% and 23% percent of the school may be grade ones to be able to find a solution. 4. Adjust your recommendation to enable each area to be assigned to just one school by formulat- ing a binary integer model for the problem under the current policy of providing transportation for students having to travel more than a km.(The question 1 model.) 5. How much does this increase the total transportation cost? Option 2 is to also eliminate transport for students traveling 1,5 to 2 km, where the estimated cost per student is R300. The school board is considering eliminating some transport to reduce costs. Option 1 is to only eliminate transport for students traveling 1 to 1,5 km, where the cost per student is given in the table as R200. Option 2 is to also eliminate transport for students traveling 1,5 to 2 km, where the estimated cost per student is R300. They have to let go of the percentages of learners in grade one to be able to solve this problem.( no 17 and 23 percent constraints) 6. Revise the model from question 1 to fit Option 1, and solve