I am trying to calculate JNJ

Excel File Edit View Insert Insert Format Tools Data Window Window Help 51% 0 Thu 1:24 PM a FIN3312-finalexam-Summer2020-stu Q- Search Sheet Home Page Layout Formulas Data Review View + Share Insert X Cut Calibri (Body) AutoSum 11 A- A General Wrap Text X 49 Copy Fill Paste BIU 7 T A = 3 Merge & Center $ - % % ) 4.0 .00 .00 0 Insert Delete Format Format Conditional Format Formatting as Table Cell Styles Clear Sort & Filter A16 fx D E F G H - 1 This problem is similar to the GE example we did "in class together" 2 3 If the last number in your X-number is 1,3,5,7,9 you will do Kohl's If the last number in your X-number is 0,2,4,6,8 you will do JNJ (Johnson and Johnson) 4 5 6 7 Kohl's JNJ Historical dividends 8 9 10 10 11 12 14 13 14 14 15 16 17 2015 2016 1.8 2 2.2 2.44 2.68 1) 2) 3) 4) 4) 5) 5 61 6) 7) 8) 9) 2.950 3.150 3.320 3.540 3.750 2017 2018 On row 32, calculate the average annual growth rate of dividends for your firm from 2015 to 2019. On row 33, calculate the average annual growth rate of dividends for your firm from 2015 to 2020. On row 30, use the ROE and plowback I have given you to calculate the growth rate discussed in the context of the DDM. On row 35, use CAPM to calculate the required rate of return, k Use the layout below to set-up the horizontal timeline showing expected future dividends from 2020 out to 2023. Calculate 3 estimates of the 2024 dividend using the 3 growth rates you calculated in 1), 2), and 3) .. Put in R20, R21, R22 Calculate the est(IV @2023) of all dividends expected to be paid after 2023, using each growth rate Calculate the PV of all cashflows back to "Now" (time 0). Look up the price of the stock today and see it is compared to your intrinsic value estimates. If the model is right, what is it telling you? 2019 2023 2024 18 Full Year 10 19 20 Consensus estimates of future dividends: 2020 0.965663 3.964718 2021 1.43 4.164565 2022 3.030701 4.373468 2023 4.055 4.65 2020 Year 1 2021 Year 2 2022 Year 3 Year 4 Horizontal timeline ...> Now 21 22 23 24 Beta 1.762 0.809 25 Present Value Calulations XXXXXX ROE 12.59% 25.36% 26 27 28 Plowback 0.3872 0.3342 est(IV2023) est(IV2023) est(IV2023) 8= ROE Total of PVs -est (IV now, time 0) using growth rate 1 using growth rate 2 using growth rate 3 29 30 31 32 2015-19 4-year geo average: 33 2015-20 5-year geo average: 34 35 35 5.02 CAPM K: 36 E[r(mkt) - 1] = .085 37 38 39 40 41 42 Grades MFs Investment Proc Port Model CAPM Bonds Equity Val PES and RelVal + Ready B + 100% Excel File Edit View Insert Insert Format Tools Data Window Window Help 51% 0 Thu 1:24 PM a FIN3312-finalexam-Summer2020-stu Q- Search Sheet Home Page Layout Formulas Data Review View + Share Insert X Cut Calibri (Body) AutoSum 11 A- A General Wrap Text X 49 Copy Fill Paste BIU 7 T A = 3 Merge & Center $ - % % ) 4.0 .00 .00 0 Insert Delete Format Format Conditional Format Formatting as Table Cell Styles Clear Sort & Filter A16 fx D E F G H - 1 This problem is similar to the GE example we did "in class together" 2 3 If the last number in your X-number is 1,3,5,7,9 you will do Kohl's If the last number in your X-number is 0,2,4,6,8 you will do JNJ (Johnson and Johnson) 4 5 6 7 Kohl's JNJ Historical dividends 8 9 10 10 11 12 14 13 14 14 15 16 17 2015 2016 1.8 2 2.2 2.44 2.68 1) 2) 3) 4) 4) 5) 5 61 6) 7) 8) 9) 2.950 3.150 3.320 3.540 3.750 2017 2018 On row 32, calculate the average annual growth rate of dividends for your firm from 2015 to 2019. On row 33, calculate the average annual growth rate of dividends for your firm from 2015 to 2020. On row 30, use the ROE and plowback I have given you to calculate the growth rate discussed in the context of the DDM. On row 35, use CAPM to calculate the required rate of return, k Use the layout below to set-up the horizontal timeline showing expected future dividends from 2020 out to 2023. Calculate 3 estimates of the 2024 dividend using the 3 growth rates you calculated in 1), 2), and 3) .. Put in R20, R21, R22 Calculate the est(IV @2023) of all dividends expected to be paid after 2023, using each growth rate Calculate the PV of all cashflows back to "Now" (time 0). Look up the price of the stock today and see it is compared to your intrinsic value estimates. If the model is right, what is it telling you? 2019 2023 2024 18 Full Year 10 19 20 Consensus estimates of future dividends: 2020 0.965663 3.964718 2021 1.43 4.164565 2022 3.030701 4.373468 2023 4.055 4.65 2020 Year 1 2021 Year 2 2022 Year 3 Year 4 Horizontal timeline ...> Now 21 22 23 24 Beta 1.762 0.809 25 Present Value Calulations XXXXXX ROE 12.59% 25.36% 26 27 28 Plowback 0.3872 0.3342 est(IV2023) est(IV2023) est(IV2023) 8= ROE Total of PVs -est (IV now, time 0) using growth rate 1 using growth rate 2 using growth rate 3 29 30 31 32 2015-19 4-year geo average: 33 2015-20 5-year geo average: 34 35 35 5.02 CAPM K: 36 E[r(mkt) - 1] = .085 37 38 39 40 41 42 Grades MFs Investment Proc Port Model CAPM Bonds Equity Val PES and RelVal + Ready B + 100%