As a new analyst for a large brokerage firm, you are anxious to demonstrate the skills you learned in your MBA program and prove that you are worth your attractive salary. Your first assignment is to analyze the stock of the Columbia Sportswear Corporation. Your boss recommends determining prices based on both the dividend-discount model and discounted free cash flow valuation methods. Columbia has no debt and an 8% equity cost of capital. You are ready for the challenge, but also are a little concerned because your finance professor told you that these two methods can result in widely differing estimates when applied to real data. You are really hoping that the two methods will reach similar prices. 1. Go to Morningstar (www.morningstar.com) and enter the symbol for Columbia Sportswear (COLM). From the main page for COLM, record the current stock price (last trade) at the top of the page. 2. Next, click the Dividends tab above the quote. From the Dividends tab, record the current annual dividend per share amount. Data Case 3. Next, click the Financials tab and then All Financials Data below the table. Export the entire five years of 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 Statement and record it on your main worksheet. 4. Next, click the Valuation tab and then click on Wall Street Estimates . From this tab, find the five-year growth forecast for earnings under Analyst Ratings five-year growth and enter it into your spreadsheet. It will be near the middle of the page. 5. Using the three financial statements, calculate the five-year historical average for Columbias Return on Equity (ROE = Net income / Total stockholders equity) and average dividend payout rate (Dividend paid / Net income). 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 4). c. Determine the long-term dividend growth rate using Eq. 9.12. , and Columbias retention rate (1- average payout rate) and expected return on new investments (use average ROE). d. Use the long-term growth rate to determine the stock price for year four using Eq. 9.13. e. Determine the current stock price using Eq. 9.14. 7. To determine the stock value based on the discounted free cash flow method: a. 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 years 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 computed in part (a) to forecast EBIT (Sales * EBIT to Sales ratio), Net Investment (change in Sales * PPE to Sales ratio), and Increases in NWC (change in sales * NWC to Sales ratio) for the next six years. e. 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. g. Determine the enterprise value of the firm as the present value of the free cash flows. h. Determine the stock price using Eq. 9.22. 8. Compare the stock prices from the two methods to the actual stock price. What recommendations can you make as to whether clients should buy or sell Columbia stock based on your price estimates? 9. Explain to your boss why the estimates from the two valuation methods differ. Specifically, address the assumptions implicit in the models themselves as well as those you made in preparing your analysis. Why do these estimates differ from the actual stock price of Columbia?