Data Case..I HAVE ATTCHED AN EXCEL AS AN EXAMPLE (IT WAS DONE ON A DIFFERENT STOCK_GE) MINE IS FOR HD

Create a timeline in Excel for five years.

"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 Home Depot 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 Home Depot (HD). 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 HD. 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 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/ 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 Home Depot's stock based on your price estimates?

8. Explain to your boss why the estimates from the two valuation methods differ."

1 a) b) Current stock price Current dividend $24.95 4.00PM EST February 6th 2014. $0.76 2 a) b) Number of shares outstand Payout ratio 109,000,000 58.00% Expected growth rate for ne 8.48% 3 General Electric Company (GE) -NYSE 24.95 0.43(1.75%) Feb 6, 4:00PM EST|Pre-Market : 25.11 Up0.16 (0.64%) 8:58AM EST - Nasdaq Real Time Price Add to Portfolio Income Statement View: Annual Data | Quarterly Data Period Ending Total Revenue Cost of Revenue All numbers in thousands 31-Dec-12 147,359,000 77,167,000 Gross Profit 70,192,000 Operating Expenses Research Development Selling General and Administrative Non Recurring Others Total Operating Expenses 36,387,000 3,891,000 - Operating Income or Loss 29,914,000 Income from Continuing Operations Total Other Income/Expenses Net Earnings Before Interest And Taxes Interest Expense Income Before Tax Income Tax Expense Minority Interest 29,914,000 12,508,000 17,406,000 2,504,000 -223,000 Net Income From Continuing Ops Non-recurring Events Discontinued Operations Extraordinary Items Effect Of Accounting Changes Other Items Net Income Preferred Stock And Other Adjustments Net Income Applicable To Common Shares Sign Up for a Free Trial to EDGAR Online Premium! 14,902,000 -1,038,000 - 13,641,000 13,641,000 Get the critical business and financial information you need for more than 15,000 U.S. public companies. Sign Up Now - Learn More Currency in USD. Privacy - About Our Ads - Terms - Send Feedback - Yahoo! - ABC News Network Quotes are real-time for NASDAQ, NYSE, and NYSE MKT. See also delay times for other exchanges. All information provided US Financials data provided by Edgar Online and all other Financials provided by Capital IQ. 95941440 - Nasdaq Real Time Price l numbers in thousands Ratios 31-Dec-11 31-Dec-10 31-Dec-12 147,288,000 149,567,000 5.36% 71,190,000 74,725,000 76,098,000 - 31-Dec-11 4.93% 31-Dec-10 Average 3.96% 4.75% Working capital/Sales 74,842,000 37,362,000 3,951,000 38,033,000 7,085,000 - - - - 34,785,000 - 29,724,000 20.30% 23.62% 19.87% 14.39% 28.33% 7.32% 21.26% EBIT/Sales 34,785,000 14,528,000 20,257,000 5,738,000 -292,000 29,724,000 15,537,000 14,187,000 1,039,000 -535,000 14,519,000 13,148,000 -76,000 - -969,000 - 14,151,000 -1,031,000 11,644,000 -300,000 13,120,000 11,344,000 16.68% Income tax/Income before tax public companies. exchanges. All information provided "as is" for informational purposes only, not intended for trading purposes or advice. Neither Yaho apital/Sales x/Income before tax poses or advice. Neither Yahoo! nor any of independent providers is liable for any informational errors, incompleteness, or delays, or fo pleteness, or delays, or for any actions taken in reliance on information contained herein. By accessing the Yahoo! site, you agree not t hoo! site, you agree not to redistribute the information found therein. Real-Time continuous streaming quotes are available through ou s are available through our premium service. You may turn streaming quotes on or off. General Electric Company (GE) -NYSE 24.95 0.43(1.75%) Feb 6, 4:00PM EST|Pre-Market : 25.12 Up0.17 (0.68%) 8:59AM EST - Nasdaq Real Time Price Add to Portfolio Balance Sheet View: Annual Data | Quarterly Data Period Ending All numbers in thousands 31-Dec-12 Assets Current Assets Cash And Cash Equivalents Short Term Investments Net Receivables Inventory Other Current Assets Total Current Assets Long Term Investments Property Plant and Equipment Goodwill Intangible Assets Accumulated Amortization Other Assets Deferred Long Term Asset Charges 77,356,000 48,510,000 287,489,000 15,374,000 428,729,000 69,954,000 73,447,000 11,987,000 101,211,000 - Total Assets 685,328,000 Liabilities Current Liabilities Accounts Payable Short/Current Long Term Debt Other Current Liabilities Total Current Liabilities Long Term Debt Other Liabilities Deferred Long Term Liability Charges Minority Interest Negative Goodwill Total Liabilities Stockholders' Equity 28,532,000 131,515,000 61,356,000 221,403,000 236,084,000 99,446,000 -75,000 5,444,000 562,302,000 Misc Stocks Options Warrants Redeemable Preferred Stock Preferred Stock Common Stock Retained Earnings Treasury Stock Capital Surplus Other Stockholder Equity 702,000 144,055,000 -34,571,000 12,840,000 Total Stockholder Equity 123,026,000 Net Tangible Assets 37,592,000 Sign Up for a Free Trial to EDGAR Online Premium! Get the critical business and financial information you need for more than 15,000 U.S. public companies. Sign Up Now - Learn More Currency in USD. Privacy - About Our Ads - Terms - Send Feedback - Yahoo! - ABC News Network Quotes are real-time for NASDAQ, NYSE, and NYSE MKT. See also delay times for other exchanges. All information provided US Financials data provided by Edgar Online and all other Financials provided by Capital IQ. 95941443 q Real Time Price l numbers in thousands 31-Dec-11 Ratios 31-Dec-10 31-Dec-12 31-Dec-11 31-Dec-10 Average 84,501,000 78,943,000 47,374,000 43,938,000 307,957,000 329,204,000 13,792,000 11,526,000 453,624,000 463,611,000 66,450,000 103,099,000 72,625,000 64,388,000 12,068,000 9,971,000 113,422,000 106,724,000 - 718,189,000 747,793,000 29,546,000 56,943,000 166,869,000 147,977,000 57,911,000 67,328,000 254,326,000 272,248,000 243,459,000 293,323,000 102,401,000 55,271,000 -131,000 2,753,000 1,696,000 5,262,000 601,751,000 628,857,000 47.47% 13.36% 45.12% 13.82% 68.93% 9.49% 53.84% Property plant equipment/sales 12.22% Depreciation/Property plant equipment - - 702,000 702,000 137,786,000 131,137,000 -31,769,000 -31,938,000 9,719,000 19,035,000 116,438,000 118,936,000 31,745,000 44,577,000 ges. All information provided "as is" for informational purposes only, not intended for trading purposes or advice. Neither Yahoo! nor an plant equipment/sales on/Property plant equipment or advice. Neither Yahoo! nor any of independent providers is liable for any informational errors, incompleteness, or delays, or for any a ess, or delays, or for any actions taken in reliance on information contained herein. By accessing the Yahoo! site, you agree not to redis te, you agree not to redistribute the information found therein. Real-Time continuous streaming quotes are available through our prem vailable through our premium service. You may turn streaming quotes on or off. General Electric Company (GE) -NYQ 24.95 Feb 6, 4:00PM EST|Pre-Market : 25.12 Up0.17 (0.68%) 8:59AM EST - Nasdaq Real Time Price Add to Portfolio Cash Flow View: Annual Data | Quarterly Data Period Ending Net Income Operating Activities, Cash Flows Provided By or Used In Depreciation Adjustments To Net Income Changes In Accounts Receivables Changes In Liabilities Changes In Inventories Changes In Other Operating Activities Total Cash Flow From Operating Activities Investing Activities, Cash Flows Provided By or Used In Capital Expenditures Investments Other Cash flows from Investing Activities Total Cash Flows From Investing Activities Financing Activities, Cash Flows Provided By or Used In Dividends Paid Sale Purchase of Stock Net Borrowings Other Cash Flows from Financing Activities Total Cash Flows From Financing Activities Effect Of Exchange Rate Changes Change In Cash and Cash Equivalents Sign Up for a Free Trial to EDGAR Online Premium! Get the critical business and financial information you need for more than 15,000 U.S. public companies. Sign Up Now - Learn More Currency in USD. Privacy - About Our Ads - Terms - Send Feedback - Yahoo! - ABC News Network Quotes are real-time for NASDAQ, NYSE, and NYSE MKT. See also delay times for other exchanges. All information provided US Financials data provided by Edgar Online and all other Financials provided by Capital IQ. 95941457 Get Cash Flow for: All numbers in thousands 31-Dec-12 31-Dec-11 31-Dec-10 13,641,000 14,151,000 11,644,000 9,346,000 -356,000 3,117,000 -1,344,000 -1,274,000 7,899,000 9,185,000 -419,000 3,237,000 89,000 -1,168,000 7,255,000 9,786,000 1,364,000 7,025,000 -360,000 342,000 5,921,000 31,331,000 33,359,000 36,124,000 -15,126,000 -12,650,000 -9,800,000 6,872,000 14,630,000 21,758,000 19,556,000 17,902,000 20,478,000 11,302,000 19,882,000 32,436,000 -7,189,000 -6,458,000 -4,790,000 -204,000 -9,334,000 -3,896,000 -40,722,000 -29,160,000 -48,915,000 -2,959,000 -1,911,000 -3,985,000 -51,074,000 -46,863,000 -61,586,000 1,278,000 -841,000 -333,000 -7,163,000 5,537,000 6,641,000 blic companies. changes. All information provided "as is" for informational purposes only, not intended for trading purposes or advice. Neither Yahoo! n advice. Neither Yahoo! nor any of independent providers is liable for any informational errors, incompleteness, or delays, or for any ac ss, or delays, or for any actions taken in reliance on information contained herein. By accessing the Yahoo! site, you agree not to redistri e, you agree not to redistribute the information found therein. Real-Time continuous streaming quotes are available through our premiu ailable through our premium service. You may turn streaming quotes on or off. Current dividend Payout ratio Expected growth rate for next 5 years Return on equity Rention ratio Growth rate Required rate of return Dividend Present value factor @8.19% Present value of dividends Price at 5th year Present value of 5th year stock price Total value $0.76 58.00% 8.48% 11.63% 42.00% 4.88% 8.19% Risk-free rate 10-year US Beta Market return Year 1 Year 2 $ 0.82 $ 0.9243 $ 0.76 $ $ 28.38 3% 1.28 7% Year 3 0.89 $ 0.97 0.8543301995 0.789657 0.76 $ 0.77 Year 4 Year 5 Year 6 $ 1.05 $ 1.14 $ 1.20 0.72988 0.677441 $ 0.77 $ 0.77 $ 36.23 $ 24.54 3-year average EBIT/Sales 21.26% Income tax/Income before tax 16.68% Property plant equipment/sale 53.84% Depreciation/Property plant 12.22% Working capital/Sales 4.75% Sales EBIT Income taxes Property plant equipment Capital expenditure Depreciation Working capital Increase in working capital Free cash flow Horizondal enterprise value Present value Free cash flow Value Price Expected growth rate Return on equity Rention ratio Growth rate Discount rate Year 1 Year 2 $ 159,855,043.20 $ 173,410,750.86 $ 33,990,748.62 $ 36,873,164.10 $ 5,669,142.57 $ 6,149,885.86 $ 86,065,508.51 $ 93,363,863.63 $ 16,111,508.51 $ 7,298,355.12 $ 10,521,358.85 $ 11,413,570.08 $ 7,590,377.36 $ 8,234,041.36 $ (308,622.64) $ 643,664.00 $ 23,040,079.03 $ 34,194,829.20 $ $ 3,348,174,874.87 $ 29.26 0.9403799135 21,666,427.53 $ 0.8843143817 30,238,979.24 8.48% Cost of debt 11.63% 42.00% 4.88% 6.34% Year 3 $ 188,115,982.54 $ 40,000,008.42 $ 6,671,396.18 $ 101,281,119.27 $ 7,917,255.64 $ 12,381,440.82 $ 8,932,288.06 $ 698,246.71 $ 37,094,550.72 $ Year 4 $ 204,068,217.86 $ 43,392,009.13 $ 7,237,130.58 $ 109,869,758.18 $ 8,588,638.91 $ 13,431,387.00 $ 9,689,746.09 $ 757,458.03 $ 40,240,168.62 0.8315914817 30,847,512.39 $ 4.88% Year 5 $ 221,373,202.73 $ 47,071,651.51 $ 7,850,839.25 $ 119,186,713.68 $ 9,316,955.49 $ 27,062,489.33 $ 10,511,436.56 $ 821,690.47 $ 56,144,655.62 0.7820119257 31,468,291.75 $ Year 6 $ 232,186,398.19 $ 49,370,913.40 $ 8,234,321.35 $ 125,008,507.89 $ 5,821,794.22 $ 28,384,383.68 $ 11,024,878.19 $ 513,441.63 $ 63,185,739.88 0.735388307 41,288,123.24 $ Year 7 $ 243,527,775.00 $ 51,782,485.03 $ 8,636,535.01 $ 131,114,673.47 $ 6,106,165.58 $ 29,770,847.28 $ 11,563,399.39 $ 538,521.20 $ 66,272,110.53 $ 4,775,954,242.53 0.6915443925 0.650314456 43,695,744.10 $ 3,148,969,796.61 7 The actual $24.95 Stock price 28.37651 Stock price 29.26098 From the above calculation it is clear that the instrinc value of the stock is greater than the current stock price. should buy this company stock as the value of this stock will increase in the future. Thus, client should bu 8 With regard to dividend discount model, the growth in the dividends is being considered and cost of equity is t investor is being used as the discounting rate. By discounting future dividends and the price that is determine price is determined. Whereas, in case of free cash flow method, the entire earnings of the company is being tak sales, growth in assets, changes in working capital. In this case, the WACC of the comapny is being used and no discount model. Then terminal value is determined for the free cash flow and all the year cash flows are discou enterprise value. Both uses different variables in determining the intrinsic value and this has resulted in differe various factors like market sentimetns, the overall industrial projection, trading climate and so on will have imp these estimates provides information about the intrinsic value that is the actual worth of the stock, where as cu by hte stock market movements and not always as per the intrinsic value n the current stock price. Therefore investors should invest that is ure. Thus, client should buy GE stocks as per the estiamtes. red and cost of equity is the required rate of return for an equity he price that is determined using the long-term growth rate the f the company is being taken into consideration by projecting the apny is being used and not the cost of equity like that of dividend year cash flows are discounted using the WACC to determine the this has resulted in difference. In case of market price, there are e and so on will have impact on the stock price. Stock price using h of the stock, where as current stock price is greately influenced s as per the intrinsic value. The WACC is obtained from Risk-free rate 10-year US treasury yield Market return All other variables are as per yahoo finance.com http://www.stockresearching.com/2013/12/07/general-electric-fu http://www.bloomberg.com/markets/rates-bonds/government-bo http://www.stockresearching.com/2014/01/30/chesapeake-energ 2/07/general-electric-fundamental-analysis-wacc-cost-of-debt-and-cost-of-equity-ge/ s-bonds/government-bonds/us/ 1/30/chesapeake-energy-fundamental-analysis-wacc-cost-of-debt-and-cost-of-equity-chk/