You were hired on as a new analyst for a brokerage firm and you are anxious to demonstrate your knowledge and skills acquired with your MBA and demonstrate to your firm their return on their investment. You are given your first assignment by your boss which is to analyze the stock of the Columbia Sportswear Corporation. Your boss advises that you should determine price based on both the dividend-discount model and discounted free cash flow valuation methods.

In your review of Columbia you notice that they have no debt and an 8% equity cost of capital. You are ready for the challenge, but also are a little concerned because you know 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.

Steps to Obtain Data

1. Open up an Excel spreadsheet. Youll use Excel as part of this assignment.
2. Go to Morningstar (www.morningstar.com) and enter the symbol for Columbia Sportswear (COLM). From the main page for COML, record the current stock price (last trade) at the top of the page in a worksheet of your Excel workbook and title it (master data)..
3. Next, click the Dividends tab above the quote. Then, record the current annual dividend per share amount in the master data worksheet of your Excel workbook.
4. Then, click the Financials tab and then All Financials Data below the table. Export the entire five years of income statements into a new worksheet of your Excel workbook. Youll repeat step number four for both the balance sheet and cash flow statement for Columbia. Record the most recent total number of shares outstanding from the income statement in your master data worksheet of your Excel workbook.
5. Click the Valuation tab then, click on Wall Street Estimates. From there, find the five-year growth forecast for earnings under Analyst Ratings five-year growth and enter it into your master data worksheet of your Excel workbook. (hint: this number should be near the middle of the page).

Analytic Steps: Columbias Return on Equity

1. Open a new worksheet in your workbook and label it (Columbias Return on Equity).
2. Using the three financial statements, calculate the five-year historical average for Columbias Return on Equity.
   1. ROE = Net Income / Total stockholders equity.
   2. Average dividend payout rate = dividend paid / net income.

Analytic Steps: Dividend-Discount Model

1. Open a new worksheet in your workbook and label it (dividend-discount model).
2. Create a timeline in Excel for five years.
3. Forecast the next five annual dividends based on the current dividend amount (see Step 3 under Obtaining Data) and the five-year growth rate (see Step 4 under Obtaining Data).
4. Determine the long-term dividend growth rate using Eq. 9.12 from your text, Columbias retention rate (1-average payout rate), and expected return on new investments (use average ROE).
5. Use the long-term growth rate to determine the stock price for year four using Eq. 9.13.
6. Determine the current stock price using Eq. 9.14.

Analytic Steps: Discounted Free Cash Flow Method

1. Open a new worksheet in your workbook and label it (discounted free cash flow).
2. Forecast the free cash flows using the historic data from the financial statements to compute the five-year average of the following ratios:
   1. EBIT/Sales: Morningstar doesnt report EBIT. As an alternative calculate EBIT from EBITDA (see income statement) by subtracting depreciation and amortization (Statement of Cash Flow).
   2. Net Property Plant and Equipment/Sales.
   3. Net Working Capital (excluding cash)/Sales.
3. Create a timeline for the next seven years.
4. Forecast future sales based on the most recent years total revenue growing at the five-year growth rate (see Step 5 under Obtaining Data) for the first five years. Use a long-run revenue growth rate of 3% for year six.
5. Assume that the ratios see step 2 of this section remain constant and use these ratios 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 seven years.
6. Forecast free cash flow for the next six years using Eq. 9.20 in the text and the current corporate tax rate of 21%.
7. Estimate the terminal enterprise value in year five using the free cash flow in year six and Eq. 9.24 of the text.
8. Determine the enterprise value of the firm as the present value of the cash flows.
9. Determine the stock price using Eq. 9.22.

One you have completed your analysis in Excel write a two to three page report that does the following:

1. Compare the stock prices from the two methods to actual stock price. Give recommendations as to whether clients of the firm should buy or sell Columbia stock based on your price estimates? Back up your recommendations with at least two scholarly sources.
2. Give an explanation 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 different from the actual stock price Colubmia. Use at least two sources outside the weekly readings to support your explanation.