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