Toefield Inc. has developed a powerful efficient snow remover that is significantly less polluting than existing snow removers currently on the market. The company spent $2,000,000 developing this product and the marketing department spent another $300,000 to assess the market demand. It would cost $20 million at Year 0 to buy the equipment necessary to manufacture the efficient snow blower. The project would require net working capital at the beginning of each year equal to 20% of sales (NOWC0 = 20%(Sales1), NOWC1 = 20%(Sales2), etc.). The efficient snow blowers would sell for $3,000 per unit, and Toefield believes that variable costs would amount to $790 per unit. The company expects that the sales price and variable costs would increase at the inflation rate of 2% after year 1. The companys non-variable costs would be $800,000 in Year 1 and are expected to increase with inflation. The efficient snow blower project would have a life of 4 years. If the project is undertaken, it must be continued for the entire 4 years. Also, the projects returns are expected to be highly correlated with returns on the firms other assets. The firm believes it could sell 3,500 units per year. The equipment would be depreciated using a CCA rate of 30%. The estimated market value of the equipment at the end of the projects 4-year life is its undepreciated capital cost (i.e. book value) at the end of year 4. Toefield has other assets in this asset class. Toefield Inc.s federal-plus-provincial tax rate is 30%. Its cost of capital is 9% for average risk projects. Low-risk projects are evaluated with a WACC of 6%, and high-risk projects at 12%. Assume that the half-year rule applies to the CCA.

a. Develop a spreadsheet model and use it to find the projects NPV, IRR, and payback.

Part 1. Input Data (in thousands of dollars except for unit amount)

Equipment cost

$ 20,000

Net Operating WC/sales

20%

Yearly sales (in units)

3,500

Tax rate

30%

Sales price per unit

WACC

9%

Variable cost per unit

Inflation

2.0%

Non-variable costs

CCA rate

30.0%

Part 3. Projected Net Cash Flows (Time line of annual cash flows)

Years

0

1

2

3

4

Investment Outlays at Time Zero:

Equipment

$ 20,000

Operating Cash Flows over the Project's Life:

Units sold

3,500

3,500

3,500

3,500

Sales price

Variable costs

Sales revenue

Variable costs

Non-variable operating costs

Depreciation (equipment)

Oper. income before taxes (EBIT)

Taxes on operating income (40%)

Net Operating Profit After Taxes (NOPAT)

Add back depreciation

Operating cash flow

Working Capital:

Required level of net operating working capital

Required investment in NOWC

Terminal Year Cash Flows:

Net salvage value

Net Cash Flow (Time line of cash flows)

Please show excel formulas