Question

Assume that you recently graduated and you just landed a job as a financial planner with the Cleveland Clinic. Your first assignment is to invest $100,000. Because the funds are to be invested at the end of one year, you have been instructed to plan for a one-year holding period. Further, your boss has restricted you to the following investment alternatives, shown with their probabilities and associated outcomes.

State of Economy	Probability	T-Bills	Alta Inds.	Repo Men	American Foam	Market Port.
Recession	0.1	8.00%	-22.0%	28.0%	10.0%	-13.0%
Below Average	0.2	8.00%	-2.0%	14.7%	-10.0%	1.0%
Average	0.4	8.00%	20.0%	0.0%	7.0%	15.0%
Above Average	0.2	8.00%	35.0%	-10.0%	45.0%	29.0%
Boom	0.1	8.00%	50.0%	-20.0%	30.0%	43.0%

Barney Smith Investment Advisors recently issued estimates for the state of the economy and the rate of return on each state of the economy. Alta Industries, Inc. is an electronics firm; Repo Men Inc. collects past due debts; and American Foam manufactures mattresses and various other foam products. Barney Smith also maintains an "index fund" which owns a market-weighted fraction of all publicly traded stocks; you can invest in that fund and thus obtain average stock market results. Given the situation as described, answer the following questions using Excel (Please Show Excel Formulas).   

a. Calculate the expected rate of return on each alternative.

b. Calculate the standard deviation of returns on each alternative.                                               

c. Calculate the coefficient of variation on each alternative.                        

d. Calculate the beta on each alternative.                                                                                            

e. Do the SD, CV, and beta produce the same risk ranking? Why or why not?

Answer 1

(A) To calculate Expected rate of return use SUMPRODUCT() function in excel where Array1 is probability distribution which is fixed and Array2 is return on asset in different scenarios. Following is the snapshot of Excel Calculation :

(B) To calculate Standard Deviation of each asset use STDEV.P() function in excel as wee need to find standard deviation of whole population. Choose Array as returns in different scenarios. For e.g to calculate standard deviation of Alta Inds. G11 = STDEV.P(G3:G7) is used. Similarly use for every asset. Following is the snapshot:

(C) To calculate Coefficient of Variation (= Standard deviation / Expected return) just divide your findings in (B) by those in (A). Following is the snapshot :

(D) Beta of a security = Covariance (R_i , R_M ) / Variance of (R_i)

To calculate Covariance use COVARIANCE.P() function with Array1 as Market portfolio returns which will be fixed and array2 as individual security returns in different scenarios.

Next Calculate Variance of security by VAR.P() function in which input will be returns in different scenarios for a security

Then divide both of them to get beta

Following is the snapshot :

(E) No the risk ranking is different in all the scenarios:

• According to standard Deviation Risk ranking is:

Alta Inds. > Market Portfolio > American Foam > Repo Men > T-bills

• According to CV Risk Ranking is :

Repo Men > Alta Inds. > American Foam > Market Portfolio > T-bills

• According to beta, risk ranking is :

Repo Men > Alta Inds. > American Foam > Market Portfolio > T-bills

As can be seen beta and CV gives the same rank which is the true rank regarding risk nature of assets as it calculates how much risky the asset is in comparison to the market conditions while SD only measures inherent risk of the asset.

Beta measures total volatility while SD measure individual risk