For this assignment, you must use Walmart (WMT) as your company This project enables you to analyze the risk and return characteristics of one stock that you own or would like to purchase You should input your data on Excel or an alternative electronic spreadsheet Perform the following tasks Obtain stock price data at the end of each of the last 20 quarters and fill in that information in Column B of your electronic spreadsheet Column A should be the date Historical stock price data are available on the Yahoo Finance website Here is the link for Walmart https finance yahoo com quote WMT p WMT tsrc fin srch Select DAILY FREQUENCY You should use December 31, March 31, June 30, and September 30 The last quarter ends on 12 31 2021 If the market was closed on one of these dates, select the previously available closing information For example, if March 31 was a Sunday, select the previous Friday (March 29) Obtain the data on dividend per share for this firm for each of the last 20 quarters and input that information in Column C of your electronic spreadsheet Make sure you assign the dividend to the correct quarter Use compute statements to derive the quarterly return on your stock in Column D of your electronic spreadsheet The return on the stock during any quarter is computed as follows First, compute the stock price at the end of that quarter minus the stock price at the end of the previous quarter, then add the quarterly dividend, and then divide by the stock price at the end of the previous quarter Input the S P 500 stock index level as of the end of each of the 20 quarters in Column E of your electronic spreadsheet Again, you should use December 31, March 31, June 30, and September 30 The last quarter ends on 12 31 2021 Use compute statements to derive the quarterly stock market return in Column F, which is equal to the percentage change in the S P 500 index level from the previous quarter Using the tools in an electronic package, run a regression analysis in which your quarterly stock return (Column D) represents the dependent variable and the stock market return (Column F) represents the independent variable This analysis can be easily run by Excel Based on your regression results, what is the relationship between the market return and your stocks return ( The slope coefficient represents the estimate of your firms beta , which is a measure of its systematic risk ) Based on your regression results, does it appear that there is a significant relationship between the market return and your stocks return (The t statistic or p value for the slope coefficient can be used to determine whether there is a significant relationship ) Based on your regression results, what proportion of the variation in the stocks returns can be explained by movements (returns) in the stock market overall (The R SQUARED statistic measures the proportion of variation in the dependent variable that is explained by the independent variable in a regression model like the one described previously ) Does it appear that the stocks return is driven mainly by stock market movements or by other factors that are not captured in the regression model What is the standard deviation of your stocks quarterly returns over the 20 quarter period (You can easily compute the standard deviation of your column of stock return data by using a compute statement ) What is the standard deviation of the quarterly stock market returns (as measured by quarterly returns on the S P 500 index) over the 20 quarter period Is your stock more volatile than the stock market in general If so, why do you think it is more volatile than the market Assume that the average risk free rate is 5 Determine the Sharpe index for your stock (The Sharpe index is equal to your stocks average quarterly return, minus the average risk free rate, divided by the standard deviation of your stocks returns ) Determine the Treynor index for your stock (The Treynor index is equal to your stocks average quarterly return, minus the average risk free rate, divided by the estimated beta of your stock ) Interpret both indices

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 27, 2024

For this assignment, you must use Walmart (WMT) as your company. This project enables you to analyze the risk and return characteristics of one stock

For this assignment, you must use Walmart (WMT) as your company.

This project enables you to analyze the risk and return characteristics of one stock that you own or would like to purchase. You should input your data on Excel or an alternative electronic spreadsheet. Perform the following tasks.

Obtain stock price data at the end of each of the last 20 quarters and fill in that information in Column B of your electronic spreadsheet. Column A should be the date. Historical stock price data are available on the Yahoo! Finance website. Here is the link for Walmart.

https://finance.yahoo.com/quote/WMT?p=WMT&.tsrc=fin-srch Select DAILY FREQUENCY!

You should use December 31, March 31, June 30, and September 30. The last quarter ends on 12/31/2021. If the market was closed on one of these dates, select the previously available closing information. For example, if March 31 was a Sunday, select the previous Friday (March 29).

Obtain the data on dividend per share for this firm for each of the last 20 quarters and input that information in Column C of your electronic spreadsheet. Make sure you assign the dividend to the correct quarter.
Use compute statements to derive the quarterly return on your stock in Column D of your electronic spreadsheet. The return on the stock during any quarter is computed as follows. First, compute the stock price at the end of that quarter minus the stock price at the end of the previous quarter, then add the quarterly dividend, and then divide by the stock price at the end of the previous quarter.
Input the S&P 500 stock index level as of the end of each of the 20 quarters in Column E of your electronic spreadsheet. Again, you should use December 31, March 31, June 30, and September 30. The last quarter ends on 12/31/2021.
Use compute statements to derive the quarterly stock market return in Column F, which is equal to the percentage change in the S&P 500 index level from the previous quarter.
Using the tools in an electronic package, run a regression analysis in which your quarterly stock return (Column D) represents the dependent variable and the stock market return (Column F) represents the independent variable. This analysis can be easily run by Excel.
Based on your regression results, what is the relationship between the market return and your stocks return? (The slope coefficient represents the estimate of your firms beta, which is a measure of its systematic risk.)
Based on your regression results, does it appear that there is a significant relationship between the market return and your stocks return? (The t-statistic or p-value for the slope coefficient can be used to determine whether there is a significant relationship.)
Based on your regression results, what proportion of the variation in the stocks returns can be explained by movements (returns) in the stock market overall? (The R-SQUARED statistic measures the proportion of variation in the dependent variable that is explained by the independent variable in a regression model like the one described previously.) Does it appear that the stocks return is driven mainly by stock market movements or by other factors that are not captured in the regression model?
What is the standard deviation of your stocks quarterly returns over the 20-quarter period? (You can easily compute the standard deviation of your column of stock return data by using a compute statement.) What is the standard deviation of the quarterly stock market returns (as measured by quarterly returns on the S&P 500 index) over the 20-quarter period? Is your stock more volatile than the stock market in general? If so, why do you think it is more volatile than the market?
Assume that the average risk-free rate is 5%. Determine the Sharpe index for your stock. (The Sharpe index is equal to your stocks average quarterly return, minus the average risk-free rate, divided by the standard deviation of your stocks returns.) Determine the Treynor index for your stock. (The Treynor index is equal to your stocks average quarterly return, minus the average risk-free rate, divided by the estimated beta of your stock.) Interpret both indices.