c 13.10-6. Because of population growth, the state of Washington has been given an additional seat in the House of Representatives, making a total of 10. The state legislature, which is currently con- trolled by the Republicans, needs to develop a plan for redistricting the state. There are 18 major cities in the state of Washington that need to be assigned to one of the 10 congressional districts. The table below gives the numbers of registered Democrats and registered Re- publicans in each city. Each district must contain between 150,000 and 350,000 of these registered voters. Use Evolutionary Solver to assign each city to one of the 10 congressional districts in order to maximize the number of districts that have more registered Repub- licans than registered Democrats. (Hint: Use the SUMIF function.) City Democrats (Thousands) 152 81 75 Republicans (Thousands) 62 59 83 52 87 87 69 49 34 1 2 3 4 5 6 7 8 9 10 11 12 13 14 62 38 48 74 62 98 66 83 86 72 28 112 45 93 72 72 75 82 83 53 98 82 68 98 15 16 17 18 a) Build a spreadsheet for the Descriptive Model of this problem. i) Clearly define and color-code three sections of the spreadsheet for decision Variables, performance measures and parameters. All entries must have a name and a value. ii) Enter formulas in the cells for the values of the performance measures (10 points) b) Set appropriate stopping rules in Solver Options. Set up Solver and generate the solution using the Evolutionary algorithm. Show the optimal solution. (15 points) c 13.10-6. Because of population growth, the state of Washington has been given an additional seat in the House of Representatives, making a total of 10. The state legislature, which is currently con- trolled by the Republicans, needs to develop a plan for redistricting the state. There are 18 major cities in the state of Washington that need to be assigned to one of the 10 congressional districts. The table below gives the numbers of registered Democrats and registered Re- publicans in each city. Each district must contain between 150,000 and 350,000 of these registered voters. Use Evolutionary Solver to assign each city to one of the 10 congressional districts in order to maximize the number of districts that have more registered Repub- licans than registered Democrats. (Hint: Use the SUMIF function.) City Democrats (Thousands) 152 81 75 Republicans (Thousands) 62 59 83 52 87 87 69 49 34 1 2 3 4 5 6 7 8 9 10 11 12 13 14 62 38 48 74 62 98 66 83 86 72 28 112 45 93 72 72 75 82 83 53 98 82 68 98 15 16 17 18 a) Build a spreadsheet for the Descriptive Model of this problem. i) Clearly define and color-code three sections of the spreadsheet for decision Variables, performance measures and parameters. All entries must have a name and a value. ii) Enter formulas in the cells for the values of the performance measures (10 points) b) Set appropriate stopping rules in Solver Options. Set up Solver and generate the solution using the Evolutionary algorithm. Show the optimal solution. (15 points)
