Reference no: EM13888936
We are analyzing the stock of the General Electric. We will estimate prices based on both the dividend-discount model and discounted free cash flow valuation methods. GE uses a cost of equity of 10.5% and a WACC of 7.5%. The expected ROE on new investments is 12%. These rates should be inputs to your model. For all time-value of money calculations, we are using annual cash flows which we will assume take place one year from now.
Go to Yahoo! Finance (finance.yahoo.com ) and enter the symbol for General Electric (GE). From the main page for GE, gather the following information and enter it onto a spreadsheet:
The current stock price (last trade) at the top of the page.
The current dividend amount, which is in the bottom-right cell in the same box as the stock price.
Next, click "Key Statistics" from the left side of the page. From the Key Statistics page, gather the following information and enter it on the same spreadsheet:
The number of shares of stock outstanding.
The Payout ratio. You will notice the current payout ratio is > 100% which would eventually drive a firm into the ground. The analyst estimate for 2015 is $1.31 per share so you can calculate a better value for the payout ratio.
Next, click "Analyst Estimates" from the left side of the page. From the Analyst Estimates page, find the expected growth rate for the next five years and enter it onto your spreadsheet. It will be near the very bottom of the page.
Next, click "Income Statement" near the bottom of the menu on the left. Copy and paste the entire three years of income statements into a new worksheet in your existing Excel file. (Note: if you are using IE as your browser, you can place the cursor in the middle of the statement, rightclick, and select "Export to Microsoft Excel" to download an Excel version.) Repeat this process for both the balance sheet and cash flow statement for General Electric. Keep all the different statements in the same Excel worksheet.
To determine the stock value based on the dividend-discount model:
Create a timeline in Excel for five years.
Use the dividend obtained from Yahoo!Finance as the current dividend to forecast the next five annual dividends based on the five-year growth rate. You may allow for fractions of a penny in dividend estimates.
Determine the long-term growth rate based on GE's payout ratio (which is one minus the retention ratio) using the formula in the "Estimating Dividends" slide.
Use the long-term growth rate and the estimated dividend for year 6 to produce a terminal value at t=5. Use the dividend discount model to estimate stock price.
What combinations of long-term growth rate and short-term growth rates would produce the price you are currently observing? Please create a column of short-term growth rates ranging from 3% to 9% incremented by 1%. For each value calculate the long-term growth rate that produces the observed stock price. You can do this using Goal Seek or solver. Please present your results in a table. You will note that data tables aren't capable of handling this task and macros are very hard to use with Solver or Goal Seek.
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 downloaded from Yahoo! to compute the three-year average of the following ratios:
EBIT/Sales
Tax Rate (Income Tax Expense/Income Before Tax)
Property Plant and Equipment/Sales
Depreciation/PPE
Net Working Capital/Sales
Create a timeline for the next seven years.
Forecast future sales based on the most recent year's total revenue growing at the five-year growth rate from Yahoo! for the first five years and the long-term growth rate for years 6 and 7.
Use the average ratios computed in part (a) to forecast EBIT, property, plant and equipment, depreciation, and net working capital for the next seven years.
Forecast the free cash flow for the next seven years using the first equation in the slide "Discounted Free Cash Flow Model."
Determine the terminal value for all of the free cash flows for years 8 and beyond. The terminal value is the present value as of t=7.
Estimate the stock price per the equations on slide "Discounted Free Cash Flow Model." This the primary output of this section of the assignment.