Snow Inc. has developed a powerful efficient snow blower that is significantly less polluting than existing snow blowers currently on the market. The company spent $$1,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,600$ per unit, and the company believes that variable costs would amount to $$1100$ 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. Its 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.Snow Inc. has developed a powerful efficient snow blower that is significantly less polluting than existing snow blowers currently on the

market. The company spent $$1,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,600$ per unit, and the company believes that variable costs would amount

to $$1100$ 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. Its

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

Investment Outlays at Time Zero:

Equipment

Operating Cash Flows over the Project's Life:

Units sold

Sales price per unit

Variable costs per unit

Sales revenue

Variable costs

Non$-$variable 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:

$ $1,500,000$

$20\%$

$3,500$

$$3,600\mathrm{.}00$

$$1,100\mathrm{.}00800,000\mathrm{.}00$

Please answer in excel