Explore BrainMass
Share

Explore BrainMass

    Create a timeline in Excel for five years.

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    "You are a new analyst for a large brokerage firm. You are anxious to demonstrate the skills
    you learned in your MBA class and prove that you are worth your attractive salary. Your
    first assignment is to analyze the stock of the General Electric Corp. Your boss
    recommends determining prices based on both the dividend-discount model and discounted
    free cash flow valuation methods. The expected return on a new investment is 12%.
    However, you are a little concerned because your finance professor has 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. Good luck with that!
    1- Go to Yahoo! Finance (http://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:
    a. The current stock price (last trade) at the top of the page
    b. The current dividend amount, which is in the bottom-right cell in the same
    box as the stock price.
    2- Next click on "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:
    a. The number of shares, of stock outstanding
    b. The Payout ratio
    3 .Next click on "Analyst Estimates" from the left side of the page. From the Analyst
    Estimates page find the expected growth rate for the next 5 years and enter it onto your
    spreadsheet. It will be near the very bottom of the page.
    4. Next click on "income Statement" near the bottom of the menu on the left. Place the
    cursor in the middle of the income statement and right-click. Select "Export to Microsoft
    Excel." Copy and paste the entire three 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 GE. Keep all the different statements in the same Excel worksheet.
    5. To determine the stock value based on the dividend-discount mode:
    a. Create a timeline in Excel for five years.
    b. Use the dividend obtained From Yahoo! Finance as the current dividend to
    forecast the next 5 annual dividends based on the five-year growth rate.
    c. Determine the long-term growth rate based on GE's retention ratio and the return on
    new investment.
    d. Use the long-term growth rate to determine the stork price for year five
    (using dividend discount model)
    e. Determine the cost of equity using the CAPM model. (note: use
    http://finance.google.com/finance) to get the beta of the stock. Assume risk free rate =
    3% and market risk premium = 7%.
    f. Determine the current stock price using (using dividend discount model)
    6. To determine the stock value based on the discounted free cash flow method:
    a. Forecast the free cash flows (for seven years) using the historic data from
    the financial statements downloaded From Yahoo! To build your forecast, compute
    the three-year average of the following ratios:
    i. EBIT /Sales
    ii. Tax Rare (Income Tax Expenses /Income Before Tax)
    iii. Property Plans and equipment/Sales
    iv. Depreciation Expenses/ Property Plans and Equipment
    v. Net Working Capital /Sales
    b. Create a timeline for the next seven years
    c. Forecast future sales based on the most recent year's total revenue growing
    at the five year growth rate from Yahoo for the firs five years and the longterm
    growth rate for years six and seven
    d. Use the average ratios computed in pare (a) to forecast EBIT, Property
    Plans and Equipment, depreciation, and net working capital for the next
    seven years.
    e. Forecast the free cash flow for the next seven years
    3
    f. Calculate the WACC by calculating the weights of debt and equity and the cost
    of debt and equity.
    To Calculate the weights: determine the amount of debt and equity,
    calculate the total capital (sum of debt and equity), then, calculate the weights
    of debt and equity.
    To calculate the cost of debt, calculate the after tax cost of debt using the
    information on http://bonds.yahoo.com/ (note: you should search by the
    company name "GENERAL ELECTRIC CO"). Assume a marginal tax rate of 30%.
    Cost of equity is determined based on the CAPM model (as in 5-e)
    g. Determine the value of the firm based on discounted free cash flow
    h. Determine the value of equity by subtracting long term debt from the firm
    value
    i. Determine the stock price
    7. 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 General
    Electric's stock based on your price estimates?
    8. Explain to your boss why the estimates from the two valuation methods differ."

    © BrainMass Inc. brainmass.com October 10, 2019, 4:18 am ad1c9bdddf
    https://brainmass.com/economics/bonds/create-timeline-excel-five-years-455109

    Attachments

    Solution Summary

    The expert creates a timeline in Excel for five years.

    $2.19