Please complete everything from Working Capital Section. and include formulas

Banff 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,000,000 developing this product and the marketing department spent another $400,000 to assess the market demand. It would cost $28 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 (NOWCO = 20% (Sales1), NOWC1 = 20%(Sales2), etc.). The efficient snow blowers would sell for $3,800 per unit, and the firm believes that variable costs would amount to $880 per unit. The company expects that the ales price and variable costs would increase at the inflation rate of 3% after year 1. The company's non-variable costs would be $950,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. The firm believes it could sell 3,400 units per year. The firm thinks that this is an average risk roject. 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 ife is its undepreciated capital cost (i.e. book value) at the end of year 4. The firm has other assets in this asset class. Its federal-plus-provincial ax 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. 1. 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) S Equipment cost Net Operating WC/sales Yearly sales in units) Sales price per unit Variable cost per unit Non-variable costs 28,000,000 10% 3,400 $3,800.00 S880.00 $950,000.00 Tax rate WACC Inflation CCA rate 30% 6.0% 3 30.0% Part 2. CCA Schedule year 3 year 4 Beg. UCC SCA End UCC year 1 year 2 28,000,000 23,800,000 4,200,000 7,140,000 $23,800,000 $16,660,000 16,660,000 4,998,000 $11,662,000 11,662,000 3,498,600 $8,163,400 Part 3. Projected Net Cash Flows (Time line of annual cash flows) Years 0 1 2 3 4 Investment Outlays ar Time Zero: Equipment $ 5,000,000 Operating Cash Flows over the Project's Life: Units sold Sales price per unit Variable costs per unit 3,400 $3,800.00 $880.00 3,400 3,800.00 880.00 3,400 3,800.00 880.00 3,400 3,800.00 880.00 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 $12,920,000 880 950,000 4,200,000 7,769,120 2,330,736 5,438,384 4,200,000 $9,638,384 $12,920,000 880 978,500 7,140,000 4,800,620 1,440,186 3,360,434 7,140,000 $10,500,434 $12,920,000 880 1,007,855 4,998,000 6,913,265 2,073,980 4,839,286 4,998,000 $9,837,286 $12,920,000 880 1,038,091 3,498,600 8,382,429 2,514,729 5,867,701 3,498,600 $9,366,301 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) Part 4. Key Output: Appraisal of the Proposed Project Net Present Value IRR MIRR Payback (See calculation below) Data for Payback Years Net cash flow Cumulative CF Part of year required for payback: 0.00 0.00 0.00 0.00 Banff 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,000,000 developing this product and the marketing department spent another $400,000 to assess the market demand. It would cost $28 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 (NOWCO = 20% (Sales1), NOWC1 = 20%(Sales2), etc.). The efficient snow blowers would sell for $3,800 per unit, and the firm believes that variable costs would amount to $880 per unit. The company expects that the ales price and variable costs would increase at the inflation rate of 3% after year 1. The company's non-variable costs would be $950,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. The firm believes it could sell 3,400 units per year. The firm thinks that this is an average risk roject. 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 ife is its undepreciated capital cost (i.e. book value) at the end of year 4. The firm has other assets in this asset class. Its federal-plus-provincial ax 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. 1. 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) S Equipment cost Net Operating WC/sales Yearly sales in units) Sales price per unit Variable cost per unit Non-variable costs 28,000,000 10% 3,400 $3,800.00 S880.00 $950,000.00 Tax rate WACC Inflation CCA rate 30% 6.0% 3 30.0% Part 2. CCA Schedule year 3 year 4 Beg. UCC SCA End UCC year 1 year 2 28,000,000 23,800,000 4,200,000 7,140,000 $23,800,000 $16,660,000 16,660,000 4,998,000 $11,662,000 11,662,000 3,498,600 $8,163,400 Part 3. Projected Net Cash Flows (Time line of annual cash flows) Years 0 1 2 3 4 Investment Outlays ar Time Zero: Equipment $ 5,000,000 Operating Cash Flows over the Project's Life: Units sold Sales price per unit Variable costs per unit 3,400 $3,800.00 $880.00 3,400 3,800.00 880.00 3,400 3,800.00 880.00 3,400 3,800.00 880.00 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 $12,920,000 880 950,000 4,200,000 7,769,120 2,330,736 5,438,384 4,200,000 $9,638,384 $12,920,000 880 978,500 7,140,000 4,800,620 1,440,186 3,360,434 7,140,000 $10,500,434 $12,920,000 880 1,007,855 4,998,000 6,913,265 2,073,980 4,839,286 4,998,000 $9,837,286 $12,920,000 880 1,038,091 3,498,600 8,382,429 2,514,729 5,867,701 3,498,600 $9,366,301 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) Part 4. Key Output: Appraisal of the Proposed Project Net Present Value IRR MIRR Payback (See calculation below) Data for Payback Years Net cash flow Cumulative CF Part of year required for payback: 0.00 0.00 0.00 0.00