This is a comprehensive assessment of the material related to our first two class meetings. You are NOT being tested on material related to capital budgeting (NPV, IRR, etc.). That material will come be covered by your final exam. This assessment requires computations, analysis, and synthesis of data and concepts. You will be assessed on written components (text) and computations (numbers). The end product you submit for grading should be a SINGLE file (either a Word doc, an Excel spreadsheet, or a PDF file). Whatever type of file you choose to submit, it needs to be neatly formatted, thoroughly proofed, clearly written, and self-documenting. Obviously if you choose to submit a spreadsheet I can review your calculations more thoroughly - but formatting the written sections may be a bit of a challenge. If you submit a Word doc or a PDF file it will be easy to format the text, but creating and inserting tables may be a challenge. You will need to select the format that you find easiest to work with - as long as you meet the requirements above.
The file that you upload should be named YOUR NAME Mid-term Exam. Example: Jeff Jewell Mid-term Exam. If you choose to upload a spreadsheet your tabs should be clearly labeled. For the text sections you are free to write "efficiently" just as you do in your homework assignments. I ENCOURAGE you to use bullet points, numbered lists, etc. whenever appropriate. This will not only likely improve the clarity of your writing (by imposing some structure) - it will also make your papers easier to grade.
PLEASE NOTE: Point deductions for poor writing, poor proofing, poor formatting, etc. will be applied to your exam if appropriate. Whichever type of file you choose to submit, your project should contain the following sections:
I. Situation Analysis
This should be written section (text). It is NOT A SUMMARY of the case. Rather you should try to briefly pull together all of the relevant contextual information that will inform your later analysis. This information can be specific to Lowe's and Home Depot, it can be general info about the lumber/housing/construction markets or it can be information about the economy as a whole. This should NOT just be a data dump where you try to overwhelm me with information. The point is for you to identify the most salient facts in the case and pull them together into one place. You DO NOT need to include specific analysis about your ratio or forecast computations. Those items will be included in later sections.
II. Lowe's Ratio Calculations
This is a computation section. Case Exhibit 7 contains ratio calculations for Home Depot. I want you to recreate this table EXACTLY (identical computations) for Lowe's. Make sure you use the same mathematical computations that are shown in Exhibit 7. (The formulas are either specified or should be clear from the name of the computation. Obviously if there is any doubt about a computation you can test your formula by trying to duplicate the numbers for Home Depot in Exhibit 7.)
III. Ratio Analysis
This is a text section. Here I want you to compare and contrast the Home Depot numbers in case Exhibit 7 with your Lowe's numbers (from part II). You DO NOT have to comment on every single ratio.
- Identify the most interesting and informative differences between the companies.
- Draw some conclusions about their performance over this five-year period. Which company do you think has been better managed? Why?
- Make some inferences about where these companies are headed over the next five years.
IV. Lwe's Forecast
a. Forecasting Assumptions
This is a text section. The top part of case Exhibit 8 shows you all of the assumptions that were necessary for the 5-year forecast of Home Depot. You will need to make assumptions about the identical set of variables (growth in new stores, gross margin, etc.). There are 13 variables that require assumptions and each variable needs assumptions for 5 years - so there are 65 total assumptions (13 x 5). Obviously I don't want you to describe your logic for all 65 assumptions (though you will have to come up 65 specific assumptions in the computation section of the forecast). Rather I want you to briefly describe the logic that you will use to craft your assumptions. Some of these descriptions may be very short (like for the Income-tax rate), while others may require more explanation.
Obviously you can get some ideas about what the assumptions should look like by studying the Home Depot assumptions - but there is certainly no guarantee that the Lowe's assumptions should have the same numerical values (or even the same patterns) as the Home Depot assumptions. There is not a single "correct answer" for most of these assumptions - though there are certainly incorrect answers. Most of these assumptions will have a "reasonable range" of correct answers. I will be looking to see if the logic you use to craft your assumptions is sounds and defensible.
You will want to make sure that your assumptions are based on:
- What we know about Lowe's historic performance
- What we know about Lowe's expected future performance
- What we know about the lumber/construction/housing markets
- What we know about the economy as a whole
Obviously there are a lot of moving parts there. Part of your task is to recognize when we have information that is in conflict and to figure out how to resolve that conflict. For example (I am just making these numbers up) if Lowe's historic growth rate is 10% per year, but management thinks the firm will 20% per year in the future you will need to reconcile those numbers. Is it reasonable to expect faster future growth? What facts do we have that support faster future growth?
I know that sounds like you might be able to write a page on each of these thirteen variables, but that is not what I am after. You should be able to boil everything down to just a few lines (or less) for each item. You do not need to give me specifics for each year - you just need to convey the patterns you think should exist. For example (again I am just making these facts up):
- Inventory Turnover: Lowe's has historically averaged XX.XX in Receivables Turnover. However the firm has a plan in place to implement best practices and to put a new inventory management system in place. So I expect this ratio to show steady improvement over the next three years before plateauing.
- Payables/COGS: The firm seems to be happy with its payables policy and management. There are no facts to support a change in this ratio. So I think it should be constant over the next five years.
Again, those are just examples that I made up off the top of my head, but they should give you an idea of what I am looking for here.
b. Lowe's Forecast Computation
Obviously this is a computation section. Case Exhibit 8 shows the five-year forecast for Home Depot. I want you to recreate this table exactly for your Lowe's forecast. Make sure you specify all of your assumptions at the top of the table (all 65 of them). All of the numbers for 2002 - 2006 in the bottom part of the table (the Forecast section) are a direct result of the assumptions (combined with the relevant 2001 data).
Your assumptions should mathematically determine your forecast. In other words you should set the table up so that if you change one assumption in one year the entire table automatically updates. This means your entire "Forecast section" (except for the 2001 data) should be composed of formulas (not hard coded numbers). Obviously if you submit a spreadsheet I'll be able to see your formulas. If you submit a Word doc or a PDF you DO NOT have to include a table showing all of your formulas - I'll just trust that you have them there. You will need to make sure you have the table set up correctly (with formulas) so that you can easily answer the questions in the last part of the exam.
V. Forecast Analysis
a. Forecast Comparison (20 points)
This is a text section. Compare and contrast the Home Depot Forecast in Case Exhibit 8 to the Lowe's forecast you just completed. Like the ratio analysis you do not have to compare every single element of the forecast.
- Identify the most interesting and informative differences between the companies. If significant differences in assumptions are driving different forecast outcomes make sure you explain why those different assumptions make sense.
- Draw some conclusions about where the companies stand at the end of this five-year period. What is each company's market share forecast to be? Do these numbers make sense?
- Draw a conclusion about which company you would rather invest in today based on the analysis.
b. Testing the Forecast (20 points)
This primarily a computation section, though there is a bit of text required as well. Here we are going to do some sensitivity analysis on your Lowe's forecast. If you have built your forecast properly this should be very easy. For each item below you should present numbers and a brief text explanation.
- Reduce your sales growth forecast by 10% per year. (NOTE: Not 10 percentage points, 10 percent. So if your growth forecast for a year is 20%, change it to 18% = 20% - 2%.) What impact does this change have on Return on Capital each year?
- Now increase your sales growth forecast by 10% per year. What impact does this have on ROC?
- Now repeat this experiment for Gross Margin and Inventory Turnover (increase each by 10% and observe the effect on ROC, then decrease each by 10%).
- What are these tests telling us about the forecast? How sensitive is our forecast to your assumptions?
- NOTE: I want to see the exact ROC for each year for each of these experiments. If you want to build a single table that shows all of the results then discuss the table as a whole that will be fine.