FIN 550: Milestone #1 Instructions
As I mentioned in my Week #2 announcement posted on Monday, 2/19, the Excel spreadsheet you find in the Final Project Workbook is locked and neither the instructor nor students are permitted access in order to unlock it. That said, you are surely allowed to use Excel (or a financial calculator) to determine the present value of the cash flows given on page 43 of Home Depot’s 10-K financial statement. The link to HD’s 10-K is:
https://www.sec.gov/Archives/edgar/data/354950/000035495015000008/hd-212015x10xk.htm
Also please note that the data (cash flows) you will be using are the Capital Leases column for the years 2015 through 2019 (those values are $113m, $111m, $108m, $101m, and $97m)
and $880m thereafter through 2097. Therefore you have a total of six (6) cash flows in your analysis. In truth, these lease payments are not free cash flows per se however we will be assuming them to be FCF’s for the years in question and then finding the present value of the cash flows back to 2014 to find the value of Home Depot at the interest (discount) rates outlined below.
NOTE: Assume this aggregate $880m is a one-time cash flow that occurs in the year 2020—I will note this in a bit more detail in the instructions below.
Following the FIN550 Milestone One Guidelines and Rubric document, please follow these specific instructions for the critical elements you must address:
- Time Value of Money
- Calculate the following time value of money figures:
- Calculate the present value of the company in 2014 based on the given interest rate and expected revenues (cash flows) over time. Assume the cost of capital is 8% initially as noted in your workbook.
NOTE/HINT: Assume each of the six (6) cash flows are end-of-year cash flows so you will be discounting each of the cash flows back to the year 2014 and adding them up. For example, this means the 2015 cash flow of $113m will be discounted one year, the $111m will be discounted two years and so on for the remaining three years of cash flows. Similarly, as per my
NOTE above, assume the aggregate $880m cash flow for the years after 2019 occurs in 2020, so this $880m cash flow needs to be discounted back to 2014 as well. Financiers call this $880m cash flow the “horizon value” of the firm, something we will discuss later this term. The sum of the PV of these six cash flows will be your total value of the firm (HD) in 2014.
- Suppose the risk of the company changes based on an internal event. Now recalculate the present value of the company in 2014 based on the higher cost of capital. Here, assume the level of firm risk increases so that the new cost of capital is 10%.
- Suppose that a potential buyer has offered to buy this company in five years (i.e., in 2019). Based on the present value you calculated above assuming a higher level of risk (i.e., the 10% cost of capital), what would be a reasonable amount for which the company should be sold at that future time?
Hint: Do not lose sight of the fact that we are talking about the year 2019
after five (5) years (or cash flows) have elapsed, so therefore think about the cash flows which remain from that point forward.
- What are the implications of the change in present value based on risk? In other words, what does the change mean to the company, and how would you, as a financial manager, interpret it? Be sure to justify your reasoning.
- Based on the future value of the company that you calculated in A 1.3 above (in the Year 2019), and being mindful of the need to effectively balance portfolio risk with return, what recommendation would you make about purchasing the company as an investment at the 2019 price? Be sure to substantiate your reasoning.
Guidelines for Submission (as per the rubric):
Your paper must be submitted as a 2-to-3-page Microsoft Word document, not including
your calculations, which should be completed in the Final Project Student Workbook. Use
double spacing, 12-point Times New Roman font, and one-inch margins. Sources should be cited according to APA style.
