Requirements

$1$ In cell range D$16$:I$16,$ by using cell references, calculate the earnings per share for years $1$:$6,$ respectively $(1$ pt$.).$

$2$ In cell range D$19$:I$19,$ by using cell references, calculate the dividend payout ratios for years $1$:$6,$ respectively $(1$ pt$.).$

$3$ In cell range D$20$:I$20,$ by using cell references, calculate the dividends for years $1$:$6,$ respectively $(1$ pt$.).$

$4$ In cell D$22,$ by using cell references, calculate the present value in Year $5$ of the dividends after year $5(1$ pt$.).$

$5$ In cell D$23,$ by using cell references, calculate the price of the stock today $(1$ pt$.).$Halliford Corporation eupects to have earnings this coming year of $$3$ per share. Halliford plans to retain all of its

earnings for the nest wo years. Then. for the subsequent wo years, the firm will retain $50\%$ of its earnings. It will

retain $20\%$ of is earnings from that point onw ard. Each year, retained earnings will be invested in new projects with

an expected return of $25\%$ per year. Any earnings thet are not retained will be paid out as dividenids. Assume

Halliford's share count remains constant and all earnings grow th comes from the inwestment of retained earninge. If

Halliford's equity cost of capital is $10\%,$ what price would you estimate for Halliford stock?

Complete the steps below wing cell references to given data or provious calculations. In some cases, a simple cell reference is

all you need. To copplpaste a fonnila across a row or down a colum, an absolute cell reference or a mixed cell reference

mav be prefered if a specift Excel function is to be used the directions will specifl the ise of that flonction. Do not tvpe in

nimerical data mo a cell or funcrion. Instead, nake a reference to the cell in which the data is found. Male your

computations ony in the blie cells highighted below. In all cases, whess otherwise directed, lise the earliest appear ance of

the data in your formulas, whath the Gien Data section.

Earnings per share

Retention period $($years$)$

Retention rate $[1]$

Aetention rate $(2)$

Expected return

Equity cost of capital

Year

Earnings

EPS growthrate $($us$,$ prior year$)$

EPS

Dividends

Petention rate

Llividend peyour rakio

Dividends

$1$

Year $5$ walwe of dividends after year $5$

Stock price