Devon Inc. has developed a powerful efficient snow blower that is significantly less polluting than existing snow blowers currently on the market. The company spent $$2,500,000$ developing this product and the marketing department spent another $$350,000$ to assess the market demand. It would cost $$25$ 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 $($NOWC$0=20\%($Sales$1),$ NOWC$1=20\%($Sales$2),$ etc.$).$ The efficient snow blowers would sell for $$3,500$ per unit, and the company believes that variable costs would amount to $$990$ per unit. The company expects that the sales price and variable costs would increase at the inflation rate of $4\%$ after year $1.$ The company$$s 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 project is expected to be of average risk. 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 project$$s $4-$year life is its undepreciated capital cost $($i$.$e$.$ book value$)$ at the end of year $4.$ The company has other assets in this asset class. Toefield Inc.$$s federal$-$plus$-$provincial tax rate is $30\%.$ Its cost of capital is $7\%$ for average risk projects. Low$-$risk projects are evaluated with a WACC of $6\%,$ and high$-$risk projects at $10\%.$ Assume that the half$-$year rule applies to the CCA.

a$.$ Develop a spreadsheet model and use it to find the project's NPV$,$ IRR, and payback.

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

Equipment cost

Net Operating WC$/$sales

Yearly sales $($in units$)$

Sales price per unit

Variable cost per unit

Non$-$variable costs

Part $2.$ CCA Schedule

Beg. UCC

CCA

End UCC

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

Equipment

Operating Cash Flows over the Project's Life:

Units sold

Sales price per unit

Variable costs per unit

Sales revenue

Variable costs

Non$-$rariable operating costs

Depreciation $($equipment$)$

Oper. income before taxes $($EBIT$)$

Taxes on operating income

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$)$ I. Sensitivity of NPV to Changes in Inputs. Here we use an Excel "Data Table" to find NPV for different unit sales, variable costs, WACC, and sales prices, holding other thing constant.

Note about data tables. The data in the column input should NOT be input using a cell reference to the column input cell. For example the base case number of units sold in cell B$100$ should be the number $1000$; you should NOT have the formula $=$D$29$ in that cell. This is because you'll use D$29$ as the column input cell in the data table and if Excel tries to iteratively replace cell D$29$ with the formula $=$D$29$ rather than a series of numbers, Excel will calculate the wrong answer. Unfortunately, Excel won't tell you that there is a problem, so you'll just get the wrong values for the data table!

Sensitivity Analysis

c$.$ Would you recommend that the project be accepted? Explain.