Using the excel file “NKY as of Feb 12 20201” and “Nikkei Summary, 2-12-2020”, calculate the... 1 answer below »

a) Using the excel file “NKY as of Feb 12 20201” and “Nikkei Summary, 2-12-2020”, calculate the current value of the index. Please put your final answer on the word document and provide your excel calculation (4 points):

2) Using the data from the excel “SHCOMP Stratified Data”, let’s try to replicate the index through stratified sampling (18 pts).

i) First organize the names by GICs Sector. Which sector has the largest weighting and what is that weighting? (2 pts)

ii) What is the most underrepresented sector in the SHCOMP weighting and what is that weighting (2 pts)?

iii) In the consumer discretionary sector, what are the two largest companies by Market Cap, and what are their weightings? (4 pts)

iv) In an excel, recreate the SHCOMP index by first sector and then market cap, taking the two largest names by Market Cap in each sector (ignore any companies that have an N/A for their Market Cap). Allocate to each sector the weighting that each sector had originally in the SHCOMP and then allocate between the two names within the sector based on their proportional weighting to each other. In your excel, include the original titles and the new weights of your new Index. Put a screenshot in your word document please. (10 pts)

Attachments:

FIN-443-Sprin….docx