Reference no: EM13881064

On a popular air route, an airline offers two classes of service: business class (B) and economy class (E). The respective demands are given by:
PB = 540 - .5Q B and PE = 380 - .25Q E . Because of ticketing restrictions, business travelers cannot take advantage of economy's low fares. The airline operates two flights daily. Each flight has a capacity of 200 passengers. The cost per flight is $20,000.

a. The airline seeks to maximize the total revenue it obtains from the two flights. To address this question, create a spreadsheet patterned on the example shown. (In your spreadsheet, only cells E2, E3, E4, C9, and D9 should contain numerical values. The numbers in all other cells are computed by using spreadsheet formulas. For instance, the total available seats in cell E5 is defined as the product of cells E2 and E3.)

b. What fares should the airline charge, and how many passengers will buy tickets of each type? Remember that maximum revenue is obtained by setting MRB equal to MRE. After you have explored the decision by hand, confirm your answer using your spreadsheet's optimizer. (Hint: Be sure to include the constraint that the total number of seats sold must be no greater than the total number of seats available-that is, cell E9 must be less than or equal to cell E5.

c. Suppose the airline is considering promoting a single "value fare" to all passengers along the route. Find the optimal single fare using your spreadsheet's optimizer. (Hint: Simply modify the optimizer instructions from part (b) by adding the constraint that the prices in cells C11 and D11 must be equal.)

	A	B	C	D	E	F
1
2	DUAL AIRFARES			Planes	2
3				Seats/Plane	200
4				Cost/Plane	20,000
5				Total Seats	400
6
7			Business	Non-Bus.	Total
8
9	Number of Seats		200	200	400
10
11	One-way Fare		440	330	-
12
13	Revenue		88,000	66,000	154,000
14	MR		340	280
15
16	MC		100	Total Cost	40,000
17
18				Total Profit	114,000
19