I need help with Financial Modeling Homework #5
We are analyzing the stock of the General Electric. We will estimate prices based on both the dividenddiscount 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 timevalue of money calculations, we are using annual cash flows which we will assume take place one year from now.
Save your time  order a paper!
Get your paper written from scratch within the tight deadline. Our service is a reliable solution to all your troubles. Place an order on any task and we will take care of it. You won’t have to worry about the quality and deadlines
Order Paper 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 bottomright 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 dividenddiscount 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 fiveyear growth rate. You may allow for fractions of a penny in dividend estimates.

Determine the longterm 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 longterm 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 longterm growth rate and shortterm growth rates would produce the price you are currently observing? Please create a column of shortterm growth rates ranging from 3% to 9% incremented by 1%. For each value calculate the longterm 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 threeyear 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 fiveyear growth rate from Yahoo! for the first five years and the longterm 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.

Business & Finance homework help