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

Corporate reporting group assignmentquestion 1 - although

Corporate Reporting Group Assignment Question 1 - "Although the 2005 adoption of IFRS was a major regulatory transition affecting several tens of thousands of companies worldwide, its costs and benefits were initially un ...

Calculating financial ratiovital to any ratio analysis are

Calculating Financial Ratio Vital to any ratio analysis are the steps of gathering financial data and selecting and calculating relevant ratios. This assignment provides you with an opportunity to do just that. Task: · D ...

Assignment the weighted average cost of capitalcomplete the

Assignment: The Weighted Average Cost of Capital Complete the following assignment: Coogly Company is attempting to identify its weighted average cost of capital for the coming year and has hired you to answer some quest ...

Assignmentwhen appropriate provide a citation and a copy of

Assignment When appropriate, provide a citation and a copy of your source. 1. a. Describe the basic characteristics of stock insurers. b. Describe the basic features of mutual insurers. c. Identify the major types of mut ...

Company valuationusing the internet or academic databases

Company Valuation Using the Internet or Academic databases, research Facebook's Initial Public Offering (IPO) and subsequent financial performance. Based on your research: Write a six page paper in which you: 1. Evaluate ...

Use graphs to show what happens to the demand for and

Use graphs to show what happens to the demand for and supply of dollars in the foreign exchange market in the event of each of the following: a. Domestic income rises. b. Foreign income rises. c. Domestic inflation rises ...

If the yendollar exchange rate is 125 how much will 25000

If the yen/dollar exchange rate is 125, how much will 25,000 yen cost in dollars? If the dollar appreciates to 150 yen/dollar, how much will the 25,000 yen cost in dollars?

Analyzing the dividend policies of various companiesplease

Analyzing the Dividend Policies of Various Companies Please recall the company that you selected for the Module 1 SLP. Please review the company's dividends over the past three years. Then, answer the following questions ...

Do some internet research to learn what happened to greeces

Do some Internet research to learn what happened to Greece's financial system and economy since the fall of 2010. Has the crisis worsened or eased? Has it affected other European or non-European economies? Have events fo ...

Assignment 3 long-term investment decisionsassume that the

Assignment 3: Long-Term Investment Decisions Assume that the low-calorie frozen, microwavable food company from Assignments 1 and 2 wants to expand and has to make some long-term capital budgeting decisions. The company ...

  • 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

A cola-dispensing machine is set to dispense 9 ounces of

A cola-dispensing machine is set to dispense 9 ounces of cola per cup, with a standard deviation of 1.0 ounce. The manuf

What is marketingbullwhat is marketing think back to your

What is Marketing? • "What is marketing"? Think back to your impressions before you started this class versus how you

Question -your client david smith runs a small it

QUESTION - Your client, David Smith runs a small IT consulting business specialising in computer software and techno

Inspection of a random sample of 22 aircraft showed that 15

Inspection of a random sample of 22 aircraft showed that 15 needed repairs to fix a wiring problem that might compromise

Effective hrmquestionhow can an effective hrm system help

Effective HRM Question How can an effective HRM system help facilitate the achievement of an organization's strate