Question

Using data from the Southwest case, create a chart that plots the relationship between each airline’s market share, in terms of revenue or airline seat miles flown, and its profitability for two periods: 1995-2000 and 2001-2005. Does your analysis suggest that market share is correlated with profitability in this industry? If you exclude Southwest Airlines and Jet Blue airlines from the analysis (companies that use “point-to-point” route structure rather than a “hub and spoke” route structure), how well does market share predict profitability?

Southwest Airlines Balance Sheet American America West Continental Delta Alaska Northwest Southwest United US Airways JetBlue 19,820 37,864 7,504 6,416 2015 33,581 40,816 5,594 2013 25,760 37,818 17,699 38,287 | 14,936 5,442 5,151 2012 24,825 36,917 16,411 37,160 14,121 4,982 4,650 2011 23,958 16,175 35,316 13,655 21,155 13,341 4,509 4,310 2010 22,151 14,011 31,894 12,104 19,682 12,196 3,781 3,427 2009 19,898 12,361 18,047 10,863 10,350 16,359 10,781 3,287 3,006 15,033 20,973 | 14,096 11,023 3,390 3,221 2008 23,696 2007 22,833 20,237 12,459 20,049 9,318 2737 14,105 19,239 12,735 9,861 2,843 3,070 2006 22,493 3770 13,010 17,339 12,555 9,086 2,363 2,693 19,334 8,076 17,3047,212 2005 20,657 3397 11,108 16,112 12,316 7,584 1,703 2,416 2004 18,608 2482 9,851 15,154 11,266 6,530 15,701 7,073 1,266 2,241 2003 | 17,403 2223 7,333 14,2039,184 5,937 13,395 6,762 998 2,027 2002 15,871 2021 7,353 12,410 9,152 5,522 13,916 6,915 635 1,832 2001 15,639 2035 7,972 13,211 9,592 5,555 16,087 8,253 320 1,763 2000 18,117 2309 9,129 15,321 | 10,957 5,650 19,331 9,181 105 1,760 1999 16,090 2164 8,024 14,901 9,868 4,736 17,967 8,460 1,696 1998 16,299 1983 7,299 14,630 8,707 4,164 17,518 8,556 1,581 1997 15,856 1887 6,361 14,2049,984 3,817 17,335 8,501 1,457 1996 15,136 1752 5,487 13,318 9,751 3,407 16,317 7,704 1,307

Answer 1

:: Solution ::

Book24.xlsx - Excel File Home Insert Page Layout Formulas Data Review View Tell me what you want to do... Show Queries Connections Clear 21 A2 38 From Table E Properties 5 Reapply From From From From Other Existing New Refresh al Sort Filter Text to Flash Remove Data Access Web Text Sources Connections Query - D Recent Sources All Edit Links Advanced Columns Fill Duplicates Validation Get External Data Get & Transform Connections Sort & Filter Data Tools 174 fx M M N Р A D E F H K L 1 Exhibit 3 US Domestic Airline Total Operating Revenues (in $ Milions) 2 America US American South- Delta Continental 3 North-west United West JetBlue west Alaska Airways Total 4 1996 15,136 1,752 5,487 13,318 9,751 3,407 16,317 7,704 1,307 74,179 5 1997 15,856 1,887 6,361 14,204 9,984 3,817 17,335 8,501 1,457 79,402 6 1998 16.299 1,983 7,299 14,630 8,707 4,164 17,518 8,556 1,581 80,737 7 1999 16,090 2,164 8,024 14,901 9,868 4.736 17,967 8,460 1.696 83,906 8 2000 18,117 2,309 9,129 15,321 10,957 5,650 19,331 9,181 105 1,760 91,860 9 1996 to 2000 81,498 10,095 36,300 72,374 49,267 21,774 88,468 42,402 105 7,801 410.084 10 2001 15,639 2,035 7,972 13,211 9,592 5,555 16,087 8,253 320 1,763 80,427 11 2002 15,871 2.021 7,353 12,410 9,152 5,522 13,916 6,915 635 1,832 75,627 12 2003 17,403 2,223 7,333 14,203 9,184 5,937 13,395 6,762 998 2,027 79,465 13 2004 18,608 2,482 9,851 15,154 11,266 6,530 15,701 7,073 1,266 2,241 90,172 14 2005 20,657 3,397 11,108 16,112 12,316 7,584 17,304 7,212 1,703 2,416 99,809 15 2001 to 2005 88,178 12,158 43,617 71,090 51,510 31,128 76,403 36,215 4,922 10,279 425,500 16 17 18 Exhibits 19 US 20 South- American Continental Delta North-west United JetBlue Airways Alaska west 21 1996 1,331 69 394 571 1,108 349 1.130 369 81 22 1997 1,447 164 645 1,621 1,204 524 1,225 586 133 23 1998 1,749 198 660 1.793 (129) 684 1,436 990 195 24 1999 1,004 198 478 1,730 769 782 1,358 202 177 25 2000 1,243 (13) 587 1,460 664 1,021 741 (45) (15) (12) 26 1996 to 2000 6,774 616 2,764 7,175 3,616 3,360 5,890 2,102 (15) 574 27 2001 (2,558 (424) (341) (972) () (796) 631 (3,743) (1,182) 35 (65) 28 2002 (3,313) ( (164) (481) (1,036) (783) 418 (3,021) (919) 105 (81) 29 2003 (1,444) 24 29 (1,157) (276) 482 (1,556) (421) 169 (11) 30 2004 (421) (3) (290) (1,613) (434) 554 (1,167) (348) 113 (39) 31 2005 (351) (121) (94) (1,197) (895) 820 (242) (213) 62 (8) 32 2001 to 2005 (8,087) (688) (1,177) (5,975) (3,184) 2,905 (9,729) (3,083) 484 (204) 33 34 2 Callation Defits! -மககககாமககைககாக + Ready US Domestic Airline Operating profits (in $ Milions) America I West Create a chart using Insert Cha scalatin Sheet2 Sheet Det Type here to search e P OM 0

Book2xdsx - Excel Home Insert Page Layout Formulas Data Review View Tell me what you want to do.. Puneet Sabharwal Share Show Queries Connections Clear 22 AL 2 Solver From Table E Properties Rapply Dala Analysis From From From From Other Existing New Refresh Filter Text to Mash Remove Dala Consolidale Pelationships What If Forecast Group Ungroup Sublocal Access Web Text Sources Connections Ouery To Recent Sources Edit Links Advanced Columns Fill Duplicates Validation Analysis Sheet Gel Caternal D GEL Transform Connections Sort & liter Dals Tools Forecast Oulline Analyze 2 Eu Son 174 G M N C P O E 5 T U V х Y 2,905 (9,729) (0,0 18 (200) Create a chart using Insert Chart-> Line->XY Scatter plut and add trendline using Add Chart element -> Trendline 7 910 25 South were west United 3 US Airways JetBlue Alaska 15 m 100% 15.4% 6.7% -14.3% 7.9% A E F 32 2001 to 2006 (R.C67) (1.177) (184) 33 34 35 calculation-Profitability = Operating profits/ Revenue 1 2 3 America American 37 Continental Delta North West West Profitability (1996 38 to 2000) 6.1% 7.5% 9.9% 7.35 Profitability (2001 39 to 2005) -9.2% -5.7% -2.7% -9.4% 40 41 Calculation Market Share - Revenues for Alvine/Total Revenues of all Alrlines 42 | 1 23 5 ------------ American America 13 Continental Delta North West West Market share 44 (1996 to 20001 19.9% 2.5% 8.91 17.0% 12.0% Market share 45 (2001 to 2005 20.7% 2.9% 10.3% 16.7 12.16 46 9.35 -12.7% -9.39 % 9.85 -2.0% % n -5.00 10.0 1 2 4 2 8 10 7 9 10 LIS -20.00 Sauth. west United JetBlue Alaska Airways . Proby 1996 10 2000 • ( Market Stere (1996 20001... Linear Profitability (1996 to 200011... Linear Marcet Share (1996 10 200011 21.0% 10.35 0.05 1.9% 25.0% 18.01 8.522 1.25 2.4% 20.0% 15% 47 48 49 10.05 5.04 51 OP 2 5. 10 10.0% -15.15 54 55 56 57 58 Al 59 B) Proby 2001-2006) . Marcat Share 2001 2005....... Proty 2003 to 2005) ....... Lars Maat 2001 2005 Above two Scatter charts shows relationship between Profitability and market share for two periods (1990 to 2000) and (2001 to 2000) Yes Market share is highly correlated with Profitability. For period of 1995 to 2000, there is a definitely some positive correlation between profitability and market share. As market share increases, profitability also increases. In the other period from 2000 to 2005, we again sec correlation between profitability and market share but it is negative. As market share goes down, profitability goes up. 61 Antilatinha In Sheet1 + dunha Sheet2 Sheets Ready UU O Type here to search O e e P X] . . * 2:43 PM 2/7/2019

Connections ALBA PEEEEEEE از عدم آمد El Sort Above two Scatter charts shows relationship between profitability and market share for two periods (1935 to 5 Book2.xlsx - Excel X Home Insert Page Layout Formulas Data Review View Tell me what you want to do Puneet Sabharwal Share Show Queries Clear 2. Solver A F From Table Properties Rapply Dala Analysis From From From From Other Existing New Textlu Flash Remove Data Consolidate Relationships What if forecast Group Ungroup Subtotal Access Web Text Sources Text Sources Connections Query to Recent Sources All Edit Links y Advanced Columns Fill Duplicates Validation Analysis Sheet Cenele Gel & Transform Connections Sert ΓΙΑ Teres Outine Analyze 174 C F к L M N R 5 T W Y 56 Poutatility All 2005 Markut Shure (2011.02 2003) .... Linarty 2000 to 2005 .....La MarketShare (UII 2005 57 58 A) (2000) and (2001 to 2000) 59 B) Yes Market share is highly correlated with Profitability. For period of 1996 to 2000, there is a definitely some positive correlation between profitability and market share. As market share increases, profitability also increases. GO In the other period from 2000 to 2005, we again 2 correlation between profitability and market share but it is negative. As market share goes down, profitability goes up. GI 62 Also check for the correlation coefficient in both periods to see the correlation between profitability and market share 63 1995 to 2000 0.19 Correl function 66 2001 to 2005 -11.57 Correfoc 05 05 ) To estimate how well market share predict profitability (excluding South west airlines and Jet Blue), we will use regression analysis A H z AL Go to Options-> Excel Addins Data Analysis Pack Now go to Date Data Analysis. Regression american GB Revenue Operating profits Profitability Market Share 70 1996 15,126 1,91 RANI 1.99 71 1997 15,856 1.417 2.0 72 19 16.299 1,199 10.7% 2.1% 13 1999 10,090 1.004 0.25 2.15 74 18.117 2000 1.243 99 American 2.3% 75 2001 15,639 12.558 16.4% 2.0) 76 2002 15,871 13,313) -20.9% 2.0 77 2003 17403 11.444 -BI 2.2 78 2004 18,60 (421) -2.941 2.49 79 2005 20.657 (151 -1.75 2.7% 20 1.02 0.2K 81 1,587 104 0.2% az 1.983 198 10.0% 0.37 2,164 198 9.1% 0.32 2,309 (13) -0.65 0.3% America West 2.00 (424) -20% 0.99 Ah 2,001 (164) 0.SK 27 2.223 115 0.3 2,42 (3) 0.3% 3,297 (121) 90 5,487 394 7.25 0.7 91 G,361 CAE - 645 10.151 0.8% W. Sheet2 Sheet1 + Heavy הרי T- + BUR 243 PM 2/7/2019 Type here to search е е P B

Book24.xlsx - Excel E- > Files Home Insert Page Layout Formulas Data Review View Tell me what you want to do... Show Queries Connections Clear AL LA From Table E Properties Reapply From From From From Other Existing New Refresh Sort Filter Access Web Text Sources Connections Query - D Recent Sources All Edit Links T Advanced Get External Data Get & Transform Connections Sort & Filter 14 f R2 is low hence predicatability is low Text to Columns Flash rill Remove Data Consolidats Duplicates Validation - Data Tools 1 M N N P B C D F F H 1 K 1 SUMMARY OUTPUT 2 3 Regression Statistics 4 Multiple R 0.081254 R2 is low Hence predicatability is low 5 R Square 0.006602 6 Adjusted R -0.00613 7 Standard 0.08968 8 Observatic 80 9 10 ANOVA 11 df SS MS F ignificance F 12 Regressior 1 0.004169 0.004169 0.518397 0.473678 13 Residual 78 0.627312 0.008012 14 Total 79 0.631481 15 16 Coefficientsandard Ernt Stat p.value Lower 9596Upper 95%ower 95.0"pper 95.096 17 Intercept 0.013237 0.019925 0.664343 0.50843 -0.02643 0.052904 0.02643 0.052904 18 x Variable -0.99177 1.377467 -0.72 0.473678 -3.7341 1.750553 -3.7341 1.750553 19 20 21 22 23 24 25 26 27 28 Sheet2 Sheet1 + Ready I Type here to search e e P3 x]