finance using excel

FIN139DIAGNOSTICTEST.xlsx

Home >Business & Finance homework help >finance using excel

COVER

FIN 139 - FINANCIAL POLICY AND STRATEGY
PROFESSOR MANUCHEHR SHAHROKHI
DIAGNOSTIC TEST
THIS IS AN INDIVIDUAL TEST AND NOT A GROUP OR TEAM ASSIGNMENT.
BY SUBMITTING YOUR COMPLETED TEST, YOU ARE ATTESTING THAT ALL AND EVERYTHING INCLUDING SOLUTIONS OR DISCUSSIONS ARE DONE BY YOU ONLY AND YOU HAVE NOT RECEIVED ANY HELP FROM ANYONE.
Note: Please use excel to do your calculations and show your work for partial credit. This test is open book, open notes and you may access the Internet.
However, communication of any kind is NOT allowed. Once you are done, please save it and submit it via Bb.
Time allowed: 6 HOURS
GOOD LUCK!
NAME: ____________________________	STUDENT ID: ___________________________

1

2

3

4

Watkins Inc. has never paid a dividend, and when it might begin paying dividends is unknown. Its current free cash flow (FCF) is $100,000 which is expected to grow at a constant 7% rate. The weighted average cost of capital (WACC=K) is 11%. Watkins currently holds $325,000 of non-operating marketable securities. Its long-term debt is $1,000,000, but it has never issued preferred stock. a. Calculate Watkin’s value operations b. Calculate the company’s total value c. Calculate the value of common equity

5

Some Relevant and Useful Formulas: VU =EBIT/Ku; VL = VU;D + SL = VL; KsL = KsU + (KsU - Kd) (D/S) WACC = Wd Kd + WceKs = (D/V)Kd + (S/V)Ks M&M Proposition I: VL=VU + TD M&M Proposition II: KsL = KsU + (KsU – Kd) (1 - T) (D/S). VU = EBIT(1-T)/KsU KsL= KsU + (KsU - Kd) (1-T)(D/S) WACCL= (D/V) Kd (1-T) + (S/V)Ks and VL = VU + TCD VL = VU +D

6

7

You are analyzing the beta for IBM and have broken down the company into four broad business groups, with market values and betas for each group.
Business Group	Market Value of Equity	Beta
Mainframes	$ 2.0 billion	1.10
Personal Computers	$ 2.0 billion	1.50
Software	$ 1.0 billion	2.00
Printers	$ 3.0 billion	1.00
a. Estimate the beta for IBM as a company. Is this beta going to be equal to the beta estimated by regressing past returns on IBM stock against a market index. Why or Why not?
b. If the treasury bond rate is 7.5%, estimate the cost of equity for IBM. Estimate the cost of equity for each division. Which cost of equity would you use to value the printer division?
c. Assume that IBM divests itself of the mainframe business and pays the cash out as a dividend. Estimate the beta for IBM after the divestiture. (IBM had $ 1 billion in debt outstanding.)

8

You are an investment advisor who has been approached by a client for help on his financial strategy. He has $250,000 in savings in the bank. He is 55 years old and expects to work for 10 more years, making $100,000 a year.

(He expects to make a return of 5% on his investments for the foreseeable future. You can ignore taxes)

a. Once he retires 10 years from now, he would like to be able to withdraw $80,000 a year for the following 25 years (his actuary tells him he will live to be ninety years old.).

How much would he need in the bank 10 years from now to be able to do this?

b. How much of his income would he need to save each year for the next 10 years to be able to afford these planned withdrawals ($80,000 a year) after the tenth year?

c. Assume that interest rates decline to 4% 10 years from now. How much, if any, would you client have to lower his annual withdrawal by, assuming that he still plans to withdraw cash each year for the next 25 years?

9

10

What is the value of stock in a company that currently pays out $1.00 per share in dividends, and expects these dividends to grow 15% a year for the next 5 years, and 6% a year forever after that?

11

Savemart, which owns and operates grocery stores across the United States, currently has $50 million in debt and $100 million in equity outstanding. Its stock has a beta of 1.2.

It is planning a leveraged buyout , where it will increase its debt/equity ratio of 8.

If the tax rate is 40%, what will the beta of the equity in the firm be after the LBO?

Today is Rachel’s 30th birthday. Five years ago, Rachel opened a brokerage account when her grandmother gave her $25,000 for her 25th birthday. Rachel added $2,000 to this account on her 26th birthday, $3,000 on her 27th birthday, $4,000 on her 28th birthday, and $5,000 on her 29th birthday. Rachel’s goal is to have $400,000 in the account by her 40th birthday. Starting today, she plans to contribute a fixed amount to the account each year on her birthday. She will make 11 contributions, the first one will occur today, and the final contribution will occur on her 40th birthday. Complicating things somewhat is the fact that Rachel plans to withdraw $20,000 from the account on her 35th birthday to finance the down payment on a home. How large does each of these 11 contributions have to be for Rachel to reach her goal? Assume the account has earned (and will continue to earn) an effective return of 12% a year.

Today is Rachel’s 30

birthday. Five years ago, Rachel opened a brokerage account when her grandmother gave her $25,000 for her 25

birthday. Rachel added $2,000 to this account

on her 26

birthday, $3,000 on her 27

birthday, $4,000 on her 28

birthday, and $5,000 on her 29

birthday. Rachel’s goal is to have $400,000 in the account by her 40

birthday.

Starting today, she plans to contribute a fixed amount to the account each year on her birthday. She will make 11 contributi ons, the first one will occur today, and the final contribution

will occur on her 40

birthday. Complicating things somewhat is the fact that Rachel plans to withdraw $20,000 from the account on her 35

birthday to finance the down payment on

a home. How large does each of these 11 contributions have to be for Rachel to reach her goal? Assume the account has earned (and will continue to earn) an effective return of 12% a

year.

A financial analyst has been following Fast Start Inc., a new high-growth company. She estimates that the current risk-free rate is 6.25%, the market risk premium is 5%, and that Fast Start's beta is 1.75. The current earnings per share (EPS0) is $2.50. The company has a 40% payout ratio. The analyst estimates that the company's dividend will grow at a rate of 25% this year, 20% next year, and 15% the following year. After three years the dividend is expected to grow at a constant rate of 7% a year. The company is expected to maintain its current payout ratio. The analyst believes that the stock is fairly priced. What is the current price of the stock?

A financial analyst has been following Fast Start Inc., a new high -growth company. She estimates that the current risk -free rate is 6.25%, the market risk premium is 5%, and

that Fast Start's beta is 1.75. The current earnings per share (EPS

) is $2.50. The company has a 40% payout ratio. The analyst estimates that the company's dividend will

grow at a rate of 25% this year, 20% next year, and 15% the following year. After three years the dividend is expected to gr ow at a constant rate of 7% a year. Th e company is

expected to maintain its current payout ratio. The analyst believes that the stock is fairly priced. What is the current pr ice of the stock?

A financial analyst has been following Fast Start Inc., a new high -growth company. She estimates that the current risk -free rate is 6.25%, the market risk premium is 5%,

and that Fast Start's beta is 1.75. The current earnings per share (EPS

) is $2.50. The company has a 40% payout ratio. The analyst estimates that the company's

dividend will grow at a rate of 25% this year, 20% next year, and 15% the following year. After three years the dividend is expected to grow at a constant rate of 7% a

year. The company is expected to maintain its current payout ratio. The analyst believes that the stock is fairly priced. What is t he current price of the stock?

Firms U and L are in the same risk class and that both have EBIT = $1,000,000. Firm U uses no debt financing and its cost of equity is KsU=15%. Firm L has $2 million of debt outstanding at a cost of Kd = 5%. There are no taxes and MM assumptions hold.

1. Find V, S, Ks, and WACC for firms U and L.

2. Using the data given above, but now assuming that firms L and U are both subject to a 40% corporate tax rate, repeat the analysis under the MM with-tax model.

3. Now suppose investors are subject to the following tax rates: TD=20% and TS=10%. What is the gain from leverage according to the Miller’s Model?

4. How does this gain compare to the gain in the MM model with corporate taxes?

Firms U and L are in the same risk class and that both have EBIT = $1,000,000. Firm U uses no debt financing and its cost of equity is K

=15%. Firm L has $2 million of

debt outstanding at a cost of K

= 5%. There are no taxes and MM assumptions hold.

1. Find V, S, K

, and WACC for firms U and L.

2. Using the data given above, but now assuming that firms L and U are both subject to a 40% corporate tax rate, repeat the anal ysis under the MM with-tax model.

3. Now suppose investors are subject to the following ta x rates: T

=20% and T

=10%. What is the gain from leverage according to the Miller’s Model?

4. How does this gain compare to the gain in the MM model with corporate taxes?

You have just inherited $300,000 and have decided to purchase at least one established franchise in the fast food industry or possibly two if profitable. Your investment horizon is 3 years. You have narrowed down your choices to two choices: (1) Franchise L: Lisa’s Soups, Salads, and Stuff and (2) Franchise S: Sam’s Fried Chicken. The net cash flows shown below include the price you would receive for selling the franchise in 3 years and the forecast of how each franchise will do over the 3-year period. Franchise L’s cash flows will start off slowly but will increase rather quickly as people become more health conscious, while Franchise S’s cash flows will start off high but will trail off as other chicken competitors enter the marketplace and as people become more health conscious and avoid fried foods. Franchise L serves breakfast and lunch, while franchise S serves only dinner, so it is possible to invest in both franchises. You see these franchises as perfect complements to on another: you could attract both the lunch and dinner crowds and the health conscious and not so health conscious crowds with the franchises directly competing against one another.

Here are the projects’ net cash flows (in thousands of dollars):

Expected Net Cash Flows

Year Franchise L Franchise S

0 ($100) ($100)

1 10 70

2 60 50

3 80 20

Depreciation, salvage values, net working capital requirements, and tax effects are all included in these cash flows. You have made subjective risk assessment of each franchise, and concluded that both franchise have risk characteristics that require a return 10%. You must now determine whether one or both of the projects should be accepted.

1. The NPV of franchise L is ____________

2. The NPV of franchise S is ____________

3. The IRR of franchise L is _____________

4. The IRR of franchise S is _____________

5. The MIRR of L is ________

6. The MIRR is S is ________

7. Draw NPV profiles for both franchises. Show at what discount rate do the profiles cross?

down your choices to two choices: (1) Franchise L: Lisa’s Soups, Salads, and Stuff and (2) Franchise S: Sam’s Fried Chicken. The net cash flows shown below include the pric e you would receive for selling

the franchise in 3 years and the forecast of how each franchise will do over the 3 -year period. Franchise L’s cash flows will start off slowly but will increase rather quickly as people become more health

conscious, while Franchise S’s cash flows will start off high but will trail off as other chicken competitors enter the marke tplace and as people become more health conscious and avoid fried foods. Franchise

L serves breakfast and lunch, while franchise S serves only dinner, so it is possible to invest in both franchises. You see these franchises as perfect complements to on another: you could attra ct both the lunch

and dinner crowds and the health conscious and not so health conscious crowds with the franchises directly competing against one another.

Here are the projects’ net cash flows (in thousands of dollars):

Expected Net Cash Flows

Year Franchise L Franchise S

0 ($100) ($100)

1 10 70

2 60 50

3 80 20

franchise have risk characteristics that require a return 10%. You must now determine whether one or both of the projects sh ould be accepted.

1. The NPV of franchise L is ____________

2. The NPV of franchise S is ____________

3. The IRR of franchise L is _____________

4. The IRR of franchise S is _____________

5. The MIRR of L is ________

6. The MIRR is S is ________

7. Draw NPV profiles for both franchises. Show at what discount rate do the profiles cro ss?

During the past few years, Harry Davis Industries (HDI) has been constrained by high cost of capital to make many capital investments. Recently, though, capital costs have been declining and the company has decided to look seriously at a major expansion program that had been proposed by the marketing department. Assume that you are an assistant to the CFO. Your first task is estimate HDI’s cost of capital. The CFO has provided you with the following data, which is considered to your task:

1. The current price of HDI’s 12% coupon , seminal annual, non-callable bonds with 15 years to maturity is $1153.72. HDI does not use short-term interest bearing debt on a permanent basis. New bonds would be privately placed with no flotation costs.

2. The current price of HDI’s 10%, $100 par value, quarterly dividend, perpetual preferred stock is $113.10. HDI would incur flotation cost of $2.00 per share.

3. HDI’s common stock is currently selling for $50 per share. Its last dividend (d0) was $4.19, and dividends are expected to grow at a constant rate of 5% in the foreseeable future. HDI’s beta is 1.2; the yield on T-Bonds is 7%; and the market risk premium is estimated to be 6%. For the bond-yield-plus-risk-premium approach, the firm uses a 4% point risk premium.

4. HDI’s target capital structure:30% long-term, 10% pref. stock, and 60% common equity.

5. The firm’s tax rate is 40%

To structure the task somewhat, CFO has asked you to answer the following questions:

1. The after-tax cost of HDI’s debt, Kd is ___________

2. The firm’s cost of preferred stock, Kp, is __________

3. Using CAPM the firm’s cost of equity, Ke is _______

4. The estimated cost of equity using Discounted Cash Flow (DCF) model is ______

5. The cost of equity, Ke, based on the bond-yield-plus-risk-premium method is ____

6. The overall cost of equity, Ke is __________

7. The weighted average cost of capital (WACC) is _________

8. HDI estimates that if it issues new common stock, the flotation cost will be 15%. HDI incorporates the flotation costs into the DCF approach. The estimated cost of newly issued common stock, taking into account the flotation cost is _________

9. Suppose HDI has historically earned 15% on equity (ROE) and retained 35% of earnings, and investors expect this situation to continue in the future. How could you use this information to estimate the future dividend growth rate, and what growth rate would you get? Is this consistent with the 5% growth rate given?

HDI is interested in establishing a new division, which will focus primarily on developing new internet-based projects. In trying to determine the cost of capital for this new division, you discover that stand-alone firms involved in similar projects have on average the following characteristics:

- Capital structure of 10% debt and 90% equity.

- Cost of debt of 12% and beta of 1.7.

10. Given this information, your estimate of the division’s cost capital is _______

During the past few years, Harry Davis Industries (HDI) has been constrained by high cost of capital to make many capital inv estments. Recently, though, capital costs have been

declining and the company has decided to look seriously at a major expansion pr ogram that had been proposed by the marketing department. Assume that you are an assistant to the

CFO. Your first task is estimate HDI’s cost of capital. The CFO has provided you with the following data, which is considere d to your task:

1. The current price of HDI’s 12% coupon , seminal annual, non -callable bonds with 15 years to maturity is $1153.72. HDI does not use short -term interest bearing debt on a

permanent basis. New bonds would be privately placed with no flotation costs.

2. The current price of HDI’s 10%, $100 par value, quarterly dividend, perpetual preferred stock is $113.10. HDI would incur flotation cost of $2.00 per share.

3. HDI’s common stock is currently selling for $50 per share. Its last dividend (d

) was $4.19, and dividends are expected to grow at a constant rate of 5% in the foreseeable future.

HDI’s beta is 1.2; the yield on T-Bonds is 7%; and the market risk premium is estimated to be 6%. For the bond -yield-plus-risk-premium approach, the firm uses a 4% point risk

premium.

4. HDI’s target capital structure:30% long-term, 10% pref. stock, and 60% common equity.

5. The firm’s tax rate is 40%

To structure the task somewhat, CFO has asked you to answer the following questions:

1. The after-tax cost of HDI’s debt, K

is ___________

2. The firm’s cost of preferred stock, K

, is __________

3. Using CAPM the firm’s cost of equity, K

is _______

4. The estimated cost of equity using Discounted Cash Flow (DCF) model is ______

5. The cost of equity, K

based on the bond-yield-plus-risk-premium method is ____

6. The overall cost of equity, K

is __________

7. The weighted average cost of capital (WACC) is _________

8. HDI estimates that if it issues new common stock, the flotation cost will be 15%. HDI incorporates the flota tion costs into the DCF approach. The estimated cost of newly

issued common stock, taking into account the flotation cost is _________

9. Suppose HDI has historically earned 15% on equity (ROE) and retained 35% of earnings, and investors expect this situa tion to continue in the future. How could you use this

information to estimate the future dividend growth rate, and what growth rate would you get? Is this consistent with the 5% growth rate given?

HDI is interested in establishing a new division, which w ill focus primarily on developing new internet -based projects. In trying to determine the cost of capital for this new division,

you discover that stand-alone firms involved in similar projects have on average the following characteristics:

- Capital structure of 10% debt and 90% equity.

- Cost of debt of 12% and beta of 1.7.

10. Given this information, your estimate of the division’s cost capital is _______