Ask Question, Ask an Expert


Ask Corporate Finance Expert

Data Case:

Today is April 30, 2012, and you have just started your new job with a financial planning firm. In addition to studying for all your license exams, you have been asked to review a portion of a client’s stock portfolio to find out the risk/return profiles of 12 stocks in the portfolio. Unfortunately, your small firm could not afford the expensive databases that will provide all this information with a few simple keystrokes, but that’s why they hired you. Specially, you have been asked to find the monthly average returns and standard deviations for the 12 stocks for the past five years. The stocks (with their symbols in parentheses) are:

Archer Daniels Midland (ADM)            International Business Machines Corporation (IBM)
Boeing (BA)                                   JP Morgan Chase & Co. (JPM)
Caterpillar (CAT)                             Microsoft (MSFT)
Deere & Co. (DE)                            Procter and Gamble (PG)
Google Inc. (GOOG)                        Hershey (HSY)
Wal-Mart (WMT)

1. Collect price information for each stock from Yahoo! Finance (

2. Convert these prices to monthly returns as the percentage change in the monthly prices (Hint: Create a separate worksheet within the Excel file.) Note that to compute a return for each month, you need a beginning and an ending price, so you will not be able to compute the return for the first month.

3. find out the mean monthly returns and standard deviations for the monthly returns of each of the stocks. Convert the monthly statistics to annual statistics for easier interpretation (multiply the mean monthly return by 12, and multiply the monthly standard deviation by √12).

4. Add the column in your Excel worksheet with average return across stocks for each monthly. This is the monthly return to an equally weighted portfolio of these 12 stocks. find out the mean and standard deviation of monthly returns for the equally weighted portfolio. Double check that the average return on this equally weighted portfolio is equal to the average return of all of the individual stocks. Convert these monthly statistics to annual statistics (as described in Step 3) for interpretation.

5. Using annual statistics, create an Excel plot with standard deviation (volatility) on the x-axis and average return on the y-axis as follows:

a. Create three columns on your spreadsheet with the statistics you created in problem 3 and 4 for each of the individual stocks and the equally weighted portfolio. First column would have the ticket, the second would have annual standard deviation, and the third will have annual mean return.

b. Highlight the data in last two columns (standard deviation and mean), select Insert>Chart>XY Scatter Plot. Complete chart wizard to finish the plot.

6. What do you notice about average of the volatilities of the individual stocks, compared to the volatility of the equally weighted portfolio?

Corporate Finance, Finance

  • Category:- Corporate Finance
  • Reference No.:- M91704

Have any Question? 

Related Questions in Corporate Finance

Assignmentbradford services inc bsi is considering a

Assignment Bradford Services Inc. (BSI) is considering a project that has a cost of $10 million and an expected life of 3 years. There is a 30 percent probability of good conditions, in which case the project will provid ...

Write a congratulations noteimagine that your former boss

Write a congratulations note. Imagine that your former boss just won a "Manager of the Year" award. Handwrite a note congratulating the manager, and make it meaningful by referring to your own experience as his or her em ...

Provide feedbackimagine that youre working as part of a

Provide feedback Imagine that you're working as part of a team to create a five-year marketing plan. Everyone had agreed to have his or her part drafted by the time your team met today. What would be an appropriate respo ...

1 how would stephanies credit management decisions be

1. How would Stephanie's credit management decisions be different if she were a single mother of two children? 2. How would Stephanie's credit management decisions be affected if she were 35 years old? If she were 50 yea ...

Explain why an options time value is greatest when the

Explain why an option's time value is greatest when the stock price is near the exercise price and why it nearly disappears when the option is deep-in- or out-of-the-money? Critique the following statement, made by an op ...

1 why is a vibrant ipo market considered vital to the

1. Why is a vibrant IPO market considered vital to the success of a nation's venture capital industry? What impact did the collapse of Germany's Neuer Markt have on the European venture capital industry? 2. Describe the ...

Directions answer completely and fully with every step

Directions: Answer completely and fully with every step lined out, I need this done in excel and in word. 1.      Snake Farm Inc. (SFI) has been offered to submit a competitive bid for building 31 and 22, 18, and 11offsh ...

1 identify the three categories of depreciation associated

1. Identify the three categories of depreciation associated with real property. 2. How would you capitalize an income stream so as to show a property value? What is the advantage of a discounted or present-worth analysis ...

Taskthis week is the final part to your course project

Task This week is the final part to your course project. Using the information that you have gathered throughout the quarter: • Prepare a report that will be delivered to the CEO and CIO. o Your report should include you ...

Assignmentthe assignment requires you to prepare a full

Assignment The assignment requires you to prepare a full company overview that includes detailed information on your company's history, executive management team, future projections and other pertinent information. Futur ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

WalMart Identification of theory and critical discussion

Drawing on the prescribed text and/or relevant academic literature, produce a paper which discusses the nature of group

Section onea in an atwood machine suppose two objects of

SECTION ONE (a) In an Atwood Machine, suppose two objects of unequal mass are hung vertically over a frictionless

Part 1you work in hr for a company that operates a factory

Part 1: You work in HR for a company that operates a factory manufacturing fiberglass. There are several hundred empl

Details on advanced accounting paperthis paper is intended

DETAILS ON ADVANCED ACCOUNTING PAPER This paper is intended for students to apply the theoretical knowledge around ac

Create a provider database and related reports and queries

Create a provider database and related reports and queries to capture contact information for potential PC component pro