Question

how can i solve this problem using solver on excel

Paragraph Styles 10.The Westchester County Superintendent of Education is responsible for assigning students to the three magnet high schools in her county. She recognizes the need to bus a certain number of students, for several sectors of the county are beyond walking distance to a magnet school. The superintendent partitions the county into five geographic sectors as she attempts to establish a plan that will minimize the total number of student miles traveled by bus. She also recognizes that if a student happens to live in a certain sector and is assigned to the magnet high school in that sector, there is no need to bus him/her since he/she can walk to school. The three magnet schools are located in sectors B, C, and E. The accompanying table reflects the number of magnet high-school-age students living in each sector and the distance in miles from each sector to each school. Distance to Magnet School School in School in School in Number Sector Sector B Sector C SectorE of students 700 500 100 800 400 TOTAL2500 12 12 Each Magnet High School has a capacity of 900 students. Set up the objective function and constraints of this problem, and solve using MS Excel

Answer 1

Answer:

Given data Information given about the three high schools and different sectors in AC for superintendent of education divided the country into five geographical locations to minimize the total number of student miles traveled by bus. These three schools are located in sectors B, C, and E. The capacity of a high school is 900 students. The number of students studying in sector A is 700; the age groups in sector B is 5, sector C is 8, and sector E is 6 The number of students studying in sector B is 500; the age groups in sector B is 0, sector C is 4, and sector E is 12. The number of students studying in sector C is 100; the age groups in sector B is 4, sector C is 0, and sector E is 7 The number of students studying in sector D is 800; the age groups in sector B is 7, sector C is 2, and sector E is 5 The number of students studying in sector Е is 400, the age groups in sector B is 12, sector c is 7, and sector E is 0

Explanation It is given that the five sectors in the AC are from A E with the student age groups in sectors B, C, and E Now, construct linear programming equation for the superintendent of education to determine the number of student miles traveled by bus is minimized. Set up: Let X, -Number of students traveling in bus from sector i to school j. Objective Minimize total travel miles in the bus Sector A: 5XAB +8Xc +6XE Sector B: X 4X 12XBB Sector C: 4XCB + 0xee + 7xer Sector D: 7XDB2X5XC Sector E: 12XER 7XEC OXER Now, as per the objective given, categorize the students from A to E through the linear equation

Subject to: XBXACX 700students studying in sector A XBXc X 500students studyingin sectorB..(1) Xcs +XccXc 100students studying in sector C XDRCX 800 students studying in sector D XERXEC XE 400 students studying in sector E Now, arrange the equations according to the total capacity of the each school using the total travel miles. XB X8+XXEB S 900students (school B capacity) X,c + XBc + X㏄ + XDC + XECS 900 students (School C capacity) XAE XBEXE XDE+X900 students(school E capcity) (2)

b) To solve: Using the function and constraints find the optimal solution Explanation using excel solver, the following results are obtained

B18 SUMPRODUCT(B12:D16,84:D8) 0 School in in in Sector flow out 700 500 100 800 400 From Sector B |SectorC E Flow balance e 300 4 Sector A 5 Sector E 6 Sector C 7 Sector D 8 Sector E 9 Total flow 10 400 500 Location Sector A Sector B Sector C Sector D Sector E School in Sector B Net flow Sign RHS -700 500 100 800 - 400 900 700 -500 100 800 400 100 800 400 700 900 900 900 School in School in School in Sector B Sector C Sector E 11 Distances: 12 Sector A 13 Sector B 14 Sector C 15 Sector D 16 Sector E 17 School in SectorC 900 <900 School in Sector E | 700 | 900 12 12 The optimal values of the solution obtained from excel solver are as given below XAB 400 XAE 300 Xa,-500 Xcc_ 100 Xpc 800 4x = 400

Substituting these values in the objective equation to obtain the distance of student miles Zmin(400)+8(0)+6(300)+0+40+12(0)+40)+0100)+70) +7(0)2(800) +5(0)+12(0)+7(0)+0(400) Zmin 5400 Hence, the cost of the distance of the student miles traveled by bus is $5,400