Need to solve in Excel.
The Excel Data File in includes the total compensation in Smillions) of CEO's of 170 large public companies and the corporate returns in 2012. a- Find the descriptive measures of the two data sets and describe the shape of the data sets based on comparison of Mean and Median. b- Find the Coefficient of Variation for the two data sets and determine which set has higher variability. C- Find the histogram of the two sets. For Total Compensation use Bins (5,10,15, ... 40) and for Return use Bins (-40, -30, -20...... 100,110,120). Describe the shapes based on histograms. d. Find the ratio of Return Total Comp." in a new column. Based on this ratio, list top 5 corporations with highest ratio and 5 with lowest ratios. List 3 companies that you would invest in. e Find the Coefficient of variation for Return Total Comp" and compare with answers in part (b). What can you conclude? f- Construct scatter plot of Compensation (X) vs. Return (Y). Find best fitted linear line and R. Is CEO compensation a good indicator of corporate performance? Explain. 10.2 8.7 15.4 Company Compensation (Smillions) Return in 2012 (%) Abbott Laboratories 27 19 Adobe Systems 15 26 AES 10 -12 Aflac 12.3 30 Agilent Technologies 10.1 -5 AGL Resources 4.3 -1 Alcoa 11.7 2 Allegheny Technologies 7.2 -35 Allergan 20.4 5 Ameren 5.8 -2 American Electric Power 7.1 3 American Express 28 22 Ameriprise Financial 16.1 29 AmerisourceBergen 7.1 5 Analog Devices 12.7 10 Apartment Investment and Manag 4.7 22 Apple 12.3 76 Applied Materials 9.2 -11 AT&T 21 17 Avery Dennison 7.9 26 Baker Hughes 6.9 -15 Ball 9.1 25 Bank of New York Mellon 12.8 32 Baxter International 16 38 BB&T 8.6 19 Beam 8 21 Bemis 5.7 15 Berkshire Hathaway 0.5 27 Boeing 21.1 5 BorgWarner 11.8 12 Bristol-Myers Squibb 16 -4 C.H. Robinson Worldwide 5 -7 C.R. Bard 8.1 -10 Cabot Oil & Gas 7.9 31 Capital One Financial 22.6 37 CareFusion 10 -6 Carnival 6.3 19 Center Point Energy 5.3 -4 Chubb 11.6 11 Cigna 12.8 27 Cincinnati Financial 2.1 34 Coca-Cola 21.6 7 Coca-Cola Enterprises 26 Comerica 7.2 20 Corning 11.5 0 Covidien 10.3 37 Deere 17.1 15 Delphi Automotive 12.2 78 DirecTV 17.9 17 Discover Financial Services 9.9 76 Dominion Resources 8.2 2 Dover 11.6 15 DTE Energy 7.8 15 Duke Energy 8.3 1 E.I. du Pont de Nemours 12.7 2 Eastman Chemical 5.9 77 Eaton 18.5 28 eBay 49 68 Ecolab 8.8 24 Edison International 8.7 9 EMC 16.6 17 EQT 9 Equifax 11.7 42 F5 Networks 5.2 47 Fastenal 3.8 9 Fifth Third Bancorp 8.9 22 First Horizon National 5.7 24 FLIR Systems 4.7 -10 Fluor 9.3 18 FMC 7.6 37 Franklin Resources 12.3 32 Gannett 5.5 41 General Dynamics 18 7 General Electric 20.5 21 Genuine Parts 4.9 7 Gilead Sciences 15.3 79 Goodyear Tire & Rubber 13.4 -3 Harley-Davidson 10.3 27 HCP 11.8 14 Helmerich & Payne 5.1 18 Hershey 8.5 19 Hess 10 -6 Hewlett-Packard -46 Honeywell International 16.3 20 Humana 8.4 -20 Huntington Bancshares 6.7 19 IBM 15.4 6 International Flavors & Fragrance 8.2 29 International Game Technology 6 -8 Intuitive Surgical 5 6 Joy Global 6.6 -28 Kellogg 5.7 14 Kimberly-Clark 10.6 19 Kimco Realty 3.3 24 L-3 Communications Holdings 11.3 18 Lennar 13 107 Life Technologies 10.3 26 Lockheed Martin 23.8 19 M&T Bank 3 29 Marathon Oil 13 7 Marathon Petroleum 12.4 89 McCormick & Company 6.1 35 McGraw-Hill 9.1 24 Mead Westvaco 4.5 10 MetLife 13.2 8 MetroPCS Communications 7 15 Moody's 6.8 51 Motorola Solutions 10.3 22 Newfield Exploration 5.3 -29 Newmont Mining 9 -20 Noble 9.5 15 Norfolk Southern 9.7 -12 Northern Trust 7.2 29 NRG Energy 7.6 27 NYSE Euronext 12 25 Occidental Petroleum 28.5 -16 O'Reilly Automotive 3.8 12 PACCAR 7.1 23 Peabody Energy 9.5 -19 Pentair 14.7 50 People's United Financial 3.7 -1 PepsiCo 12.6 6 PerkinElmer 9.8 60 Pfizer 18.5 20 PNC Financial Services Group 6.3 4 PPG Industries 13.5 65 Praxair 12 4 Progressive 9.7 10 Prologis 25 32 Public Service Enterprise Group 8.4 -3 Qualcomm 20.7 30 Reynolds American 8.7 6 Rowan 7.4 3 Ryder System 5.5 -4 Schlumberger 15 3 Sempra Energy 6.7 29 Sherwin-Williams 11 74 Sigma-Aldrich 4.6 19 Snap-on 7.9 59 Spectra Energy 7 -7 St. Jude Medical 6.7 8 Stanley Black & Decker 10.7 12 Starbucks 28.9 38 SunTrust Banks 8.6 61 T. Rowe Price Group 8.4 17 TE Connectivity 9.8 24 TECO Energy 4.2 -8 Tenet Healthcare 8.1 58 Teradata 10.3 28 Tesoro 11.2 90 Texas Instruments 13.1 9 Textron 11.3 35 Total System Services 5 12 Tyco International 23.9 39 U.S. Bancorp 10.2 21 United Parcel Service 10.7 4 United States Steel 8.6 -9 United Technologies 21.8 15 Valero Energy 12.5 65 Verizon Communications 13.8 13 VE 10.1 21 Viacom 35 41 W.W. Grainger 8.5 10 Walt Disney 39 73 Wells Fargo 19.3 27 Weyerhaeuser 6.4 52 Whirlpool 12.9 100 Whole Foods Market 1.3 50 Wisconsin Energy 9.1 9 XL Group 9.4 29 The Excel Data File in includes the total compensation in Smillions) of CEO's of 170 large public companies and the corporate returns in 2012. a- Find the descriptive measures of the two data sets and describe the shape of the data sets based on comparison of Mean and Median. b- Find the Coefficient of Variation for the two data sets and determine which set has higher variability. C- Find the histogram of the two sets. For Total Compensation use Bins (5,10,15, ... 40) and for Return use Bins (-40, -30, -20...... 100,110,120). Describe the shapes based on histograms. d. Find the ratio of Return Total Comp." in a new column. Based on this ratio, list top 5 corporations with highest ratio and 5 with lowest ratios. List 3 companies that you would invest in. e Find the Coefficient of variation for Return Total Comp" and compare with answers in part (b). What can you conclude? f- Construct scatter plot of Compensation (X) vs. Return (Y). Find best fitted linear line and R. Is CEO compensation a good indicator of corporate performance? Explain. 10.2 8.7 15.4 Company Compensation (Smillions) Return in 2012 (%) Abbott Laboratories 27 19 Adobe Systems 15 26 AES 10 -12 Aflac 12.3 30 Agilent Technologies 10.1 -5 AGL Resources 4.3 -1 Alcoa 11.7 2 Allegheny Technologies 7.2 -35 Allergan 20.4 5 Ameren 5.8 -2 American Electric Power 7.1 3 American Express 28 22 Ameriprise Financial 16.1 29 AmerisourceBergen 7.1 5 Analog Devices 12.7 10 Apartment Investment and Manag 4.7 22 Apple 12.3 76 Applied Materials 9.2 -11 AT&T 21 17 Avery Dennison 7.9 26 Baker Hughes 6.9 -15 Ball 9.1 25 Bank of New York Mellon 12.8 32 Baxter International 16 38 BB&T 8.6 19 Beam 8 21 Bemis 5.7 15 Berkshire Hathaway 0.5 27 Boeing 21.1 5 BorgWarner 11.8 12 Bristol-Myers Squibb 16 -4 C.H. Robinson Worldwide 5 -7 C.R. Bard 8.1 -10 Cabot Oil & Gas 7.9 31 Capital One Financial 22.6 37 CareFusion 10 -6 Carnival 6.3 19 Center Point Energy 5.3 -4 Chubb 11.6 11 Cigna 12.8 27 Cincinnati Financial 2.1 34 Coca-Cola 21.6 7 Coca-Cola Enterprises 26 Comerica 7.2 20 Corning 11.5 0 Covidien 10.3 37 Deere 17.1 15 Delphi Automotive 12.2 78 DirecTV 17.9 17 Discover Financial Services 9.9 76 Dominion Resources 8.2 2 Dover 11.6 15 DTE Energy 7.8 15 Duke Energy 8.3 1 E.I. du Pont de Nemours 12.7 2 Eastman Chemical 5.9 77 Eaton 18.5 28 eBay 49 68 Ecolab 8.8 24 Edison International 8.7 9 EMC 16.6 17 EQT 9 Equifax 11.7 42 F5 Networks 5.2 47 Fastenal 3.8 9 Fifth Third Bancorp 8.9 22 First Horizon National 5.7 24 FLIR Systems 4.7 -10 Fluor 9.3 18 FMC 7.6 37 Franklin Resources 12.3 32 Gannett 5.5 41 General Dynamics 18 7 General Electric 20.5 21 Genuine Parts 4.9 7 Gilead Sciences 15.3 79 Goodyear Tire & Rubber 13.4 -3 Harley-Davidson 10.3 27 HCP 11.8 14 Helmerich & Payne 5.1 18 Hershey 8.5 19 Hess 10 -6 Hewlett-Packard -46 Honeywell International 16.3 20 Humana 8.4 -20 Huntington Bancshares 6.7 19 IBM 15.4 6 International Flavors & Fragrance 8.2 29 International Game Technology 6 -8 Intuitive Surgical 5 6 Joy Global 6.6 -28 Kellogg 5.7 14 Kimberly-Clark 10.6 19 Kimco Realty 3.3 24 L-3 Communications Holdings 11.3 18 Lennar 13 107 Life Technologies 10.3 26 Lockheed Martin 23.8 19 M&T Bank 3 29 Marathon Oil 13 7 Marathon Petroleum 12.4 89 McCormick & Company 6.1 35 McGraw-Hill 9.1 24 Mead Westvaco 4.5 10 MetLife 13.2 8 MetroPCS Communications 7 15 Moody's 6.8 51 Motorola Solutions 10.3 22 Newfield Exploration 5.3 -29 Newmont Mining 9 -20 Noble 9.5 15 Norfolk Southern 9.7 -12 Northern Trust 7.2 29 NRG Energy 7.6 27 NYSE Euronext 12 25 Occidental Petroleum 28.5 -16 O'Reilly Automotive 3.8 12 PACCAR 7.1 23 Peabody Energy 9.5 -19 Pentair 14.7 50 People's United Financial 3.7 -1 PepsiCo 12.6 6 PerkinElmer 9.8 60 Pfizer 18.5 20 PNC Financial Services Group 6.3 4 PPG Industries 13.5 65 Praxair 12 4 Progressive 9.7 10 Prologis 25 32 Public Service Enterprise Group 8.4 -3 Qualcomm 20.7 30 Reynolds American 8.7 6 Rowan 7.4 3 Ryder System 5.5 -4 Schlumberger 15 3 Sempra Energy 6.7 29 Sherwin-Williams 11 74 Sigma-Aldrich 4.6 19 Snap-on 7.9 59 Spectra Energy 7 -7 St. Jude Medical 6.7 8 Stanley Black & Decker 10.7 12 Starbucks 28.9 38 SunTrust Banks 8.6 61 T. Rowe Price Group 8.4 17 TE Connectivity 9.8 24 TECO Energy 4.2 -8 Tenet Healthcare 8.1 58 Teradata 10.3 28 Tesoro 11.2 90 Texas Instruments 13.1 9 Textron 11.3 35 Total System Services 5 12 Tyco International 23.9 39 U.S. Bancorp 10.2 21 United Parcel Service 10.7 4 United States Steel 8.6 -9 United Technologies 21.8 15 Valero Energy 12.5 65 Verizon Communications 13.8 13 VE 10.1 21 Viacom 35 41 W.W. Grainger 8.5 10 Walt Disney 39 73 Wells Fargo 19.3 27 Weyerhaeuser 6.4 52 Whirlpool 12.9 100 Whole Foods Market 1.3 50 Wisconsin Energy 9.1 9 XL Group 9.4 29
