business valuation

profileitsbensono
fcff2st.xls

NewFCFF2Stage

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. The firm is expected to grow at a higher growth rate in the first period.
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 and Working Capital needs during the high growth period.
4. Expected growth rate in earnings during the stable growth period.
5. Inputs for the cost of capital. (Cost of equity, Cost of debt, Weights on debt and equity)
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,701.00
Current Non-cash Working Capital = $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 ? No (Yes or No)
If no, enter the debt to capital ratio to be used = (in percent)
Enter length of extraordinary growth period = 5 (in years)
Do you want to change the debt ratio in the stable growth period? No
If yes, enter the debt ratio for the stable growth period =
Costs of Components
Do you want to enter cost of equity directly? No (Yes or No)
If yes, enter the cost of equity = (in percent)
If no, enter the inputs to the cost of equity
Beta of the stock = 0.8
Riskfree rate= 5.30% (in percent)
Risk Premium= 5.50% (in percent)
Enter the cost of debt for cost of capital calculation 5.50% ( in percent)
Earnings Inputs
Do you want to use the historical growth rate? No (Yes or No)
If yes, enter EBIT from five years ago = $800.00 (in currency)
Do you have an outside estimate of growth ? Yes (Yes or No)
If yes, enter the estimated growth: 12.50% (in percent)
Do you want to calculate the growth rate from fundamentals? Yes (Yes or No)
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? No (Yes or No)
If yes, specify the values for these inputs (Please enter all variables)
ROC = 10.00%
Reinv. Rate = 100.00%
Specify weights to be assigned to each of these growth rates:
Historical Growth Rate = 0.00% (in percent)
Outside Prediction of Growth = 0.00% (in percent)
Fundamental Estimate of Growth = 100.00% (in percent)
Enter growth rate in stable growth period? 6.00% (in percent)
Beta
Will the beta to change in the stable period? No (Yes or No)
If yes, enter the beta for stable period = 1.00
Will the cost of debt change in the stable period? No (Yes or No)
If yes, enter the new cost of debt = ( in percent)
Capital Spending, Depreciation & Working Capital
Do you want all these items to grow at the same rate as earnings ? Yes (Yes or No)
If not, enter the growth rates for each of the following items:
Capital Spending Depreciation Revenues
High Growth 6% 6% 6% (in percent)
Stable Growth Do not enter Do not enter 6% (in percent)
Do you want to keep the current fraction of working capital to revenues? Yes (Yes or No)
Specify working capital as a percent of revenues: (in percent)
Capital Spending and Depreciation in Stable Growth
Is capital spending to be offset by depreciation in stable period? No (Yes or No)
Do you want your reinvestment to be computed from fundamentals? Yes
Return on captial in perpetuity 12%
If no, do you want to enter capital expenditure as % of depreciation 120% (in percent)
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 $499.00
Current FCFF $2,285.51
Growth Rate in Earnings per share
Growth Rate Weight
Historical Growth = 45.33% 0.00%
Outside Estimates = 12.50% 0.00%
Fundamental Growth = 10.56% 100.00%
Weighted Average 10.56%
Growth Rate in capital spending, depreciation and working capital
High Growth Stable Growth
Growth rate in capital spending = 10.56% Do not enter
Growth rate in depreciation = 10.56% Do not enter
Growth rate in revenues = 10.56% 6.00%
Working Capital as percent of revenues = 22.48% (in percent)
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 = 6.00%
FCFF in Stable Phase = $3,247.38
Cost of Equity in Stable Phase = 9.70%
Equity/ (Equity + Debt) = 98.56%
AT Cost of Debt in Stable Phase = 3.93%
Debt/ (Equity + Debt) = 1.44%
Cost of Capital in Stable Phase = 9.62%
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 = $500.00
Market Value of outstanding debt = $1,822.00
Market Value of Equity = $67,853.15
Value of Equity options issued by the company = $1,500.00
Market Value of Equity/share = $66.78
&C Two-Stage FCFF Discount Model
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.