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.
Your spreadsheet 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. All data should be formatted in a manner that makes sense (e.g., the sales growth rate should be formatted as a percentage).
Excel formulas should be used in the most efficient way possible. For example, instead of typing numbers into a cell to calculate a ratio, type a formula that refers to the cells that the financial statement data items are located in.
Proceed as though 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. Clearly explain what you have done. Get right to the point. More is not necessarily better. Don't make formatting and 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. The firms that you may choose from are listed below. There are several reasons that I am limiting your choices to a small group of large U.S. retailers. First, the operations of retailers are relatively simple to understand, so it is feasible to arrive at a view of the firm's competitive situation even if you don't start out with a deep knowledge of the industry. Second, the group of firms that I have selected share certain financial characteristics that allow students to apply some of the material covered in the course. Third, 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.
| Company name |
Ticker |
| Lowe's Companies, Inc. |
LOW |
| Home Depot Inc |
HD |
| Ross Stores, Inc. |
ROST |
| TJX Companies Inc |
TJX |
| Bed Bath & Beyond Inc. |
BBBY |
| Gap Inc |
GPS |
| Costco Wholesale Corporation |
COST |
| Wal-Mart Stores, Inc. |
WMT |
| Target Corporation |
TGT |
| Kroger Co, The |
KR |
| Macy's, Inc. |
M |
| Nordstrom, Inc. |
JWN |
| Dollar General Corp. |
DG |
| Best Buy Co Inc |
BBY |
| Dicks Sporting Goods Inc |
DKS |
Specific Requirements of the Project
1. Identify the firm's closest publicly-traded U.S. competitor. This is the firm that is most similar in terms of the markets that it serves and is closest in size in terms of revenues and enterprise value. Explain your choice and mention any other possible close competitors that you may have considered. I recommend completing this requirement by the end of week 2 of the course.
2. Perform the computations for a cross-sectional and time series ratio analysis of your firm and the competitor using the last 10 years of data. If the last set of annual statements doesn't bring you up to date, also use the trailing-twelve-month (ttm) income statements and cash flow statements and use the most recent quarter's (mrq) balance sheet In addition, calculate the growth rates of a few key variables such as sales. I recommend completing this requirement by the end of week 3 of the course.
3. Analyze the firm's recent financial performance and current financial health, using the ratio analysis techniques learned in the course. Be sure to touch on liquidity, capital structure, asset management efficiency, and profitability. Focus your discussion on ratios or growth rates that have changed a great deal in recent years, that differ greatly from the competitor's metrics, and that you believe to be important drivers of the firm's value. I recommend completing this requirement by the end of week 6. It might be fruitful to think about the firm's financial and competitive situation (item 4 below) at the same time.
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. I recommend completing thisrequirement by the end of week 6. It might be fruitful to think about the firm's financial and competitive situation at the same time.
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. I recommend completing this requirement by the end of week 10.
6. Analyze a scenario in which your firm acquires Barnes and Noble, Inc. (BKS). Hint: 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. Your firm has to retire any outstanding debt, but can also use any excess cash or investments to offset the purchase price. Assume also that the acquisition of BKS is considered an average risk project for your firm. Project the incremental cash flows that the acquisition will provide your firm with in perpetuity. If you want to add some extra challenge, you may 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. Lastly, calculate the net present value (NPV) and the payback period for the acquisition. I recommend completing this requirement by the end of week 12.
7. Project the firm's income statement and balance sheet one-year forward, using the techniques learned in the course. If there are items on the firm's historical financial statements that you don't understand, read the notes to the financial statements in the original filings. Items that are not material may be ignored or consolidated into a "miscellaneous" category. I recommend completing this requirement by the end of week 13. The remainder of the project should then be completed as soon as possible.
8. Estimate the firm's free cash flows in perpetuity, the value of the firm's operations, and the intrinsic value per share of the firm's common stock using the free cash flow model. Hint: the "Free Cash Flow Valuation in Excel" video presented with the Chapter 7 material may be helpful. If you follow that model, there should be some consistency between the values that you use for the key value drivers in the model and the values that you estimate for your firm's projected financial statements for next year in item 7 above.
9. Perform a scenario analysis with 3 different valuation scenarios (base case, downside case, upside case) using Excel's Scenario Manager tool. Don't make it too complicated. Pick two or three key value drivers to vary in the scenario analysis. Put the results in a separate, well labelled table. Then give your best point estimate of the intrinsic value per share. Is the stock a buy or a sell? Explain why in one to three sentences.
10. Calculate the trailing P/E ratio and EV/EBITDA ratio that are implied by your estimated intrinsic value and use these calculations to as a "reality check" on your intrinsic value estimate. For example, if your estimate of intrinsic value implies a PIE of 100, then you should be able to explain why that makes sense in this one particular case or go back and come up with a more reasonable estimate of intrinsic value.
Data Sources
Following are some data sources that might be helpful for the project:
1. The SEC's Edgar website for official corporate filings:
https://www.sec.gov/edgar/searchedgarkompanysearch.html. The most recent annual report on form 10-K usually provides a great deal of useful information about the company's business in addition to presenting its financial statements. The quarterly report on form 10-Q is typically not as detailed as the 10-K but it does contain updated financial statements, notes to the statements, and some of the other information that appears in the 10-K. Most of you will probably use Morningstar data for most of your analysis, but it occasionally contains errors and it may not have every data item that you need, so you will want to spend some time with the firm's actual filings.
2. Morningstar, available online through the Fayetteville State library, has financial statement data and a variety of other data going back 10-years, downloadable in Excel.
Note that Morningstar transforms and aggregates original financial statement data to fit its categories. This usually isn't a problem.
3. Seeking Alpha has transcripts of the quarterly conference calls held by most public companies and articles on stocks written by professional and amateur analysts:
https://seekingalpha.corni. The firm's most recent conference call can provide a quick way to get up to speed with some of the major issues facing the company. To find the transcript, click on the "Earnings" link for your company.
4. Professor Aswath Damodaran's website has a variety of useful data, spreadsheets, and other materials: https://pages.stern.nyu.edukadamodar/
5. The website of the company that you analyzing and the websites of its close competitors. Investor presentations sometimes posted in the Investor Relations section of the website are often very useful.