Data Case 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. . 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 dirrinted free cash flow method: Data Case 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. . 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 dirrinted free cash flow method
