Complete the estimation of Nikes stock price using the information given in the spreadsheet Cash Flow Estimation and assuming the analysts estimation of WACC was correct. The completed estimation should be presented in the area marked in yellow in the spreadsheet. (Note that if you use the analysts estimate of WACC of 12%, your estimation of the stock price should be $37.27)
B D E F G 1 Historical betas of Nike 2 3 4 1998 1997 1998 1999 2000 0.98 0.84 0.84 0.83 0.83 up to 2001 June 0.69 7 8 7.50% 5.90% 5.50% 31-Mar 0.1 0.12 0.12 0.12 0.12 30-Jun 0.1 0.12 0.12 0.12 0.12 30-Sep 0.1 0.12 0.12 0.12 31-Dec 0.1 0.12 0.12 0.12 $2.32 $2.67 9 10 Hostorical markket risk premium 11 Arithmetic mean 12 Geometric mean 13 14 Dividend growth rate forecasted by Value li 15 16 17 Dividend history 18 1997 19 1998 20 1999 21 2000 22 2001 23 24 Consensus EPS estimates 25 FY 2002 26 FY 2003 27 28 Current yields on U.S. Treasuries 29 3-month 30 6-month 31 1-year 32 5-year 33 10-year 34 20-year 35 36 37 Current Nike Debt 38 ISSUER 39 NIKE 40 NIKE 41 NIKE 42 NIKE 43 NIKE 44 NIKE 45 46 47 3.59% 3.59% 3.59% 4.88% 5.39% 5.74% COUPON 2.25 3.625 3.875 2.375 3.375 2.4 MATURITY value (in milMARKET PRICE 5/1/2023 212.58 104.536 5/1/2043 187.81 117.139 11/1/2045 241.33 124.383 11/1/2020 155.66 109.125 11/1/2048 210.58 114.297 3/27/2025 288.86 107.417 YTM 0.3 2.8 2.536 0.773 2.808 0.689 14 A B D E F H I K L M N O R. S T V W 1 Income statement in 2001 2 Sales 9,488.8 3 Cost of goods sold 5,784.9 4 Gross profit 3,703.9 5 Selling and administrative 2,689.7 8 Operating income 1,014.2 7 Interest expense 58.7 8 Other expense 34.1 9 Income before income taxes 921.4 10 Income taxes (36%) 331.7 11 Net income 589.7 12 13 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 15 Assumptions(Projections) 16 Rev growth % 7.00% 6.50% 6.50% 6.50% 6.00% 6.00% 6.00% 6.00% 6.00% 6.00% 17 COGS/Sales % 60.00% 60.00% 59.50% 59.50% 59.00% 59.00% 58.50% 58.50% 58.00% 58.00% 18 SG&A/sales % 28.00% 27.50% 27.00% 26.50% 26.00% 25.50% 25.00% 25.00% 25.00% 25.00% 19 Tax % 38.00% 38.00% 38.00% 38.00% 38.00% 38.00% 38.00% 38.00% 38.00% 38.00% 20 Terminal value growth rate 3.00% 21 Cost of capital S 22 23 24 Based on the income statement in 2001 and the above assumptions, complete the following projections to find firm value and equity price. 25 Discounted cash flow 28 Sales 10153.016 27 Cost of goods sold 6091.8096 28 Gross profit 4061.2064 29 Selling and administrative 2842.84448 30 Operating income 1218.36192 31 Taxes 462.9775296 32 Net operating income after tax 755.3843904 33 change in Capex 34 Change in net working capital 8.8 -174.9 -186.3 -198.4 -195-206.7 -219.1 -232.3 -246.2 -261 35 FCF 36 Terminal value Terminal value can be calcuated using constant dividend growth model because they are of the same concept. 37 Total cash flow 38 39 NPV 40 Book value of debt 1296.6 41 42 Shares outstanding 271.5 in millions 43 Equity value per share 44 45 Current share price $42.09 48 F B D E F G 1 Historical betas of Nike 2 3 4 1998 1997 1998 1999 2000 0.98 0.84 0.84 0.83 0.83 up to 2001 June 0.69 7 8 7.50% 5.90% 5.50% 31-Mar 0.1 0.12 0.12 0.12 0.12 30-Jun 0.1 0.12 0.12 0.12 0.12 30-Sep 0.1 0.12 0.12 0.12 31-Dec 0.1 0.12 0.12 0.12 $2.32 $2.67 9 10 Hostorical markket risk premium 11 Arithmetic mean 12 Geometric mean 13 14 Dividend growth rate forecasted by Value li 15 16 17 Dividend history 18 1997 19 1998 20 1999 21 2000 22 2001 23 24 Consensus EPS estimates 25 FY 2002 26 FY 2003 27 28 Current yields on U.S. Treasuries 29 3-month 30 6-month 31 1-year 32 5-year 33 10-year 34 20-year 35 36 37 Current Nike Debt 38 ISSUER 39 NIKE 40 NIKE 41 NIKE 42 NIKE 43 NIKE 44 NIKE 45 46 47 3.59% 3.59% 3.59% 4.88% 5.39% 5.74% COUPON 2.25 3.625 3.875 2.375 3.375 2.4 MATURITY value (in milMARKET PRICE 5/1/2023 212.58 104.536 5/1/2043 187.81 117.139 11/1/2045 241.33 124.383 11/1/2020 155.66 109.125 11/1/2048 210.58 114.297 3/27/2025 288.86 107.417 YTM 0.3 2.8 2.536 0.773 2.808 0.689 14 A B D E F H I K L M N O R. S T V W 1 Income statement in 2001 2 Sales 9,488.8 3 Cost of goods sold 5,784.9 4 Gross profit 3,703.9 5 Selling and administrative 2,689.7 8 Operating income 1,014.2 7 Interest expense 58.7 8 Other expense 34.1 9 Income before income taxes 921.4 10 Income taxes (36%) 331.7 11 Net income 589.7 12 13 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 15 Assumptions(Projections) 16 Rev growth % 7.00% 6.50% 6.50% 6.50% 6.00% 6.00% 6.00% 6.00% 6.00% 6.00% 17 COGS/Sales % 60.00% 60.00% 59.50% 59.50% 59.00% 59.00% 58.50% 58.50% 58.00% 58.00% 18 SG&A/sales % 28.00% 27.50% 27.00% 26.50% 26.00% 25.50% 25.00% 25.00% 25.00% 25.00% 19 Tax % 38.00% 38.00% 38.00% 38.00% 38.00% 38.00% 38.00% 38.00% 38.00% 38.00% 20 Terminal value growth rate 3.00% 21 Cost of capital S 22 23 24 Based on the income statement in 2001 and the above assumptions, complete the following projections to find firm value and equity price. 25 Discounted cash flow 28 Sales 10153.016 27 Cost of goods sold 6091.8096 28 Gross profit 4061.2064 29 Selling and administrative 2842.84448 30 Operating income 1218.36192 31 Taxes 462.9775296 32 Net operating income after tax 755.3843904 33 change in Capex 34 Change in net working capital 8.8 -174.9 -186.3 -198.4 -195-206.7 -219.1 -232.3 -246.2 -261 35 FCF 36 Terminal value Terminal value can be calcuated using constant dividend growth model because they are of the same concept. 37 Total cash flow 38 39 NPV 40 Book value of debt 1296.6 41 42 Shares outstanding 271.5 in millions 43 Equity value per share 44 45 Current share price $42.09 48 F
