Homework 4 basic -1 Auto Save of Exce William Lim O Search File Insert Draw Page Layout Formulas Data Review View Developer Help Share Comments Home SInsert. X 11 Calibri A General Delete Conditional Format as Furrmatting Table Sert &Find & Filter: Select- Cell ldeas Dacte A EE B I U- Format Styles Cell Cipboard ont Alignment Number Stdes Etiting Ideas M14 ???? A C G J K M N C 1 Question 3 Please see in the attached Excel workbook the weekly stock prices of Johnson & Johnson (INJ) between October, 2015 through October, 2018. First calculate the "BETA (b) of the stock. Assume that the Risk Free 4 Rate (Rf) is 10 year Treasury Bond Rate and Expected return of the market [E(Rm)] is 9.50 %. Stock Price on October 8, 2018 is $139.39 and dividend growth rate is 7.85 %. What is the WACC JNJ an October 8, f debt: 3.20%. 5 2018? Note: Please use cost 6 R(S&P 93.16 ??? S&P RUNJ) Date NJ 10/6/2014 1906.13 COMPUTING THE WACC FOR JNJ 10/13/2014 1886. Beta Shares outstanding 90 222 22 m) 9.50% 2,684.00 10/27/2014 11/3/2014 2.36 % $139.39 11 2018.05 99.18 ??? ??? Share price 10/8/18 ?? 12 99.57 7?? Equity Value $374,122.76 2031.92 Net Debt 13 11/10/2014 99,53 777 Tax Rate 777 37223 2039.82 11/17/2014 99.26??? ??? Tax Rate 14 2063.50 3.20 % 11/24/2014 12/1/2014 ??? Cost of Debt, rd 15 2067.56 100.27 ??? 16 2075.37 100.51 ??? ??? E(Rm) 9.50% 2.36 % 17 12/8/2014 96,73 777 7?7 Risk-free Rate, rf 2002.33 12/15/2014 97.76 ?7? JNJ beta, b 2070.6 12/29/2014 20 2058.20 96.81 ??? ??? WACC base classic CAPM equity, re 277 227 22 1/12/2015 96,37 77? 2?7 WACC 2019.42 1/19/2015 94.66 ??? 2?? 23 2051.82 1/26/2015 2/2/2015 ??? 24 1994.99 92.75??? 25 93.64 7?? ??? WACC based on Gordon Dividend Growth Model 2055.47 Current quarterly dividendishare 26 2/9/2015 92.27 777 $0,84 2096.99 2/16/2015 92.86 ??? ??? Growth Rate ??? 27 2110.30 2/23/2015 3/2/2015 ??? Cost of equity, re WACC based on Gordan Dividend Growlh Model ??: 28 2104.50 95.61 ??? ??? 2071.26 93.37 ??? ??? 30 3 JNJ-IS JNJ-Bs Question + B - + 10% Ready 9:35 PM O Type here to search 6/5/2019 WE Homework 4 basic -1 Auto Save of Exce William Lim O Search File Insert Draw Page Layout Formulas Data Review View Developer Help Share Comments Home SInsert. X 11 Calibri A General Delete Conditional Format as Furrmatting Table Sert &Find & Filter: Select- Cell ldeas Dacte A EE B I U- Format Styles Cell Cipboard ont Alignment Number Stdes Etiting Ideas M14 ???? A C G J K M N C 1 Question 3 Please see in the attached Excel workbook the weekly stock prices of Johnson & Johnson (INJ) between October, 2015 through October, 2018. First calculate the "BETA (b) of the stock. Assume that the Risk Free 4 Rate (Rf) is 10 year Treasury Bond Rate and Expected return of the market [E(Rm)] is 9.50 %. Stock Price on October 8, 2018 is $139.39 and dividend growth rate is 7.85 %. What is the WACC JNJ an October 8, f debt: 3.20%. 5 2018? Note: Please use cost 6 R(S&P 93.16 ??? S&P RUNJ) Date NJ 10/6/2014 1906.13 COMPUTING THE WACC FOR JNJ 10/13/2014 1886. Beta Shares outstanding 90 222 22 m) 9.50% 2,684.00 10/27/2014 11/3/2014 2.36 % $139.39 11 2018.05 99.18 ??? ??? Share price 10/8/18 ?? 12 99.57 7?? Equity Value $374,122.76 2031.92 Net Debt 13 11/10/2014 99,53 777 Tax Rate 777 37223 2039.82 11/17/2014 99.26??? ??? Tax Rate 14 2063.50 3.20 % 11/24/2014 12/1/2014 ??? Cost of Debt, rd 15 2067.56 100.27 ??? 16 2075.37 100.51 ??? ??? E(Rm) 9.50% 2.36 % 17 12/8/2014 96,73 777 7?7 Risk-free Rate, rf 2002.33 12/15/2014 97.76 ?7? JNJ beta, b 2070.6 12/29/2014 20 2058.20 96.81 ??? ??? WACC base classic CAPM equity, re 277 227 22 1/12/2015 96,37 77? 2?7 WACC 2019.42 1/19/2015 94.66 ??? 2?? 23 2051.82 1/26/2015 2/2/2015 ??? 24 1994.99 92.75??? 25 93.64 7?? ??? WACC based on Gordon Dividend Growth Model 2055.47 Current quarterly dividendishare 26 2/9/2015 92.27 777 $0,84 2096.99 2/16/2015 92.86 ??? ??? Growth Rate ??? 27 2110.30 2/23/2015 3/2/2015 ??? Cost of equity, re WACC based on Gordan Dividend Growlh Model ??: 28 2104.50 95.61 ??? ??? 2071.26 93.37 ??? ??? 30 3 JNJ-IS JNJ-Bs Question + B - + 10% Ready 9:35 PM O Type here to search 6/5/2019 WE
