Could you help me solve this problem that listed in the forth picture . Thank you a lot for your help. The problem requires solver. The right answer of objective: total cost should be 243,500. The picture of excel is how I constructed a solver with constraints that sent=demand, flow in=flow out, I think should be calculate this way. but it turns out that my solver result is wrong.

APEC 3on4 Spring 2o21 Assignment 3: Network Models Submit a word or PDF document with your responses as well as the spreadsheet model that you worked with. Remember I will also be grading howr 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 les. 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 [M SP], New York-JFK {IFK]. New Yo rk-La Guardia[ LEA} 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 ights. Hubs have no capacity constraints. in other words, there is no limit on how many vaccines can y 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.\fExample: 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)Port of Entry Houston (IAH] Miami (MIA) San Francisco [SFO) Sioux Falls (FSD] Quantity 250000 500000 150000 150000 Destination Airport Minneapolis [MSP] Atlanta (ATL] Raleigh (RDU] Salt Lake City (SLC] Cincinnati (CVG] Quantity 350000 150000 200000 250000 70000 HUB MSP JFK LGA SEA To from Vaccines to Distribute decisions and Cost Objective MSP JFK LG SEA Allocate MSP JFK LGA SEA Sent LAH 0.35 0.35 0.35 0.6 250000 LAH 250000 250000 1020000 Total Cost 478800 d. total cost would be 478800 MIA 0.41 0.3 0.7 500000 MIA 320000 150000 470000 1020000 SFO 0.88 0.96 0.96 0.15 150000 SFO 150000 150000 FSD 0.1 0.34 0.34 150000 FSD 150000 150000 From 720000 150000 150000 Distribute cost 301200 Vaccines Destination TO MSP JFK LGA SEA Demands MS JFK LGA SEA Received MSP 0 0.3 0.3 0.8 850000 MSP 350000 O 350000 ATL 0.5 0.4 0.4 0.9 150000 ATL 150000 150000 RDU 0.26 0.5 0.5 0.6 200000 ADU 200000 0 200000 SLC 0.2 0.6 0.6 0.27 250000 SLC 170000 10000 250000 CVG 0.2 0.6 0.6 D.2 70000 CVG 70000 70000 720000 150000 150000 Destination Cost 177600Decisions and Cost Objective MSP JFK LGA SEA Sent LAH 250000 0 250000 1020000 Total Cost 478800 d. total cost would be 478800 MIA 320000 150000 470000 1020000 Solver Parameters SFO 0 150000 150000 FSD 150000 150000 720000 150000 150000 Distribute cost 301200 Set Objective: $Q$11 VISP JFK LGA SEA Received MSP 350000 350000 To: O Max O Min O Value Of: 0 ATL 150000 150000 RDU 200000 200000 By Changing Variable Cells: SLC 170000 30000 250000 CVG 70000 70000 $1$11:$L$14,$1$18:$L$22 720000 150000 150000 Destination Cost 177600 Subject to the Constraints: $1$15:$L$15 = $1$23:$L$23 Add $M$11:$M$14 = $F$18:$F$22 Change Delete Reset All Load/ Save Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non- smooth.Jecisions and Cost Objective MSP JFK GA SEA Sent LAH 250000 0 250000 1020000 Total Cost 478800 d. total cost would be 478800 MIA 320000 150000 al 470000 1020000 SFO 150000 150000 Solver Parameters FSD 150000 150000 720000 150000 150000 Distribute cost 301200 Set Objective: $Q$11 MSP JFK LGA Received MSP 350000 350000 To: O Max Min O Value Of: 0 ATL 150000 ol 150000 RDU 200000 200000 By Changing Variable Cells: SLC 170000 80000 250000 CVG 70000 70000 $1$11:$L$14,$1$18:$L$22 720000 150000 150000 Destination Cost 177600 Subject to the Constraints: $1$15:$L$15 = $1$23:$L$23 Add $M$11:$M$14 = $F$18:$F$22 Change Delete Reset All Load/ Save Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non- smooth