Companies to choose from:
1. WALMART STORES INC.
2. HOME DEPOT
3. COSTCO WHOLESALE CORPORATION.
4. TAGET CORPORATION.
FORMATTING AND PRESENTATION:
The entire project should be contained in one Excel file. This is not a term paper, but some narrative will be necessary. There are various ways to insert narrative into Excel, from using comments to merging cells and wrapping text to inserting whole Word documents or other objects.
I am not going to specify exact guidelines for how you should format your Excel sheets, but everything should be laid out in a clear and logical manner. You will need to use more than one “sheet,” and each sheet should be labeled. Excel formulas should be used in the most efficient way possible.
Pretend that I am either your boss or an important client, instead of an instructor who is obligated to try to figure out what you have done even if it is not presented clearly. Get right to the point when explaining your work. Focus on the information that matters most. More is not necessarily better. Don’t make presentation an afterthought
GENERAL DESCRIPTION OF PROJECT:
The assignment is to perform a financial and business analysis of a large, publicly traded U.S. retailer. I will provide a list of firms for you to choose from. There are several reasons that I am limiting your choices to a small group of large U.S. retailers. First, I want to make it easy to compare student work, and some types of firms are easier to analyze than others. Second, the operations of retailers are relatively simple to understand, so students with deep knowledge of a particular firm or industry have less of an advantage than they might have if I let students pick any firm. Third, the group of firms that I have selected share certain characteristics that allow students to apply some of the material covered in the course: they use some debt, they distribute some cash to shareholders, they need to invest in net operating assets, they have certain corporate governance issues to confront, etc. Finally, I want to stick with U.S. firms because it is relatively easy to obtain free, up to date, and accurate financial data on publicly traded U.S. firms.
SPECIFIC REQUIREMENTS OF THE PROJECT:
1. First, identify the firm’s closest publicly-traded U.S. competitor. This is the firm that is most similar in terms of its business model and its size in terms of both revenues and enterprise value. Then, analyze the firm’s recent financial performance and current financial health, using the techniques learned in prerequisite courses or on your own. Use at minimum, the last five years of income statements, balance sheets, and cash flow statements. If the last set of annual statements doesn’t bring you up to date, also construct trailing-twelve month (ttm) income statements and cash flow statements and use the latest available quarterly balance sheet. Compute and analyze ten to twenty key ratios and two or three growth rates of key variables over the period. Focus your discussion on ratios or growth rates that have changed a great deal in recent years or that differ greatly from competitor metrics.
2. Analyze the firm’s competitive environment. You may use a framework such as Porter’s five forces or a SWOT analysis to organize your thoughts if you wish, but that isn’t required. You don’t need to go into as much detail here as you would in a strategic management class. Focus on the most important issues. If you do use one of the above frameworks, don’t include the parts that are irrelevant or only marginally relevant. Your conclusions from this analysis, along with your analysis of the firm’s recent financial performance, should inform your financial projections.
3. Project the firm’s financial statements (income statement, balance sheet, and statement of cash flows) at least three years, but no more than ten years, into the future, using the techniques learned in the course. It is very likely that there will be some items on the firm’s historical financial statements that you don’t understand. Read the notes to the financial statements if necessary. Items that are not material may be ignored or consolidated into a “miscellaneous” category.
4. Estimate the firm’s future free cash flows in perpetuity using your projected financial statements and the techniques learned in this course and other finance courses.
5. Estimate the firm’s weighted average cost of capital (WACC) using the techniques presented in the course. Be sure to explain how you arrived at your estimates of all components of the WACC.
6. Estimate the firm’s intrinsic value per share using the corporate valuation model (FCF model) presented in the course. Then, calculate some simple valuation multiples (trailing P/E ratio, EV/EBITDA ratio) that are implied by your estimated value, and use these calculations and other information to evaluate how reasonable your estimate is. Is the stock a buy or a sell? Explain why in one sentence.
7. Evaluate the firm’s capital structure policies. Many, if not most, firms don’t explicitly describe their target debt ratio or other aspects of their capital structure policies. If that is the case for your firm, you may infer them based on what they have actually done. Focus on evaluating any major changes that might have been implemented in recent years. Could management increase the value of the firm with a different policy?
8. Evaluate the firm’s payout policies. As with capital structure policies, firms don’t always explain their policies on dividends and repurchases explicitly. Focus on evaluating any major changes that might have been implemented in recent years. Could management increase the value of the firm with a different different policy?
9. Evaluate the firm’s corporate governance. How is the CEO paid, and how does it compare to the competition? Are there any other aspects of the firm’s corporate governance that raise a “red flag?”
10. Analyze a scenario in which the firm acquires its closest competitor. Following are some instructions, assumptions, and hints for this part:
*Remember that an acquisition of a whole company can be thought of as a capital budgeting “project” and evaluated accordingly.
*Assume that the acquisition is made for cash, for a 20% premium to the current stock price, with the firm financing the acquisition in a manner consistent with its current capital structure policy.
*As you did with the firm before the acquisition, project the next three to ten years of financials and free cash flow for the post-acquisition combined entity and estimate the value per share of the firm after the acquisition. What is the net present value (NPV) of the acquisition? What is the payback period?
*When doing your projections, assume that there will be some cost benefits from the acquisition. This is frequently described as “synergy” and typically takes the form of lower “overhead,” or general and administrative expenses as a percentage of sales. If you like, you may also assume that some of the acquired firm’s stores that overlap geographically will be closed, which should reduce overall sales but increase margins.
*Don’t worry too much about being stone cold accurate on this part – real merger projections from investment bankers never are. Just clearly explain your assumptions and conclusions.