Question

Portfolio Analysis Problem (Excel Spreadsheet) Solve the following portfolio analysis problem using excel. Be sure to...

Portfolio Analysis Problem (Excel Spreadsheet)

Solve the following portfolio analysis problem using excel. Be sure to create a general spreadsheet as you will be graded by the accuracy of your spreadsheet for the 8 computations below with a change in some or all of the following: Portfolio Weights, Probabilities, Asset Returns. Be sure to put your name at the top of the spreadsheet and submit as an excel attachment in Canvas. Work out the problem manually before you create your spreadsheet. Check figures have been provided below. Along with the table below, remember to put your portfolio weights (for numbers 5 and 6) at the top of your spreadsheet and cell reference them in your formulas.

State

Probability

% Return A

% Return M

Good

.3

20

16

Normal

.4

18

10

Bad

.3

10

14

Compute:

1) Expected return for A and M    A = 16.2   M = 13

2) Standard deviation for A and M (population) A = 4.1425 M = 2.5690

3) Covariance(A,M)    COV = -.6

4) Correlation(A,M) CORR = -.0564

5) Expected return on a portfolio consisting of 30% A and 70% M.   P = 13.96

6) Standard deviation of a portfolio consisting of 30% A and 70% M. P = 2.1275

7) The Beta of A. (assume that M is the market) BETA = -.0909

8) The portfolio weights for the minimum risk portfolio. W(A) = .2885 W(M) = .7115

Homework Answers

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Create an Excel spreadsheet to organize your answers to the following problem A portfolio has been...
Create an Excel spreadsheet to organize your answers to the following problem A portfolio has been constructed from the following securities: Security Expected Return Amount Invested A 6% $30,000 B 14% $15,000 C 12% $55,000 A. What is the expected rate of return from this portfolio? B. If Security B is sold, what will be the expected rate of return on the remaining two-stock portfolio? C. Without respect to item B above, if the investor buys $50,000 of Security D,...
chapter 12 Create an Excel spreadsheet to organize your answers to the following problem, and submit...
chapter 12 Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A company with annual sales of $22,000,000 is considering changing its payment terms from net 40 to net 30 to encourage customers to pay more promptly. The company forecasts that customers would respond by paying on day 32 rather than day 44 as at present (assume a 360 day...
Excel Online Structured Activity: CAPM, portfolio risk, and return Consider the following information for three stocks,...
Excel Online Structured Activity: CAPM, portfolio risk, and return Consider the following information for three stocks, Stocks A, B, and C. The returns on the three stocks are positively correlated, but they are not perfectly correlated. (That is, each of the correlation coefficients is between 0 and 1.) Stock Expected Return Standard Deviation Beta A 8.70 % 16 % 0.8 B 10.30 16 1.2 C 11.50 16 1.5 Fund P has one-third of its funds invested in each of the...
Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas…..only use...
Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas…..only use cell references to the input data. I will change the input data in your problem to check alternate solutions. You will turn in a complete working Excel spreadsheet with your solution. 1. A firm is considering the two mutually exclusive investments projects. Project Alpha requires an initial outlay of $600 and will return $160 per year for the next seven years; Project Beta requires...
Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas…..only use...
Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas…..only use cell references to the input data. I will change the input data in your problem to check alternate solutions. You will turn in a complete working Excel spreadsheet with your solution. 1. A firm is considering the two mutually exclusive investments projects. Project Alpha requires an initial outlay of $600 and will return $160 per year for the next seven years; Project Beta requires...
You are required to use a financial calculator or spreadsheet (Excel) to solve 10 problems (provided...
You are required to use a financial calculator or spreadsheet (Excel) to solve 10 problems (provided on page 5) on the applications of the time value of money. You are required to show the following four steps for each problem (sample problems and solutions are provided for guidance): (i) Develop a timeline (linear representation of the timing of cash flows). (ii) Identify the time value of money variable (PV, FV, PMT, N, or I/YR) that needs to be solved/calculated in...
PRBSET10- 1ABCD 1. You have the following information on monthly international stock returns and risk, all...
PRBSET10- 1ABCD 1. You have the following information on monthly international stock returns and risk, all measured in U.S. dollars. Ignore the risk-free rate for this problem. Enter the returns, standard deviations and correlations coefficient values below into the Markowitz Portfolio Optimizer Excel spreadsheet: Monthly Stock Return (%) Standard Deviation (%) U.K. 1.26% 3.50% Canada 1.54% 3.90% Mexico 2.25% 6.05% U.S. 1.15% 3.16% Correlation Coefficients U.K. Can. Mex. U.S. U.K. 1.000 Canada 0.755 1.000 Mexico 0.725 0.695 1.000 U.S. 0.682...
Please create your own Excel file to conduct scenario analysis illustrated below: Suppose you have been...
Please create your own Excel file to conduct scenario analysis illustrated below: Suppose you have been hired as a financial consultant to Cape Cod Fishing, Inc. (CCF), a large firm that is the market share leader in manufacturing fishing products. The company is looking at setting up a manufacturing plant to produce a new line of fishing bait, which specially formulated for attracting bluefin tuna. To illustrate, suppose the company can sell 87,300 cans of bait per year at a...
THIS IS THE GENERAL EQUILIBRIUM PROBLEM THAT I PROMISED. YOU FIRST SOLVE FOR THE INITIAL EQUILIBRIUM...
THIS IS THE GENERAL EQUILIBRIUM PROBLEM THAT I PROMISED. YOU FIRST SOLVE FOR THE INITIAL EQUILIBRIUM AS POINT A. WE CONSIDER TWO DIFFERENT AND SEPARATE SHOCKS (I CALL THEM SCENARIOS). THE FIRST SHOCK IS TO THE IS CURVE, THE SECOND SHOCK IS A ‘LM’ SHOCK. AGAIN, WE CONSIDER THESE SHOCKS SEPARATELY SO THAT AFTER YOU COMPLETE SCENARIO 1 (THE IS SHOCK), WE GO BACK TO THE ORIGINAL CONDITIONS AND CONSIDER THE SECOND SCENARIO WHICH IS THE ‘LM’ SHOCK. Consider the...
Assessment Module 9 – Marginal Analysis Please create a tab in your Excel Workbook and name...
Assessment Module 9 – Marginal Analysis Please create a tab in your Excel Workbook and name it “Module 9 Marginal Analysis.” You have same basic templates in your Resources workbook, but you may need to modify these to fit the individual business problem. I am evaluating your professional ability to do so, and to create professional models and reports, so take your time, think through a logical, clear, well-structured, well-formatted model. Remember, assumption tables (like T-accounts) are your friends! Be...
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT