Instructions:

There are three parts to this problem. Use Excel to perform the following:

A. Format the data as a Table. Sort from smallest to largest institutional holdings. Insert a combination chart that shows institutional holdings (line) and Total Dividends (bar). Add a trendline for the dividends. Be sure to put a proper heading, use a secondary axis. and have a legend.

B. Use the r-squared formula to see if there is a relationship between Institutional Holdings (X) and Total Dividends (Y).

C. Discuss vour results in the chart and the r-squared calculation.

Industry Name	Number of firms	Total Dividends	Dividend Payout	Average Dividend Yield	Market Cap	Institutional Holdings
Advertising	61	$1,004.62	912.85%	2.71%	$37,043.59	30.37%
Aerospace/Defense	72	$11,609.14	120.55%	1.87%	$621,095.05	53.43%
Air Transport	17	$1,184.41	0.09%	1.22%	$97,074.29	64.91%
Apparel	51	$1,705.15	0.32%	1.81%	$94,360.52	48.69%
Auto & Truck	19	$2,628.30	64.58%	0.33%	$787,476.54	45.17%
Auto Parts	52	$569.61	0.42%	0.54%	$104,907.93	49.79%
Bank (Money Center)	7	$30,567.25	46.95%	3.14%	$972,991.00	67.57%
Banks (Regional)	598	$17,984.63	44.35%	2.83%	$636,597.74	36.91%
Beverage (Alcoholic)	23	$1,162.41	41.58%	1.14%	$102,167.68	44.43%
Beverage (Soft)	41	$13,268.37	74.19%	2.42%	$547,731.57	25.32%
Broadcasting	29	$1,227.16	0.17%	1.58%	$77,567.15	50.38%
Brokerage & Investment Banking	39	$4,835.84	20.84%	1.29%	$373,798.51	62.96%
Building Materials	42	$966.54	21.49%	0.65%	$149,217.43	65.81%
Business & Consumer Services	169	$2,083.11	65.87%	0.65%	$322,045.63	51.09%
Cable TV	13	$4,331.40	26.20%	0.86%	$505,147.69	52.85%
Chemical (Basic)	48	$4,216.18	0.44%	4.06%	$103,899.37	41.50%
Chemical (Diversified)	5	$661.00	51.84%	2.83%	$23,344.50	58.66%
Chemical (Specialty)	97	$6,095.00	163.69%	1.32%	$461,441.34	55.43%
Coal & Related Energy	29	$291.95	2.24%	5.07%	$5,755.37	30.87%
Computer Services	116	$7,577.51	75.57%	2.46%	$308,582.73	54.21%
Computers/Peripherals	52	$16,794.66	26.83%	0.69%	$2,423,136.95	44.93%
Construction Supplies	46	$4,310.85	49.85%	1.62%	$265,765.31	67.54%
Diversified	29	$7,376.20	14.32%	0.76%	$966,912.22	55.55%
Drugs (Biotechnology)	547	$14,285.00	0.13%	1.15%	$1,244,611.67	37.32%
Drugs (Pharmaceutical)	287	$23,905.17	60.86%	1.75%	$1,367,318.28	25.70%
Education	38	$84.90	0.06%	0.21%	$39,800.68	48.14%
Electrical Equipment	122	$2,267.16	43.55%	1.00%	$227,433.22	40.78%
Electronics (Consumer & Office)	22	$-	0.00%	0.00%	$5,868.62	30.52%
Electronics (General)	157	$1,559.07	41.48%	0.61%	$257,223.74	41.98%
Engineering/Construction	61	$479.20	52.79%	0.52%	$92,680.32	60.47%
Entertainment	118	$3,229.24	0.11%	0.39%	$824,069.15	35.98%
Environmental & Waste Services	86	$1,955.74	94.76%	1.23%	$159,488.36	43.04%
Farming/Agriculture	32	$2,396.13	38.93%	1.53%	$157,024.80	38.49%
Financial Svcs. (Non-bank & Insurance)	235	$11,238.49	23.86%	1.47%	$763,548.02	38.81%
Food Processing	101	$9,542.67	60.98%	2.27%	$419,578.14	45.96%
Food Wholesalers	18	$935.40	0.20%	1.69%	$55,272.89	56.34%
Furn/Home Furnishings	40	$579.58	27.17%	1.17%	$49,646.11	43.67%
Green & Renewable Energy	25	$1,042.50	0.15%	3.26%	$31,933.54	47.87%
Healthcare Products	265	$5,920.83	30.11%	0.47%	$1,250,800.18	46.78%
Healthcare Support Services	129	$11,192.24	25.63%	1.22%	$919,581.00	46.56%
Heathcare Information and Technology	139	$1,184.72	10.55%	0.17%	$717,648.57	48.78%
Homebuilding	30	$806.85	7.58%	0.67%	$121,040.74	73.12%
Hospitals/Healthcare Facilities	32	$486.04	10.91%	0.48%	$100,806.98	56.15%
Hotel/Gaming	66	$4,006.64	0.50%	1.00%	$399,597.71	54.78%
Household Products	140	$12,825.92	60.85%	1.93%	$665,902.87	34.72%
Information Services	77	$8,851.72	32.41%	0.51%	$1,722,654.89	57.58%
Insurance (General)	21	$3,410.86	192.79%	1.85%	$184,254.75	68.63%
Insurance (Life)	26	$5,604.86	37.95%	3.36%	$166,674.96	55.68%
Insurance (Prop/Cas.)	55	$5,769.16	38.39%	2.09%	$276,321.04	52.24%
Investments & Asset Management	348	$11,561.52	52.70%	2.26%	$510,840.03	37.28%
Machinery	125	$4,716.55	45.37%	1.12%	$422,335.84	66.85%
Metals & Mining	86	$1,378.46	197.36%	1.26%	$109,828.92	21.74%
Office Equipment & Services	22	$323.70	91.77%	2.10%	$15,432.41	55.23%
Oil/Gas (Integrated)	3	$25,769.92	4.48%	7.30%	$353,000.60	60.76%
Oil/Gas (Production and Exploration)	278	$5,877.24	1.68%	2.65%	$221,949.95	30.29%
Oil/Gas Distribution	57	$25,204.45	4.66%	9.78%	$257,833.37	41.30%
Oilfield Svcs/Equip.	135	$9,148.14	0.30%	4.67%	$195,816.63	44.74%
Packaging & Container	26	$2,504.20	64.94%	1.69%	$148,538.93	63.84%
Paper/Forest Products	15	$359.50	117.52%	3.62%	$9,919.81	81.71%
Power	55	$21,765.11	87.59%	3.10%	$702,547.15	67.35%
Precious Metals	93	$708.32	31.91%	1.09%	$65,249.43	16.79%
Publishing & Newspapers	29	$495.49	0.54%	1.64%	$30,206.51	48.47%
R.E.I.T.	238	$48,876.97	430.49%	3.87%	$1,262,844.50	71.38%
Real Estate (Development)	25	$0.03	0.00%	0.00%	$6,012.41	23.62%
Real Estate (General/Diversified)	11	$24.80	67.61%	0.56%	$4,457.11	39.56%
Real Estate (Operations & Services)	61	$344.26	39.08%	0.51%	$68,131.00	34.03%
Recreation	69	$1,042.23	0.71%	0.86%	$121,614.50	46.75%
Reinsurance	2	$178.00	36.15%	1.08%	$16,513.90	90.98%
Restaurant/Dining	79	$7,071.93	114.33%	1.60%	$440,835.04	60.10%
Retail (Automotive)	30	$241.00	3.72%	0.17%	$144,407.04	64.28%
Retail (Building Supply)	15	$8,226.40	45.51%	1.87%	$441,067.33	69.91%
Retail (Distributors)	85	$2,290.69	52.37%	1.35%	$169,532.80	55.18%
Retail (General)	17	$9,631.73	36.12%	1.25%	$771,264.98	67.15%
Retail (Grocery and Food)	14	$641.47	12.85%	1.33%	$48,064.88	54.06%
Retail (Online)	75	$1,214.00	5.66%	0.06%	$1,958,174.66	39.21%
Retail (Special Lines)	85	$4,493.09	0.32%	1.36%	$330,475.44	61.49%
Rubber& Tires	3	$95.10	0.07%	2.07%	$4,586.06	71.58%
Semiconductor	70	$20,012.45	42.10%	1.29%	$1,555,904.54	58.94%
Semiconductor Equip	40	$2,233.34	23.31%	0.79%	$282,762.43	64.28%
Shipbuilding & Marine	11	$71.78	0.16%	1.01%	$7,108.74	52.47%
Shoe	11	$1,649.52	48.27%	0.67%	$245,774.48	76.64%
Software (Entertainment)	101	$147.80	0.26%	0.01%	$2,246,029.50	29.59%
Software (Internet)	36	$14.00	0.18%	0.01%	$264,399.38	42.53%
Software (System & Application)	388	$20,728.83	29.35%	0.51%	$4,026,925.39	46.83%
Steel	32	$1,174.97	0.65%	2.12%	$55,407.94	59.14%
Telecom (Wireless)	16	$99.96	3.04%	0.06%	$175,997.26	49.86%
Telecom. Equipment	96	$6,873.16	64.71%	2.12%	$324,063.68	44.59%
Telecom. Services	58	$15,148.95	51.92%	3.14%	$482,192.64	37.73%
Tobacco	15	$13,806.43	164.95%	6.59%	$209,420.78	42.92%
Transportation	21	$4,475.70	55.44%	1.70%	$262,720.24	61.51%
Transportation (Railroads)	6	$4,404.10	41.03%	1.52%	$289,175.22	79.76%
Trucking	35	$480.32	0.27%	0.26%	$187,610.07	53.17%
Utility (General)	16	$10,447.20	100.90%	3.61%	$289,495.40	81.59%
Utility (Water)	17	$768.22	66.37%	1.53%	$50,225.24	47.53%