Computer applications in Finance

Ps: Solving on excel please

Stock Valuation $($LO#$4)$

Because the cash flows provided by bonds are set by contract, it is generally easy to predict their

cash flows. Preferred stock dividends are also set by contract, which makes them similar to bonds,

and they are valued in much the same way. However, common stock dividends are not

contractual$$they depend on the firm$$s earnings, which in turn depend on many random factors,

making their valuation more difficult. Two fairly straightforward models are used to estimate

stocks$$ intrinsic $($or $$true$)$ values: $(1)$ the discounted dividend model and $(2)$ the corporate

valuation model. A stock should, of course, be bought if its price is less than its estimated intrinsic

value and sold if its price exceeds its intrinsic value. Business Students, basically finance students,

should be able to discuss the legal rights of stockholders. They have to explain the distinction

between a stock$$s price and its intrinsic value. They have to identify the two models that can be

used to estimate a stock$$s intrinsic value: the discounted dividend model and the corporate

valuation model.

Stock valuation is interesting in its own right, but you also need to understand valuation when

estimating a firm$$s cost of capital for use in its capital budgeting analysis, which is probably a

firm$$s most important task.

In the light of the above discussion. Apply your understanding of the topic in regards to the

following case:

Robert Balik and Carol Kiefer are senior vice presidents of the Mutual of Chicago Insurance

Company. They are codirectors of the company$$s pension fund management division, with Balik

having responsibility for fixed$-$income securities $($primarily bonds$)$ and Kiefer being responsible

for equity investments. A major new client, the California League of Cities, has requested that

Mutual of Chicago present an investment seminar to the mayors of the represented cities; and Balik

and Kiefer, who will make the actual presentation, have asked you to help them.

To illustrate the common stock valuation process, Balik and Kiefer have asked you to analyze

the Bon Temps Company, an employment agency that supplies word$-$processor operators and

computer programmers to businesses with temporarily heavy workloads. You are to answer the

following questions.

A$.$ Describe briefly the legal rights and privileges of common stockholders.

B$.(1)$ Write a formula that can be used to value any stock, regardless of its dividend pattern.

$(2)$ What is a constant growth stock? How are constant growth stocks valued?

$(3)$ What are the implications if a company forecasts a constant g that exceeds its rs$?$ Will

many stocks have expected g $>$ rs in the short run $($i$.$e$.,$ for the next few years$)?$ In the

long run $($i$.$e$.,$ forever$)?$

$2$

C$.$ Assume that Bon Temps has a beta coefficient of $1\mathrm{.}2,$ that the risk$-$free rate $($the

yield on T$-$bonds$)$ is $3\%,$ and that the required rate of return on the market is $8\%.$

What is Bon Temps$$s required rate of return?

D$.$ Assume that Bon Temps is a constant growth company whose last dividend $($D$0,$

which was paid yesterday$)$ was $$2\mathrm{.}00$ and whose dividend is expected to grow

indefinitely at a $4\%$ rate.

$(1)$ What is the firm$$s expected dividend stream over the next $3$ years?

$(2)$ What is its current stock price?

$(3)$ What is the stock$$s expected value $1$ year from now?

$(4)$ What are the expected dividend yield, capital gains yield, and total return

during the first year?

E$.$ Now assume that the stock is currently selling at $$40\mathrm{.}00.$ What is its expected rate of

return?

F$.$ What would the stock price be if its dividends were expected to have zero growth?

G$.$ Now assume that Bon Temps$$s dividend is expected to grow $30\%$ the first year, $20\%$

the second year, $10\%$ the third year, and return to its long$-$run constant growth rate

of $4\%.$ What is the stock$$s value under these conditions? What are its expected

dividend and capital gains yields in Year $1?$ Year $4?$

H$.$ Suppose Bon Temps is expected to experience zero growth during the first $3$ years

and then resume its steady$-$state growth of $4\%$ in the fourth year. What would be its

value then? What would be its expected dividend and capital gains yields in Year $1?$

In Year $4?$

I. Finally, assume that Bon Temps$$s earnings and dividends are expected to decline at

a constant rate of $4\%$ per year, that is$,$

g $=-4\%.$ Why would anyone be willing to buy such a stock, and at what price should

it sell? What would be its dividend and capital gains yields in each year?

J$.$ Suppose Bon Temps embarked on an aggressive expansion that requires additional

capital. Management decided to finance the expansion by borrowing $$40$ million and

by halting dividend payments to increase retained earnings. Its WACC is now $7\%,$

and the projected free cash flows for the next $3$ years are $-$$$5$ million, $$10$ million,

and $$20$ million. After Year $3,$ free cash flow is projected