I need solution for this word doc. Excel solution would be helpful. Please get back to me ASAP

List of Commonly Used Financial Formulae (HCAD 7514) N FVN = PV x (1 + I) . N PV = FVN / (1 + I ) . = (1 + ) 1 ( ) = (1 + 1 ) + (2 + 2 ) + + ( + ) = () = 1 (1 [ ])2 + 2 (2 [ ])2 + + ( [ ])2 = [] ( ) = + [( ) ] = + + + + + = = (0 ) = (1 ) ( ) () 0 (1 + ()) (0 ) = ( ) () ( ) = (1 ) + () 0 (1 ) 0 (1 ) 0 1 = 0 1 = = ( )(1 ) + ( ) Please refer to the following information about Meridian Bioscience, Inc. (VIVO) and the interest rates for Treasury as well as the list of commonly used financial formulae. Meridian Biosciences- (VIVO)-is a fully integrated life science company that manufactures, markets and distributes a broad range of innovative diagnostic test kits, purified reagents and related products and offers biopharmaceutical enabling technologies. The company was founded in 1976 and is based in Cincinnati, Ohio. Assume all financial information here is for 2015. 1. Based on financial information for 2015 above, the valuation of the stock for Meridian Bioscience was $19.69. Assume that at the end of 2015, the stock of Meridian Bioscience continues to be valued at $19.69 and financial analysts have estimated the growth in dividend to be 3%. Also assume that the required rate of return for the stock market is a constant 6% per year for 2014 through 2016. Interest rates on US Treasury Bills are provided for 2015 and 2016. a. Use all of this information to compute the required rate of return for HealthSouth's stock for 2015. (4 points) b. If the beta of HealthSouth's stock was re-estimated by financial analysts to be 1.0, how does this change the computation of the required rate of return? What is the required rate of return for a beta of 1.0? (3 points) c. One year later, at the end of 2016, what is your valuation of the stock of HealthSouth for the original beta? State any assumptions that you use in computing the value of the stock. (5 points) d. Compute the dividend yield and the capital gains yield for 2015 and 2016 assuming that the required return on equity for 2015 continues into 2016. (8 points) X ---X ---X 1 a 4 points R(Re) = RF + (R(RM) - RF) X b Where RF = Risk Free Rate = Rate on 10 year Treasury = 2.16% (one year ago, since 2015) R(RM) = Required return for stock market = 6% b = 0.72 R(Re) = 2.16 + (6-2.16)X0.72 R(Re) = 2.16+3.84X0.72 = b R(Re) = 2.16 + (6-2.16)X1.0 3 points R(Re) = 2.16+3.84X1.0 = c E(P0) = D0 X [1 + E(g)] 5 points 4.92 6.00 The required return for the stock rises to 6% since the risk (beta) has risen. R(Re) - E(g) Since the valuation of the stock is required, one year later, the dividend growth has to be for two time periods. D0 = Dividend in 2015 = $0.80 E(g) = Expected growth in dividend = 3% R(Re) = Required Return on equity = 4.92% Value of Stock at end of 2016 = Dividend in 2015 X [1 + E(g)]^2 / [R(Re) - E(g)] 44.2041666667 Assumptions: Constant growth in dividend Required return on equity is greater than constant expected growth in dividends d 8 points Dividend yield for 2015 = Dividend in 2015/Stock valuation in 2015 =0.80/42.92 1.86% Stock valuation in 2015 =Dividend in 2015 X [1 + E(g)] / R(Re) - E(g) 42.9166666667 Capital Gains yield for 2015 = Change in valuation of stock (2016 - 2015) / Stock valuation in 2015 =(44.20 - 42.92)/42.92 2.98% Dividend yield for 2016 = Dividend in 2016 / Stock valuation in 2016 Dividend for 2016 = D0 (1 + E(g)) 0.824 Dividend yield for 2016 = 1.86% To compute capital gains yield in 2016, we need stock price in 2017 Value of Stock at end of 2017 = Dividend in 2015 X [1 + E(g)]^3 / [R(Re) - E(g)] 45.5302916667 Capital Gains yield for 2016 = Change in valuation of stock (2017 - 2016) / Stock valuation in 2016 =(45.53 - 44.20)/44.20 3.01% 1 a 4\tpoints R(Re)\t=\tRF\t+\t(R(RM)\t-\tRF)\tX\tb Where RF\t=\tRisk\tFree\tRate\t=\tRate\ton\t10\tyear\tTreasury\t=\t2.16%\t(one\tyear\tago,\tsince\t2015) R(RM)\t=\tRequired\treturn\tfor\tstock\tmarket\t=\t6% b\t=\t0.72 R(Re)\t=\t2.16\t+\t(6-2.16)X0.72 R(Re)\t=\t2.16+3.84X0.72\t= 4.92 b R(Re)\t=\t2.16\t+\t(6-2.16)X1.0 3\tpoints R(Re)\t=\t2.16+3.84X1.0\t= 6.00 The\trequired\treturn\tfor\tthe\tstock\trises\tto\t6%\tsince\tthe\trisk\t(beta)\thas\trisen. c E(P0)\t=\tD0\tX\t[1\t+\tE(g)] 5\tpoints R(Re)\t-\tE(g) Since\tthe\tvaluation\tof\tthe\tstock\tis\trequired,\tone\tyear\tlater,\tthe\tdividend\tgrowth\thas\tto\tbe for\ttwo\ttime\tperiods. D0\t=\tDividend\tin\t2015\t=\t$0.80 E(g)\t=\tExpected\tgrowth\tin\tdividend\t=\t3% R(Re)\t=\tRequired\tReturn\ton\tequity\t=\t4.92% Value\tof\tStock\tat\tend\tof\t2016\t=\tDividend\tin\t2015\tX\t[1\t+\tE(g)]^2\t/\t[R(Re)\t-\tE(g)] 44.20416667 Assumptions: Constant\tgrowth\tin\tdividend Required\treturn\ton\tequity\tis\tgreater\tthan\tconstant\texpected\tgrowth\tin\tdividends d 8\tpoints Dividend\tyield\tfor\t2015\t=\tDividend\tin\t2015/Stock\tvaluation\tin\t2015 =0.80/42.92 1.86% Stock\tvaluation\tin\t2015 =Dividend\tin\t2015\tX\t[1\t+\tE(g)]\t/\tR(Re)\t-\tE(g) 42.91666667 Capital\tGains\tyield\tfor\t2015\t=\tChange\tin\tvaluation\tof\tstock\t(2016\t-\t2015)\t/\tStock\tvaluation\tin\t2015 =(44.20\t-\t42.92)/42.92 2.98% Dividend\tyield\tfor\t2016\t=\tDividend\tin\t2016\t/\tStock\tvaluation\tin\t2016 Dividend\tfor\t2016\t=\tD0\t(1\t+\tE(g)) 0.824 Dividend\tyield\tfor\t2016\t= 1.86% To\tcompute\tcapital\tgains\tyield\tin\t2016,\twe\tneed\tstock\tprice\tin\t2017 Value\tof\tStock\tat\tend\tof\t2017\t=\tDividend\tin\t2015\tX\t[1\t+\tE(g)]^3\t/\t[R(Re)\t-\tE(g)] 45.53029167 Capital\tGains\tyield\tfor\t2016\t=\tChange\tin\tvaluation\tof\tstock\t(2017\t-\t2016)\t/\tStock\tvaluation\tin\t2016 =(45.53\t-\t44.20)/44.20 3.01%