7. To determine the stock value based on the discounted free cash flow method: * Forecast the free cash flows using the historic data from the financial statements to compute the five-year average of the following ratios: i. EBIT/Sales: Because Morningstar does not report EBIT, calculate EBIT from EBITDA (Income Statement) by subtracting Depreciation and Amortization (Statement of Cash Flow) ii. Net Property Plant and Equipment/Sales iii. Net Working Capital (excluding cash)/Sales b. Create a timeline for the next six years. c. Forecast future sales based on the most recent year's total revenue growing at the five-year growth rate (from 4) for the first five years. Use a long-run revenue growth rate of 3% for year six d. Under the assumption that the ratios in part (a) remain constant, use the average ratios com puted in part (a) to forecast EBIT (Sales X EBIT to Sales ratio), Net Investment (change in Sales X PPE to Sales ratio), and increases in NWC (change in sales X NWC to Sales ratio) for the next six years, c. Forecast free cash flow for the next six years using Eq. 9.20 and the current corporate tax rate of 21%. f. Estimate the terminal enterprise value in year five using the free cash flow in year six and Eq.9.24 Determine the enterprise value of the firm as the present walue of the free cash flows. h. Determine the stock price using Eq, 9.22. i. Compare your result to the actual stock price. What assumptions might you change to justify this price? 3 4 5 1. Current Stock Price 2. Current Annual Dividend Per Share Amount 3. Total Number of Share Outstanding 4a. Return on Equity 591.43 $1.04 67,530,000 Average 11.36% 2018 15.87% 2017 6.36% 2016 12.13% 2015 12.31% 2014 10.12% 4b. Average Divident Payout Rate Average 30.18% 2018 23.38% 2017 48.43% 2016 25.08% 2015 24.98% 2014 20.04% 8% 0 2021 125.5-year Divident Growth Rate 13 14a/b. Excel Timeline 15 Dividend 16 6c/d Long-Term Growth Rate of 3% 17 Cost of Equity 18 Price for year four P4 19 6e Current Price (PO) 2020 $1.12 2022 2019 $1.04 3% $1.21 2023 $1.41 $13 2024 $1.52 $30.47 $26.55 2018 2015 21 22 23 24 25 26 7 a EBITDA (line 21 Pretax income?) Dep & Amort (Cash flow line 7) EBIT Sales (Income stunt-line 3 Total revenue?) EBIT/Sales 419,006,000 103,461,000 315,545,000 3,005,209,000 0.1049903528 2017 2016 2014 5 year average 360.717.000 206,734,000 256,898,000 247.319.000 198,521,000 58.230,000 59,945,000 60,016,000 56,521,000 54,017.000 302,487,000 206,780,000 196,882,000 190,798,000 44,504,000 2,802,326,000 2,466,105.000 2,377,045,000 2,326,180,000 2,100,590,000 0.107941403 0.08385247181 0.08282036035 0.08202202753 0.06878210127 0.005007398 291,596,000 279,650,000 281,394,000 291,687,000 26 291,563,000 Not Property Plant & Equipment (BS line 21) 287178000 Sheet1 - Income Statement - Balance Sheet Cash Flow Sheets O Type here to search 21 22 23 24 25 26 7 a EBITDA (line 21 Pretax income?) Dep & Amort (Cash flow line 7) EBIT Sales (Income stmt-line 3 Total revenue?) EBIT/Sales 419,006,000 103,461,000 315,545,000 3,005,209,000 0.1049993528 2018 2017 2016 2015 2014 5 year average 360,717,000 266.734,000 256,898,000 247,319.000 198,521.000 58,230,000 59,945,000 60,016,000 56,521,000 54,017,000 302,487,000 206,789.000 196,882,000 190,798,000 144,504.000 2,802,326,000 2,466,105,000 2,377,045,000 2,326,180,000 2,100,500,000 0.107941403 0.08385247181 0.08282636635 0.08202202753 0.06879210127 0,08508687398 28 29 Net Property Plant & Equipment (BS line 21) Sales ii Net Property Plant & Equipment/sales 3,005,209,000 291,596,000 2,802,326.000 0.1040549886 281,394,000 2.466,105,000 0.1141046306 279,650,000 2,377,045,000 0.117646069 291,687,000 2,326,180,000 0.1253931338 291,563.000 2,100,590,000 0.1388005275 287178000 2414449200 0.1199998699 32 33 1,649,497,000 673,166,000 522,695.000 1,412,023,000 551,389.000 497,783,000 1,249,389,000 369.770.000 513,549,000 1.266,041,000 413.558,000 479,363,000 Current assets cash (BS line 5) cash and cash equivalents Current liabilities Working Capital-cash(current assets-current liabilities) sales 3,005,209,000 till Not working capital sales 1,765,306,000 437,825,000 572,882,000 754,599,000 2,802,326,000 0.2692759515 35 30 37 2.466,105,000 0.2119516403 2,377,045,000 0.2094125269 2,326,180,000 0.220769244 2,100,590,000 0.2282039808 0.2279226687 Balance Sheet Cash Flow Sheets Sheet Income Statement + O Type here to search Columbia Sportswear Co COLM Morningstar Rating Rating as of Feb 21, 2020 Quote Stock Analysis News Price vs Fair Value Trailing >: Show Full Chart > Market Closed $90.89 0.69 0.75% 93.10 Previous Close 91.58 10a 12p USD NASDAQ | Last close prices updated as of Feb 21, 2020, 4:15 PM EST BATS BZX Real-Time Price Quote V Bid/Size 88.00x1 Ask/Size 90.90x25 Day Range 90.20 - 91.48 Volume / Avg 450.6/516.5 Year Range 87.60 -109.44 Forward Div Yield 1.14% Market Cap 6.1374 Bil Investment Style Mid Core Price/Sales 2.05 7. To determine the stock value based on the discounted free cash flow method: * Forecast the free cash flows using the historic data from the financial statements to compute the five-year average of the following ratios: i. EBIT/Sales: Because Morningstar does not report EBIT, calculate EBIT from EBITDA (Income Statement) by subtracting Depreciation and Amortization (Statement of Cash Flow) ii. Net Property Plant and Equipment/Sales iii. Net Working Capital (excluding cash)/Sales b. Create a timeline for the next six years. c. Forecast future sales based on the most recent year's total revenue growing at the five-year growth rate (from 4) for the first five years. Use a long-run revenue growth rate of 3% for year six d. Under the assumption that the ratios in part (a) remain constant, use the average ratios com puted in part (a) to forecast EBIT (Sales X EBIT to Sales ratio), Net Investment (change in Sales X PPE to Sales ratio), and increases in NWC (change in sales X NWC to Sales ratio) for the next six years, c. Forecast free cash flow for the next six years using Eq. 9.20 and the current corporate tax rate of 21%. f. Estimate the terminal enterprise value in year five using the free cash flow in year six and Eq.9.24 Determine the enterprise value of the firm as the present walue of the free cash flows. h. Determine the stock price using Eq, 9.22. i. Compare your result to the actual stock price. What assumptions might you change to justify this price? 3 4 5 1. Current Stock Price 2. Current Annual Dividend Per Share Amount 3. Total Number of Share Outstanding 4a. Return on Equity 591.43 $1.04 67,530,000 Average 11.36% 2018 15.87% 2017 6.36% 2016 12.13% 2015 12.31% 2014 10.12% 4b. Average Divident Payout Rate Average 30.18% 2018 23.38% 2017 48.43% 2016 25.08% 2015 24.98% 2014 20.04% 8% 0 2021 125.5-year Divident Growth Rate 13 14a/b. Excel Timeline 15 Dividend 16 6c/d Long-Term Growth Rate of 3% 17 Cost of Equity 18 Price for year four P4 19 6e Current Price (PO) 2020 $1.12 2022 2019 $1.04 3% $1.21 2023 $1.41 $13 2024 $1.52 $30.47 $26.55 2018 2015 21 22 23 24 25 26 7 a EBITDA (line 21 Pretax income?) Dep & Amort (Cash flow line 7) EBIT Sales (Income stunt-line 3 Total revenue?) EBIT/Sales 419,006,000 103,461,000 315,545,000 3,005,209,000 0.1049903528 2017 2016 2014 5 year average 360.717.000 206,734,000 256,898,000 247.319.000 198,521,000 58.230,000 59,945,000 60,016,000 56,521,000 54,017.000 302,487,000 206,780,000 196,882,000 190,798,000 44,504,000 2,802,326,000 2,466,105.000 2,377,045,000 2,326,180,000 2,100,590,000 0.107941403 0.08385247181 0.08282036035 0.08202202753 0.06878210127 0.005007398 291,596,000 279,650,000 281,394,000 291,687,000 26 291,563,000 Not Property Plant & Equipment (BS line 21) 287178000 Sheet1 - Income Statement - Balance Sheet Cash Flow Sheets O Type here to search 21 22 23 24 25 26 7 a EBITDA (line 21 Pretax income?) Dep & Amort (Cash flow line 7) EBIT Sales (Income stmt-line 3 Total revenue?) EBIT/Sales 419,006,000 103,461,000 315,545,000 3,005,209,000 0.1049993528 2018 2017 2016 2015 2014 5 year average 360,717,000 266.734,000 256,898,000 247,319.000 198,521.000 58,230,000 59,945,000 60,016,000 56,521,000 54,017,000 302,487,000 206,789.000 196,882,000 190,798,000 144,504.000 2,802,326,000 2,466,105,000 2,377,045,000 2,326,180,000 2,100,500,000 0.107941403 0.08385247181 0.08282636635 0.08202202753 0.06879210127 0,08508687398 28 29 Net Property Plant & Equipment (BS line 21) Sales ii Net Property Plant & Equipment/sales 3,005,209,000 291,596,000 2,802,326.000 0.1040549886 281,394,000 2.466,105,000 0.1141046306 279,650,000 2,377,045,000 0.117646069 291,687,000 2,326,180,000 0.1253931338 291,563.000 2,100,590,000 0.1388005275 287178000 2414449200 0.1199998699 32 33 1,649,497,000 673,166,000 522,695.000 1,412,023,000 551,389.000 497,783,000 1,249,389,000 369.770.000 513,549,000 1.266,041,000 413.558,000 479,363,000 Current assets cash (BS line 5) cash and cash equivalents Current liabilities Working Capital-cash(current assets-current liabilities) sales 3,005,209,000 till Not working capital sales 1,765,306,000 437,825,000 572,882,000 754,599,000 2,802,326,000 0.2692759515 35 30 37 2.466,105,000 0.2119516403 2,377,045,000 0.2094125269 2,326,180,000 0.220769244 2,100,590,000 0.2282039808 0.2279226687 Balance Sheet Cash Flow Sheets Sheet Income Statement + O Type here to search Columbia Sportswear Co COLM Morningstar Rating Rating as of Feb 21, 2020 Quote Stock Analysis News Price vs Fair Value Trailing >: Show Full Chart > Market Closed $90.89 0.69 0.75% 93.10 Previous Close 91.58 10a 12p USD NASDAQ | Last close prices updated as of Feb 21, 2020, 4:15 PM EST BATS BZX Real-Time Price Quote V Bid/Size 88.00x1 Ask/Size 90.90x25 Day Range 90.20 - 91.48 Volume / Avg 450.6/516.5 Year Range 87.60 -109.44 Forward Div Yield 1.14% Market Cap 6.1374 Bil Investment Style Mid Core Price/Sales 2.05
