Delta Airlines has frequent (every hour or every two hours) flights between key metropolises in the Northeast - they call these flights the "Delta Shuttle" service. At the time of writing this problem set, the Shuttle service is offered between New York and Boston (LGA-BOS), DC (LGA-DCA), and Chicago (LGA-ORD).

In the late 90s, Delta offered special student pricing on the Boston route, perhaps due to the fact that Boston is a college city (Boston University, Boston College, MIT, Harvard, etc.), but then discontinued the program in early 2000. Now Delta is re-considering decisions on their shuttle route. The MD-88 plane has 148 seats and the Embraer 175 has 76 seats. Please see the accompanying Excel with the pricing data.

GIVEN DATA SET

COST

DEMAND

1. Graph quantity (x-axis) v price (y-axis) for the business and youth segment. So you should have two separate charts. Add a trendline to each chart & include the formula for the trendline.

2. On a new sheet in your Excel, using the trendlines for prices between $220 and $20 (at increments of $10) find the quantity each segment will buy, as predicted by the trendlines. So now you should have a new sheet with Price, Quantity Predicted Y, and Quantity Predicted B.

HINT: your trendline from (1) will be in the format of Price = a*Quantity + b. To do (2) you will need to use algebra to get the formula so that it is Quantity = c*Price + d.

Delta Airlines Aircraft Operating Costs Cost Per Block Hour (Block Hour is the time from gate departure to gate arrival) E-175 $4,457 MD-88 $6,128 Pilots Salaries and Wages Pilot Training Benefits and Payroll Taxes Per Diem/ Personnel $592 $363 $6 $175 $48 Pilots Salaries and Wages Pilot Training Benefits and Payroll Taxes Per Diem/ Personnel $686 $458 $8 $172 $47 Purchased Goods Fuel/Oil Insurance Other (inc. Tax) $2,263 $2,250 $13 $0 Purchased Goods Fuel/Oil Insurance Other (inc. Tax) $3,463 $3,450 $13 $0 Maintenance Labor Materials Third Party Total Direct Burden $1,267 $65 $168 $270 $485 $279 Maintenance Labor Materials Third Party Total Direct Burden $1,387 $56 $163 $337 $556 $274 Aircraft Ownership Rentals Depreciation and Amortization $104 $68 $36 Aircraft Ownership Rentals Depreciation and Amortization $203 $123 $79 Other $230 Other $391 Week Price Business Quantity Busine Price Youth Quantity Youth 19 $ 190 82 $ 86 8 20 $ 190 87 $ 86 8 21 $ 200 76 $ 90 7 22 $ 200 73 $ 90 7 23 $ 200 85 $ 90 8 24 $ 200 81 $ 90 8 25 $ 200 78 $ 90 8 26 $ 205 75 $ 92 8 27 $ 205 84 $ 92 7 28 $ 205 75 $ 92 6 29 $ 205 85 $ 92 7 30 $ 205 74 $ 92 7 31 $ 195 80 $ 88 8 32 $ 195 77 $ 88 8 33 $ 195 74 $ 88 9 34 $ 195 82 $ 85 9 35 $ 195 91 $ 85 9 36 $ 185 89 $ 85 9 37 $ 185 86 $ 83 9 38 $ 185 82 $ 83 9 39 $ 185 95 $ 83 9 40 $ 185 84 $ 83 10 41 $ 175 102 $ 79 11 42 $ 175 92 $ 79 10 43 $ 175 92 $ 70 12 44 $ 175 92 $ 70 11 45 $ 175 87 $ 70 13 46 $ 175 92 $ 70 12 47 $ 165 95 $ 74 12 48 $ 165 94 $ 74 12 49 $ 165 92 $ 74 12 50 $ 175 92 $ 79 9 51 $ 175 92 $ 79 11 52 $ 175 93 $ 10 Delta Airlines Aircraft Operating Costs Cost Per Block Hour (Block Hour is the time from gate departure to gate arrival) E-175 $4,457 MD-88 $6,128 Pilots Salaries and Wages Pilot Training Benefits and Payroll Taxes Per Diem/ Personnel $592 $363 $6 $175 $48 Pilots Salaries and Wages Pilot Training Benefits and Payroll Taxes Per Diem/ Personnel $686 $458 $8 $172 $47 Purchased Goods Fuel/Oil Insurance Other (inc. Tax) $2,263 $2,250 $13 $0 Purchased Goods Fuel/Oil Insurance Other (inc. Tax) $3,463 $3,450 $13 $0 Maintenance Labor Materials Third Party Total Direct Burden $1,267 $65 $168 $270 $485 $279 Maintenance Labor Materials Third Party Total Direct Burden $1,387 $56 $163 $337 $556 $274 Aircraft Ownership Rentals Depreciation and Amortization $104 $68 $36 Aircraft Ownership Rentals Depreciation and Amortization $203 $123 $79 Other $230 Other $391 Week Price Business Quantity Busine Price Youth Quantity Youth 19 $ 190 82 $ 86 8 20 $ 190 87 $ 86 8 21 $ 200 76 $ 90 7 22 $ 200 73 $ 90 7 23 $ 200 85 $ 90 8 24 $ 200 81 $ 90 8 25 $ 200 78 $ 90 8 26 $ 205 75 $ 92 8 27 $ 205 84 $ 92 7 28 $ 205 75 $ 92 6 29 $ 205 85 $ 92 7 30 $ 205 74 $ 92 7 31 $ 195 80 $ 88 8 32 $ 195 77 $ 88 8 33 $ 195 74 $ 88 9 34 $ 195 82 $ 85 9 35 $ 195 91 $ 85 9 36 $ 185 89 $ 85 9 37 $ 185 86 $ 83 9 38 $ 185 82 $ 83 9 39 $ 185 95 $ 83 9 40 $ 185 84 $ 83 10 41 $ 175 102 $ 79 11 42 $ 175 92 $ 79 10 43 $ 175 92 $ 70 12 44 $ 175 92 $ 70 11 45 $ 175 87 $ 70 13 46 $ 175 92 $ 70 12 47 $ 165 95 $ 74 12 48 $ 165 94 $ 74 12 49 $ 165 92 $ 74 12 50 $ 175 92 $ 79 9 51 $ 175 92 $ 79 11 52 $ 175 93 $ 10