Questions:

1. (30 pts) Use S&P 500 Company Data.xlsx to answer the following questions (in data file)

a) Create 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 three-year total return (average) by GICS Sector.

b) Create a pivot column chart that show the data from part a. Which GICS sector has the lowest

total return for the last three years?

c) 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.

d) Create a new pivot table from the original data that shows the top 20 GICS Sub-Industries by

average of market capitalization (market value). Also include a count of the number of

companies in each sub-industry. (Moving Symbol to Values will create a count of Symbol,

which gives you the # of count.)

e) Use Get & Transform to remove errors from the net profit margin column, and then create a

pivot table that shows the average net profit margin by GICS Industry. Which is the most

profitable industry? Which is the least profitable industry?

f) Create a pivot table that shows beta and the Total Return 3 Yr for each company. Now, copy

and paste the data to another location in the same sheet. Create a regular XY scatter chart of the

data. Is there a relationship between beta and historical three-year returns?