Solve each problem in one microsoft excel worksheet. Solve each in excel using excel formulas. Show your work.
1. You are analyzing two mutually exclusive projects with the following cash flows:
Year A B
0 -$4,000,000 -$4,000,000
1 $2,000,000 $1,000,000
2 $1,500,000 $1,500,000
3 $1,250,000 $1,700,000
4 $1,000,000 $2,400,000
a. Estimate the NPV of each project, assuming a cost of capital of 10%. Which is the better project?
b. Estimate the IRR for each project. Which is the better project?
c. What is the modified IRR on each of these projects? Which is a better project?
2. Today is Joe’s 30th birthday. He has been saving for his retirement since he was 15 years old. On his 15th birthday, Joe made the first contribution to his retirement account; he deposited $2,000 into an account which paid 9% interest, compounded monthly. Each year on his birthday, Joe contributes another $2,000 to the account. The 15th (and last) contribution was made last year on his 29th birthday. Joe wants to close the account today and move the money to a stock fund which is expected to earn an effective return of 12% a year. Joe’s plan is to continue making contributions to this new account each year on his birthday. His next contribution will come today (age 30) and his final planned contribution will be on his 25th birthday. If Joe wants to accumulate $3,000,000 in his account by age 55, how much must he contribute each year until age 55 (26 contributions in all) to achieve his goal?
3. CRI Corp is a holding company with four main subsidiaries. The percentage of its business from each of the subsidiaries and their betas:
Subsidiary Percentage of Business Beta
International 5% 1.50
Real estate 10 1.30
Cable Co 25 0.90
Elec. Utilities 60 0.70
a. What is the holding company’s beta?
b. At risk free rate of 6% and market risk premium of 5%, what is the holding company’s rate of return?
c. CRI is considering changing its focus; reduce electric utilities to 50% and increase international activities to 15%. What would be required rate of return if CRI adopts these changes?
4. You run an online business. You are considering replacing your manual ordering system with an automated system to make your operations more efficient and to increase sales. (All the cash flows given below are in real terms.)
- The new automated system will cost $10 million to install, and $500,000 to operate each year. It will replace a manual order system that costs $1.5 million to operate each year.
- The system is expected to last 10 years, and have no salvage value at the end of the period.
- The new system is expected to increase annual revenues from $5 million to $8 million for the next 10 years.
- The costs of goods sold is expected to remain at 50% of revenues.
- The tax rate is 40%.
- As a result of the automated system, the firm will be able to cut its inventory from 50% of revenues to 25% of revenues immediately. There is no change expected in the other working capital components. The real discount rate is 8%.
a. What is your expected cash flow at time=0?
b. What is the expected incremental annual cash flow from automating the system?
c. What is the net present value of this project?
5. As a consultant to a regional corporation, you are tasked to find beta of a private manufacturing firm. You have managed to obtain betas for publicly traded the following manufacturing
Firm Beta Debt MV of Equity
1 1.40 $ 2,500 $ 3,000
2 1.20 $ 5 $ 200
3 1.20 $ 540 $ 2250
4 0.70 $ 8 $ 300
5 1.50 $ 2900 $ 4000
The private firm has a debt equity ratio of 25%, and faces a tax rate of 40%. The publicly traded firms all have marginal tax rates of 40%, as well.
a. Estimate the beta for the private firm.
b. What concerns, if any, would you have about using betas of comparable firms?
6. The following is a description of the cost structure and betas of five firms in the food production industry:
Company Fixed Costs Variable Costs Beta D/(D+E)
CPC International 62% 38% 1.23 18.83%
Ralston Purina 47% 53% 0.81 38.32%
Quaker Oats 45% 55% 0.75 13.28%
Chiquita 50% 50% 0.88 75.35%
Kellogg's 40% 60% 0.76 5.57%
(Assume that all firms have a tax rate of 40%.)
A. Based upon just the operating leverage, which firms would you expect to have the highest and lowest betas (assuming that they are in the same business)?
B. Chiquita's beta is believed to be misleading because its financial leverage has increased dramatically since the period when the beta was estimated. If the average D/(D+E) ratio during the period of the regression (to estimate the betas) was only 30%, what would your new estimate of Chiquita's beta be?
7. Big 5 Sports is trying to determine its optimal capital structure. The company’s capital structure consists of debt and common equity. In order to estimate the cost of debt, the company has produced the following table:
The company’s tax rate is 40%. The risk-free rate is 5% and the market risk premium is 6%. If the firm had no debt, its beta would be 1.0.
On the basis of this information, what is the company’s optimal captial structure, and what is the firm’s WACC at this optimal capital structure?
8. SCS Corp. is trying to estimate its optimal capital structure. Right now, SCS has a capital structure that consists of 20% debt and 80% equity. (Its D/E ratio is 0.25.) The risk The risk-free rate is 2% and the market risk premium is 7%. Currently the company’s cost of equity is 12% and its tax rate is 40%. What would be SCS’s estimated cost of equity if it were to change its capital structure to 50% debt and 50% equity?
9. The following are the projected cash flows to equity and to the firm over the next five years:
Year CF to Equity Int (1-t) CF to Firm
1 $250.00 $90.00 $340.00
2 $262.50 $94.50 $357.00
3 $275.63 $99.23 $374.85
4 $289.41 $104.19 $393.59
5 $303.88 $109.40 $413.27
Terminal Value $3,946.50 $6,000.00
(The terminal value is the value of the equity or firm at the end of year 5.)
The firm has a cost of equity of 12% and a cost of capital of 9.94%. Answer the following questions:
A. What is the value of the equity of this firm?
B. What is the value of the firm?
10. SMC is a full-service leasing, maintenance, and rental firm with operations in the US and Europe. The following are selected numbers from the financial statements for 2014 and 2015(in millions).
2014 2015
Revenues $5,192.0 $5,400.0
(Less) Operating Expenses ($3,678.5) ($3848.0)
(Less) Depreciation ($573.5) ($580.0)
= EBIT $940.0 $972.0
(Less) Interest Expenses ($170.0) ($172.0)
(Less) Taxes ($652.1) ($670.0)
= Net Income $117.9 $130.0
Working Capital $92.0 <$370.0>
Total Debt $2,000 mil $2,200 mil
The firm had capital expenditures of $800 million in 2014 and $850 million in 2015. The working capital in 2013 was $34.8 million, and the total debt outstanding in 2013 was $1.75 billion. There were 77 million shares outstanding, trading at $29.00 per share.
A. Estimate the cash flows to equity in 2014and 2015.
B. Estimate the cash flows to the firm in 2014 and 2015.
C. Assuming that revenues and all expenses (including depreciation and capital expenditures) increase 6%, and that working capital remains unchanged in 2016 estimate the projected cash flows to equity and the firm in 2016. (The firm is assumed to be at its optimal financial leverage.)
D. How would your answer in (c) change if the firm planned to increase its debt ratio in 2016 by financing 75% of its capital expenditures (net of depreciation) with new debt issues?