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: 77% (9 reviews) 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... The factory payroll for the week is $500,000, consisting of $300,000 earned by 250 direct laborers and $200,000 earned by 150 indirect laborers. The total of factory bonuses to be received at year... What do you understand by the setting of sales objectives in the context of the marketing planning process? Give three examples of objectives for an organisation of your choice. On July 1, 2014, Flanagin Corporation issued $2,000,000, 10%, 10-year bonds at $2,271,813. This price resulted in an effective-interest rate of 8% on the bonds. Flanagin uses the effective-interest... How do cultural differences across global branches of a multinational organization affect organizational commitment, and what can be done to ensure consistent commitment levels across diverse teams ? 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... What total costs will Ronald pay if he enrolls in the HMO plan? Define memo and write different types of memo in detail. Doolarie assigned her whole life policy to her favorite charity. At that time the CSV of the policy was $60,000 and her ACB was $40,000. What is the result of Doolarie's donation to her charity? OBJECTIVE: The objective of this assignment is to: Analyze business letters for effective use of information. Use the correct words in communicating ideas and information. Thus, you will develop your... explain the difference between an external memo of law and an internal memo of law.? Using only publicly available data from the U.S. Census website, develop brief (1 page) consumer profiles of the following zip codes: 60624 and 60462 Use these profiles to identify five products that... Like a lot of other Americans, John Wayne died of cancer. But is there more to this story? In 1955, Wayne was in Utah shooting the film The Conqueror. Across the state line, in Nevada, the United... If M = 7, s = 2, and X = 9.5, what is z? 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... What types of plans would you like to be most involved in - strategic, tactical or operational? Explain What are the best approaches to get employees involved in the planning process? Explain Why is... Juan, who is single, is a self-employed carpenter as well as an employee of Frame It, Incorporated. His self-employment net income is $43,000, and he received a W-2 from Frame it for wages of $32 250... Look at this article about our airport expansion https://flyfrompti.com/whats-going-on-at-pti-current-construction-projects/ What is the purpose of the PTI airport? How can this expansion support... Responding To China S Rise Us And Eu Strategies 1st Edition - ISBN: 3319180436 - 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