||Times New R...-1112-11A-Av 1 = Paste A1 1 Rank Company Name Return I Apple 29.
ID: 2921291 • Letter: #
Question
||Times New R...-1112-11A-Av 1 = Paste A1 1 Rank Company Name Return I Apple 29.8 49.3 3 Google 4Berkshire Hathaway I 11.7 5 Starbucks - 27.1 6 Coca-Cola 7 Walt Disney 8 FedEx 9 Southwest Airlines 10 General Electric 11 American Express 12 Costco Wholesale 13 Nike 14 BMW 15 Procter &Gamble; 16 IBM 17 Nordstrom 18 Singapore Airlines 19 Johnson & Johnson 20 Whole Foods Market 24.4 84.5 12.3 39.6 8.1 32.8 15.1 12.4 -13.9 20.5 21 Samsung Electronics12.9 22 McDonald's 23 3M 24 Microsoft 25 Toyota Motor 26 Boeing 27 Exxon Mobil 28 Wal-Mart Stores 29 Target 30 J.P. Morgan Chase 31 Nestle 32 UPS 28.3 34.2 31.3 63.2 5.5 19.3 33 Caterpillar 33 Goldman Sachs Group ReadyExplanation / Answer
The data have been provided in Excel and, hence, we use it to solve the problems.
Exercise (3.53):
We have been given data on one year total return (%) of 50 most admired companies in the world for 2014. The S&P average return is given at 18.4%. We enter this data in Excel.
(a) To find the median return for the top-ranked 50 companies, we use the MEDIAN function of Excel on the one-year total return (%) column in the data. We get the median return as 13.9%.
(b) To find the percentage of the top-ranked 50 companies have a one-year return greater than the S&P average return (18.4%), we first calculate a column, using the IF function, beside the one-year total return (%) column in the data, where we shall assign a value of 1 to a company if its return is greater than the S&P average return and 0 if not. We do this and we find that 20 companies have returns greater than the S&P average return %. The percentage that we obtain is = (20/50)*100% = 40%.
(c) Now, we are to develop the five-number summary for the data, i.e., we calculate the minimum, 1st quartile, median, 3rd quartile and maximum values.
We use the MINIMUM, QUARTILE, MEDIAN AND MAXIMUM functions to calculate these figures.
They are as given below.
(d) To find whether there are any outliers or not, we construct a boxplot of this data, which will readily identify the outliers. Now, boxplot uses the logic that if any value is lesser than "Q1 - 1.5*(Q3 - Q1)" or greater than "Q3 + 1.5*(Q3 - Q1)", then the value is labelled as an outlier by boxplot (Here Q1 is the 1st quartile and Q3 is the 3rd quartile).
Using the same logic, we construct a column beside the one-year total return (%) column, where we shall assign 1 if the return value satisfies the above condition or 0 if it doesn't. The returns assigned 1 will be the outlier values. We use the IF function of Excel to construct this column.
We notice that there are 3 outliers in the data, more specifically, the return values of Southwest Airlines, Facebook and Delta Air Lines are outliers. The outlier values are 84.5, 117.1 and 116.6.
Exercise (3.61):
We have a random sample of 30 colleges, whose Admit Rates (%) and 4-yr Grad Rates (%) are given.
(a) Among the 4 options that we have, the scatter diagram with Admit Rate (%) as the independent variable, will be the 1st option (1st scatter diagram).
The scatter diagram that we have plotted indicates a negative linear relationship between Admit Rates and 4-yr Grad Rates.
(b) We are to calculate the sample correlation coefficient between Admit Rates and 4-yr Grad Rates. We use the CORREL function in Excel to compute this and we get the value as -0.76.
This sample correlation coefficient value indicates that there is a (quite strong) negative linear relationship between Admit Rates and 4-yr Grad Rates, i.e., an increase in Admit Rate (%) is associated with a decrease in 4-yr Grad Rate (%) and vice-versa.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.