Solved by: AllAcademicHelp.com
Assignment #4_Risk and Return
FIN 320: MANAGERIAL FINANCE
In this assignment, students will continue to use the data of the
three companies including S&P 500 in Assignment #3 to determine
the correlation and beta of individual stocks. To do so, students
are required to use Analysis Tool Bar.
Instruction on how to use Analysis Tool Bar.
the computer you are using do not have “Data Analysis”. You need
to Add-Ins by following the instructions below.
Open the excel spreadsheet
Click on the icon on the top left corner
Click on the “word options”
Click on the “Add-in”
Click on “Analysis Tool-Pack”
Using the regression function in Excel, determine the beta of each
stock by using return series on a particular stock as the dependent
variable and the S&P 500 return series as the explanatory
variable. Repeat the same procedure for each stock to obtain beta
of each stock. You also need to attach your regression output when
submitting your work.
Which of the stocks would you classify as
defensive? Which would be classified as aggressive? Why? In a boom,
which stock you would recommend to invest? In a recession, which
stock you would recommend to invest?
Assume that you are currently holding a
well-diversified portfolio, which of the three stocks would be a
good candidate to include in a well-diversified
portfolio? (you are well-diversified investor). Hint: a
well-diversified investor does not concern about unique risk but the
market risk which is measured by beta?
Using Excel Correlation function (CORREL),
find the correlation between each pair of three stocks and S&P
500. Which pair of stock has the highest/lowest correlation?
Now, you can find estimates of stock beta by
logging on to finance.yaho.com and
looking at a company’s profile of three stock chosen. Use the
capital asset price model (CAPM) to estimate expected return of each
stock. You will need a figure for the current Treasury bill and you
can find this also on finance.yaho.com by clocking on Bonds-Rates.
You can use 30-year or 10-year bond as a risk-free rate since stocks
are long-term investment. Assume that the market risk premium of
Compare the expected return of three stocks
based on CAPM with the average returns you obtained. Which stock(s)
is (are) overvalued or undervalued? Why? Fill in the following
table based on the numbers you calculated.
Use the following Tables 1and 2 to present
Following shows the U.S. Treasury Bond Rates on August 18, 2015
obtained from http://finance.yahoo.com/market-overview/
U.S. Treasury Bonds Rates
Instructions on How to Determine the Beta of Each Stock
On the Excel Spreadsheet, go to Tools, Add-Ins, choose Analysis
Go to Tools, Data Analysis, choose Regression.
The Input X-Range will be the return on market portfolio—Highlight
The Input Y-Range will be the return on individual stock—Highlight
on How to Use Excel Spread Sheet to Determine the Correlation Among
1. Place the return series of Stock A, B, C and S&P 500
next to each other
2. On the Excel Spreadsheet, go to Tools, Add-Ins, choose Analysis
3. Go to Tools, Data Analysis, choose Correlation—Highlight the
three columns of the returns.
Prepare a write-up of your report of at least 3 pages or more, 12
points font and double space. The write-up should be a report
format, summarizing all your findings and support your findings with
numbers (using the questions asked as a guideline to form the report
format). The report should have three main parts, introduction,
body, and conclusion. The introduction should provide a brief
introduction what you will be discussing the project including the
purpose of the assignment. In the body, students should summarize
their findings in words and support the findings with the numbers.
Insert the table that summarizes your finding where appropriate.
Insert graphical representation if necessary where the text is being
discussed. Use the tables to present your finding particularly when
it involves comparison and numbers. Even when the data is presented
in that table, the explanations of those numbers are needed. In the
conclusion, you need provide the main finding of your report,
reemphasize your key findings. There is no need to explain the
calculation in the report. The report should not be laid out
as an answer to each question. Attached all raw data, calculation,
and regression output at the end of project. The assignment report
has to be well-written and well-organized and free from grammatical
mistakes. Please format all the number to 4 decimals for easy
comparison. Please place the $ or % where it is appropriate.
Insert the raw data, regression output, and long table at the end of
the report. Create graphical representation as needed. The report
should contain the main Tables 1 and 2.
Let’s block ads! (Why?)
READY TO PLACE AN ORDER