Financial analysis on excel

Learning Goal: I’m working on a finance question and need an explanation and answer to help me learn.

-Prepare a short (maximum 2 pages single-sided) executive summary that summarizes your analysis and addresses the questions that are given. You must also prepare an Excel file with the solution.

-I need to submit both the Excel file and the executive summary

Question:

Chevron and Coca Cola Go to finance.yahoo.com and download the ending monthly stock prices for Chevron (CVX), and Coca Cola (KO) for the specified time period (TIME PERIOD: February 28, 2017 – February 28, 2022). Use the adjusted closing price, which adjust for dividend payments and stock splits. *

*Data*: Go to finance.yahoo.com and search for the companies using their ticker symbols (CVX and KO) and then click on the “Historical Data” on the menu. Set Time Period as February 28, 2017 – February 28, 2022, choose “Monthly” for frequency and use “Adj Close” prices. \

Questions 1. Calculate monthly returns for Chevron and Coca Cola for this period.

2. Calculate the average return, variance, and standard deviation of returns for each firm. (Use Excel’s AVERAGE, VAR and STDEV functions.)

3. Which stock is riskier if held on its own? Why?

4. Assume that you held an equally weighted portfolio of Chevron and Coca Cola (i.e., a portfolio with equal dollar investments in each stock). Compute the portfolio’s rate of return for each month, and calculate the variance and standard deviation of the portfolio’s monthly rate of return.

5. Is the portfolio standard deviation more or less than the average of the standard deviations of the

two component stock?

6. What are the betas provided by finance.yahoo.com for each firm? Which stock has more

systematic risk?