Use Microsoft Excel to conduct your analysis, calculate values and plot figures. Write-up a report of the answers, the write-up report should summarize the answers, show final results and provide all the figures. -The Excel spreadsheet should contain all the formulas (not just numbers) in the calculation steps.

Question:

Download price data for S&P500 ETF (SPY), Costco (COST), and General Motors (GM) from Yahoo Finance.

(a) Plot daily prices of General Motors (GM) along with its 50-day moving averages from March 23, 2021 to December 31, 2021. What are the highest and lowest prices of GM in 2021?

(b) Compare two investment plans with the same initial capital, one holds Costco only, and the other purchases S&P500 index ETF only. Which one has higher variation in 2021? Provide risk measures.

(c) Compared with Costco and General Motors, which stock is cyclical and which is defensive? Estimate the beta of both stocks using regression analysis to support your conclusion. Use monthly data in the past 5 years to run regression. Discuss your estimated beta values and those provided by Bloomberg terminal.

(d) Suppose you have $100,000 and you borrow $40,000 to invest Costco stock at $500 per share.

Assuming the loan rate is 0%. Calculate and plot the return on investment for a margin trade and a trade with no margin for ending stock prices that range from $450 to $550 in increments of $10.