Question

Fortune magazine's list of the world's most admired companies for 2014 is provided the data contained in the WEBfile named AdmiredCompanies (Fortune, March 17, 2014). The data in the column labelled Return shows the one-year total return (%) for the top ranked 50 companies. For the same time period the S&P average return was 18.4%. Click on the webfile logo to reference the data. WEB file a. Compute the median return for the top ranked 50 companies (to 1 decimal) b. What percentage of the top-ranked 50 companies had a one-year return greater than the S&P average return? c. Develop the five-number summary for the data. If required, round your answers to two decimals places. If required, enter negative values as negative numbers. Return Minimum First Quartile Median Third Quartile Maximum IQR 1.5CIQR) Lower Limit Upper Limit d. Are there any outliers? Sclect your answer-T

Rank	Company Name	Return
1	Apple	14.8
2	Amazon.com	29.8
3	Google	49.3
4	Berkshire Hathaway	11.7
5	Starbucks	27.1
6	Coca-Cola	1.4
7	Walt Disney	33.8
8	FedEx	24.4
9	Southwest Airlines	84.5
10	General Electric	12.3
11	American Express	39.6
12	Costco Wholesale	8.1
13	Nike	32.8
14	BMW	15.1
15	Procter & Gamble	4.1
16	IBM	-12.4
17	Nordstrom	5.8
18	Singapore Airlines	-13.9
19	Johnson & Johnson	20.5
20	Whole Foods Market	13
21	Samsung Electronics	-12.9
22	McDonald's	1.8
23	3M	28.3
24	Microsoft	34.2
25	Toyota Motor	31.3
26	Boeing	63.2
27	Exxon Mobil	2.5
28	Wal-Mart Stores	5.5
29	Target	-9.6
30	J.P. Morgan Chase	16.3
31	Nestlé	5.7
32	UPS	19.3
33	Caterpillar	-4
33	Goldman Sachs Group	8
35	Wells Fargo	30.6
36	Volkswagen	6.1
37	Unilever	-7.3
38	Facebook	117.1
39	Marriott International	21.7
40	Home Depot	15.7
41	BlackRock	24.2
42	PepsiCo	11.7
43	DuPont	34.3
44	eBay	-5.1
45	Accenture	8.3
46	Deere	-7.1
47	Intel	15.3
48	Delta Air Lines	116.6
49	Cisco Systems	7.4
50	AT&T	-4.5

Please show Excel Formulas, thank you.

Answer 1

(а) The median return for top 50 companies can be obtained using "MEDIAN(" command in MS Excel as follows: |=MEDIAN(F4:F53) Thus, the median return for the top-ranked 50 companies is 13.9| (b) To find the number of the top-ranked 50 companies having return greater than 18.4% use "COUNTIFO" function in MS Excel as follows: COUNTIF(F4:F53,">18.4") This gives a count that 20 companies have return greater than 18.4% Thus, the percentage of the top-ranked 50 companies having a return greater than 18.4% is computed as follows: 20 -x100 50 = 40 Thus, 40% companies among the top-ranked companies have a return greater than 18.4% (c) The minimum value in MS Excel can be computed using "QUARTILE.NCO" function as follows |-QUARTILE.INC(F4:F53,0) Thus, the minimum value is-13.9 The first quartile (Q) in MS Excel can be obtained using "QUARTILE.INC" function as follows

QUARTILE.INC(F4:F53,1) Thus, the first quartile is 4.45 The median value in MS Excel can be computed using "QUARTILE.INCO" function as follows: | =QUARTILE.INC(F4:F53,2) Thus, the median is 13.9 The third quartile (Q) in MS Excel can be obtained using "QUARTILE.INC()" function as follows: | =QUARTILE.INC(F4:F53,3) Thus, the third quartile is 29.425 The maximum value in MS Excel can be computed using "QUARTILE.INC()" function as follows |-QUARTILE.INC(F4:F53,4) Thus, the maximum value is |117.1| The Interquartile Range (IOR) is computed as follows: IQR Q = 29.425 -4.45 24.975 Thus, IOR 24.975 The lower limit of the interquartile range is computed as follows:

Lower limit Q-1.5 IQR -4.45- (1.5x24.975) -33.0125 Thus, the lower limit of the interquartile range is -33.0125

The upper limit of the interquartile range is computed as follows: Upper limit 1.51QR = 29.425 (1.5x24.975) = 66.8875 Thus, the upper limit of the interquartile range is |66.8875 (d) There are three outliers 84.5, 117.1, and 116.6 as these values are greater than the upper quartile range