Calculating investment statistics and graphing output, Financial Management

Assignment Help:

Step 1) Opportunity Set Graph:Combine 2 of your stocks (Ignore the other 2 stocksfor this step only).  Construct an investment opportunity set (the curved set) between the two risky assets.  Graph the resulting curve and on a Mean-StDev graph. Use the X Y (Scatter) option and allow the lines to cross. On the same graph also graph the capital allocation line for one of your portfolios and the risk-free asset.  The risky asset for thecapital allocation line should be a portfolio consisting of 40% of Stock 1 and 60% of stock 2.

Step 2) Regressions: Use the "data analysis" and "regression" features in Excel to complete a regression equation for each of your four stocks.  Have Excel display the regression statistics on the "Regression Output" page.  Enter the alpha, beta, and R-squared from this in your analysis table.  Also, use the "slope" function in Excel to calculate the slope of your line.  For both the regression analysis and the slope function, specify the data range for the excess return for your stocks from the "Regression Data" page as the y-variable and the data range for the excess return for the S&P index on the "Regression Data" page as your x-variable. 

Step 3) Scatter-Plots: Create a scatter-plot of each of your four stocks where the excess returns for the stock are on the y-axis and the excess returns for the S&P 500 are on the x-axis.  Have Excel display the plot on the "Scatter Plot" page. Add a trend-line line by selecting (highlighting) the data series, then right click and select "add trend-line." Also display the regression equation and the R2 value on the chart by right-clicking on the trend-line and then selecting "format trend-line."  Enter the alpha and beta from this in your analysis table.

Step 4) Complete Table: On the "Analysis" page you will need tocomplete the table of information.  Be sure to use the appropriate cell references where applicable.


Related Discussions:- Calculating investment statistics and graphing output

Dividend decision, Dividend Decision: The Dividend Decision is a decis...

Dividend Decision: The Dividend Decision is a decision taken by the directors of a company. It relates to the timing of any cash payments and amount made to the company's stoc

Calculate the future value of annuity, 1.  An investor is thinking of inves...

1.  An investor is thinking of investing in a recurring deposit scheme that offers an interest rate of 12% per annum. The investment that he is planning is for the higher education

The ex-dividend price of novis stock, The net income of Novis Corporation i...

The net income of Novis Corporation is $45,000.  The company has 20,000 outstanding shares and a 100 percent payout policy.  The expected value of the firm one year from now is $1,

Pre-requisites for effective budgetary control system, ORGANISATION FOR BUD...

ORGANISATION FOR BUDGETARY CONTROL (or) PRE-REQUISITES FOR THE INTRODUCTION OF AN EFFECTIVE BUDGETARY CONTROL SYSTEM 1.   BUDGET CENTRE:  It is a section of the organization

Global equity indexes, Global Equity Indexes: As described earlier in t...

Global Equity Indexes: As described earlier in this chapter, there are several stock market indexes available which depict the performance of particular sectors and a country a

Introduction to mortgage-backed securities, A mortgage may be defined as a ...

A mortgage may be defined as a pledge of property to secure payment of a debt. Depending upon the terms of mortgage agreed upon between the lender and the borrower, mor

Explain the factors which company should apply, Explain the factors which c...

Explain the factors which company should apply Companies to be the very best must Establish what competition is doing Set the very best standards to exceed Es

Define the term- cash purchases, Define the term- Cash purchases     Shar...

Define the term- Cash purchases     Shareholders of the target company are bought out completely and have no further stake in business. This is good if predator shareholders want

Describe the dividend yield method, Q. Describe the Dividend Yield Method? ...

Q. Describe the Dividend Yield Method? Dividend Yield Method: - This process is based on the assumption that when an investor invests in the equity shares of a company he expec

Illustrate coefficient of correlation, Q. Illustrate Coefficient of Correla...

Q. Illustrate Coefficient of Correlation? The square of the correlation co-efficient is the co-efficient of determination. It gives the percentage of variation in the stock's r

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd