**Solved by: AllAcademicHelp.com**

Assignment #4_Risk and Return

CookMyProject

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.

If

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

7%.

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

your finding.

Table 1:

Stock A

Stock B

Stock C

S&P

500

Average

Variance

Standard Deviation

Beta

Table

2:

Stock

CAPM

Return

>,

=, <

Average

Returns

Over/Under/Correctly

Priced

Recommendations

(Buy/Sell)

A

B

C

Following shows the U.S. Treasury Bond Rates on August 18, 2015

obtained from http://finance.yahoo.com/market-overview/

Bonds

U.S. Treasury Bonds Rates

Maturity

Yield

Yesterday

Last Week

Last Month

3 Month

0.05

0.06

0.05

0.01

6 Month

0.22

0.22

0.21

0.10

2 Year

0.71

0.70

0.67

0.67

3 Year

1.06

1.06

1.00

1.05

5 Year

1.57

1.56

1.52

1.67

10 Year

2.17

2.17

2.14

2.35

30 Year

2.84

2.82

2.81

3.08

Instructions on How to Determine the Beta of Each Stock

On the Excel Spreadsheet, go to Tools, Add-Ins, choose Analysis

Toolpak.

Go to Tools, Data Analysis, choose Regression.

The Input X-Range will be the return on market portfolio—Highlight

this column

The Input Y-Range will be the return on individual stock—Highlight

this column

Instructions

on How to Use Excel Spread Sheet to Determine the Correlation Among

Stocks

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

Toolpak.

3. Go to Tools, Data Analysis, choose Correlation—Highlight the

three columns of the returns.

Report

Instructions:

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**

CLICK HERE TO ORDER 100% ORIGINAL PAPERS FROM AllAcademicHelp.com <<