Home Insert - 3 4 H26 T Poste 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 AutoSave OFF 37 38 39 40 41 42 44 46 46 OFF H Sv hesa Draw Page Layout Ready [B Arial B + x fxx B District I U Students assigned Time= v North South East West Central School capacky 500 District North South East West Central Bus Travel Time (min): M Addison Addison 12 26 10 Transportation Model 10 29 15 + Formulas E I N Maryville has built a new elementary school so that the town now has a total of four schools. Each has a capacity of 400 students. The city wants to assign children to schools so that their travel time by bus is as short aspossible. It has partitioned the town into five districts conforming to population density. The average bus trave time from each district to each school is shown below. Determine the number of children that should be assigned from each district to each school in order to minimize total student travel time. mos & A D A A 400 Area Student Becks Canfield Dalny population 370 310 320 20 24 Becks 23 15 10 Area Data Review 450 Canfield 35 21 22 35 23 400 Deley 17 47 27 31 G 10 16 View == 220 280 H Tell me Students assigned Wras Text Transportation Problem Merge & Center General J $ % Y K " + D.D. Cell Conditional Format Formatting as Table Styles L 0 P Q S.R 8 Format Insert Delete R V S T *47. O. Find & Select V Share u Sort & Filter V W Q Comments FG- M Analyze Data D-( + 100% 6 Task 1) Complete the following problem which is explained in the template. See below for the link to this template. (15 pts). Use this template: Transportation Problem.xlsx Do not alter this template, it makes grading difficult and alterations are not needed. Add in the formulations needed in the excel spreadsheet. Open solver and complete all the necessary fields as shown in the Transportation video. Run solver How many children will be assigned from the North District to Addison school? 2) Complete problem #2 (10 pts). Using the same template as before create a new worksheet and alter the problem based on the new constraint. The school district has determined that it does not want any of the schools to be overly crowded compared with the other schools. It would like to balance enrollments between the four schools. However, the concern is that this might significantly increase travel time. Determine the number of students to be assigned to each school so that enrollments are evenly balanced. Does this new solution appear to significantly increase travel time per student? Answer the question, What was the impact on time given this new constraint