Question

1. Calculate the mean and standard deviation for each variable using formulas or functions. 2. Calculate descriptive statistics for each variable using the Analysis Toolpak. 3. Calculate the coefficient of variation for each variable. What general interpretation can you make from these values? 4. Calculate the correlation between Revenue and Employees using a formula. Calculate the coefficient of determination. 5. Create a correlation matrix for the eight numerical variables. Note any relationship of interest Rank 1. Calculate the mean and standard deviation for each variable using formulas or functions. 2. Calculate descriptive statistics for each variable using the Analysis Toolpak. 3. Calculate the coefficient of variation for each variable. What general interpretation can you make from these values? 4. Calculate the correlation between Revenue and Employees using a formula. Calculate the coefficient of determination. 5. Create a correlation matrix for the eight numerical variables. Note any relationship of interest	COMPANY NAME	City	State	Revenue $ millions	Profits $ millions	Assets $ millions	Stock- holder's Equity $ millions	Market Value 3/31/15 $ millions	Earnings Per Share 2014	2014 Total Return to Investors	# Employees
1	Wal-Mart Stores	Bentonville	AR	485,651	16,363	203,706	81,394	265,344	5.05	11.9	2,200,000
2	Exxon Mobil	Irving	TX	382,597	32,520	349,493	174,399	356,549	7.60	(6.0)	83,700
3	Chevron	San Ramon	CA	203,784	19,241	266,026	155,028	197,381	10.14	(6.9)	64,700
4	Berkshire Hathaway	Omaha	NE	194,673	19,872	526,186	240,170	357,344	12,092.00	27.0	316,000
5	Apple	Cupertino	CA	182,795	39,510	231,839	111,547	724,773	6.45	40.5	97,200
6	General Motors	Detroit	MI	155,929	3,949	177,677	35,457	60,389	1.65	(11.6)	216,000
7	Phillips 66	Houston	TX	149,434	4,762	48,741	21,590	42,627	8.33	(4.8)	14,000
8	General Electric	Fairfield	CT	148,321	15,233	648,349	128,159	249,775	1.50	(6.7)	305,000
9	Ford Motor	Dearborn	MI	144,077	3,187	208,527	24,805	64,154	0.80	3.6	187,000
10	CVS Health	Woonsocket	RI	139,367	4,644	74,252	37,958	117,171	3.96	36.6	177,800
11	McKesson	San Francisco	CA	138,030	1,263	51,759	8,522	52,669	5.41	29.3	42,800
12	AT&T	Dallas	TX	132,447	6,224	292,829	86,370	169,459	1.19	0.8	243,620
13	Valero Energy	San Antonio	TX	130,844	3,630	45,550	20,677	32,703	6.85	0.2	10,065
14	UnitedHealth Group	Minnetonka	MN	130,474	5,619	86,382	32,454	112,813	5.70	36.4	170,000
15	Verizon Communications	New York	NY	127,079	9,625	232,708	12,298	198,410	2.42	(0.5)	177,300
16	AmerisourceBergen	Chesterbrook	PA	119,569	277	21,532	1,957	24,963	1.17	29.9	13,500
17	Fannie Mae	Washington	DC	116,461	14,208	3,248,176	3,680	2,722	(0.19)	(31.7)	7,600
18	Costco Wholesale	Issaquah	WA	112,640	2,058	33,024	12,303	66,654	4.65	20.4	153,500
19	Hewlett-Packard	Palo Alto	CA	111,454	5,013	103,206	26,731	56,635	2.62	46.2	302,000
20	Kroger	Cincinnati	OH	108,465	1,728	30,556	5,412	37,648	3.44	64.7	400,000
21	J.P. Morgan Chase & Co.	New York	NY	102,102	21,762	2,573,126	232,065	225,861	5.29	10.0	241,359
22	Express Scripts Holding	St. Louis	MO	100,887	2,008	53,799	20,054	63,237	2.64	20.5	29,500
23	Bank of America Corp.	Charlotte	NC	95,181	4,833	2,104,534	243,471	161,896	0.36	15.7	223,715
24	International Business Machines	Armonk	NY	94,128	12,022	117,532	11,868	158,642	11.90	(12.4)	412,775
25	Marathon Petroleum	Findlay	OH	91,417	2,524	30,460	10,751	27,959	8.78	0.5	45,340
26	Cardinal Health	Dublin	OH	91,084	1,166	26,033	6,401	29,801	3.38	23.1	34,000
27	Boeing	Chicago	IL	90,762	5,446	99,198	8,665	105,032	7.38	(2.6)	165,500
28	Citigroup	New York	NY	90,646	7,313	1,842,530	210,534	156,304	2.20	3.9	241,000
29	Amazon.com	Seattle	WA	88,988	(241)	54,505	10,741	172,797	(0.52)	(22.2)	154,100
30	Wells Fargo	San Francisco	CA	88,372	23,057	1,687,155	184,394	279,920	4.10	24.0	264,500
31	Microsoft	Redmond	WA	86,833	22,074	172,384	89,784	333,525	2.63	27.5	128,000
32	Procter & Gamble	Cincinnati	OH	84,537	11,643	144,266	69,214	221,280	4.01	15.4	118,000
33	Home Depot	Atlanta	GA	83,176	6,345	39,946	9,322	148,533	4.71	30.3	371,000
34	Archer Daniels Midland	Chicago	IL	81,201	2,248	44,027	19,575	29,812	3.43	22.3	33,900
35	Walgreens Boots Alliance	Deerfield	IL	76,392	1,932	37,182	20,457	92,365	2.00	35.2	213,000
36	Target	Minneapolis	MN	74,520	(1,636)	41,404	13,997	52,668	(2.56)	23.6	347,000
37	Johnson & Johnson	New Brunswick	NJ	74,331	16,323	131,119	69,752	279,717	5.70	17.3	126,500
38	Anthem	Indianapolis	IN	73,874	2,570	62,065	24,251	41,195	8.99	38.1	51,500
39	MetLife	New York	NY	73,316	6,309	902,337	72,053	56,578	5.42	2.8	68,000
40	Google	Mountain View	CA	71,487	14,444	131,133	104,500	377,542	21.02	(5.3)	53,600
41	State Farm Insurance Cos.	Bloomington	IL	71,160	4,191	239,143	79,982				73,262
42	Freddie Mac	McLean	VA	69,367	7,690	1,945,539	2,651	1,482	(0.72)	(29.0)	4,982
43	Comcast	Philadelphia	PA	68,775	8,380	159,339	52,711	143,494	3.20	13.5	139,000
44	PepsiCo	Purchase	NY	66,683	6,513	70,509	17,438	141,744	4.27	17.2	271,000
45	United Technologies	Hartford	CT	65,100	6,220	91,289	31,213	106,470	6.82	3.2	211,500
46	American International Group	New York	NY	64,406	7,529	515,581	106,898	74,184	5.20	10.7	65,000
47	United Parcel Service	Atlanta	GA	58,232	3,032	35,471	2,141	87,492	3.28	8.6	336,150
48	Dow Chemical	Midland	MI	58,167	3,772	68,796	22,423	55,546	2.87	5.8	53,216
49	Aetna	Hartford	CT	58,003	2,041	53,402	14,483	37,147	5.68	31.1	48,800
50	Lowe's	Mooresville	NC	56,223	2,698	31,827	9,968	70,797	2.71	41.2	220,500
51	ConocoPhillips	Houston	TX	55,997	6,869	116,539	51,911	76,671	5.51	1.6	19,100
52	Intel	Santa Clara	CA	55,870	11,704	91,956	55,865	148,095	2.31	44.1	106,700
53	Energy Transfer Equity	Dallas	TX	55,691	633	64,469	664	34,137	1.15	44.4	27,605
54	Caterpillar	Peoria	IL	55,184	3,695	84,681	16,746	48,512	5.88	3.4	114,233
55	Prudential Financial	Newark	NJ	54,123	1,381	766,655	41,770	36,461		0.5	48,331
56	Pfizer	New York	NY	49,605	9,135	169,274	71,301	213,622	1.42	5.2	78,300
57	Walt Disney	Burbank	CA	48,813	7,501	84,186	44,958	178,267	4.26	24.7	180,000
58	Humana	Louisville	KY	48,500	1,147	23,466	9,646	26,633	7.36	40.5	57,000
59	Enterprise Products Partners	Houston	TX	47,951	2,787	47,101	18,063	67,355	1.47	13.3	6,900
60	Cisco Systems	San Jose	CA	47,142	7,853	105,134	56,654	140,508	1.49	27.9	74,042
61	Sysco	Houston	TX	46,517	932	13,168	5,267	22,349	1.58	13.5	50,300
62	Ingram Micro	Santa Ana	CA	46,487	267	12,831	4,166	3,925	1.67	17.8	21,700
63	Coca-Cola	Atlanta	GA	45,998	7,098	92,023	30,320	177,142	1.60	5.3	129,200
64	Lockheed Martin	Bethesda	MD	45,600	3,614	37,073	3,400	64,193	11.21	33.7	112,000
65	FedEx	Memphis	TN	45,567	2,097	33,070	15,277	46,948	6.75	21.4	298,099
66	Johnson Controls	Milwaukee	WI	43,855	1,215	32,804	11,311	33,154	1.80	(4.1)	168,000
67	Plains GP Holdings	Houston	TX	43,464	70	23,983	1,657	17,361	0.47	(1.8)	5,300
68	World Fuel Services	Miami	FL	43,386	222	4,880	1,855	4,144	3.11	9.1	4,041
69	CHS	Inver Grove Heights	MN	42,664	1,081	15,147	6,449				11,824
70	American Airlines Group	Fort Worth	TX	42,650	2,882	43,771	2,021	36,769	3.93	113.4	113,300
71	Merck	Kenilworth	NJ	42,237	11,920	98,335	48,647	163,139	4.07	17.1	70,000
72	Best Buy	Richfield	MN	41,903	1,233	15,256	4,995	13,309	3.49	-	125,000
73	Delta Air Lines	Atlanta	GA	40,362	659	54,121	8,813	37,059	0.78	80.4	79,655
74	Honeywell International	Morris Township	NJ	40,306	4,239	45,451	17,657	81,427	5.33	11.5	127,000
75	HCA Holdings	Nashville	TN	40,087	1,875	31,199	(7,894)	31,559	4.16	53.8	197,000
76	Goldman Sachs Group	New York	NY	40,085	8,477	856,240	82,797	81,884	17.07	10.7	34,000
77	Tesoro	San Antonio	TX	40,052	843	16,584	4,454	11,479	6.44	29.4	5,641
78	Liberty Mutual Insurance Group	Boston	MA	39,796	1,833	124,304	20,218				50,000
79	United Continental Holdings	Chicago	IL	38,901	1,132	37,353	2,396	25,839	2.93	76.8	84,000
80	New York Life Insurance	New York	NY	38,680	1,602	249,664	18,606				11,563
81	Oracle	Redwood City	CA	38,275	10,955	90,344	46,878	188,439	2.38	19.0	122,000
82	Morgan Stanley	New York	NY	37,953	3,467	801,510	70,900	70,545	1.60	25.1	55,802
83	Tyson Foods	Springdale	AR	37,580	864	23,956	8,890	15,565	2.37	20.8	124,000
84	Safeway	Pleasanton	CA	36,643	113	13,377	5,450		0.48	23.4	137,000
85	Nationwide	Columbus	OH	36,257	432	182,575	14,869				33,672
86	Deere	Moline	IL	36,067	3,162	61,336	9,063	29,770	8.63	(0.6)	59,623
87	DuPont	Wilmington	DE	36,046	3,625	49,876	13,320	64,710	3.92	17.0	63,000
88	American Express	New York	NY	35,999	5,885	159,103	20,673	79,618	5.56	3.7	54,000
89	Allstate	Northbrook	IL	35,239	2,850	108,533	22,304	29,637	6.27	31.3	39,950
90	Cigna	Bloomfield	CT	34,914	2,102	55,896	10,774	33,453	7.83	17.7	37,200
91	Mondelez International	Deerfield	IL	34,244	2,184	66,815	27,750	59,181	1.28	4.6	104,000
92	TIAA-CREF	New York	NY	34,230	967	526,048	33,920				12,322
93	INTL FCStone	New York	NY	34,063	19	3,040	345	561	0.98	11.2	1,141
94	Massachusetts Mutual Life Insurance	Springfield	MA	33,572	1,327	253,858	14,231				11,418
95	DirecTV	El Segundo	CA	33,260	2,756	25,459	(5,213)	42,788	5.40	25.5	30,925
96	Halliburton	Houston	TX	32,870	3,500	32,240	16,267	37,284	4.11	(21.6)	80,000
97	Twenty-First Century Fox	New York	NY	31,867	4,514	54,793	17,418	71,948	1.99	10.1	27,000
98	3M	St. Paul	MN	31,821	4,956	31,269	13,109	104,796	7.49	20.0	89,800
99	Sears Holdings	Hoffman Estates	IL	31,198	(1,682)	13,209	(951)	4,409	(15.82)	(15.9)	196,000
100	General Dynamics	Falls Church	VA	30,852	2,533	35,355	11,829	44,816	7.42	47.0	99,500

Answer 1

	Revenue $ millions	Profits $ millions	Assets $ millions	Stock- holder's Equity $ millions	Market Value 3/31/15 $ millions	Earnings Per Share 2014	2014 Total Return to Investors	# Employees
Mean	164,413	18,403	263,655	39,945	104,594	12,092	17	2,200,000
Standard Deviation	90812.55	7571.915	546075.6	54295.83	109741.8	0	22.66188	0
Coefficient of variation	55.2345	41.145	207.1178	135.927	104.9215	0	131.8551	0

Excel Command:

Mean=Average(data range)

Standard Deviation=Stdevp(data range)

Coefficient of variation=(standard deviation/mean)*100

Descriptive Statistics: 1) Menu bar in data select

2) Select Analysis tool in Data Analysis

3) Descriptive Statistics then ok

4) we select input range (Select Data) then output range

5) ok

Correlation= Correl(select data 1 range, Select data 2 range)