60.1K

Verified Solution

Link Copied!

You will find historical data for Ford, Walmart, Bank of America, Market Index and T-bills in the Excel Spreadsheet that provided to you You need to calculate followings: PART A: Calculate the Excess returns for each company and Market Index. PART B: Prepare the followings using the Excel Spreadsheet 1) Prepare a xy scatter plot with no line inserted. Be sure that firms excess returns represent the y-variable and the markets excess returns represent the x-variables. 2) Select one of the data points by pointing to it and clicking the left mouse button. After the point is selected, right click to pull up a shortcut menu. Select Add Trend line, choose the linear type, then click on the Options tab and select Display Equation on Chart. When you click on OK, the trend line and the equation appear. The trend line represents the regression equation. 3) Based on the regression equation, what is the alpha and Beta of each company? 4) Based on the alpha, which company you would like to invest? Explain why? PART C: You will find historical Excess Return data for portfolio P, portfolio Q, and Market Index in the Excel Spreadsheet that provided to you You need to calculate followings: 1. Average excess return of portfolio P, Q and Market Index 2. Standard deviation of excess return of portfolio P, Q, and Market Index 3. By running the regression, what is the alpha, Beta, standard error of each portfolio and Market Index? 4. Sharpe Ratio, Treynor Ratio, Information Ratio, and R-square. 5. Bases on Sharpe Ratio, Treynor Ratio, and Information ratio, which portfolio is better? Explain Why?

Answer & Explanation Solved by verified expert

See Answer