Using the data for Johnson & Johnson (NYSE:JNJ), presented below: Johnson & Johnson Fiscal Year Sales 2014 Selected Financial Data For the Years 2014 to 2018 2018 2017 2016 2015 $ 81,534 $ 76,481 $ 71,937 $ 70,200 $ 74,334 21,253 19,565 21,350 18,368 20.929 1,194 1,017 763 552 518 15,297 1,300 16,540 15,409 16,323 6.67 1.34 6.33 5.89 6.29 EBIT Interest Expense Net Income Basic EPS Total Assets 152,954 131,119 157,303 7,310 Accounts Payable 141,208 6,918 70,790 7,537 93,202 7,633 Total Liabilities 97.143 61,367 133,411 6,668 62,261 103,879 19,279 15,816 106,216 101.793 110,551 97,245 Retained Earnings Net Cash from Operating Activities 22,201 21,056 18,767 18,471 Free Cash Flow 18,531 17.777 15,541 14.757 a) Calculate the ratio of each years' data to the previous year for each of the above items for JNJ, and describe in percentage. For example, for the year 2018, the ratio for sales is $81,534/576,481=1.06607 (Analysis 4pts). b) From your calculations in part a, calculate each year's rate of growth. Using the example in part a, the ratio is 1.06607, so the percentage growth in sales for 2018 is 1.06607-1 or 6.607% (Identify and Access Needed Information 4pts). d) Use the GEOMEAN function to estimate the compound annual average growth rate (CAGR) for each of the above items using the results that you calculated in part a. Be sure to subtract 1 from the result to arrive at a percent change. These are geometric averages (Use Information Effectively to Accomplish a Specific Purpose 4 pts). e) Interpretation: Please include your written description in the "interpretation" tab. If you do not complete this section with your words or leave the interpretation tab as a blank, you receive O point in this HW (Use Information Effectively to Accomplish a Specific Purpose 4 pts). H I J K A B D E F G 1 Please complete the yellow highlighted parts. Please show the formulas if the number or figures are not provided in the problem, 2 Johnson & Johnson 3 Selected Financial Data 4 For the Years 2014 to 2018 5 Fiscal Year 2018 2017 2016 2015 2014 6 Sales $ 81,534 $ 76,481 $ 71,937 $ 70,200 $ 74,334 7 EBIT 21.253 19,565 21,350 18,368 20.929 8 Interest Expense 1.194 1,017 763 552 518 9 Net Income 15,297 1,300 16,540 15,409 16,323 10 Basic EPS 6.67 1.34 6.33 5.89 6 29 11 Total Assets 152.954 157,303 141 208 133,411 131,119 12 Accounts Payable 7.537 7,310 6,918 6,668 7.633 13 Total Liabilities 93,202 97,143 70 790 62.261 61 367 14 Retained Eamings 106,216 101.793 110,551 103,879 97.245 15 Net Cash from Operating Activities 22,201 21.056 18.767 19.279 18,471 16 Free Cash Flow 18,531 17.777 15,541 15,816 14,757 17 a) 18 Johnson & Johnson 19 Annual Growth Relatives 20 For the Years 2015 to 2018 21 Fiscal Year 2018 2017 2016 2015 22 Sales 23 EBIT 24 Interest Expense 25 Net Income 26 Basic EPS 27 Total Assets 28 Accounts Payable 29 Total Liabilities 30 Retained Earnings 31 Net Cash from Operating Activities 32 Free Cash Flow 33 b) 34 Johnson & Johnson 35 Annual Growth Rates 36 For the Years 2015 to 2018 To avoid negative numbers for calculating GEOMEAN 2018 2017 2016 2015 2018 2017 2016 2015 37 Fiscal Year 38 Sales 1.000 1.000 1.000 1.000 39 EBIT 40 Interest Expense B D E F G H 1 J K L 31 Net Cash from Operating Activities 32 Free Cash Flow 33 b) 34 Johnson & Johnson 35 36 Annual Growth Rates For the Years 2015 to 2018 2018 To avoid negative numbers for calculating GEOMEAN 37 Fiscal Year 2017 2016 2015 2018 2017 2016 2015 38 Sales 1.000 1.000 1.000 1.000 39 EBIT 40 Interest Expense 41 Net Income 42 Basic EPS 43 Total Assets 14 Accounts Payable 45 Total Liabilities 46 Retained Earings 47 Net Cash from Operating Activities 48 Free Cash Flow 49 50 Johnson & Johnson c) d) 51 Arithmetic Average Growth Rates 52 For the Years 2015 to 2018 53 Fiscal Year 54 Sales Arithmetic Geometric Difference Std Dev 55 EBIT 56 Interest Expense 57 Net Income 58 Basic EPS 59 Total Assets 50 Accounts Payable 51 Total Liabilities 52 Retained Earings 53 Net Cash from Operating Activities 54 Free Cash Flow 55 56 Shouten imga tout Using the data for Johnson & Johnson (NYSE:JNJ), presented below: Johnson & Johnson Fiscal Year Sales 2014 Selected Financial Data For the Years 2014 to 2018 2018 2017 2016 2015 $ 81,534 $ 76,481 $ 71,937 $ 70,200 $ 74,334 21,253 19,565 21,350 18,368 20.929 1,194 1,017 763 552 518 15,297 1,300 16,540 15,409 16,323 6.67 1.34 6.33 5.89 6.29 EBIT Interest Expense Net Income Basic EPS Total Assets 152,954 131,119 157,303 7,310 Accounts Payable 141,208 6,918 70,790 7,537 93,202 7,633 Total Liabilities 97.143 61,367 133,411 6,668 62,261 103,879 19,279 15,816 106,216 101.793 110,551 97,245 Retained Earnings Net Cash from Operating Activities 22,201 21,056 18,767 18,471 Free Cash Flow 18,531 17.777 15,541 14.757 a) Calculate the ratio of each years' data to the previous year for each of the above items for JNJ, and describe in percentage. For example, for the year 2018, the ratio for sales is $81,534/576,481=1.06607 (Analysis 4pts). b) From your calculations in part a, calculate each year's rate of growth. Using the example in part a, the ratio is 1.06607, so the percentage growth in sales for 2018 is 1.06607-1 or 6.607% (Identify and Access Needed Information 4pts). d) Use the GEOMEAN function to estimate the compound annual average growth rate (CAGR) for each of the above items using the results that you calculated in part a. Be sure to subtract 1 from the result to arrive at a percent change. These are geometric averages (Use Information Effectively to Accomplish a Specific Purpose 4 pts). e) Interpretation: Please include your written description in the "interpretation" tab. If you do not complete this section with your words or leave the interpretation tab as a blank, you receive O point in this HW (Use Information Effectively to Accomplish a Specific Purpose 4 pts). H I J K A B D E F G 1 Please complete the yellow highlighted parts. Please show the formulas if the number or figures are not provided in the problem, 2 Johnson & Johnson 3 Selected Financial Data 4 For the Years 2014 to 2018 5 Fiscal Year 2018 2017 2016 2015 2014 6 Sales $ 81,534 $ 76,481 $ 71,937 $ 70,200 $ 74,334 7 EBIT 21.253 19,565 21,350 18,368 20.929 8 Interest Expense 1.194 1,017 763 552 518 9 Net Income 15,297 1,300 16,540 15,409 16,323 10 Basic EPS 6.67 1.34 6.33 5.89 6 29 11 Total Assets 152.954 157,303 141 208 133,411 131,119 12 Accounts Payable 7.537 7,310 6,918 6,668 7.633 13 Total Liabilities 93,202 97,143 70 790 62.261 61 367 14 Retained Eamings 106,216 101.793 110,551 103,879 97.245 15 Net Cash from Operating Activities 22,201 21.056 18.767 19.279 18,471 16 Free Cash Flow 18,531 17.777 15,541 15,816 14,757 17 a) 18 Johnson & Johnson 19 Annual Growth Relatives 20 For the Years 2015 to 2018 21 Fiscal Year 2018 2017 2016 2015 22 Sales 23 EBIT 24 Interest Expense 25 Net Income 26 Basic EPS 27 Total Assets 28 Accounts Payable 29 Total Liabilities 30 Retained Earnings 31 Net Cash from Operating Activities 32 Free Cash Flow 33 b) 34 Johnson & Johnson 35 Annual Growth Rates 36 For the Years 2015 to 2018 To avoid negative numbers for calculating GEOMEAN 2018 2017 2016 2015 2018 2017 2016 2015 37 Fiscal Year 38 Sales 1.000 1.000 1.000 1.000 39 EBIT 40 Interest Expense B D E F G H 1 J K L 31 Net Cash from Operating Activities 32 Free Cash Flow 33 b) 34 Johnson & Johnson 35 36 Annual Growth Rates For the Years 2015 to 2018 2018 To avoid negative numbers for calculating GEOMEAN 37 Fiscal Year 2017 2016 2015 2018 2017 2016 2015 38 Sales 1.000 1.000 1.000 1.000 39 EBIT 40 Interest Expense 41 Net Income 42 Basic EPS 43 Total Assets 14 Accounts Payable 45 Total Liabilities 46 Retained Earings 47 Net Cash from Operating Activities 48 Free Cash Flow 49 50 Johnson & Johnson c) d) 51 Arithmetic Average Growth Rates 52 For the Years 2015 to 2018 53 Fiscal Year 54 Sales Arithmetic Geometric Difference Std Dev 55 EBIT 56 Interest Expense 57 Net Income 58 Basic EPS 59 Total Assets 50 Accounts Payable 51 Total Liabilities 52 Retained Earings 53 Net Cash from Operating Activities 54 Free Cash Flow 55 56 Shouten imga tout