Question
The goal of this project is to explore the topic of risk and return. The project requires you to work in Excel with the provided
The goal of this project is to explore the topic of risk and return. The project requires you to work in Excel with the provided spreadsheet. Be sure to fill in the yellow boxes in the Excel file. In addition, type up a report in Word with an introduction (description of the project), findings (answer assignment questions, plots, etc.), and conclusion (summary). Upon completion, please submit both your Word report and Excel file to blackboard.
See FINC 3304 Mini Project 4 - Risk and Return.rtf
See FINC 3304 Mini Project 4 - Risk and Return.xlsx
SINC 3304 Mini Project 4 - Risk and Return.xlsx
Business Finance Mini Project Risk and Return The goal of this project is to explore the topic of risk and return. The project requires you to work in Excel with the provided spreadsheet. Be sure to fill in the yellow boxes in the Excel file. In addition, type up a report in Word with an introduction (description of the project), findings (answer assignment questions, plots, etc.), and conclusion (summary). Upon completion, please submit both your Word report and Excel file to blackboard. Risk and Return Use the following data to explore the risk-return relation and the concept of beta for Goodman Industries stock, Landry Incorporated stock, and the market index: Year Goodman Industries Stock Price $25.80 $22.13 $24.75 $16.13 $17.06 $11.00 2013 2012 2011 2010 2009 2008 Landry Incorporated Stock Price $73.13 $78.45 $73.13 $85.88 $90.00 $83.63 Market Index 17,495.97 13,178.55 13,019.97 9,651.05 8,403.42 7,058.96 Part 1: Risk and Beta A) Calculate the return each year for Goodman, Landry, and the Market using the equation Return = (Value this year - Value last year) / Value last year. In addition, use the Excel function to find the average of the returns (10 Points) B) Calculate the standard deviation of rates of return for Goodman, Landry, and the Market using the Excel function (10 Points) C) Make a scatter plot of stock returns (y-axis) against market returns (x-axis) for both Goodman and Landry stock in one plot. Add a linear trendline to the scatter plot for each stock and include the equation on the chart. Label the y-axis, x-axis, legend, and chart title (10 Points) D) For each stock, use the Excel function to calculate the correlation between the stock returns and market returns. Furthermore, label the standard deviations and calculate the beta according to the equation Beta = (Standard deviation of stock / Standard deviation of market) (Correlation between stock and market) (10 Points) Part 2: Required Return E) Calculate the expected return on the market according to Expected Return on Market = Risk-Free Rate + Market Risk Premium. Also calculate the required return for Goodman and Landry according to Required Return = Risk-Free Rate + (Beta) (Market Risk Premium) (10 Points) F) If you formed a portfolio that consisted of 50% Goodman stock and 50% Landry stock, what would be its beta? (10 Points) G) Calculate the portfolio beta of the four stock portfolio and the required return on the portfolio (10 Points) FINC 3304 - Mini project 4 - Risk and Return Last Name First Name Part 1: Risk and Beta Year 2013 2012 2011 2010 2009 2008 Goodman Industries Stock Price Landry Incorporated Stock Price $25.80 $22.13 $24.75 $16.13 $17.06 $11.00 Market Index $73.13 $78.45 $73.13 $85.88 $90.00 $83.63 17,495.97 13,178.55 13,019.97 9,651.05 8,403.42 7,058.96 A) Rates of return Year Goodman Landry Market 2013 2012 2011 2010 2009 Hint: Return = (Value This Year - Value Last Y Average (https://support.office.com/en-us/article/averag Hint: Use the excel function "=average(range B) Standard Deviation of Returns Goodman Landry Market (https://support.office.com/en-US/article/STDEV-function-a0179959-f6e Hint: Use the excel function "=stdev(range of values)" C) Graph Stock Returns and Market Returns Hint: Right click on graph and choose "select data" and then "add" f(x) = NaNx Hint: Select data point and right click then select "add trendline" and cho D) Beta Correlation Stock Standard Deviation (in decimal form)Market Standard Deviation (in decimal form)Beta Goodman Landry (https://support.office.com/en-U Hint: Use the Excel function "=c Hint: Beta = (Standard deviation Part 2: Required Return Market risk premium: RPM = 5.00% Risk-free rate: rRF = 6.00% E) Required Return Expected return on the market: rm = Hint: Expected Return on Market = Risk-Free Rate + Market Risk Premium Goodman Required Return = Landry Required Return = Hint: Required Return = Risk-Free Rate + (Beta)(Market Risk Premium) F) Portfolio Beta and Return Beta Portfolio Weight Goodman Landry Portfolio Beta Hint: Portfolio Beta = (W1)(B1) + (W2)(B2) Business Finance Mini Project Risk and Return The goal of this project is to explore the topic of risk and return. The project requires you to work in Excel with the provided spreadsheet. Be sure to fill in the yellow boxes in the Excel file. In addition, type up a report in Word with an introduction (description of the project), findings (answer assignment questions, plots, etc.), and conclusion (summary). Upon completion, please submit both your Word report and Excel file to blackboard. Risk and Return Use the following data to explore the risk-return relation and the concept of beta for Goodman Industries stock, Landry Incorporated stock, and the market index: Year Goodman Industries Stock Price $25.80 $22.13 $24.75 $16.13 $17.06 $11.00 2013 2012 2011 2010 2009 2008 Landry Incorporated Stock Price $73.13 $78.45 $73.13 $85.88 $90.00 $83.63 Market Index 17,495.97 13,178.55 13,019.97 9,651.05 8,403.42 7,058.96 Part 1: Risk and Beta A) Calculate the return each year for Goodman, Landry, and the Market using the equation Return = (Value this year - Value last year) / Value last year. In addition, use the Excel function to find the average of the returns (10 Points) B) Calculate the standard deviation of rates of return for Goodman, Landry, and the Market using the Excel function (10 Points) C) Make a scatter plot of stock returns (y-axis) against market returns (x-axis) for both Goodman and Landry stock in one plot. Add a linear trendline to the scatter plot for each stock and include the equation on the chart. Label the y-axis, x-axis, legend, and chart title (10 Points) D) For each stock, use the Excel function to calculate the correlation between the stock returns and market returns. Furthermore, label the standard deviations and calculate the beta according to the equation Beta = (Standard deviation of stock / Standard deviation of market) (Correlation between stock and market) (10 Points) Part 2: Required Return E) Calculate the expected return on the market according to Expected Return on Market = Risk-Free Rate + Market Risk Premium. Also calculate the required return for Goodman and Landry according to Required Return = Risk-Free Rate + (Beta) (Market Risk Premium) (10 Points) F) If you formed a portfolio that consisted of 50% Goodman stock and 50% Landry stock, what would be its beta? (10 Points) G) Calculate the portfolio beta of the four stock portfolio and the required return on the portfolio (10 Points) Report The average rate of return for Goodman, Landry, and the Market is calculated as 21.1%,-2.3% and 20.6% respectively. A standard deviation is a measure of the dispersion of a set of data from its mean. The more spread apart the data, the higher the deviation. Standard deviation is calculated as the square root of variance. In finance, standard deviation is applied to the annual rate of return of an investment to measure the investment's volatility. Standard deviation is also known as historical volatility and is used by investors as a gauge for the amount of expected volatility. A large dispersion tells us how much the fund's return is deviating from the expected normal returns. Statistical correlation is measured by what is called coefficient of correlation (r). Its numerical value ranges from +1.0 to -1.0. It gives us an indication of the strength of relationship. In general, r > 0 indicates positive relationship, r 0 indicates positive relationship, rStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started