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

Issuer’s considerations, Issuer's Considerations Cash Flows: Issuers ma...

Issuer's Considerations Cash Flows: Issuers may consider the period for which the funds are required and try to spread the borrowings in a way to minimize the costs. Generally,

Determine the strategy of market development, Market development A stra...

Market development A strategy which seeks to sell existing products in new geographical markets or new market segments. A strategy to find new uses for existing products or ser

Liquidity of a company, Eco Tyre Ltd. (ETL) - incorporated in year 2003 and...

Eco Tyre Ltd. (ETL) - incorporated in year 2003 and entered into automobile tyre manufacturing business by introducing a new tire manufacturing technology. Over the years, ETL has

Explain the risk-return relationship, Explain the risk-return relationship....

Explain the risk-return relationship. The relationship among risk and required rate of return is known as the risk-return relationship.  It is a positive relationship for the r

Analysis of company position, Analysis of Company Position Associated ...

Analysis of Company Position Associated International Supplies Ltd Circulation: Associated International Supplies Ltd (AIS Ltd.) Author: A. Consultant, AXY Consultin

Deefine market price of a bond be influenced, If all other things held cons...

If all other things held constant, how would the market price of a bond be influenced if coupon interest payments were made semiannually in place of annually? Several bonds iss

OPERATING CYCLE, discuss the applicability of operation cycle in avegetab...

discuss the applicability of operation cycle in avegetable growing business

Valuation using treasury spot rates, To understand how treasury spot ...

To understand how treasury spot rates are used to calculate the arbitrage-free value of the treasury security, we will take imaginary treasury spot rates (given i

Nature of the business, Q. Nature of the business? The working capital ...

Q. Nature of the business? The working capital requirement of the firm basic depends upon the nature of the business. public utility undertaking like the water supply and rai

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