Description
A spreadsheet explaining the workings of Free Cash Flow to Equity (FCFE) Valuation Model for organizations with three stages of growth, high growth initially, decline in growth, and then stable growth.
Three-stage FCFE Model
THREE-STAGE FCFE DISCOUNT MODEL
This model is designed to value the equity in a firm with three stages of growth - an initial period of high growth, a transition period of declining growth and a final period of stable growth.
Assumptions 1. The firm is assumed to be in an extraordinary growth phase currently. 2. This extraordinary growth is expected to last for an initial period that has to be specified. 3. The growth rate declines linearly over the transition period to a stable growth rate. 4. The relationship between capital spending and depreciation changes consistently with the growth rate.
The user should enter the following inputs: 1. Length of each growth phase 2. Growth rate in each growth phase 3. Capital Spending, Depreciation and Working Capital in each growth phase. 4. Costs of Equity in each growth phase
Page 1
Three-stage FCFE Model
Inputs to the model General Inputs 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 = $1.00 $0.80 $12.50 $5.00 $0.50 (in currency) $0.85 (in currency) (in currency) (in currency) (in currency)
Do you want to enter cost of equity directly? If yes, enter the cost of equity =
No
(Yes or No) (in percent)
If no, enter the inputs to the cost of equity for the initial high growth stage Beta of the stock = Riskfree rate= Risk Premium= 1.1 7.00% 5.50% (in percent) (in percent)
Earnings Inputs Growth Rate during the initial high growth phase Enter length of extraordinary growth period = 5 (in years)
Do you want to use the historical growth rate? If yes, enter EPS from five years ago =
No $0.90
(Yes or No) (in currency)
Do you have an outside estimate of growth ? If yes, enter the estimated growth:
Yes 20.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 = $10.00 $2.50 $16.10 $29.50 Last year $15.20 $28.10
No
(Yes or No)
(in currency) (in currency) (in currency) (in currency)
Page 2
Three-stage FCFE Model
Tax Rate on Income=
45.00%
(in percent)
The following will be the inputs to the fundamental growth formulation: ROC = Retention = 26.27% 100.00% D/E = Interest Rate= 54.58% 15.53% (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 = 25.00% 100.00% D/E = Interest Rate= 54.58% 15.53%
(in percent) (in percent) Yes (Yes or No)
Do you want to change any of these inputs for the stable growth period? If yes, specify the values for these inputs ROC = 20.00% D/E = Interest Rate= 54.58% 14.00%
(in percent) (in percent)
Page 3
Three-stage FCFE Model
Specify weights to be assigned to each of these growth rates: Historical Growth Rate = Outside Prediction of Growth = Fundamental Estimate of Growth = 100.00% (in percent) (in percent) (in percent)
Growth Rate during the transition period Enter length of the transition period = 10 (in years)
Do you want the beta to adjust gradually to stable beta? If no, enter the beta for the transition period =
Yes
(Yes or No)
Growth Rate during the stable phase Enter growth rate in stable growth period? 5.00% (in percent)
Will the beta change in the stable period? If yes, enter the beta for stable period =
Yes 0.9
(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 Transition period Stable Growth 20.00% 12.00% Do not enter 20.00% 12.00% Do not enter Revenues 18.00% 12.00% 6.00% (in percent) (in percent) (in percent) Yes (Yes or No)
Do you want to keep the current fraction of working capital to revenues? If no, specify working capital as a percent of revenues:
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 15.00% 15.00%
No
(Yes or No)
(in percent) (in percent)
Page 4
Three-stage FCFE Model
Stable Growth Inputs for Capital Expenditures and Depreciation 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 No Yes 12% (Yes or No)
Page 5
Three-stage FCFE Model
If no, enter capital expenditures as % of depreciation in steady state:
125%
(in percent: > 100%)
Output from the program Initial High Growth Phase Cost of Equity = Current Earnings per share= 13.05% $0.85
Proportion of Debt: Capital Spending (DR)= Proportion of Debt: Working Capital (DR)=
15.00% 15.00%
Current Earnings per share=
$0.85
Page 6
Three-stage FCFE Model
(Capital Spending - Depreciation)*(1-DR) Change in Working Capital * (1-DR) Current FCFE
$0.17 $0.43 $0.26
Growth Rate in Earnings per share - Initial High Growth phase Growth Rate Historical Growth = Outside Estimates = Fundamental Growth = Weighted Average 20.00% 20.00% 100.00% Weight
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% 20.00% Transition period Stable Growth Earnings g Earnings g Earnings g 5.00% 5.00% 5.00%
Working Capital as percent of revenues =
40.00%
(in percent)
The dividends for the high growth phase are shown below (upto 10 years) Year Earnings (CapEx-Depreciation)*(1-DR) Chg. Working Capital *(1-DR) FCFE Present Value 1 $1.02 $0.20 $0.85 ($0.03) ($0.03) 2 $1.22 $0.24 $1.02 ($0.04) ($0.03) 3 $1.47 $0.29 $1.22 ($0.05) ($0.03) 4 $1.76 $0.35 $1.47 ($0.06) ($0.04)
Transition period (upto ten years) Year Growth Rate Cumulated Growth Earnings (CapEx-Depreciation)*(1-DR) 6 18.50% 18.50% $2.51 $0.50 7 17.00% 38.65% $2.93 $0.59 8 15.50% 60.13% $3.39 $0.68 9 14.00% 82.55% $3.86 $0.77
Page 7
Three-stage FCFE Model
Chg. Working Capital *(1-DR) FCFE Beta Cost of Equity Present Value End-of-Life Index Stable Growth Phase Growth Rate in Stable Phase = FCFE in terminal year = Cost of Equity in Stable Phase = Price at the end of growth phase =
$1.96 $0.05 1.08 12.94% $0.02
$2.13 $0.22 1.06 12.83% $0.09
$2.27 $0.44 1.04 12.72% $0.16
$2.37 $0.72 1.02 12.61% $0.24
5.00% $3.76 11.95% $54.13
Present Value of FCFE in high growth phase = Present Value of FCFE in transition phase = Present Value of Terminal Price = Value of the stock =
($0.17) $3.36 $9.07 $12.26
Page 8
Three-stage FCFE Model
T MODEL
ages of growth - an initial d a final period of stable
Page 9
Three-stage FCFE Model
Page 10
Three-stage FCFE Model
Page 11
Three-stage FCFE Model
Page 12
Three-stage FCFE Model
Page 13
Three-stage FCFE Model
ercent: > 100%)
Page 14
Three-stage FCFE Model
5 $2.12 $0.42 $1.76 ($0.07) ($0.04)
10 12.50% 105.37% $4.34 $0.87
11 11.00% 127.96% $4.82 $0.96
12 9.50% 149.62% $5.28 $1.06
13 8.00% 169.59% $5.70 $1.14
14 6.50% 187.11% $6.07 $1.21
15 5.00% 201.47% $6.38 $1.28
Terminal Year
$6.70 $1.34
Page 15
Three-stage FCFE Model
$2.41 $1.06 1 12.50% $0.32
$2.39 $1.47 0.98 12.39% $0.39
$2.29 $1.93 0.96 12.28% $0.46
$2.11 $2.45 0.94 12.17% $0.52
$1.85 $3.00 0.92 12.06% $0.56
$1.52 $3.58 0.9 11.95% $0.60 1
$1.59 $3.76 0.9 11.95%
Page 16
doc_893830905.xls
A spreadsheet explaining the workings of Free Cash Flow to Equity (FCFE) Valuation Model for organizations with three stages of growth, high growth initially, decline in growth, and then stable growth.
Three-stage FCFE Model
THREE-STAGE FCFE DISCOUNT MODEL
This model is designed to value the equity in a firm with three stages of growth - an initial period of high growth, a transition period of declining growth and a final period of stable growth.
Assumptions 1. The firm is assumed to be in an extraordinary growth phase currently. 2. This extraordinary growth is expected to last for an initial period that has to be specified. 3. The growth rate declines linearly over the transition period to a stable growth rate. 4. The relationship between capital spending and depreciation changes consistently with the growth rate.
The user should enter the following inputs: 1. Length of each growth phase 2. Growth rate in each growth phase 3. Capital Spending, Depreciation and Working Capital in each growth phase. 4. Costs of Equity in each growth phase
Page 1
Three-stage FCFE Model
Inputs to the model General Inputs 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 = $1.00 $0.80 $12.50 $5.00 $0.50 (in currency) $0.85 (in currency) (in currency) (in currency) (in currency)
Do you want to enter cost of equity directly? If yes, enter the cost of equity =
No
(Yes or No) (in percent)
If no, enter the inputs to the cost of equity for the initial high growth stage Beta of the stock = Riskfree rate= Risk Premium= 1.1 7.00% 5.50% (in percent) (in percent)
Earnings Inputs Growth Rate during the initial high growth phase Enter length of extraordinary growth period = 5 (in years)
Do you want to use the historical growth rate? If yes, enter EPS from five years ago =
No $0.90
(Yes or No) (in currency)
Do you have an outside estimate of growth ? If yes, enter the estimated growth:
Yes 20.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 = $10.00 $2.50 $16.10 $29.50 Last year $15.20 $28.10
No
(Yes or No)
(in currency) (in currency) (in currency) (in currency)
Page 2
Three-stage FCFE Model
Tax Rate on Income=
45.00%
(in percent)
The following will be the inputs to the fundamental growth formulation: ROC = Retention = 26.27% 100.00% D/E = Interest Rate= 54.58% 15.53% (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 = 25.00% 100.00% D/E = Interest Rate= 54.58% 15.53%
(in percent) (in percent) Yes (Yes or No)
Do you want to change any of these inputs for the stable growth period? If yes, specify the values for these inputs ROC = 20.00% D/E = Interest Rate= 54.58% 14.00%
(in percent) (in percent)
Page 3
Three-stage FCFE Model
Specify weights to be assigned to each of these growth rates: Historical Growth Rate = Outside Prediction of Growth = Fundamental Estimate of Growth = 100.00% (in percent) (in percent) (in percent)
Growth Rate during the transition period Enter length of the transition period = 10 (in years)
Do you want the beta to adjust gradually to stable beta? If no, enter the beta for the transition period =
Yes
(Yes or No)
Growth Rate during the stable phase Enter growth rate in stable growth period? 5.00% (in percent)
Will the beta change in the stable period? If yes, enter the beta for stable period =
Yes 0.9
(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 Transition period Stable Growth 20.00% 12.00% Do not enter 20.00% 12.00% Do not enter Revenues 18.00% 12.00% 6.00% (in percent) (in percent) (in percent) Yes (Yes or No)
Do you want to keep the current fraction of working capital to revenues? If no, specify working capital as a percent of revenues:
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 15.00% 15.00%
No
(Yes or No)
(in percent) (in percent)
Page 4
Three-stage FCFE Model
Stable Growth Inputs for Capital Expenditures and Depreciation 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 No Yes 12% (Yes or No)
Page 5
Three-stage FCFE Model
If no, enter capital expenditures as % of depreciation in steady state:
125%
(in percent: > 100%)
Output from the program Initial High Growth Phase Cost of Equity = Current Earnings per share= 13.05% $0.85
Proportion of Debt: Capital Spending (DR)= Proportion of Debt: Working Capital (DR)=
15.00% 15.00%
Current Earnings per share=
$0.85
Page 6
Three-stage FCFE Model
(Capital Spending - Depreciation)*(1-DR) Change in Working Capital * (1-DR) Current FCFE
$0.17 $0.43 $0.26
Growth Rate in Earnings per share - Initial High Growth phase Growth Rate Historical Growth = Outside Estimates = Fundamental Growth = Weighted Average 20.00% 20.00% 100.00% Weight
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% 20.00% Transition period Stable Growth Earnings g Earnings g Earnings g 5.00% 5.00% 5.00%
Working Capital as percent of revenues =
40.00%
(in percent)
The dividends for the high growth phase are shown below (upto 10 years) Year Earnings (CapEx-Depreciation)*(1-DR) Chg. Working Capital *(1-DR) FCFE Present Value 1 $1.02 $0.20 $0.85 ($0.03) ($0.03) 2 $1.22 $0.24 $1.02 ($0.04) ($0.03) 3 $1.47 $0.29 $1.22 ($0.05) ($0.03) 4 $1.76 $0.35 $1.47 ($0.06) ($0.04)
Transition period (upto ten years) Year Growth Rate Cumulated Growth Earnings (CapEx-Depreciation)*(1-DR) 6 18.50% 18.50% $2.51 $0.50 7 17.00% 38.65% $2.93 $0.59 8 15.50% 60.13% $3.39 $0.68 9 14.00% 82.55% $3.86 $0.77
Page 7
Three-stage FCFE Model
Chg. Working Capital *(1-DR) FCFE Beta Cost of Equity Present Value End-of-Life Index Stable Growth Phase Growth Rate in Stable Phase = FCFE in terminal year = Cost of Equity in Stable Phase = Price at the end of growth phase =
$1.96 $0.05 1.08 12.94% $0.02
$2.13 $0.22 1.06 12.83% $0.09
$2.27 $0.44 1.04 12.72% $0.16
$2.37 $0.72 1.02 12.61% $0.24
5.00% $3.76 11.95% $54.13
Present Value of FCFE in high growth phase = Present Value of FCFE in transition phase = Present Value of Terminal Price = Value of the stock =
($0.17) $3.36 $9.07 $12.26
Page 8
Three-stage FCFE Model
T MODEL
ages of growth - an initial d a final period of stable
Page 9
Three-stage FCFE Model
Page 10
Three-stage FCFE Model
Page 11
Three-stage FCFE Model
Page 12
Three-stage FCFE Model
Page 13
Three-stage FCFE Model
ercent: > 100%)
Page 14
Three-stage FCFE Model
5 $2.12 $0.42 $1.76 ($0.07) ($0.04)
10 12.50% 105.37% $4.34 $0.87
11 11.00% 127.96% $4.82 $0.96
12 9.50% 149.62% $5.28 $1.06
13 8.00% 169.59% $5.70 $1.14
14 6.50% 187.11% $6.07 $1.21
15 5.00% 201.47% $6.38 $1.28
Terminal Year
$6.70 $1.34
Page 15
Three-stage FCFE Model
$2.41 $1.06 1 12.50% $0.32
$2.39 $1.47 0.98 12.39% $0.39
$2.29 $1.93 0.96 12.28% $0.46
$2.11 $2.45 0.94 12.17% $0.52
$1.85 $3.00 0.92 12.06% $0.56
$1.52 $3.58 0.9 11.95% $0.60 1
$1.59 $3.76 0.9 11.95%
Page 16
doc_893830905.xls