Here are the minimal instructions/guidance for your final project for the semester.
- In “Valuation Reference Files” under “Course Documents” pay particular attention to the two Home Depot Files
Note: The NOA approach to Free Cash Flow to the Firm is used here that we talked about in the last lecture we had. I would like you to use what we have already discussed (i.e. use ‘NOPAT – change in Net PPE (i.e. capital expenditure spending – depreciation) – changes in Net Working Capital for operations’).
Perform the intrinsic valuation using the FCFF approach leading to the Market Value of Invested Capital to Enterprise Value (MVIC – Cash and Marketable Securities) to Equity Value (Enterprise Value – Long-term Term debt – PV of Operating Leases) to Value per Share (Equity Value / Shares Outstanding)
Perform a Market Multiple analysis to compare to your intrinsic value. A minimum of two and a maximum of three multiples should be used.
NOTE: THERE MAY BE SOME CALCULATION ERRORS IN THE SPREADSHEETS THAT ARE USED FOR REFERENCE. I WANT YOU TO USE WHAT WE HAVE LEARNED. THE EARLY 2000s SPREADSHEETS ARE THERE FOR CONTENT AND GUIDANCE RATHER THAN COPYING ALL OF THE FORMULAS, ETC. - Use the last day of the last fiscal year reporting as the Date of Value (Relying on Shares Outstanding from the 10k)
- In addition to presentable spreadsheets, you are to also provide a written analysis in lieu of presenting. The analysis will include:
An ExectiveSummary no longer than one-page. Less is more here but it must be able to stand alone and provide a busy top-level decision-maker with a complete briefing, what course of action to follow and a succinct description of why. Use whatever facts are necessary to show the significance and/or priority.
An Analysis that is 1-3pages listing the key assumptions you make in your analysis, your logic of your argument, and the use of each key assumption. Justify your assumption. Be specific but express your ideas clearly with support, evidence, explanation, and references if appropriate. The analysis portion should “build” to support your opinion/recommendation regarding the market value of Lowes on the last day of the Fiscal Year. Remember, only use information that was known and knowable at the time.
Include the exhibits to follow the Analysis. - This should be a professional work product provided as a single document
- In addition to submitting the “professional work product” please submit our valuation spreadsheets as well. Both will be graded.
>NewFCFF2Stage
. The firm is expected to grow at a higher growth rate in the first period.
. , and Working Capital needs during the high growth period.
. Expected growth rate in earnings during the stable growth period.
. Inputs for the cost of capital. (Cost of equity, Cost of debt, s on debt and equity)
=
n-cash Working Capital =
No 5 No No (Yes or No) (in percent) of the stock =
(in percent) (in percent) 5.50% No (Yes or No) (in currency) Yes (Yes or No) (in percent) Yes (Yes or No) No (Yes or No) =
0.00% (in percent) 0.00% (in percent) 100.00% (in percent) (in percent) No (Yes or No) 1.00 No (Yes or No) ( in percent) Yes (Yes or No) 6% 6% (in percent) Do not enter 6% (in percent) Yes (Yes or No) (in percent) No (Yes or No) Yes (in percent) =
$499.00 0.00% 12.50% 0.00% 100.00% 10.56% 10.56% Do not enter 10.56% Do not enter 10.56% 6.00% (in percent) 6.00% $3,247.38 9.70% 98.56% 3.93% 1.44% 9.62% $500.00 $1,822.00 $1,500.00 &C Two-Stage FCFF Discount Model
Two-Stage FCFF Discount Model
This model is designed to value a firm, with two stages of growth, an initial
period of higher growth and a subsequent period of stable growth.
For a richer version of this model, try the fcffginzu.xls spreadsheet.
Assumptions
1
2. The growth rate will drop at the end of the first period to the stable growth rate.
The user has to define the following inputs:
1. Length of high growth period
2. Expected growth rate in earnings during the high growth period.
3
Capital Spending
Depreciation
4
5
Weight
Inputs to the model
Current EBIT =
$5,186.00
Current Interest Expense =
$118.00
Current Capital Spending
$2,152.00
Current Depreciation & Amort’n =
$1,228.00
Tax Rate on Income =
28.49%
Current
Revenues
$16,70
1.00
Current
No
$3,755.00
Chg. Working Capital =
$499.00
Last year
Cash and Marketable Securities
$500.00
Value of equity options issued by firm =
$1,500.00
Book Value of Debt =
$1,479.00
$1,315.00
Book Value of Equity =
$12,941.00
$12,156.00
Weights on Debt and Equity
Is the firm publicly traded ?
Yes
( Yes or No)
If yes, enter the market price per share =
$125.50
(in currency)
& Number of shares outstanding =
993.57
(in #)
& Market Value of Debt =
$1,822.00
( in currency)
If no, do you want to use the book value debt ratio ?
(Yes or No)
If no, enter the debt to capital ratio to be used =
(in percent)
Enter length of extraordinary growth period =
(in years)
Do you want to change the debt ratio in the stable growth period?
If yes, enter the debt ratio for the stable growth period =
Costs of Components
Do you want to enter cost of equity directly?
If yes, enter the cost of equity =
If no, enter the inputs to the cost of equity
Beta
0.8
Riskfree rate=
5.30%
Risk Premium=
5.50%
Enter the cost of debt for cost of capital calculation
( in percent)
Earnings Inputs
Do you want to use the historical growth rate?
If yes, enter EBIT from five years ago =
$800.00
Do you have an outside estimate of growth ?
If yes, enter the estimated growth:
12.50%
Do you want to calculate the growth rate from fundamentals?
The following will be the inputs to the fundamental growth formulation:
ROC =
27.53%
Reinv. Rate =
38.37%
Do you want to change any of these inputs for the high growth period?
If yes, specify the values for these inputs (Please enter all variables)
ROC =
1
0.00%
Reinv. Rate =
100.00%
Specify weights to be assigned to each of these growth rates:
Historical
Growth Rate
Outside Prediction of Growth =
Fundamental Estimate of Growth =
Enter growth rate in stable growth period?
6.00%
Beta
Will the beta to change in the stable period?
If yes, enter the beta for stable period =
Will the cost of debt change in the stable period?
If yes, enter the new cost of debt =
Capital Spending, Depreciation & Working Capital
Do you want all these items to grow at the same rate as earnings ?
If not, enter the growth rates for each of the following items:
Capital Spending Depreciation Revenues
High Growth
6%
Stable Growth
Do not enter
Do you want to keep the current fraction of working capital to revenues?
Specify working capital as a percent of revenues:
Capital Spending and Depreciation in Stable Growth
Is capital spending to be offset by depreciation in stable period?
Do you want your reinvestment to be computed from fundamentals?
Return on captial in perpetuity
12%
If no, do you want to enter capital expenditure as % of depreciation
120%
Output from the program
Cost of Equity =
9.70%
Equity/(Debt+Equity ) =
98.56%
After-tax Cost of debt =
3.93%
Debt/(Debt +Equity) =
1.44%
Cost of Capital =
9.62%
Current
EBIT * (1 – tax rate)
$3,708.51
– (Capital Spending – Depreciation)
$924.00
– Change in Working Capital
Current FCFF
$2,285.51
Growth Rate in Earnings per share
Growth Rate Weight
Historical Growth =
45.33%
Outside Estimates =
Fundamental Growth =
10.56%
Weighted Average
Growth Rate in capital spending, depreciation and working capital
High Growth Stable Growth
Growth rate in capital spending =
Growth rate in depreciation =
Growth rate in revenues =
Working Capital as percent of revenues =
22.48%
The FCFE for the high growth phase are shown below (upto 10 years)
1 2 3 4 5
Terminal Year
EBIT * (1 – tax rate)
$4,100.25
$4,533.38
$5,012.26
$5,541.73
$6,127.13
$6,494.75
– (CapEx-Depreciation)
$1,021.61
$1,129.52
$1,248.84
$1,380.76
$1,526.62
$2,875.14
-Chg. Working Capital
$396.66
$438.56
$484.88
$536.10
$592.74
$372.24
Free Cashflow to Firm
$2,681.99
$2,965.30
$3,278.54
$3,624.87
$4,007.78
$3,247.38
Present Value
$2,446.69
$2,467.82
$2,489.13
$2,510.62
$2,532.30
Growth Rate in Stable Phase =
FCFF in Stable Phase =
Cost of Equity in Stable Phase =
Equity/ (Equity + Debt) =
AT Cost of Debt in Stable Phase =
Debt/ (Equity + Debt) =
Cost of Capital in Stable Phase =
Value at the end of growth phase =
$89,782.26
Present Value of FCFF in high growth phase =
$12,446.56
Present Value of Terminal Value of Firm =
$56,728.59
Value of the firm =
$69,175.15
Cash and Marketable Securities =
Market Value of outstanding debt =
Market Value of Equity =
$67,853.15
Value of Equity options issued by the company =
Market Value of Equity/share =
$66.78
Page &p
Aswath Damodaran:
Enter the current EBIT for the firm. If your current EBIT is negative, you will have to normalize EBIT.
Aswath Damodaran:
Enter the total interest expenses, corresponding to the dollar debt that you enter below.
Aswath Damodaran:
Enter the current capital expenditures, including acquisitions made. You might want to normalize this, if it is volatile.
Aswath Damodaran:
Enter the aggreate depreciation and amortization claimed by the firm.
Aswath Damodaran:
Enter the effective tax rate if it is greater than 35%, or 35% if the effective tax rate is lower.
Aswath Damodaran:
Enter aggregate revenues during the year.
Aswath Damodaran:
Non-cash WC = Inventory + Acc Rec – Acc Payable
Aswath Damodaran:
Enter the change in non-cash working capital from last year to this year.
Aswath Damodaran:
Enter total interest-bearing debt. If you are capitalizing operating leases, add them here.
Aswath Damodaran:
Enter the total shareholders equity.
Aswath Damodaran:
Enter the current market price per share.
Aswath Damodaran:
Enter number of shares in the same units as inputs above.
Aswath Damodaran:
If you can estimate the market value of the debt, enter that here. Else, enter the book value of debt from above.
Aswath Damodaran:
You should almost never use the book value debt ratio. You can try this, if you want, to see how much value will shift.
Aswath Damodaran:
In some cases, you might want to replace the current debt ratio with the industry average debt ratio.
Aswath Damodaran:
Enter the length of the growth period. I will restrict you to 10 years.
Aswath Damodaran:
If your firm has leverage which is out of the industry norm, you should generally say yes here.
Aswath Damodaran:
If you said yes to the previous question, you should enter the industry-average or optimal debt ratio.
Aswath Damodaran:
Generally, you should try to input the beta below. You can override this by inputting a cost of equity directly.
Aswath Damodaran:
Enter the cost of equity in percent terms, if you want to input it directly.
Aswath Damodaran:
Enter the current beta for the firm. Use the bottom-up beta, if you can get it.
Aswath Damodaran:
Enter the current long term government bond rate.
Aswath Damodaran:
Enter the risk premium for equities over riskfree investments. You can use an implied or historical premium.
Aswath Damodaran:
The best way to do this is to use the firms’s rating (actual and synthetic) and estimate an appropriate interest rate by adding a default spread to the long term treasury bond rate.
Aswath Damodaran:
If your firm has had a volatile or short history, you might want to ignore the historical growth rate.
Aswath Damodaran:
Enter the EBIT from five years prior to the current year. Thus, if the current year is 1998, look up earnings in 1993.
Aswath Damodaran:
Tough to get for operating income. You can look up analysts estimates for EPS growth over next 5 years.
Aswath Damodaran:
If you are entering EPS growth, you should reduce it to reflect the fact that operating income grows slower than EPS.
Aswath Damodaran:
You should try to do this, if you want an internally consistent vauation.
Aswath Damodaran:
This is estimated using the inputs from above on EBIT and book value of capital.
Aswath Damodaran:
Estimated from cap ex, working capital and EBIT estimated above.
Aswath Damodaran:
Compare your firm’s numbers to those of the industry. If they look strange, you might want to change them below.
Aswath Damodaran:
Replace with a more reasonable ROC. Two possible choices – the firm’s average ROC over time, the sector’s ROC or the firm’s cost of capital.
Aswath Damodaran:
Replace either with the firm’s average reinvestment rate over time or sector average.
Aswath Damodaran:
This defines the weight to be attached to the historical growth rate. If earnings history has been volatile, input zero.
Aswath Damodaran:
Reflects how much you trust analysts to do a good job of predicting expected future growth.
Aswath Damodaran:
Should be whatever is left over in terms of weight. The weights have to add up to one.
Aswath Damodaran:
This is the growth rate after your high growth phase. Since it is forever, it cannot be greater than 5-6%. If your firm might still have growth potential at the end of high growth, go with the upper end of the range. Else, go lower.
Aswath Damodaran:
If your beta currently is a high number (>1.20) or a low number (<0.80), you will want to move it at least towards these limits - 1.20 for high beta, and 0.80 for low beta.
Aswath Damodaran:
Enter the beta for the stable period. Keep between 0.80 and 1.20.
Aswath Damodaran:
Generally, firms get more stable as they age. The cost of debt should get lower for high risk firms.
Aswath Damodaran:
Enter the cost of debt for a safer firm. I would use an A rating for riskier firms in stable growth.
Aswath Damodaran:
If you have a reasonable cap ex/depreciation ratio, relative to the sector, answer yes. If not, enter no.
Aswath Damodaran:
If the capital expenditures currently are very high relative to depreciation, set this to a lower number than the growth rate in earnings and depreciation.
Aswath Damodaran:
If depreciation is higher than cap ex currently, set this growth rate above the growth rate in cap ex.
Aswath Damodaran:
If margins are expected to improve over time, this rate will be lower than the growth rate in earnings.
Aswath Damodaran:
If your firm's non-cash working capital as a percent of revenues is close to industry average, say yes. Else, say no.
Aswath Damodaran:
Enter the industry average working capital as a percent of revenues.
Aswath Damodaran:
Generally, it is not a good idea to say yes. Where will your growth come from?
Aswath Damodaran:
Enter the industry average cap ex/depreciation ratio
Aswath Damodaran:
Yes or no. If yes, enter the return on capital that your firm will have in stable growth. If no, enter cap ex as a percent of depreciation in the cell below.