Final Excel Assignment for MBA 6355
Work problem 1 and either problem 2 or problem 3 (two problems total)
1. (Three-stage DDM) The current dividend for Connell Corp is $1.00. In stage 1, which lasts 3 years,
the dividend will grow at 0% per year. In stage 2, which also lasts 4 years, the dividend will grow at 30%
per year. Finally, in stage 3, the dividend will grow at 6% forever.
A. If the required rate of return is 10%, what is the value per share?
B. If the current market price of Connell Corp is $35, what will be your rate of return if you realize all of
the dividends described in the model?
2. (FCFF valuation model) Build a valuation model for Dragon Limited based on the following
information:
• Sales were $100 million in the year just ended. Sales will grow at 100% annually for two years,
and then at 5% annually forever.
• EBIT will be 30% of sales.
• Depreciation is 4% of the current year’s sales
• Capital expenditures are 4% of the current year’s sales plus 40% of the current year’s increase in
sales
• The investment in working capital will be 10% of the current year’s increase in sales
• The income tax rate for Dragon Limited is 25%
• The weighted average cost of capital is 12%
• Dragon has 40 million outstanding shares
• Dragon has $200 million of outstanding debt.
What is the Dragon Limited value per share?
3. (FCFE valuation model) From the text, FCFF = EBIT(1-t) + Dep – Capex – InvWC, and FCFE = FCFF –
Int(1-t) + net borrowing. Or FCFE = EBIT(1-t) + Dep – Capex – InvWC – Int(1-t) + net borrowing. Since
EBIT(1-t) – Int(1-t) = NI, we can use FCFE = NI + Dep – Capex – InvWC + net borrowing.
Value the shares of Moose Service Co. The modeling assumptions are:
• Current sales are $60 million. They will grow at 30% annually for the first four years, and then
grow at 6% annually thereafter.
• Net income will be 10% of sales.
• The net investment in fixed capital (Capex – Depr) will be 50% of the sales increase each year
• The investment in working capital will be 10% of the sales increase.
• Debt financing will be 40% of the net investments in fixed capital and working capital each year
• The required rate of return for equity is 11%.
• There are 2 million outstanding shares.
What is the intrinsic value per share of Moose Service?
https://mediacast.blob.core.windows.net/production/MBA/6355/Excel_Valuation_Models_Part_1/Excel_Valuation_Models_Part_1.html
https://mediacast.blob.core.windows.net/production/MBA/6355/Excel_Valuation_Models_Part_2/Excel_Valuation_Models_Part_2.html
MBE 6355 Excel exercises on equity valuation models.
These four problems will be discussed in the online lecture.
1. (Basic single-stage DDM) Kwok Company’s current earnings per share are $2.00. Earnings will grow
forever at 4% and the required rate of return is 12%.
A. What is the value per share?
B. Create a table showing the cumulative percentage of the share price coming from the first n
dividends. Let n go from 1 to 20.
2. (Three-stage DDM) The current dividend for Hugh Corp is $4.00. In stage 1, which lasts 4 years, the
dividend will grow at 30% per year. In stage 2, which also lasts 4 years, the dividend will grow at 15%
per year. Finally, in stage 3, the dividend will grow at 6% forever.
A. If the required rate of return is 11%, what is the value per share?
B. If the current market price of Hugh Corp is $80, what will be your rate of return if you realize all of
the dividends described in the model?
3. (FCFF valuation model) Build a valuation model for Stutz Enterprises based on the following
information:
• Sales were $100 million in the year just ended. Sales will grow at 15% annually for five years,
and then at 6% annually forever.
• EBIT will be 40% of sales.
• Depreciation is 4% of the current year’s sales
• Capital expenditures are 4% of the current year’s sales plus 40% of the current year’s increase in
sales
• The investment in working capital will be 10% of the current year’s increase in sales
• The income tax rate for Stutz is 35%
• The weighted average cost of capital is 12%
• Stutz has 10 million outstanding shares
• Stutz has $75 million of outstanding debt.
What is the Stutz value per share?
4. (FCFE valuation model) From the text, FCFF = EBIT(1-t) + Dep – Capex – InvWC, and FCFE = FCFF –
Int(1-t) + net borrowing. Or FCFE = EBIT(1-t) + Dep – Capex – InvWC – Int(1-t) + net borrowing. Since
EBIT(1-t) – Int(1-t) = NI, we can use FCFE = NI + Dep – Capex – InvWC + net borrowing.
Value the shares of RonTek Co. The modeling assumptions are:
• Current sales are $25 million. They will grow at 20% annually for the first three years, and then
grow at 6% annually thereafter.
• Net income will be 10% of sales.
• The net investment in fixed capital (Capex – Depr) will be 50% of the sales increase each year
• The investment in working capital will be 20% of the sales increase.
• Debt financing will be 40% of the net investments in fixed capital and working capital each year
• The required rate of return for equity is 12.4%.
• There are 2 million outstanding shares.
What is the intrinsic value per share of RonTek?
Gordon growth model, showing percent of value from first n dividends
Kwok Company’s current dividends per share are $2.00. Dividends will grow forever at 4% and the
the required rate of return is 12%.
A. What is the value per share?
B. Create a table showing the cumulative percentage of the share price coming from the first n
dividends. Let n go from 1 to 20.
D0 2
g 0.04
r 0.12
P0 26 << P0 = D0(1+g)/(r-g)
year Dt PV of Dt Cum PV Cum % of P0
1 2.080 1.857 1.857 7.14%
2 2.163 1.724 3.582 13.78%
3 2.250 1.601 5.183 19.93%
4 2.340 1.487 6.670 25.65%
5 2.433 1.381 8.051 30.96%
6 2.531 1.282 9.333 35.90%
7 2.632 1.191 10.523 40.47%
8 2.737 1.105 11.629 44.73%
9 2.847 1.027 12.655 48.67%
10 2.960 0.953 13.608 52.34%
11 3.079 0.885 14.494 55.74%
12 3.202 0.822 15.315 58.91%
13 3.330 0.763 16.079 61.84%
14 3.463 0.709 16.787 64.57%
15 3.602 0.658 17.445 67.10%
16 3.746 0.611 18.056 69.45%
17 3.896 0.567 18.624 71.63%
18 4.052 0.527 19.151 73.66%
19 4.214 0.489 19.640 75.54%
20 4.382 0.454 20.094 77.29%
Three stage DDM (Hugh Corp)
The current dividend for Hugh Corp is $4.00. In stage 1, which lasts 4 years, the dividend
will grow at 30% per year. In stage 2, which also lasts 4 years, the dividend will grow at 15%
per year. Finally, in stage 3, the dividend will grow at 6% forever.
A. If the required rate of return is 11%, what is the value per share?
B. If the current market price of Hugh Corp is $80, what will be your rate of return if you
realize all of the dividends described in the model?
g stage 1 0.3 Length 4
g stage 2 0.15 Length 4
g stage 3 0.06
r 0.11
D0 4
Time Dividend PV
1 5.2 4.684685
2 6.76 5.486568
3 8.788 6.42571
4 11.4244 7.525606
5 13.13806 7.796799
6 15.10877 8.077765
7 17.37508 8.368856
8 19.98135 8.670436
Terminal value
8 423.6046 183.8132 << TV8 = D9/(r-g)
Total value 240.8497
Two-stage FCFF valuation model for Stutz Enterprises
The assumptions for the model are:
• Sales were $100 million in the year just ended. Sales will grow at 15% annually for five
years, and then at 6% annually forever.
• EBIT will be 40% of sales.
• Depreciation is 4% of the current year’s sales
• Capital expenditures are 4% of the current year’s sales plus 40% of the current year’s
increase in sales
• The investment in working capital will be 10% of the current year’s increase in sales
• The income tax rate for Stutz is 35%
• The weighted average cost of capital is 12%
• Stutz has 10 million outstanding shares
• Stutz has $75 million of outstanding debt.
What is the Stutz value per share?
year 0 sales 100
initial growth rate 15%
terminal growth rate 6% FCFF = EBIT(1-t) + Dep – Capex – InvNWC
EBIT as % of sales 40%
Depr as % of sales 4% All values except per share amounts are in $millions
Capex as % of sales 4%
Capex as % of S inc 40%
NWC Inv as % of S incr 10%
Income tax rate 35%
WACC 12%
Shares out 10
Debt out 75
Year 0 1 2 3 4 5 6 7
grow in S 15% 15% 15% 15% 15% 6% 6%
Sales 100 115.00 132.25 152.09 174.90 201.14 213.20 226.00
EBIT 46.00 52.90 60.84 69.96 80.45 85.28 90.40
Depreciation 4.60 5.29 6.08 7.00 8.05 8.53 9.04
Capex 10.60 12.19 14.02 16.12 18.54 13.36 14.16
NWC invest 1.50 1.73 1.98 2.28 2.62 1.21 1.28
EBIT(1 – tax rate) 29.90 34.39 39.54 45.47 52.30 55.43 58.76
Depreciation 4.60 5.29 6.08 7.00 8.05 8.53 9.04
Capex 10.60 12.19 14.02 16.12 18.54 13.36 14.16
NWC invest 1.50 1.73 1.98 2.28 2.62 1.21 1.28
FCFF 22.40 25.76 29.62 34.07 39.18 49.40 52.36
PV of FCFF 20.00 20.54 21.09 21.65 22.23
g in FCFF 15% 15% 15% 15% 26.09% 6%
Terminal value at t = 5 823.32 << FCFF6/(WACC – terminal growth rate)
PV of FCFF for t = 1-5 105.50 << Discounted at WACC
PV of TV at t = 5 467.17 << Discounted at WACC
Total value 572.67
Less debt 75.00
Value of equity 497.67
Value per share 49.77 << Value of equity / Outstanding shares
(Two-stage FCFE model)
Value the shares of RonTek Co. The modeling assumptions are:
• Current sales are $25 million. They will grow at 20% annually for the first three years,
and then grow at 6% annually thereafter.
• Net income will be 10% of sales.
• The net investment in fixed capital (Capex – Depr) will be 50% of the sales increase each year
each year
• The investment in working capital will be 20% of the sales increase.
• Debt financing will be 40% of the net investments in fixed capital and working capital each year
each year
• The required rate of return for equity is 12.4%.
• There are 2 million outstanding shares.
What is the intrinsic value per share of RonTek?
Hint: We will use the definition from the book where FCFE = FCFF – Int(1-t) + net borrowing
and use an equivalent expression where FCFE = NI + Dep – Capex – InvWC + net borrowing
sales growth rate stage 1 20%
sales growth rate stage 2 6%
Year 0 sales ($million) 25
Net profit margin 10%
Net capex invest (capex – depr)
as percentage of sales increase 50%
Invest in NWC as % of sales increase 20%
Debt financing as a % of the net
capex invest and NWC invest 40%
Required return on equity 12.40%
Outstanding shares (million) 2.00
Year 1 2 3 4 5 6
Sales growth 20% 20% 20% 6% 6% 6%
Sales 30.000 36.000 43.200 45.792 48.540 51.452
Net prof mar 10% 10% 10% 10% 10% 10%
Net profit 3.000 3.600 4.320 4.579 4.854 5.145
Net capex 2.500 3.000 3.600 1.296 1.374 1.456
Invest NWC 1.000 1.200 1.440 0.518 0.550 0.582
Total invests 3.500 4.200 5.040 1.814 1.923 2.039
Debt financ 1.400 1.680 2.016 0.726 0.769 0.815
FCFE 0.900 1.080 1.296 3.491 3.700 3.922
growth FCFE 20% 20% 169% 6% 6%
Terminal value 54.540 << TV3 = FCFE4/(r-g)
Present values
FCFE1 0.801
FCFE2 0.855
FCFE3 0.913
Terminal value 38.408
Total PV 40.976
Value per share 20.488 << Total PV / Outstanding shares