##### Reference no: EM132234485

**Question 1.** All fund managers fall into two groups (stars and non-star managers). The probability that a star will beat the market in any given year is 80%. Non-star managers are just as likely to beat the market as they are to underperform it. Of a given pool of managers, only 10% turn out to be stars.

a. What is the probability that a manager beats the market in a given year?

b. What is the probability that the manager is a star if (s)he beats the market?

c. What is the probability that the manager is a star if (s)he beats the market 5 years in a row?

Note: their probability of beating the market in one year is independent of the prior year.

**Question 2.** You have been provided with data for Sales and Advertising. Assume there are no outliers in the data.

a. Estimate this model using regression analysis...

Sales = β_{0} + β_{1}*advertising + β_{2}*advertising^{2}

• State the hypothesis that tests if a non-linear relationship exists between Sales and Advertising.

• Based on your regression analysis what is your conclusion? Give reason.

b. What is the marginal impact of advertising on sales if advertising is increased from $1500 to $1600? What is the marginal impact on sales if advertising is increased from $2400 to $2500? Please comment on any differences.

c. Is there a lead/lag relationship between Advertising and Sales? That is, estimate this model ...

Sales_{t} = β_{0} + β_{1}*advertising_{t} + β_{2}*advertisingt^{2} + β_{3}*advertising_{t-1}

What is your conclusion? Give reason.

**Question 3.** As an investor you are studying the stock returns for Yahoo (YHOO), Google (GOOG) and Adobe Systems (ADBE). You are convinced that daily returns on all these assets are normally distributed.

You have collected one year of historical stock returns for all three companies (see data provided.) You want to build an investment portfolio with your money divided between just these three stocks.

Portfolio P = a*(YHOO) + b*(GOOG) + c*(ADBE)

a) Using historical returns, find the fraction of your investment in each stock (i.e., a, b, c) to maximize your return. You are risk averse, so you do not want the standard deviation of your portfolio to exceed 0.015.

Hints (from your kind and caring Professor): (1) You cannot invest more money than you have. (2) Also, it will be better to use Method 2 (do you know why?).

b) How should your investment in each asset (i.e., a, b, c) change if you do not want the standard deviation of your portfolio to exceed 0.013?

c) Based on above results can you tell which stock is more volatile? Explain.

**Question 4.** Consider two SPDR ETFs, SPY and GLD. It is known that both assets are normally distributed with an average return for SPY of 10% and an average return for GLD of 7%. It is known that the standard deviation of SPY is 8% and the standard deviation of GLD is 12%. As you would expect, the asset classes represented by SPY and GLD are independent. Create a Monte Carlo simulation that estimates P(SPY < GLD). Write the answer below.

Note: Let the simulation size be 2000 each for both SPY and GLD; for SPY use a random seed of 16 and for GLD use a random seed of 32.

**Question 5.** Visual Basic For Applications (VBA)

a) Create a function using VBA in Excel called Square that calculates the square of any number. For example, when I open your spreadsheet and type this in any cell: =square(5)

It should give me the result of 25.

b) Write a subroutine using VBA in Excel called SquareIt that calculates the square of numbers from 1 to 20 and writes them in cells A1, A2, ... , A20. (Use a For loop in your VBA code to do this).

Assign this subroutine to a button labeled SquareIt on your spreadsheet so I can simply click it to run your subroutine.