Question

COMPLETE IN EXCEL SHOWING EQUATIONS

Variable Growth Valuation Model Given:

Most recent annual cash flow (CFo) $1,000

Stage 1 annual growth rate (gt) 0.15

Stage 2 annual growth rate (gm) 0.05

Duration of stage 1 growth (years) 3

Required return (k) 0.10

Steps in solution:

1) Estimate future cash flows for the duration of the stage 1 growth period:

CF(0) = given $1,000

CF(1) = CF(0) * (1+gt) $1,150

CF(2) = CF(1) * (1+gt) $1,323

CF(3) = CF(2) * (1+gt) $1,521

2) Use EXCEL's built-in NPV function to find the present value of these cash flows.

RATE 0.10

NPV (rate, CF1:CF3) $3,281.09

3) Use the constant growth formula to estimate the value of the firm at the end of the rapid growth period (V3) where:

CF4 = CF3 (1+gm)

CF4 = $1,597

V3 = $1,597 / (0.10-0.05)

Ve = $31,938

4) Use EXCEL's built-in PV function to find the present value of the amount determined in step 3.

FV $31,938

RATE 0.10

NPER 3

PV ($23,996)

(You may ignore the negative sign for this answer. Recall that EXCEL mandates that the amounts entered for PV and FV must have opposite signs.)

5) Add the amounts found in step 2 (the PV of the rapid growth cash flows ) and step 4 (the PV of the normal growth cash flows).

PV of rapid growth cash flows $3,281

PV of normal growth cash flows $23,996

value of the firm $27,277

Answer 1

I showed complete working in Excel Pleses refer