Description
A spreadsheet explaining the workings of Free Cash Flow to Equity (FCFE) Valuation Model for organizations with two periods of growth, high growth initially and then stable growth.
Two-Stage FCFE Discount Model
Two-Stage FCFE Discount Model
This model is designed to value the equity in a firm, with two stages of growth, an initial period of higher growth and a subsequent period of stable growth.
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. 3. The free cashflow to equity is the correct measure of expected cashflows to stockholders.
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 equity.
Page 1
Two-Stage FCFE Discount Model
Inputs to the model Current Earnings per share = Current Dividends per share = Current Capital Spending/sh = Current Depreciation / share = Current Revenues/ share = Working Capital/ share = Chg. Working Capital/share = $4.00 $0.25 $3.70 $1.70 $20.00 $8.00 $1.00 (in currency) (in currency) (in currency) (in currency) (in currency)
Enter length of extraordinary growth period =
5
(in years)
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 of the stock = Riskfree rate= Risk Premium= 1.3 7.00% 5.50%
No
(Yes or No) (in percent)
(in percent) (in percent)
Earnings Inputs Do you want to use the historical growth rate? If yes, enter EPS from five years ago = Yes $0.49 (Yes or No) (in currency)
Do you have an outside estimate of growth ? If yes, enter the estimated growth:
Yes 19.00%
(Yes or No) (in percent)
Do you want to calculate the growth rate from fundamentals? If yes, enter the following inputs: Net Income Currently = Interest Expense Currently = Book Value of Debt = Book Value of Equity = Tax Rate on Income= $1,077.00 $53.85 $600.00 $5,445.00 36.00% Last year $550.00 $5,130.00
Yes
(Yes or No)
(in currency) (in currency) (in currency) (in currency) (in percent)
The following will be the inputs to the fundamental growth formulation:
Page 2
Two-Stage FCFE Discount Model
ROC = Retention =
19.57% 93.75%
D/E = Interest Rate=
11.02% 8.98%
(in percent) (in percent) No (Yes or No)
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 = Retention = 19.57% 93.75% D/E = Interest Rate= 11.02% 8.98%
(in percent) (in percent)
Specify weights to be assigned to each of these growth rates: Historical Growth Rate = Outside Prediction of Growth = Fundamental Estimate of Growth = 10.00% 40.00% 50.00% (in percent) (in percent) (in percent)
Enter growth rate in stable growth period?
6.00%
(in percent)
Beta Will the beta to change in the stable period? If yes, enter the beta for stable period = Yes 1.1 (Yes or No)
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 High Growth Stable Growth 20% Do not enter 20% Do not enter Revenues 18% 6% (in percent) (in percent) No (Yes or No)
Do you want to keep the current fraction of working capital to revenues? Specify working capital as a percent of revenues: 40%
Yes (in percent)
(Yes or No)
Do you want to use the current debt ratio as your desired mix? If no, enter the following inputs for financing mix, Desired debt financing proportion - Capital Spending Desired debt financing proportion - Working Capital
Yes
(Yes or No)
(in percent) (in percent)
Page 3
Two-Stage FCFE Discount Model
Capital spending and Depreciation during Stable Growth Is capital spending to be offset by depreciation in stable period? Do you want to compute your reinvestment rate from fundamentals? Return on equity in stable growth period If no, enter capital expenditures as % of depreciation in stable growth No Yes 12% 150% (in percent) (Yes or No)
Page 4
Two-Stage FCFE Discount Model
Output from the program
Cost of Equity = 14.15%
Proportion of Debt: Capital Spending (DR)= Proportion of Debt: Working Capital (DR)=
9.93% 9.93%
Current Earnings per share=
$4.00
Page 5
Two-Stage FCFE Discount Model
(Capital Spending - Depreciation)*(1-DR) Change in Working Capital * (1-DR) Current FCFE
$1.80 $0.90 $1.30
Growth Rate in Earnings per share Growth Rate Historical Growth = Outside Estimates = Fundamental Growth = Weighted Average 52.19% 19.00% 19.77% 22.71% Weight 10.00% 40.00% 50.00%
Growth Rate in capital spending, depreciation and working capital High Growth Growth rate in capital spending = Growth rate in depreciation = Growth rate in revenues = 20.00% 20.00% 18.00% Stable Growth Do not enter Do not enter 6.00%
Working Capital as percent of revenues =
40.00%
(in percent)
The FCFE for the high growth phase are shown below (upto 6 years) 1 Earnings - (CapEx-Depreciation)*(1-DR) -Chg. Working Capital*(1-DR) Free Cashflow to Equity Present Value $4.91 $2.16 $1.30 $1.45 $1.27 2 $6.02 $2.59 $1.53 $1.90 $1.46 3 $7.39 $3.11 $1.81 $2.47 $1.66 4 $9.07 $3.74 $2.13 $3.20 $1.89
Growth Rate in Stable Phase = FCFE in Stable Phase = Cost of Equity in Stable Phase = Price at the end of growth phase =
6.00% $5.90 13.05% $83.65
Present Value of FCFE in high growth phase = Present Value of Terminal Price =
$8.40 $43.16
Page 6
Two-Stage FCFE Discount Model
Value of the stock =
$51.56
Estimating the value of growth Value of assets in place = Value of stable growth = Value of extraordinary growth = Value of the stock = $9.94 $9.57 $32.05 $51.56
Page 7
Two-Stage FCFE Discount Model
Model
wo stages of growth, an initial of stable growth.
Page 8
Two-Stage FCFE Discount Model
Page 9
Two-Stage FCFE Discount Model
Page 10
Two-Stage FCFE Discount Model
Page 11
Two-Stage FCFE Discount Model
Page 12
Two-Stage FCFE Discount Model
5 $11.13 $4.48 $2.51 $4.13 $2.13
Terminal Year $11.79 $4.91 $0.99 $5.90
Page 13
doc_233068062.xls
A spreadsheet explaining the workings of Free Cash Flow to Equity (FCFE) Valuation Model for organizations with two periods of growth, high growth initially and then stable growth.
Two-Stage FCFE Discount Model
Two-Stage FCFE Discount Model
This model is designed to value the equity in a firm, with two stages of growth, an initial period of higher growth and a subsequent period of stable growth.
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. 3. The free cashflow to equity is the correct measure of expected cashflows to stockholders.
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 equity.
Page 1
Two-Stage FCFE Discount Model
Inputs to the model Current Earnings per share = Current Dividends per share = Current Capital Spending/sh = Current Depreciation / share = Current Revenues/ share = Working Capital/ share = Chg. Working Capital/share = $4.00 $0.25 $3.70 $1.70 $20.00 $8.00 $1.00 (in currency) (in currency) (in currency) (in currency) (in currency)
Enter length of extraordinary growth period =
5
(in years)
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 of the stock = Riskfree rate= Risk Premium= 1.3 7.00% 5.50%
No
(Yes or No) (in percent)
(in percent) (in percent)
Earnings Inputs Do you want to use the historical growth rate? If yes, enter EPS from five years ago = Yes $0.49 (Yes or No) (in currency)
Do you have an outside estimate of growth ? If yes, enter the estimated growth:
Yes 19.00%
(Yes or No) (in percent)
Do you want to calculate the growth rate from fundamentals? If yes, enter the following inputs: Net Income Currently = Interest Expense Currently = Book Value of Debt = Book Value of Equity = Tax Rate on Income= $1,077.00 $53.85 $600.00 $5,445.00 36.00% Last year $550.00 $5,130.00
Yes
(Yes or No)
(in currency) (in currency) (in currency) (in currency) (in percent)
The following will be the inputs to the fundamental growth formulation:
Page 2
Two-Stage FCFE Discount Model
ROC = Retention =
19.57% 93.75%
D/E = Interest Rate=
11.02% 8.98%
(in percent) (in percent) No (Yes or No)
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 = Retention = 19.57% 93.75% D/E = Interest Rate= 11.02% 8.98%
(in percent) (in percent)
Specify weights to be assigned to each of these growth rates: Historical Growth Rate = Outside Prediction of Growth = Fundamental Estimate of Growth = 10.00% 40.00% 50.00% (in percent) (in percent) (in percent)
Enter growth rate in stable growth period?
6.00%
(in percent)
Beta Will the beta to change in the stable period? If yes, enter the beta for stable period = Yes 1.1 (Yes or No)
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 High Growth Stable Growth 20% Do not enter 20% Do not enter Revenues 18% 6% (in percent) (in percent) No (Yes or No)
Do you want to keep the current fraction of working capital to revenues? Specify working capital as a percent of revenues: 40%
Yes (in percent)
(Yes or No)
Do you want to use the current debt ratio as your desired mix? If no, enter the following inputs for financing mix, Desired debt financing proportion - Capital Spending Desired debt financing proportion - Working Capital
Yes
(Yes or No)
(in percent) (in percent)
Page 3
Two-Stage FCFE Discount Model
Capital spending and Depreciation during Stable Growth Is capital spending to be offset by depreciation in stable period? Do you want to compute your reinvestment rate from fundamentals? Return on equity in stable growth period If no, enter capital expenditures as % of depreciation in stable growth No Yes 12% 150% (in percent) (Yes or No)
Page 4
Two-Stage FCFE Discount Model
Output from the program
Cost of Equity = 14.15%
Proportion of Debt: Capital Spending (DR)= Proportion of Debt: Working Capital (DR)=
9.93% 9.93%
Current Earnings per share=
$4.00
Page 5
Two-Stage FCFE Discount Model
(Capital Spending - Depreciation)*(1-DR) Change in Working Capital * (1-DR) Current FCFE
$1.80 $0.90 $1.30
Growth Rate in Earnings per share Growth Rate Historical Growth = Outside Estimates = Fundamental Growth = Weighted Average 52.19% 19.00% 19.77% 22.71% Weight 10.00% 40.00% 50.00%
Growth Rate in capital spending, depreciation and working capital High Growth Growth rate in capital spending = Growth rate in depreciation = Growth rate in revenues = 20.00% 20.00% 18.00% Stable Growth Do not enter Do not enter 6.00%
Working Capital as percent of revenues =
40.00%
(in percent)
The FCFE for the high growth phase are shown below (upto 6 years) 1 Earnings - (CapEx-Depreciation)*(1-DR) -Chg. Working Capital*(1-DR) Free Cashflow to Equity Present Value $4.91 $2.16 $1.30 $1.45 $1.27 2 $6.02 $2.59 $1.53 $1.90 $1.46 3 $7.39 $3.11 $1.81 $2.47 $1.66 4 $9.07 $3.74 $2.13 $3.20 $1.89
Growth Rate in Stable Phase = FCFE in Stable Phase = Cost of Equity in Stable Phase = Price at the end of growth phase =
6.00% $5.90 13.05% $83.65
Present Value of FCFE in high growth phase = Present Value of Terminal Price =
$8.40 $43.16
Page 6
Two-Stage FCFE Discount Model
Value of the stock =
$51.56
Estimating the value of growth Value of assets in place = Value of stable growth = Value of extraordinary growth = Value of the stock = $9.94 $9.57 $32.05 $51.56
Page 7
Two-Stage FCFE Discount Model
Model
wo stages of growth, an initial of stable growth.
Page 8
Two-Stage FCFE Discount Model
Page 9
Two-Stage FCFE Discount Model
Page 10
Two-Stage FCFE Discount Model
Page 11
Two-Stage FCFE Discount Model
Page 12
Two-Stage FCFE Discount Model
5 $11.13 $4.48 $2.51 $4.13 $2.13
Terminal Year $11.79 $4.91 $0.99 $5.90
Page 13
doc_233068062.xls