What is the optimal solution to maximize the total revenue? An what are the constraints at Excel?

Case A BlueSky flies three airplanes between Houston and three cities, Chicago, Miami, and Phoenix. These three cities are the spokes connected by the Houston 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 outbound) is called a bank. Each bank can serve passengers 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 coachclass 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 passenger demand exceeds airplane capacity on every flight. 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 130C?H routes, 98 C?M routes, and only 12C?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 revenues over a single bank. The tables above are available in BlueSky Network Data.xls. 1