Calculate the following in Excel

	Year	Year	Year	Year 3	Year
	0	1	2	3	4
Unit sales
Revenues
Costs
Depreciation
EBIT
Taxes
NOPAT
Depreciation
Operating cash flow
Initial investment
After-tax salvage value
Investment in net working capital
Net cash flows
Present value of net cash flows

Investment-related cash flow forecasts:

Invoice price of the machinery = $500,000

Shipping charges = $35,000

Installation cost = $10,000

Depreciable basis = $545,000

Salvage value = $95,000

Investment in net working capital = 20% of next years sales

MACRS depreciation rates:

Year 1: 0.3333

Year 2: 0.4445

Year 3: 0.1481

Year 4: 0.0741

Operating cash flow forecasts:

Projects economic life = 4 years

Year 1 unit sales = 3,200 units

Unit growth = 4%

Year 1 price per unit = $250.00/unit

Year 1 cost per unit = $185.00/unit

Inflation rate = 2.5%

SMCs current market value capital structure:

Bonds $25,000,000 (30%)

Preferred Stock $5,000,000 (5%)

Common Equity $70,000,000 (70%)

Total $100,000,000

Data to be used in the calculation of the cost of debt:

Par value = $1,000, non-callable

Market value = $978.22

Coupon interest = 6%, annual payments

Remaining maturity = 20 years

New bonds can be privately placed without any flotation costs

Data to be used in the calculation of the cost of preferred stock:

Par value = $100

Annual dividend = 7% of par

Market value = $103.00

Flotation cost = 4%

Data to be used in the calculation of the cost of common equity:

CAPM data:

VECs beta = 1.2

The yield on T-bonds = 3%

Market risk premium = 7%

DCF data:

Stock price = $32.23

Last years dividend (D0) = $2.10

Expected dividend growth rate = 5%

Bond-yield-plus-risk-premium data:

Risk premium = 6%

Amount of retained earnings available = $100,000

Floatation cost for newly issued shares = 6%