Answer the following questions. Please upload excel spreadsheet of your calculations. You are interested in creating a portfolio of two stocksCoca Cola and Texas Utilities. Over the last decade, an investment in Coca Cola stock would have earned an average annual return of 25%, with a standard deviation in returns of 36%. An investment in Texas Utilities stock would have earned an average annual return of 12%, with a standard deviation of 22%. The correlation in returns across the two stocks is 0.28. [06] a. Assuming that the average and standard deviation, estimated using past returns, will continue to hold in the future, estimate the average returns and standard deviation of a portfolio composed 60% of Coca Cola and 40% of Texas Utilities stock. [03] b. Now assume that Coca Cola's international diversification will reduce the correlation to 0.20, while increasing Coca Cola's standard deviation in returns to 45%. Assuming all of the other numbers remain unchanged, answer (a) and (b). [03] . Assume you have all your wealth (a million dollars) invested in the Vanguard 500 index fund, and that you expect to earn an annual return of 12%, with a standard deviation in returns of 25%. Since you have become more risk averse, you decide to shift $ 200,000 from the Vanguard 500 index fund to treasury bills. The T-bill rate is 5%. Estimate the expected return and standard deviation of your new portfolio. [05] You have run a regression of returns of Devonex, a machine tool manufacturer against the S&P 500 Index using monthly returns over the last 5 years and arrived at the following regression: [02] ReturnDevonex = -0.20% +1.50 ReturnS&P 500 If the stock had a Jensen's alpha of +0.10% (on a monthly basis) over this period, estimate the monthly riskfree rate during the last 5 years. You have run a regression of weekly returns over the last 5 years for Steel Products Inc., a small manufacturing firm, against the S&P 500 and arrived at the following result: [04] Returns = 0.06% + 1.80 Returnss&P 500 R? = 28% Steel Products 1. Estimate the annualized Jensen's alpha on this stock over the last 5 years, if the average annualized riskfree rate was 5.2% over that period. (You can use the simpler non- compound version of the annualized rates) [02] 2. If the range on the beta estimate, with 67% confidence, is 1.46 2.14, estimate the standard error on the estimate. [02] Answer the following questions. Please upload excel spreadsheet of your calculations. You are interested in creating a portfolio of two stocksCoca Cola and Texas Utilities. Over the last decade, an investment in Coca Cola stock would have earned an average annual return of 25%, with a standard deviation in returns of 36%. An investment in Texas Utilities stock would have earned an average annual return of 12%, with a standard deviation of 22%. The correlation in returns across the two stocks is 0.28. [06] a. Assuming that the average and standard deviation, estimated using past returns, will continue to hold in the future, estimate the average returns and standard deviation of a portfolio composed 60% of Coca Cola and 40% of Texas Utilities stock. [03] b. Now assume that Coca Cola's international diversification will reduce the correlation to 0.20, while increasing Coca Cola's standard deviation in returns to 45%. Assuming all of the other numbers remain unchanged, answer (a) and (b). [03] . Assume you have all your wealth (a million dollars) invested in the Vanguard 500 index fund, and that you expect to earn an annual return of 12%, with a standard deviation in returns of 25%. Since you have become more risk averse, you decide to shift $ 200,000 from the Vanguard 500 index fund to treasury bills. The T-bill rate is 5%. Estimate the expected return and standard deviation of your new portfolio. [05] You have run a regression of returns of Devonex, a machine tool manufacturer against the S&P 500 Index using monthly returns over the last 5 years and arrived at the following regression: [02] ReturnDevonex = -0.20% +1.50 ReturnS&P 500 If the stock had a Jensen's alpha of +0.10% (on a monthly basis) over this period, estimate the monthly riskfree rate during the last 5 years. You have run a regression of weekly returns over the last 5 years for Steel Products Inc., a small manufacturing firm, against the S&P 500 and arrived at the following result: [04] Returns = 0.06% + 1.80 Returnss&P 500 R? = 28% Steel Products 1. Estimate the annualized Jensen's alpha on this stock over the last 5 years, if the average annualized riskfree rate was 5.2% over that period. (You can use the simpler non- compound version of the annualized rates) [02] 2. If the range on the beta estimate, with 67% confidence, is 1.46 2.14, estimate the standard error on the estimate. [02]