solve questions for case c only. Please use Excel and show the functions for it. Additional information,

solve questions for case c only. Please use Excel and show the functions for it. 

Transcribed Image Text:

Additional information, including supplemental material and rights and permission polic Case A BlueSky flies three airplanes between Houston and three cities, Chicago, Miami, and Phoenix. These three cities are the spokes connected by the Hous- ton hub. A few times each day the three airplanes fly from the spoke cities to Houston. They arrive simultaneously at Houston; connecting passengers change aircraft during a 45-minute layover, and the three airplanes depart for the spokes. One set of six flights (three inbound to Houston and three out- bound) is called a bank. Each bank can serve passen- gers flying on 12 different routes: three inbound direct routes (Chicago-Houston or C-H, Miami-Houston or M-H, and Phoenix-Houston or P-H), three outbound direct routes (H-C, H-M, and H-P), and six routes requiring two flights each (C-M, C-P, M-C, M-P, P-C, and P-M). BlueSky charges a single fee for a one-way coach- class ticket on each passenger route. Table 1 shows the prices charged by BlueSky. The marginal cost of flying a passenger on each route is virtually zero. Each of the three airplanes currently has 240 coach seats. Table 2 shows demand for the routes in a bank; assume in this case that demand is known, with no uncertainty. From Table 2 we can see that passen- ger demand exceeds airplane capacity on every flight. Table 1 Origin Houston Chicago Miami Phoenix Price for Each Passenger Route Houston ($) 190 108 110 Destination Chicago ($) 197 292 192 Miami ($) Phoenix ($) 110 282 230 125 195 238 145 Table 2 Origin Houston Chicago Miami Phoenix Demand During One Bank Total demand for flight to spoke Houston Chicago Miami Phoenix 110 88 68 0 0 130 Destination. 72 115 123 0 105 90 318 80 98 0 66 244 266 (H-C) (H-M) (H-P) Total demand for flight to hub 316 (C-H) 245 (M-H) 271 (P-H) For example, on the C-H flight, the total demand is the sum of demands for three passenger routes, C-H, C-M, and C-P, totaling 130+98 +88= 316 passengers (this is the sum of the second row of Table 2). Because only 240 passengers can travel on the C-H flight, at least 76 passengers must be turned away. When the total demand for a particular flight may be larger than the available capacity, an airline can decide whether to accept or reject an offer to buy a ticket for a particular route. Controlling sales in this way to maximize revenue is called revenue management. For example, BlueSky may decide that it is optimal to sell large numbers of tickets for the C-H and C-M routes, but might severely restrict the number of C-P tickets sold. Given the data above, BlueSky might sell tickets for 130 C-H routes, 98. C-M routes, and only 12 C-P routes, thus filling all 240 seats on the C-H flight. Build a model to determine the number of tickets to sell for each route. The objective is to maximize rev- enues over a single bank. The tables above are avail- able in BlueSky Network Data.xls. This Excel spreadsheet file can be found and downloaded from http://ite.pubs.informs.org. Additional information, including supplemental material and rights and permission polic Case A BlueSky flies three airplanes between Houston and three cities, Chicago, Miami, and Phoenix. These three cities are the spokes connected by the Hous- ton hub. A few times each day the three airplanes fly from the spoke cities to Houston. They arrive simultaneously at Houston; connecting passengers change aircraft during a 45-minute layover, and the three airplanes depart for the spokes. One set of six flights (three inbound to Houston and three out- bound) is called a bank. Each bank can serve passen- gers flying on 12 different routes: three inbound direct routes (Chicago-Houston or C-H, Miami-Houston or M-H, and Phoenix-Houston or P-H), three outbound direct routes (H-C, H-M, and H-P), and six routes requiring two flights each (C-M, C-P, M-C, M-P, P-C, and P-M). BlueSky charges a single fee for a one-way coach- class ticket on each passenger route. Table 1 shows the prices charged by BlueSky. The marginal cost of flying a passenger on each route is virtually zero. Each of the three airplanes currently has 240 coach seats. Table 2 shows demand for the routes in a bank; assume in this case that demand is known, with no uncertainty. From Table 2 we can see that passen- ger demand exceeds airplane capacity on every flight. Table 1 Origin Houston Chicago Miami Phoenix Price for Each Passenger Route Houston ($) 190 108 110 Destination Chicago ($) 197 292 192 Miami ($) Phoenix ($) 110 282 230 125 195 238 145 Table 2 Origin Houston Chicago Miami Phoenix Demand During One Bank Total demand for flight to spoke Houston Chicago Miami Phoenix 110 88 68 0 0 130 Destination. 72 115 123 0 105 90 318 80 98 0 66 244 266 (H-C) (H-M) (H-P) Total demand for flight to hub 316 (C-H) 245 (M-H) 271 (P-H) For example, on the C-H flight, the total demand is the sum of demands for three passenger routes, C-H, C-M, and C-P, totaling 130+98 +88= 316 passengers (this is the sum of the second row of Table 2). Because only 240 passengers can travel on the C-H flight, at least 76 passengers must be turned away. When the total demand for a particular flight may be larger than the available capacity, an airline can decide whether to accept or reject an offer to buy a ticket for a particular route. Controlling sales in this way to maximize revenue is called revenue management. For example, BlueSky may decide that it is optimal to sell large numbers of tickets for the C-H and C-M routes, but might severely restrict the number of C-P tickets sold. Given the data above, BlueSky might sell tickets for 130 C-H routes, 98. C-M routes, and only 12 C-P routes, thus filling all 240 seats on the C-H flight. Build a model to determine the number of tickets to sell for each route. The objective is to maximize rev- enues over a single bank. The tables above are avail- able in BlueSky Network Data.xls. This Excel spreadsheet file can be found and downloaded from http://ite.pubs.informs.org.