Using the S&P 500 Company Data.xlsx file from the official Web site (www.cengage.com/finance/mayes/analysis/9e):

a. Create a table from the data and then add a calculated column that calculates the total return for the last three years. Name it “Total Return 3 Yr.” Use Get & Transform to remove #N/A values from the calculated column. Make a pivot table that shows the average three-year total return by GICS Sector.

b. Now add the GICS Industry field to the pivot table. Which industry was the best performer in the worst sector over the past three years? Sort the fields in your pivot table so that this sector and industry is at the very top of the table.

c. Create a new pivot table from the original data that shows the top 20 GICS Sub-Industries by sum of market capitalization. Also include a count of the number of companies in each sub-industry.

Data from S&P 500 Company Data.xlsx

Company Symbol	Company Name	GICS Sector	GICS Industry	GICS Sub-Industry	Beta	Altman Z Score	Market Value	Total Return 2018	Total Return 2017	Total Return 2016	Div Yield	Net Profit Margin	Operating Profit Margin
MMM	3M Company	Industrials	Industrial Conglomerates	Industrial Conglomerates	1.09	5.8	109,861	-0.16910440	0.34915460	0.21720710	0.03230645	0.16325347	0.23726537
AOS	A. O. Smith Corporation	Industrials	Building Products	Building Products	1.55	6.1	6,144	-0.29376140	0.30781830	0.25077830	0.01858818	0.13933938	0.17287242
ABT	Abbott Laboratories	Health Care	Health Care Equipment & Supplies	Health Care Equipment	1.10	3.3	127,036	0.29037200	0.52004830	-0.12315790	0.01429933	0.07583884	0.12571130
ABBV	AbbVie, Inc.	Health Care	Biotechnology	Biotechnology	0.96	2.5	138,674	-0.00980204	0.60128870	0.09877849	0.05776893	0.17271700	0.37059201
ABMD	ABIOMED, Inc.	Health Care	Health Care Equipment & Supplies	Health Care Equipment	0.31	68.4	14,640	0.73437920	0.66320560	0.24811700	0.00000000	0.33663274	0.29098998
ACN	Accenture Plc Class A	Information Technology	IT Services	IT Consulting & Other Services	1.04	7.8	89,887	-0.06211382	0.33341500	0.14330710	0.01488201	0.09758588	0.14263599
ATVI	Activision Blizzard, Inc.	Communication Services	Entertainment	Interactive Home Entertainment	0.90	4.8	35,535	-0.26073880	0.76410270	-0.05961978	0.00783898	0.24195916	0.26424663
ADBE	Adobe Inc.	Information Technology	Software	Application Software	1.10	9.2	110,435	0.29102950	0.70218570	0.09591222	0.00000000	0.28846118	0.31083507
AAP	Advance Auto Parts, Inc.	Consumer Discretionary	Specialty Retail	Automotive Retail	0.90	3.3	11,478	0.58220620	-0.40927690	0.12535970	0.00151343	0.04424034	0.06927313
AMD	Advanced Micro Devices, Inc.	Information Technology	Semiconductors & Semiconductor Equipment	Semiconductors	3.06	3.1	18,449	0.79571970	-0.09347445	2.95122000	0.00000000	0.05204633	0.07042471
AES	AES Corporation	Utilities	Independent Power and Renewable Electricity Producers	Independent Power Producers & Energy Traders	1.03	0.8	9,577	0.38991020	-0.02693486	0.26337650	0.03120609	0.09169615	0.22249116
AMG	Affiliated Managers Group, Inc.	Financials	Capital Markets	Asset Management & Custody Banks	1.63	#N/A	5,132	-0.52147250	0.41927610	-0.09051073	0.01381462	0.10212552	0.29065526
AFL	Aflac Incorporated	Financials	Insurance	Life & Health Insurance	0.72	#N/A	34,411	0.06209338	0.28996710	0.19114820	0.01863245	0.13160860	0.19317618
A	Agilent Technologies, Inc.	Health Care	Life Sciences Tools & Services	Life Sciences Tools & Services	1.43	4.4	21,488	0.01673150	0.48320580	0.10148550	0.00873692	0.06430606	0.18945869
APD	Air Products and Chemicals, Inc.	Materials	Chemicals	Industrial Gases	0.85	4.7	35,152	0.00247967	0.17046700	0.22499310	0.01989787	0.16299747	0.21448568
AKAM	Akamai Technologies, Inc.	Information Technology	IT Services	Internet Services & Infrastructure	0.63	3.5	9,948	-0.06088561	-0.02459508	0.26695800	0.00000000	0.10991927	0.15093016
ALK	Alaska Air Group, Inc.	Industrials	Airlines	Airlines	0.88	1.9	7,500	-0.15554940	-0.15902440	0.11967540	0.02085928	0.05287996	0.08857696
ALB	Albemarle Corporation	Materials	Chemicals	Specialty Chemicals	1.63	2.6	8,185	-0.38885830	0.50204720	0.56213400	0.01934462	0.20550290	0.21461414
ARE	Alexandria Real Estate Equities, Inc.	Real Estate	Equity Real Estate Investment Trusts (REITs)	Office REITs	0.75	#N/A	12,793	-0.09010660	0.20944190	0.26912490	0.02560055	0.27904649	0.26727861
ALXN	Alexion Pharmaceuticals, Inc.	Health Care	Biotechnology	Biotechnology	1.65	3.8	21,721	-0.18588510	-0.02255827	-0.35858450	0.00000000	0.01877571	0.39237842
ALGN	Align Technology, Inc.	Health Care	Health Care Equipment & Supplies	Health Care Supplies	1.97	14.9	16,751	-0.05742836	1.31134900	0.45983290	0.00000000	0.20352740	0.23725700
ALLE	Allegion PLC	Industrials	Building Products	Building Products	1.18	4.3	7,577	0.01179135	0.25314490	-0.02204072	0.00869250	0.15920489	0.19767910
AGN	Allergan plc	Health Care	Pharmaceuticals	Pharmaceuticals	1.64	1.0	44,455	-0.16871750	-0.21087430	-0.32796800	0.01760627	-0.32281440	0.02107377
ADS	Alliance Data Systems Corporation	Information Technology	IT Services	Data Processing & Outsourced Services	1.63	1.4	8,175	-0.40177700	0.11935350	-0.17167820	0.01604278	0.12361382	0.24174710
LNT	Alliant Energy Corp	Utilities	Electric Utilities	Electric Utilities	0.30	1.1	9,973	0.02360523	0.16046870	0.25435290	0.02768305	0.14777196	0.19646343
ALL	Allstate Corporation	Financials	Insurance	Property & Casualty Insurance	0.81	#N/A	27,433	-0.19521240	0.43620810	0.21707620	0.01847753	0.05756058	0.06292813
GOOGL	Alphabet Inc. Class A	Communication Services	Interactive Media & Services	Interactive Media & Services	0.96	10.3	312,413	-0.00801224	0.32929530	0.01856017	0.00000000	0.22441916	0.23022386
GOOG	Alphabet Inc. Class C	Communication Services	Interactive Media & Services	Interactive Media & Services	0.95	10.2	362,065	-0.01031160	0.35575650	0.01705146	0.00000000	0.22441916	0.23022386
MO	Altria Group Inc	Consumer Staples	Tobacco	Tobacco	0.42	3.3	92,806	-0.27086590	0.09446144	0.20461910	0.06464647	0.35435879	0.69628573
AMZN	Amazon.com, Inc.	Consumer Discretionary	Internet & Direct Marketing Retail	Internet & Direct Marketing Retail	1.58	5.6	737,467	0.28431680	0.55956360	0.10945570	0.00000000	0.04325274	0.05460588
AMCR	Amcor PLC	Materials	Containers & Packaging	Paper Packaging	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	0.05599103	0.07768991	0.10816495
AEE	Ameren Corporation	Utilities	Multi-Utilities	Multi-Utilities	0.29	0.9	15,935	0.13975510	0.15958150	0.25663480	0.02471770	0.12955015	0.21570498
AAL	American Airlines Group, Inc.	Industrials	Airlines	Airlines	1.69	0.9	14,789	-0.37680520	0.12396800	0.11492770	0.01184133	0.03170113	0.07743427
AEP	American Electric Power Company, Inc.	Utilities	Electric Utilities	Electric Utilities	0.15	0.9	36,855	0.05391812	0.20928690	0.11914300	0.02914127	0.11899401	0.16778416
AXP	American Express Company	Financials	Consumer Finance	Consumer Finance	1.06	#N/A	80,736	-0.02632165	0.36204790	0.08570968	0.01187871	0.15866451	0.20337338
AIG	American International Group, Inc.	Financials	Insurance	Multi-line Insurance	1.20	#N/A	34,153	-0.32088150	-0.06860942	0.07769501	0.02274747	0.00076258	0.04382732
AMT	American Tower Corporation	Real Estate	Equity Real Estate Investment Trusts (REITs)	Specialized REITs	0.50	#N/A	69,681	0.13323210	0.37687560	0.11214470	0.01650876	0.16618056	0.21345143
AWK	American Water Works Company, Inc.	Utilities	Water Utilities	Water Utilities	0.19	0.8	16,393	0.01309490	0.29019110	0.23591090	0.01599074	0.16482558	0.32529070
AMP	Ameriprise Financial, Inc.	Financials	Capital Markets	Asset Management & Custody Banks	1.78	#N/A	14,545	-0.36833750	0.56366850	0.07724178	0.02428212	0.16658726	0.20184215
ABC	AmerisourceBergen Corporation	Health Care	Health Care Providers & Services	Health Care Distributors	0.94	5.0	15,768	-0.17573770	0.19516800	-0.23330410	0.01758486	0.00987501	0.00983062
AME	AMETEK, Inc.	Industrials	Electrical Equipment	Electrical Components & Equipment	1.24	4.0	15,714	-0.05892592	0.49977770	-0.08627099	0.00615385	0.16053519	0.22194973
AMGN	Amgen Inc.	Health Care	Biotechnology	Biotechnology	1.16	2.3	122,642	0.15171300	0.22248660	-0.07548201	0.03166981	0.35316392	0.43444968
APH	Amphenol Corporation Class A	Information Technology	Electronic Equipment Instruments & Components	Electronic Components	1.03	4.5	24,414	-0.06821692	0.31805300	0.29883930	0.00928824	0.14691539	0.20670568
APC	Anadarko Petroleum Corporation	Energy	Oil Gas & Consumable Fuels	Oil & Gas Exploration & Production	1.66	1.1	22,108	-0.16823860	-0.22762460	0.44056520	0.01572972	0.04633736	0.25439670
ADI	Analog Devices, Inc.	Information Technology	Semiconductors & Semiconductor Equipment	Semiconductors	1.42	3.1	31,771	-0.01647252	0.25294790	0.34942620	0.01755594	0.24020915	0.31340980
ANSS	ANSYS, Inc.	Information Technology	Software	Fantastic news! We've Found the answer you've been seeking! Answer rating: 81% (11 reviews) a Create a table from the data and then add a calculated column that calculates the total return for the last three years Name it Total Return 3 Yr Us...View the full answer Answered By Nazrin Ziad I am a post graduate in Zoology with specialization in Entomology.I also have a Bachelor degree in Education.I posess more than 10 years of teaching as well as tutoring experience.I have done a project on histopathological analysis on alcohol treated liver of Albino Mice. I can deal with every field under Biology from basic to advanced level.I can also guide you for your project works related to biological subjects other than tutoring.You can also seek my help for cracking competitive exams with biology as one of the subjects. 3.30+ 2+ Reviews 10+ Question Solved Related Book For   Financial Analysis With Microsoft Excel ISBN: 9780357442050 9th Edition Authors: Timothy R. Mayes See More Books Question Posted: May 15, 2023 03:29 AM See More Questions Students also viewed these Business questions Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during... Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes... Read the case study "Southwest Airlines," found in Part 2 of your textbook. Review the "Guide to Case Analysis" found on pp. CA1 - CA11 of your textbook. (This guide follows the last case in the... Jogger 1 is travelling east at 6 . 5 m / s and has a mass of 8 2 kg . Jogger 2 is travelling north at 5 . 8 m / s and has a mass of 5 4 . 5 kg . One of the joggers has their head down and doesnt see... Use the data in Problem 8.53 to construct a 99% confidence interval to estimate the population variance for the number of diapers used during a 24-hour period for one-month-olds. How could... Selling bonds. Lunar Vacations needs to raise $6,200,000 for its new project (a golf course on the moon). Astro Investment Bank will sell the bond for a commission of 2.3%. The market yield is... E 7-5 Consolidated income statement (purchase of parents bonds) Comparative income statements for Laode Corporation and its 80 percent-owned subsidiary, Ucok Corporation, for 2017 are summarized as... In which stage of the industry life cycle would you place the following industries? a. Oil well equipment. b. Computer hardware. c. Computer software. d. Genetic engineering. e. Railroads. Chapter 6 - Question 2 What is the delta for the call option with continuous and constant dividend yield? Prepare a spreadsheet model for the Saturday hotel occupancy data in Table 14.3 , and recalculate the forecasts using an a of 0.3. What is the new MAD? Suppose that on July 25, 2014 you purchased shares in Tesla Inc. (Nasdaq: TSLA). It is now five years later and you decide to evaluate your holdings to see if you have done well with this investment.... Using the same workbook, copy both datasets into one worksheet so that they can be used together in a pivot table. Transpose them using Paste Special Transpose. Data from Alphabet Financials.xlsx... Suppose Your Beloved Machines Inc. has a February cycle for options trading. State the months for which regular equity options on YBM (which expire on the third Friday of the month) trade on the... Give an example of a program that will cause a branch penalty in the three-segment pipeline of Sec. 9-5. Example: Three-Segment Instruction Pipeline A typical set of instructions for a RISC processor... Are Google, Microsoft, and Apple acting ethically? Are they being socially responsible? Eager to benefit from the economic growth and the job creation that foreign direct investments generate, many... On May 1, 2011, Lenny's Sandwich Shop loaned \$20,000 to Joe Lopez for one year at 6 percent interest. Required Answer the following questions: a. What is Lenny's interest income for 2011? b. What is... Tipton Corporations balance sheet indicates that the company has \($300,000\) invested in operating as sets. During 2006, Tipton earned operating income of \($45,000\) on \($600,000\) of sales.... Norton Car Wash Co. is considering the purchase of a new facility. It would allow Norton to increase its net income by \($90,000\) per year. Other information about this proposed project follows:... What are the four categories used by the World Bank to classify nations on the basis of national income per capita? Identify two nations for each of the four categories. What does non-recourse financing mean? The Dempere Imports Company's EPS in 2014 was $1.85, and in 2009 it was $1.12. The company's payout ratio is 40%, and the stock is currently valued at $62.35. Flotation costs for new equity will be... TRM Consulting Services currently has the following capital structure: New debt would mature on June 30, 2040, have a coupon rate of 8%, and would be sold for their par value of $1,000. The bonds pay... Suppose that TRM Consulting Services has discussed its need for capital with its investment bankers. The bankers have estimated that TRM can raise new funds in the capital markets under the following... Bought an old van for 4000 from Peters promising to pay laterwhat is the transactions Company has a following trade credit policy 1/10 N45. If you can borrow from a bank at 9,5% annual rate, would it be beneficial to borrow money and pay off invoices earlier? Given the following exchange rates, which of the multiple-choice choices represents a potentially profitable inter-market arbitrage opportunity? 129.87/$1.1226/$0.00864/ 114.96/ B $0.8908/ (C)... What Is Light Wave Theory Of Light And Origins Of Ether In Science 1st Edition - ISBN: 1777522501 - Free Book Study smarter with the SolutionInn App Get In Touch About Us Contact Us Career Jobs FAQ Campus Ambassador Company Info Security Copyrights Privacy Policy Terms & Condition SolutionInn Fee Scholarship Services Sitemap Fun Definitions Become Tutor Study Help Categories Recent Questions Expert Questions Copyright © 2024 SolutionInn All Rights Reserved. Get the SolutionInn - Study Help App