Data Lase 321 3. Next, click the "Financials" tab and then click on "Income Statement" below the summary data. (Be sure to select the "Restated" tab rather than "As Originally Reported.") Using the "Export to Excel" tab, export the entire five years of annual) income statements into a new worksheet in your existing Excel file. Repeat this process for both the balance sheet and cash flow statement for Columbia Record the most recent total number of shares outstanding from the Income State- ment and record it on your main worksheet. 4. Using the three financial statements, calculate the five-year historical average for Columbia's Re turn on Equity (ROE = Net income / Total stockholder's equity) and average dividend payout rate (Dividend paid / Net income). 5. Determine the 5-year dividend growth rate using Eq. 9.12, Columbia's retention rate (1 - average payout rate), and expected return on new investments (use average ROE). 6. To determine the stock value based on the dividend discount model: a. Create a timeline in Excel for five years. b. Forecast the next five annual dividends based on the current dividend amount (from 2) and the five-year growth rate (from 5, assuming the payout rate stays constant over the next 5 years). c. Assume a long-term dividend growth rate of 3%. d. Use the long-term growth rate to determine the stock price for year four using Eq. 9.13. c. Determine the current stock price using Eq.9.14. f. Compare this to the actual stock price. What long-term dividend growth rate would you need to assume to match the current stock prict on the disinted free cash flow method: 4 1. Current Stock Price 2. Current Annual Dividend Per Share Amount 3. Total Number of Share Outstanding 4a. Return on Equity $91.43 $1.04 67,530,000 Average 11.36% 2018 15.87% 2017 6.36% 2016 12.13% 2015 12.31% 2014 10.12% 9 4b. Average Divident Payout Rate Average 30.18% 2018 23.36% 2017 48.43% 2016 25.08% 2015 24.98% 2014 29.04% 8% 125.5-year Divident Growth Rate 13 14 a/b. Excel Timeline 15 Dividend 16 Bold 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 $1.31 2023 $1.41 2024 $1.52 0 2019 $1.04 3% 8% $30.47 $26.55 TTM 217a 22 EBITDA (line 21 Pretax income?) 23 Dep & Amort (Cash flow line 7) 24 EBIT 25 Sales (Income stmt-line 3 Total revenue?) 26 EBIT/Sales 2018 2017 2016 2015 2014 5 year averago 419,006,000 360,717,000 266,734,000 256,898,000 247,319,000 198,521.000 103,461,000 58,230,000 59,945,000 60,016,000 56,521.000 54,017.000 315,545,000 302,487,000 206,789,000 196,882,000 190,790,000 144,504.000 3,005,209,000 2,802,326,000 2.466,105,000 2,377.045,000 2,326,180,000 2.100,500,000 0.10499935280 .107941403 0.08385247181 0.08202636635 0.08202202753 0.06879210127 000508687398 291,696,000 281,394.000 279.650.000 291,687.000 291.563,000 28 Net Property Plant & Equipment (BS line 21) 287178000 + Sheet1 Income Statement - Balance Sheet Cash Flow - Shoots - O Type here to search Data Lase 321 3. Next, click the "Financials" tab and then click on "Income Statement" below the summary data. (Be sure to select the "Restated" tab rather than "As Originally Reported.") Using the "Export to Excel" tab, export the entire five years of annual) income statements into a new worksheet in your existing Excel file. Repeat this process for both the balance sheet and cash flow statement for Columbia Record the most recent total number of shares outstanding from the Income State- ment and record it on your main worksheet. 4. Using the three financial statements, calculate the five-year historical average for Columbia's Re turn on Equity (ROE = Net income / Total stockholder's equity) and average dividend payout rate (Dividend paid / Net income). 5. Determine the 5-year dividend growth rate using Eq. 9.12, Columbia's retention rate (1 - average payout rate), and expected return on new investments (use average ROE). 6. To determine the stock value based on the dividend discount model: a. Create a timeline in Excel for five years. b. Forecast the next five annual dividends based on the current dividend amount (from 2) and the five-year growth rate (from 5, assuming the payout rate stays constant over the next 5 years). c. Assume a long-term dividend growth rate of 3%. d. Use the long-term growth rate to determine the stock price for year four using Eq. 9.13. c. Determine the current stock price using Eq.9.14. f. Compare this to the actual stock price. What long-term dividend growth rate would you need to assume to match the current stock prict on the disinted free cash flow method: 4 1. Current Stock Price 2. Current Annual Dividend Per Share Amount 3. Total Number of Share Outstanding 4a. Return on Equity $91.43 $1.04 67,530,000 Average 11.36% 2018 15.87% 2017 6.36% 2016 12.13% 2015 12.31% 2014 10.12% 9 4b. Average Divident Payout Rate Average 30.18% 2018 23.36% 2017 48.43% 2016 25.08% 2015 24.98% 2014 29.04% 8% 125.5-year Divident Growth Rate 13 14 a/b. Excel Timeline 15 Dividend 16 Bold 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 $1.31 2023 $1.41 2024 $1.52 0 2019 $1.04 3% 8% $30.47 $26.55 TTM 217a 22 EBITDA (line 21 Pretax income?) 23 Dep & Amort (Cash flow line 7) 24 EBIT 25 Sales (Income stmt-line 3 Total revenue?) 26 EBIT/Sales 2018 2017 2016 2015 2014 5 year averago 419,006,000 360,717,000 266,734,000 256,898,000 247,319,000 198,521.000 103,461,000 58,230,000 59,945,000 60,016,000 56,521.000 54,017.000 315,545,000 302,487,000 206,789,000 196,882,000 190,790,000 144,504.000 3,005,209,000 2,802,326,000 2.466,105,000 2,377.045,000 2,326,180,000 2.100,500,000 0.10499935280 .107941403 0.08385247181 0.08202636635 0.08202202753 0.06879210127 000508687398 291,696,000 281,394.000 279.650.000 291,687.000 291.563,000 28 Net Property Plant & Equipment (BS line 21) 287178000 + Sheet1 Income Statement - Balance Sheet Cash Flow - Shoots - O Type here to search