This problem requires Excel and the solver function. Thank you so much. It is a linear programming .
Submit a word or FDF document with your responses as well as the spreadsheet model that you worked with. Remember I will also he grading how well your spreadsheet adheres to the design principles we covered in class. Limit your responses to a few sentences each Remember you should write your responses individually. If you brainstormed or worked together with someone else, please mention their names in the document you hand in. but you should still hand in your own individual files. 1. In the current pandemic, the government is faced with the problem of vaccine distribution across the country. Many airlines have offered their services to assist in getting vaccines from their ports of entry to the states where they are needed. The government has allocated some doses at different points to different airlines, and given them the task to distribute them to specic locations. The domestic branch of Delta Air Lines has asked for your expert opinion about what they should do. Below is a description of the information that they gave you. Delta Air Lines is based in Atlanta, Georgia (Airport code: ATL.) You may consider that airport a hub. Additionally, for the purposes of this problem, Delta has hubs in Minneapolis (MSP), New York-JFK (JFK), New York-La Guardia (LGA) and Seattle (SEA). Some hubs have their own demands (vaccine doses needed in those locations) but they also serve as spots for layovers for other flights. Hubs have no capacity constraints, in other words, there is no limit on how many vaccines can fly through any airport. The table below shows where the vaccines that the government allocated for Delta to distribute are, and how many doses enter at each point Port of Entry Houston (IAH) Miami (MIA) San Francisco Sioux Falls (FSD) (SFO) Quantity 250,000 500,000 150,000 150,000 The next table shows the locations to which Delta needs to deliver vaccines, and how many are needed in each.Destination Minneapolis Atlanta (ATL) Raleigh (RDU) Salt Lake City Cincinnati Airport (MSP) (SLC) (CVG) Quantity 350,000 150,000 200,000 250,000 70,000 needed Finally, the following table summarizes the cost of flying one dose of vaccine from each one of the airports to the other. 1. Note that this table is triangular (has blank cells above the diagonal) because it costs the same to fly from A to B than from B to A (We are using this assumption. This may not be true in real life, though!) 2. Note that not all routes are possible (which is why we need hubs!), even if the cost is presented below. All flights from origin to destination need to make a stop at one of the hubs.the nuns. ATL MSP JFK LGA SEA IAH MIA SFO FSD RDU SLC CVG ATL 0 MSP 0.5 0 JFK 0.4 0.3 0 LGA 0.4 0.3 0.1 0 SEA 0.9 0.85 0.9 0.9 0 IAH 0.3 0.35 0.35 0.35 0.6 0 MIA 0.1 0.41 0.3 0.3 0.7 0.3 0 SFO 0.85 0.88 0.96 0.96 0.15 0.4 0.8 0 FSD 0.6 0.1 0.34 0.34 0.4 0.4 0.23 0.55 0 RDU 0.2 0.26 0.5 0.5 0.6 0.5 0.2 0.9 0.4 0 SLC 0.55 0.2 0.6 0.6 0.22 0.45 0.35 0.5 0.2 0.5 CVG 0.3 0.2 0.6 0.6 0.2 0.5 0.6 0.89 0.45 0.6 0.35 0Example: If I am shipping one dose from Houston to Cincinnati via Minneapolis, the cost would be = (the cost of flying between IAH and MSP)+ (the cost of flying between MSP and CVG)=0.35 + 0.2 = 0.55 Delta Air Lines needs to make sure all needed vaccines are delivered at each destination, and can decide how to get them there. Remember, all flights from origin to destination need to make a stop at a hub. a. Draw a diagram that represents the problem that Delta Air Lines is facing. Add an image of it to your Word document/PDF (2pt] b. Model Delta Air Lines' problem as a LP problem in Excel. Submit your Excel worksheet. Make sure it satisfies all the design principles. (3pts) c. What is the solution to Delta's problem? (1pt) d. How much will this cost? (1pt) e. Beyond Delta's problem, what advice would you give the government after seeing your results? Can the government do better by changing something about how they allocated responsibility to Delta? (1pt) f. How would the solution to Delta's problem change if all flights out of LGA became 20% cheaper? Why?(0.5pt) g. How would the solution change if all flights INTO LGA became 20% cheaper? (with the original cost to fly out of LGA) Why? (0.5pt) 2.Give three examples of real life situations (other than transportation) where network models would be useful. Refer to the three C's of each problem (1pt)
