I have to use Linear Solving in Excel

Week 01 Assignment 04 Submission: All results should be contained in 1 file. Description: The problem described below is an oversimplified example of large-scale evacuation response. In 2017, Victoria, Dickinson and Port Arthur were among the top cities most affected by hurricane Harvey. Due to various reasons, some people were unable to evacuate early enough. Local authorities had to direct remaining residents to the closest safety zones. The goal was to determine the number of residents were to be directed to each zone that minimizes the total evacuation time (i.e. evacuation time per person x number of people to be evacuated), without exceeding route capacities. City Population Early Evacuation Rate Now Evacuated Pep Victoria 67,700 72% 18,956 Dickinson 20,100 86% 2,914 Port Arthur 55,450 84% 972 Time in hours Route Capacity City Zone 1 Zone 2 Zone 3 Zone 1 Zone 2 Zone 3 Victoria 3 2 6 6000 5500 11000 Dickinson 5 4 3.5 1500 4000 3000 Port Arthur 7 5 2.5 7000 7000 6000 Tasks: 1. (10 points) Create a setup for Solver using the information provided. Label accordingly (e.g. objective function, variable, constraints...). 2. (10 points) Solve the LP. Report Answer, Sensitivity and Limits. 3. Answer the following questions: a. (5 points) Which constraints are binding? b. (20 points) If the capacity of a route increases by 1 unit, would you expect the total evacuation time to decrease? If so, by how much? How much can you increase the capacity before you no longer observe a decrease in evacuation time? Answer those questions with respect to route (Victoria, Zone 1) and route (Port Arthur, Zone 2) C. (5 points) If the number of people that need to be evacuated from Victoria is now only 2000, is the current solution still optimal (assuming all other information is the same)? 3 hours, 6000 route capacity Victoria Total Pop: 67,700 % evacuated: 72% 2 hours, 5500 route capacity Zone 1 6 hours, 11000 route capacity Zone 2 Dickinson Total Pop: 20,100 % evacuated: 86% Zone 3 Port Aurthur Total Pop: 55,450 % evacuated: 84% Week 01 Assignment 04 Submission: All results should be contained in 1 file. Description: The problem described below is an oversimplified example of large-scale evacuation response. In 2017, Victoria, Dickinson and Port Arthur were among the top cities most affected by hurricane Harvey. Due to various reasons, some people were unable to evacuate early enough. Local authorities had to direct remaining residents to the closest safety zones. The goal was to determine the number of residents were to be directed to each zone that minimizes the total evacuation time (i.e. evacuation time per person x number of people to be evacuated), without exceeding route capacities. City Population Early Evacuation Rate Now Evacuated Pep Victoria 67,700 72% 18,956 Dickinson 20,100 86% 2,914 Port Arthur 55,450 84% 972 Time in hours Route Capacity City Zone 1 Zone 2 Zone 3 Zone 1 Zone 2 Zone 3 Victoria 3 2 6 6000 5500 11000 Dickinson 5 4 3.5 1500 4000 3000 Port Arthur 7 5 2.5 7000 7000 6000 Tasks: 1. (10 points) Create a setup for Solver using the information provided. Label accordingly (e.g. objective function, variable, constraints...). 2. (10 points) Solve the LP. Report Answer, Sensitivity and Limits. 3. Answer the following questions: a. (5 points) Which constraints are binding? b. (20 points) If the capacity of a route increases by 1 unit, would you expect the total evacuation time to decrease? If so, by how much? How much can you increase the capacity before you no longer observe a decrease in evacuation time? Answer those questions with respect to route (Victoria, Zone 1) and route (Port Arthur, Zone 2) C. (5 points) If the number of people that need to be evacuated from Victoria is now only 2000, is the current solution still optimal (assuming all other information is the same)? 3 hours, 6000 route capacity Victoria Total Pop: 67,700 % evacuated: 72% 2 hours, 5500 route capacity Zone 1 6 hours, 11000 route capacity Zone 2 Dickinson Total Pop: 20,100 % evacuated: 86% Zone 3 Port Aurthur Total Pop: 55,450 % evacuated: 84%