Need help solving both problems using Excel spreadsheets with the the following information. all information is provided.
|_l | Create a new file for each of the following two problems. 0 Please name your files "Lastname-Asmt61.xlsx\" and "Lastname-Asmt62.xlsx\" 0 In both problems, set the Integer Optimality (96) value to 0 in the Solver Parameters > Options dialog. 1. (6 pts.) Dogwood City is taking bids from six bus companies (A, B, C, D, E, F) on eight routes that must be driven each day in their school district. Each company submits its bids (in S) for how much it will charge Dogwood City to drive the routes during the school year. However, only company F actually bids on all eight routes, as seen in the table below. Blank cells indicate that a company is n_ot bidding on a particular route. Company Rte. 1 Rte. 2 Rte. 3 Rte. 4 Rte. 5 Rte. 6 Rte. 7 Rte. 8 A 9000 8400 6000 4500 B 8500 8900 7000 4000 5600 C 5500 5000 6250 4300 D 8500 5500 7500 7200 4850 E 8000 7000 4750 7100 3500 3500 F 7750 6500 8100 5200 6400 4900 6500 5000 Dogwood City must decide which company should be assigned to drive each of the eight routes. Their goal is to minimize the total cost of covering all eight bus routes subject to the following conditions: (1) if a company does not bid on a route, it cannot be assigned to that route; (2) exactly one company must be assigned to each route; (3) each company can be assigned to at mosttwo routes. 1a) Formulate this as an assignment problem in standard (tabular) form, not in the list form of the transportation model. Hint: An easy way to deal with the situation where a company does not bid on a route is to replace the blank cell with a really large bid that would never be selected by the cost minimization process. 1b) Which company should be assigned to each route, and what is the total cost of their bids? 1c) Suppose that routes 6 and 7 are so far apart that it is not economically feasible for one company to drive both of these routes. Add constraints to the model to ensure that no company is assigned both routes 6 and 7. Find the new optimal solution. How much does it increase Dogwood City's total costs? 2. (6 pts.) Using the 1,000 mile coverage criterion, expand the Western Airlines hub location model (discussed in lecture) to include two new cities: Tucson, AZ and Winnipeg, Canada. You can obtain air (flight) mileage between the new and existing cities using the web site of your choice. Use only whole numbers in your mileage data (9.9., round 234.56 up to 235 miles). Modify the model as needed, assuming that Tucson and Winnipeg (like all the other cities) are candidates for being hubs and must be covered by at least one hub. 2a) What is the URL of the web site you used to gather the air mileage data? 2b) What is the optimal number of hubs? What cities does each hub cover? 2c) What's the smallest mileage coverage criterion for which 3 hubs can still cover all the cities (i.e., at what mile limit does the minimum number of hubsjump from 3 to 4 hubs)? Try to get within 10 miles of the exact
