Reference no: EM132535413

Excel Assignment

Problem 1: Connell, three-stage DDM
Problem 2: Dragon, two-stage FCFF model
Problem 3: Moose, two-stage FCFE model

(Three-stage DDM) The current dividend for Connell Corp is $1.00. In stage 1, which lasts 4 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.

If the required rate of return is 10%, what is the value per share?
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?

(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?

(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?

Attachment:- Excel Assignment.rar

Attachment:- Item set using DDM justified PE and FCFF model.rar