Home Depot
In Excel, recreate the reported annual Income, Balance Sheet and Cash Flow statements for the last 5 fiscal years from the Company’s 10-K. Research the Company to understand the drivers of the business.
Based on your analysis, project each statement for the next 5 fiscal years. Show the following:
1. Ensure your statements are inter-connected (so changes in one are properly reflected in all) and auto balancing. Clearly show the major assumptions driving your numbers and your basic rational for those assumptions;
2. Show the company’s historic and forecasted EBITDA and EBITDA margin;
3. Show the company’s historic and forecasted diluted earnings per share (Diluted-EPS)
4. Show the company’s historic and forecasted Dupont ROE analysis
5. Show the company’s historic and forecasted free cash flow to equity holders (FCFE) and FCFE margin
6. Calculate Beta for your stock using daily returns for your company and the S&P500. Start with the closing prices on Friday, March 6, and go back 10 years
7. Calculate a discount rate for your stock using the Capital Asset Pricing Model (CAPM). Use the Beta for your stock you calculated in question 6, assume the risk free rate is 2% and the equity risk premium is 6%
8. Calculate the fair market equity value of your stock by discounting back your forecasted free cash flow to equity holders (FCFE) and a reasonable assumption (with explanation) of your stock’s terminal value using the Gordon Dividend Growth model ;
9. Calculate the weighted average cost of capital (WACC) for your company based on the most recent reported fiscal year. Clearly show cost of debt (both before and after tax), cost of equity, and weight of debt and weight of equity
10. Calculate the fair market enterprise value of your Company by discounting back your forecasted free cash flow to the firm (FCFF) and terminal firm value by the WACC. What is the fair market equity value of the Company using this approach?