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 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 Next, click the Dividends tab above the quote From the Dividends tab, record the current annual dividend per share amount 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 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 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) To determine the stock value based on the dividend discount model Create a timeline in Excel for five years Forecast the next five annual dividends based on the current dividend amount (from 2) and the five year growth rate (from 4) 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) Use the long term growth rate to determine the stock price for year four using Eq 9 13 Determine the current stock price using Eq 9 14 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 EBIT Sales Calculate EBIT as EBITDA (Income Statement) less Depreciation and Amortization (Statement of Cash Flow) Net Property Plant and Equipment Sales Net Working Capital (excluding cash) Sales Create a timeline for the next seven years 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 Use the average ratios computed in part (a) to forecast EBIT, Net Investment (change in Sales Net PPE Sales), and Increases in NWC (change in sales NWC Sales) for the next seven years Forecast free cash flow for the next six years using Eq 9 20 and the current corporate tax rate of 21 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 value of the free cash flows Determine the stock price using Eq 9 22 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 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

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Aug 13, 2024

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

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.

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.
Next, click the Dividends tab above the quote. From the Dividends tab, record the current annual dividend per share amount.
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.
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.
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).
To determine the stock value based on the dividend-discount model:
1. Create a timeline in Excel for five years.
2. Forecast the next five annual dividends based on the current dividend amount (from 2) and the five-year growth rate (from 4).
3. 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).
4. Use the long-term growth rate to determine the stock price for year four using Eq. 9.13.
5. Determine the current stock price using Eq. 9.14.
To determine the stock value based on the discounted free cash flow method:
1. 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: Calculate EBIT as EBITDA (Income Statement) less Depreciation and Amortization (Statement of Cash Flow)
  2. Net Property Plant and Equipment/Sales
  3. Net Working Capital (excluding cash)/Sales
2. Create a timeline for the next seven years.
3. 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.
4. Use the average ratios computed in part (a) to forecast EBIT, Net Investment (change in Sales * Net PPE/Sales), and Increases in NWC (change in sales * NWC/Sales) for the next seven years.
5. Forecast free cash flow for the next six years using Eq. 9.20 and the current corporate tax rate of 21%.
6. Estimate the terminal enterprise value in year five using the free cash flow in year six and Eq. 9.24.
7. Determine the enterprise value of the firm as the present value of the free cash flows.
8. Determine the stock price using Eq. 9.22.
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?
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