Financial Management Fall 2019: Stock Analysis Assignment

This assignment solution delves into the intricacies of stock analysis, providing detailed calculations and explanations for various financial metrics. It begins by calculating expected returns and standard deviations for individual securities and portfolios, including covariance analysis. The solution then explores holding period returns, effective annual rates, dividend yields, and capital gains yields for Microsoft stock. Further analysis includes the calculation of arithmetic and geometric means, and standard deviations for multiple stocks, along with holding period returns incorporating dividends and stock splits. Regression analysis is performed to determine the beta of stocks and assess systematic risk. The assignment also covers portfolio beta, expected returns, and standard deviation calculations using CAPM and other financial models. The document includes numerous figures, tables, and Excel formulas to support the calculations and analysis, providing a comprehensive guide to stock valuation and portfolio management.

Running head: STOCK ANALYSIS
Stock Analysis
Name of the Student:
Name of the University:
Author Note:

1STOCK ANALYSIS
Table of Contents
Answer to Question 1:................................................................................................................2
Part a).....................................................................................................................................2
Part b).....................................................................................................................................2
Part c).....................................................................................................................................3
Part d).....................................................................................................................................4
Part e).....................................................................................................................................5
Answer to Question 2:................................................................................................................6
Part a (i))................................................................................................................................6
Part a (ii))...............................................................................................................................6
Part b).....................................................................................................................................7
Part c).....................................................................................................................................8
Answer to Question 3:................................................................................................................8
Part a, b and c)........................................................................................................................8
Part d)...................................................................................................................................10
Part e)...................................................................................................................................10
Answer to Question 4:..............................................................................................................11
Answer to Question 5:..............................................................................................................13
Part A)..................................................................................................................................13
Part B)..................................................................................................................................14
Answer to Question 6:..............................................................................................................14
Part a & b)............................................................................................................................14

2STOCK ANALYSIS
Part c)...................................................................................................................................15
Answer to Question 7:..............................................................................................................16
Part a)...................................................................................................................................16
Part b)...................................................................................................................................17
Part c)...................................................................................................................................19
Answer to Question 8:..............................................................................................................20
Part a)...................................................................................................................................20
Part b)...................................................................................................................................20
Part c)...................................................................................................................................21
Answer to Question 9:..............................................................................................................22
Part a)...................................................................................................................................22
Part b)...................................................................................................................................22
Part c)...................................................................................................................................22
References and Bibliographies:................................................................................................23

3STOCK ANALYSIS
Answer to Question 1:
Part a)
The expected return for the two securities A and B are calculated in the table below,
Figure 1: Expected Return
Source: By the Author
The expected return on security A is 7.70% and in security B is 13.90% which is
provided in the figure above.
Part b)
The standard deviation of security A is given in the figure below,
Figure 2: Standard Deviation of Security A
Source: By the Author
The standard deviation of security A is calculated at 27.44% and the corresponding
formula are given below the calculation for your reference. The standard deviation of security
B is highlighted in the figure below,

4STOCK ANALYSIS
Figure 3: Standard Deviation of Security B
Source: By the Author
The standard deviation of security B is calculated at 21.43%. The method to calculate
the standard deviation is given in the following points,
 The probability is considered to be x and the expected return is taken as the average
return.
 The expected average return is subtracted from the probability and the results are
squared.
 The squared results are multiplied with the probability of the individual security and
the state of economy.
 The square root of the above results sum gives the standard deviation of each security.
Part c)
The expected return of the portfolio consisting of the two securities A and B with the
weights of 60% and 40% respectively is given in the figure below,

5STOCK ANALYSIS
Figure 4: Expected return in boom cycle
Source: By the Author
The expected portfolio return in the boom cycle is coming at 3.29% and the respective
formula are given in the table along with the calculation.
Part d)
The expected or forecasted return from a portfolio is calculated by multiplying the
expected return of the individual security with their weights in the portfolio. The calculation
for the same is given in the figure below,
Figure 5: Portfolio Return
Source: By the Author
The total portfolio return with the weights of 60% in security A and 40% in security B
generates 10.18%.

6STOCK ANALYSIS
Part e)
To calculate the standard deviation of the portfolio the covariance between the two
stocks A and B is calculated and provided in the table below,
Figure 6: Calculation of Covariance
Source: By the Author
The Covariance between the two stocks is at 0.0043 which is calculated by
multiplying the difference of expected return and average return of the two stock and dividing
by three. We are assuming the data to be a population data so N-1 is not incorporated in the
formula.
Figure 7: Calculation of standard deviation of Portfolio
Source: By the Author
The standard deviation of the portfolio is at 19.12%, and the excel formula for the
same is highlighted in the table below the calculation. To calculate the standard deviation of
the portfolio the following formula has been used,
S . d= √ weigh tA2∗VarianceA + √ weight B2∗varianceB+¿ ¿2 *weight A* Weight B* Cov
(A,B)*S.D A*S.D B

7STOCK ANALYSIS
Answer to Question 2:
Part a (i))
The holding period return for the Microsoft stock is calculated and provided in the
table below,
Figure 8: Holding Period Return of Microsoft
Source: By the Author
The holding period return for the Microsoft stock is at 18.04%, and has been
calculated by using adding the sale price and the dividend less the purchase price. The results
derived is then divided by the purchase price of the stock to get the holding period return.
Part a (ii))
The effective annual rate of return of the holding period return of the Microsoft stock
is provided in the figure below,

8STOCK ANALYSIS
Figure 9: Effective Annual Rate
Source: By the Author
The effective annual return for the Microsoft stock is at 92.17% and the excel formula
is shown in the figure above. The mathematical formula used to calculate the effective annual
rate is ((1+ holding period return/ time)^time)-1.
Part b)
The dividend yield of the Microsoft stock is calculated by dividing the dividend
received with the current stock price of the stock and the following results were calculated in
the figure below,
Figure 10: Dividend Yield
Source: By the Author

9STOCK ANALYSIS
The dividend yeild of the Microsoft stock is at 0.37% which is by dividing the
dividend received of $0.46 with the stock price of $126.02.
Part c)
The capital gains yeild of the Microsoft stock is the return which the stock provided
by holding the stock for a certain period of time. Thus the capital gain yeild is calculated by
dividing the result of sale price less the purchase price with the purchase price. The following
figure shows the calculation of capital gains yeild.
Figure 11: Capital Gains yield
Source: By the Author
The capital gain yeild of the Microsoft stock over the holding period is at 17.61%.
The sale price is $126.02 and the purchase price is $107.15.
Answer to Question 3:
Part a, b and c)
The arithmetic mean. Geometric mean and standard deviation of the three stocks is
calculated and highlighted in the table below,

10STOCK ANALYSIS
Figure 12: Results of V stock
Source: By the Author
Figure 13: Results of IBM stock
Source: By the Author
Figure 14: Results of Netflix stock
Source: By the Author
The arithmetic mean of the three stocks have been calculated in the table above and
are 1.22%, 0.06% and 2.7% for each of the stock respectively. The geometric mean of the
three stocks are 101.1%, 99.92% and 101.86% respectively. The standard deviation of the
three stocks are as follows 4.89%, 5.32% and 13.15% respectively. The excel formula for all
the calculation made are shown in the figure above.

11STOCK ANALYSIS
Part d)
The holding period return for each of the stock incorporating dividend and stock split
is highlighted in the table below,
Figure 15: Holding Period Return of Stock
Source: By the Author
The holding period return for the V stock over the three year horizon incorporating
dividend and stock split is at 40.08%. The holding period return for the IBM stock
incorporating dividend is at -5.16%. The holding period return for the Netflix stock is at
135.38% because there was no dividend payment by the stock but the stock split of the stock
has been incorporated in the calculation.
Part e)
The calculation of geometric mean and the holding period return of the three stock is
given in the figure below,

12STOCK ANALYSIS
Figure 16: Holding Period Return of Portfolio
Source: By the Author
The holding period return for the portfolio is at 494.11% while the Geometric mean is
at 0.1469.
Answer to Question 4:
The regression statistics of the three stocks HOG, MAR and LLY is given in the table
below,
Table 1: Regression Table of HOG
Source: By the Author
The beta of the stock HOG is calculated to be at the level of 1.29 and the risk of the
stock lies between 0.6292 and 1.9547 using the 95% confidence interval. The p-value which
is given by p<0.05 indicates that the coefficient of return is significant and it can be said that
one percentage change in market return will raise the return form HOG by 1.29%.

13STOCK ANALYSIS
Table 2: Regression Table of MAR
Source: By the Author
The beta of the stock MAR is calculated to be at the level of 1.4640 and the risk of the
stock lies between 1.0291 and 1.8989 using the 95% confidence interval. The p-value which
is given by p<0.05 indicates that the coefficient of return is insignificant.
Table 3: Regression Table of LLY
Source: By the Author

14STOCK ANALYSIS
The beta of the stock LLY is calculated to be at the level of 0.27 and the risk of the
stock lies between -0.1607 and 0.7188 using the 95% confidence interval. The p-value which
is given by p<0.05 indicates that the coefficient of return is significant and it can be said that
one percentage change in market return will raise the return form LLY by 0.27%.
Beta denotes the risk in relation to the market and the return which a stock should
generate over and above the market return and risk free rate. Beta is the measure of risk and a
Beta which is equal to 1 has the risk which is possessed by the market portfolio, and a beta
greater than 1 denotes the stock being more risky than the market and a beta less than 1
indicates a less risky stock. Gold ETF have a Beta of 0, thus the required rate of return of the
stock is equal to the risk free rate.
The above regression statistics have been calculated using the returns of the stock to
be dependent variable denoted by Y and the market return to be denoted by X over a 60
month period.
Answer to Question 5:
Part A)
The total return variability of a stock is measured by standard deviation of the stock.
The volatility of the returns of the stock over the 60 month period is calculated using the
standard deviation of the returns of the stock. The standard deviation of the stocks is
highlighted in the figure below,
Figure 17: Variability of Stock

15STOCK ANALYSIS
Source: By the Author
The highest standard deviation is reported by the stock HOG at 7.26%, which is
greater than the market and other stocks. The least variable stock is LLY having a volatility
or standard deviation of 4.35%. The market has the lowest volatility which is at the level of
2.56%.
Part B)
The measure of systematic risk is provided by the beta of the stock which is
calculated by regressing the stock return with the market return. Another way of calculation
of the beta is by using the slope function in excel which is given in the table below,
Figure 18: Beta of Stock
Source: By the Author
The most risky stock among the three stock is the stock of MAR having the highest
beta of 1.4640.
Answer to Question 6:
Part a & b)
The expected return on each stock and the expected return of the portfolio with its
beta is calculated in the figure below,

16STOCK ANALYSIS
Figure 19: Portfolio Beta and Expected Return
Source: By the Author
The expected return of each of the stock is calculated using the CAPM formula which
is the risk free rate + market risk premium multiplied with the stock beta of the individual
stock. Thus, the expected return of 2nd national bank is 7.2%, Chesapeake energy has the
expected return of 9.8%, Pegasus has an expected return of 5.25% and Soda-stream has an
expected return of 11.1%.
The expected return of the portfolio is calculated by multiplying the expected return
calculated using CAPM with the weights each security comprise in the portfolio. The
expected return of the portfolio is at 7.45%. The beta of the portfolio is calculated by
multiplying the individual beta of the stock with the weights each security comprise in the
portfolio. The Beta of the portfolio is at 0.8380.
Part c)
The standard deviation of the portfolio consisting of two stocks Soda-stream and
Pegasus is calculated in the figure below,

17STOCK ANALYSIS
Figure 20: Standard Deviation of Portfolio
Source: By the Author
The weights of the security is taken as 60% and 40% respectively for Soda-stream and
Pegasus, the risk or the standard deviation of the portfolio is calculated at 59.02%. The excel
formula for the calculation are shown below the value table.
Answer to Question 7:
Part a)
The projects which the firm should select if it uses the cost of capital of the firm are
given below, however first we calculate the cost of capital of the firm.
Figure 21: Cost of Capital of Firm
Source: By the Author
The cost of capital of the firm is 13.4% and thus now the projects will be screened by
using the cost of capital as the minimum return required.

18STOCK ANALYSIS
Figure 22: Accept or Reject
Source: By the Author
Thus based on the cost of capital of the firm as the minimum benchmark the firm can
accept four of the six projects. The project which should be accepted are PUB1, ENT1, ENT2
and ENT3.
Part b)
The firm should first calculate the project specific cost of capital which is given in the
figure below,
Figure 23: Cost of Capital of Project Publishing
Source: By the Author

19STOCK ANALYSIS
The project specific cost of capital for publishing company is 12%, thus the projects
which should be accepted using this cost of capital is given below,
Figure 24: Accept or Reject
Source: By the Author
Thus the company should accept the project PUB1 and PUB 2 at the 12% minimum
required return.
The cost of capital of the entertainment project is calculated in the figure below which
will be used as the minimum required return.
Figure 25: Cost of Capital of Project Entertainment
Source: By the Author

20STOCK ANALYSIS
The cost of capital of the entertainment project is at 17.6%, thus the projects have to
generate IRR greater than 17.6% to be accepted by the company.
Figure 26: Accept or Reject
Source: By the Author
Thus at the 17.6% required rate of return the firm should accept only the ENT1
project for its company.
Part c)
If the firm chooses a project on the basis of the firms cost of capital, it is ignoring the
fact that the firms cost of capital is specific to the risk to the firm. The individual projects
themselves possess different risk and thus should be valued based on the project specific cost
of capital. Thus using the firm cost of capital might lead to under or over consideration of risk
and a wrong project gets selected which reduces the value to the shareholders. Thus by using
the firm cost of capital NPV of the project which is positive can become zero or even
negative when using the project specific cost of capital.
Thus a company should assess a project using the cost of capital of that project or it
might lead to the selection of a wrong project which degrades its value.

21STOCK ANALYSIS
Answer to Question 8:
Part a)
The before tax and after tax cost of debt of the company Hoosier Manufacturing is
calculated in the table below,
Figure 27: Cost of Debt
Source: By the Author
The before tax cost of debt is equal to the coupon rate of the bond of the company at
7.3%, while the after tax cost of debt is calculated by reducing the tax rate from the before tax
cost of debt and is at 4.82%
Part b)
The cost of the common stock of Hoosier Manufacturing is calculated using the
CAPM formula and is calculated in the figure below,
Figure 28: Cost of Equity
Source: By the Author

22STOCK ANALYSIS
The cost of equity or common stock for the company is 10.3%. The market risk
premium and Rf is 7% and 1.9% respectively and the beta is 1.2 for the company.
Part c)
The weighted average cost of capital of a company is calculated by multiplying the
cost of equity or bond with the respective share in the capital structure of a company. After
tax cost of debt is 4.82% while the cost of equity is 10.3%. The share of equity in the capital
structure of the company is 0.6835 and the share of debt is 0.3164. The cost of capital to the
company is 8.57% which is calculated in the figure below,
Figure 29: Weighted Average Cost of Capital
Source: By the Author

23STOCK ANALYSIS
Answer to Question 9:
Part a)
The equation for the required return on firm bonds in the equation of WACC is given
below,
Kd=(WACC −Ke∗We)/Wd
Part b)
The equation for required return for equity in terms of WACC equation is given
below,
ke=(WACC−Kd∗Wd )/We
Part c)
The equation of WACC for the firm is given below,
WACC = ( Ke∗We ) + ?( Kd∗Wd )

24STOCK ANALYSIS
References and Bibliographies:
Babenko, I., Boguth, O. and Tserlukevich, Y., 2016. Idiosyncratic cash flows and systematic
risk. The Journal of Finance, 71(1), pp.425-456.
Barberis, N., Mukherjee, A. and Wang, B., 2016. Prospect theory and stock returns: An
empirical test. The Review of Financial Studies, 29(11), pp.3068-3107.
Chemmanur, T.J. and Yan, A., 2019. Advertising, attention, and stock returns. Quarterly
Journal of Finance, p.1950009.
Cieslak, A., Morse, A. and Vissing‐Jorgensen, A., 2018. Stock returns over the FOMC
cycle. The Journal of Finance.
Farber, A., Gillet, R.L. and Szafarz, A., 2006. A General Formula for the
WACC. International Journal of Business, 11(2).
Fernandez, P., 2010. WACC: definition, misconceptions, and errors. Business Valuation
Review, 29(4), pp.138-144.
Hajdasinski, M.M., 2004. The internal rate of return (IRR) as a financial indicator. The
Engineering Economist, 49(2), pp.185-197.
Harvey, C.R., Liu, Y. and Zhu, H., 2016. … and the cross-section of expected returns. The
Review of Financial Studies, 29(1), pp.5-68.
Hong, G. and Sarkar, S., 2007. Equity systematic risk (beta) and its
determinants. Contemporary Accounting Research, 24(2), pp.423-466.
Kelleher, J.C. and MacCormack, J.J., 2004. Internal rate of return: A cautionary tale. The
McKinsey Quarterly, 20, p.2004.

25STOCK ANALYSIS
Krüger, P., Landier, A. and Thesmar, D., 2015. The WACC fallacy: The real effects of using
a unique discount rate. The Journal of Finance, 70(3), pp.1253-1285.
Lakonishok, J. and Shapiro, A.C., 1986. Systematic risk, total risk and size as determinants of
stock market returns. Journal of Banking & Finance, 10(1), pp.115-132.
Lombardi, M.J. and Ravazzolo, F., 2016. On the correlation between commodity and equity
returns: implications for portfolio allocation. Journal of Commodity Markets, 2(1), pp.45-57.
Martin, I.W. and Wagner, C., 2019. What is the Expected Return on a Stock?. The Journal of
Finance, 74(4), pp.1887-1929.
Montgomery, C.A. and Singh, H., 1984. Diversification strategy and systematic
risk. Strategic Management Journal, 5(2), pp.181-191.
Moreira, A. and Muir, T., 2017. Volatility‐Managed Portfolios. The Journal of
Finance, 72(4), pp.1611-1644.
Reeb, D.M., Kwok, C.C. and Baek, H.Y., 1998. Systematic risk of the multinational
corporation. Journal of International Business Studies, 29(2), pp.263-279.
Savor, P. and Wilson, M., 2016. Earnings announcements and systematic risk. The Journal of
Finance, 71(1), pp.83-138.
Siegel, A.F., 1995. Measuring systematic risk using implicit beta. Management
Science, 41(1), pp.124-128.
Wang, N., Zhang, L., Huang, Z. and Li, Y., 2019. Asymmetric Correlations in Predicting
Portfolio Returns. International Review of Finance.