Make sure to show the excel formulas and calculations

Given:

($ '000)

Base sales:

$250,000

Base year

Fade rate

Yr. 10 and after

Note: Fade rate is the year-to-year reduction in the rate to some steady state rate

Sales growth:

1.15

0.01

1.05

Year 1 = 1.14, year 2 = 1.13, etc.

Operating PM

0.20

0.01

0.1

Fixed capital inv rate

0.15

Note: on a per $ of sales basis

WC inv rate

0.09

Note: on a per $ of sales basis

Cash tax rate

0.38

COC

0.11

Market securities

$20,000

Debt

$50,000

# shares outstanding

5,000,000

(all dollars in thousands)

Year1

Year 2

Year 3

Year 4

Year 5

Year 6

Year 7

Year 8

Year 9

Year 10

Residual

Sales

$285,000.00

Operating Profit

$54,150.00

NOPAT

$33,573.00

New Investment

$5,250.00

Add'l WC

$3,150.00

Free cash flow

$25,173.00

PV to year 10

Pv after year 10

1. Value years 1-10 ?

2. Value after year 10 ?

Market securities ?

Total Value ?

Less debt ?

3. Value of equity ?

4. Value of equity/share ?

Scenario #1= $50/share

Scenario #2= $70/share a. What is the PV of operating cash flows over the competitive advantage period? b. What is the residual value of the firm after the period of competitive advantage? c.What is the value of the firm's equity?