Question

Chapter: $7$

Problem: $25$

Selected data for the Derby Corporation are shown below. Use the data to answer the following questions. Please show calculations for each input of data. Also show all formulas for cells highlighted in yellow in the attached picture. This is meant to be completed in an excel spreadsheet using formals and is problem $25$ from Financial Management: Theory and Practice $17$th edition.

Free cash flows $($FCF$)$:

Year $1$: $-12\mathrm{.}0$

Year $2$: $15\mathrm{.}0$

Year $3$: $43\mathrm{.}0$

Year $4$: $45\mathrm{.}0$

Marketable Securities: $20$

Notes Payable: $80$

Long$-$Term Bonds: $200$

Preferred Stock: $40$

Weighted Average Cost of Capital $($WACC$)$: $8\mathrm{.}00\%$

Number of Common Shares Outstanding: $30$

a$.$ Calculate the estimated horizon value.

This is the value of operations at the end of Year $3($immediately after the Year$-4$ free cash flow$),$ assuming that free cash flows grow at a constant rate beyond Year $3.$

Use the formula:

Horizon Value $($HV$)=$ FCF in Year $4/($WACC $-$ g$)$

Where $"$g$"$ is the constant long$-$term growth rate in FCF$.$

b$.$ Calculate the present value at Year $0$ of the following:

The horizon value $($calculated in part a$)$

The projected free cash flows for Years $1,2,$ and $3$

The estimated total value of operations at Year $0,$ which is the sum of the present values from steps $1$ and $2$ above

c$.$ Calculate the estimated Year$-0$ price per share of common equity:

Start with the total value of operations $($from part b$)$

Add the value of marketable securities

Subtract the value of debt $($notes payable and long$-$term bonds$)$

Subtract the value of preferred stock

The result is the estimated value of common equity

Divide the common equity value by the number of common shares outstanding $(30)$

The result is the estimated price per share