Quiz #1 Fin3313 Fall 2018 Due: Wednesday, October 10 at 11am CT.

Instructions: Turn in an electronic version of the QUIZ (page 2 and 3 of this document only) through Assignments in TRACS, a good photo of these pages are OK (or scan). Answer all questions, be concise and to the point with your answers. Show all of your work in the numerical problems to get credit and box your final numerical answers.

For this quiz you will need to download the excel file TBILL/Q1.xlsx posted in TRACS in the folder: Quizzes. This file contains the monthly interest rate given by the 1-year Treasury bill.

STEP 1: EXCEL and the Data. Download data from finance.yahoo.com and organize it in SINGLE

excel

a)

b)

c)d)

e)f )g)

h)i)

file.

Go to finance.yahoo.com and in the Quote Lookup tab on the right side of the screen type Home Depots ticker: HD. Then go to the Historical Data tab. Change the frequency to Monthly and the time period to an start date of 10/1/2013 (longer timeframe than the valuation example made in class), then click Apply. Then download the data into Excel (use the Download Data button).

Open the Excel file you just downloaded, delete all columns except Date and Adjusted Close, compute

the monthly returns. Note: Monthly returns will just be the change in monthly prices (assume no

dividends) = P1P0 .P0

Do the same as in a) and b) for the S&P 500 ETF, ticker: SPY. SPY is our proxy for the Market. Open the attached Excel file TBILL/Q1.xlsx, and merge all 3 files into a single Excel sheet, take care to match the dates and label the respective returns. In separate columns compute the Excess returns of Home Depot and the Excess returns of SPY (respectively: HD - Tbill, SPY - Tbill).

Use the SLOPE command in Excel to compute the slope between the two series you computed above. This is Home Depots beta. (Hint: In excel, use the =slope() command). Again, go to finance.yahoo.com and in the Quote Lookup tab on the right side of the screen type Home Depots ticker: HD. Then go to the Financials tab. Click on Balance Sheet and Income statement, and for the most recent year get:

Debt (from balance sheet) = Long term debt + Short/Current Long Term Debt.

Cash (from balance sheet) = Cash and Cash equivalents.

EBITDA (from income statement) = Gross Profits - Selling, General, and Administrative expenses.

For the last 3 years of Home Depot and using their financial statements, estimate the Profit margins

(Net Income), Total Asset Turnovers ( Sales ), and Equity multipliers (T.Assets).Sales T.Assets T.Equity

For the last 3 years of Home Depot and using their financial statements, estimate the ROE. Use Dupont analysis: ROE = Profit margin Total Asset turnover Equity Multiplier.

1

Name:

STEP 2: Answer the following questions and submit them through TRACS in Assignments. Print page 2 and 3 of this document only, solve by hand, then take a good photo (or scan) of them and upload them into Assignments in TRACS. Answer all questions below, be concise and to the point. Show all of your work in the numerical problems (#1, #2, #3, #4, #10) to get credit and box your final numerical answers.

1. Using your Beta estimate from Step 1, and assuming the risk-free rate is 2.58% and the expected market return is 11%; what is Home Depots equity cost of capital rE according to the CAPM?

2. Now use rE (from #1) to find the stock price of Home Depot implied by the dividend discount model (Price = Div1 ). If Home Depots dividend for next year is $4.12 (this is Div1), and all dividends are

rE g expected to grow by 7.60% per year, what should be Home Depots current stock price?

3. Based on a comparable firms P/E of 19.19, if the EPS of Home Depot is 9.56, then what is an estimate of Home Depots stock price?

4. Based on a comparable firms EV/EBITDA of 13.43 and if Home Depot has 1,140 million shares outstanding then what is an estimate of Home Depots stock price? Use Home Depots EBITDA, CASH and DEBT from the Data downloaded in the STEP 1 in part g).

5. Home Depots current stock price (as of Sept. 24, 2018) is $208.18. Based on your analysis above, would you buy Home Depots stock?

2

Again, Name: FINANCIAL ANALYSIS: 6. Based on the profit margins for the years 2016, 2017, and 2018 (estimated in Step 1 part h)). What pattern do you see? Explain.

7. Based on the total asset turnover for the years 2016, 2017, and 2018 (estimated in Step 1 part h)). What pattern do you see? Explain.

8. Based on the equity multipliers for the years 2016, 2017, and 2018 (estimated in Step 1 part h)). What pattern do you see? Explain.

9. Looking at the ROE for the years 2016, 2017, and 2018 (estimated in Step 1 part i)), and based on your answers on #6, #7, and #8, which is the driving force behind the trend or pattern of ROE? Tip: Use Dupont analysis

10. Internal growth rate identifies the maximum rate at which the firm can grow based on its existing retained earnings only and without external financing (i.e. borrowing or selling stock). Internal growth rate = ROA Retention Rate. If Retention rate for the most recent year is about 55%. Then what is the average internal growth rate for the most recent year?

11. Based on your Internal growth rate from #10, if the average analyst expects Home Depot to grow 15% on average for the next five years, then how can Home Depot achieve this growth? Is this consistent with your answer in #8 and #9?

3