Excel Activity: Free Cash Flow Valuation Model a$.$ Calculating the estimated horizon value $($i$.$e$.,$ the value of operations at the end of the forecast

period immediately after the Year$-4$ free cash flow$)$

Constant long$-$term growth rate

b$.$ Calculating the present value of the horizon value, the present value of the free cash flows,

and the estimated Year$-0$ value of operations

Start with the partial model in the file Ch$08$ P$25$ Build a Model.xlsx$.$ Selected data for the Derby Corporation are shown here. Use the data to answer the questions.

INPUTS $($In Millions$)$

Download spreadsheet Ch$08$ P$25$ Build a Model$-$a$01$e$4$b$.$xlsx

millions. For example, an answer of $$1\mathrm{.}23$ million should be entered as $1\mathrm{.}23,$ not $1,230,000.$ Round your answer to two decimal places.

$

million

entered as $1\mathrm{.}23,$ not $1,230,000.$ Round your answers to two decimal places.

c$.$ Calculate the estimated Year$-0$ price per share of common equity. Round your answer to the nearest cent.

Free Cash Flow Valuation Model

INPUTS $($In millions$)$ Year

Current Projected

$01234$

Free cash flow $-$$$15\mathrm{.}0$ $$15\mathrm{.}0$ $$70\mathrm{.}0$ $$73\mathrm{.}5$

Marketable securities $$50$

Notes payable $$120$

Long$-$term bonds $$480$

Preferred stock $$60$

WACC $10\mathrm{.}00\%$

Number of shares of stock $25$

a$.$ Calculating the estimated horizon value $($i$.$e$.,$ the value of operations at the end of the forecast

period immediately after the Year$-4$ free cash flow$)$ Formulas

Constant long$-$term growth rate #N$/$A

Horizon value at Year $4$ million #N$/$A

b$.$ Calculating the present value of the horizon value, the present value of the free cash flows,

and the estimated Year$-0$ value of operations

Present value of HV million #N$/$A

Present value of FCF million #N$/$A

Value of operations million #N$/$A

$.$ Calculating the estimated Year$-0$ price per share of common equity

Value of operations million #N$/$A

$+$ Value of marketable securities million #N$/$A

Total intrinsic value million #N$/$A

$-$ Value of total debt million #N$/$A

$-$ Value of preferred stock million #N$/$A

Intrinsic value of equity million #N$/$A

$-$: Number of shares million #N$/$A

Intrinsic stock price #N$/$A