Are these calculations correct? Also please help with the blank yellow cells. Thanks

B 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 (NOWCO 20 % ( Salesl ) , NOWCI 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 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's returns are expected to be highly correlated with returns on the firm's other assets. The firm believes it could sell 5,000 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 $500,000. 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%. a. Develop a spreadsheet model and use it to find the project's NPV, IRR, and payback. Key Output: NPV Part 1. Input Data (in thousands of dollars except for unit amount) 2 3 Equipment cost 4 Net Operating WC/sales 5 Yearly sales (in units) 6 Sales price per unit 27 Variable cost per unit 28 Non-variable costs 29 1 IRR MIRR 20,000.00 Market value of equipment at Year 4 20% $500.00 5,000 Tax rate 30% $3.00 WACC 9% S0.79 Inflation 2.0% $800 CCA rate 30.0% 30 31 Part 2. CCA Schedule 32 33 vear 1 Beg. UCC 20,000 Years CCA End UCC 6,000 4,200 2,940 S14,000 34 year 2 14,000 $9,800 35 ar 3 vear 4 9,800 $6,860 36 6,860 2,058 $4,802 37 38 39 40 Part 3. Projected Net Cash Flows (Time line of annual cash flows) 41 42 C A Investment Outlays at Time Zero: S (20,000.00) Equipment Operating Cash Flows over the Project's Life: Units sold Sales price 5,000 $3.12 S0.82 5,000 5,000 5,000 $3.18 $3.00 $0.79 $3.06 $0.84 S0.81 Variable costs S15,918.12 $15,300.00 $15,606.00 $15,000.00 Sales revenue 4,110 4,192 4,029 3,950 Variable costs 832 849 800 816 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 4,200 6,255 2,502 3,753 2,058 2,940 7,724 6.000 8,819 4,250 3,528 3,090 4,634 1,700 5,292 2,550 2,940 2,058 600 4,200 $7.953.00 S8,550.00 S7,574.46 S7,349.63 Net Working Capital: Required level of net operating working capital Required investment in NOWC S3,121.20 S3,000.00 $3,060.00 S3,183.62 $0.00 ($3,000.00) (S61.20) ($60.00) ($62.42) $3,183.62 Terminal Year Cash Flows: 7 Net salvage value 8 9 Net Cash Flow (Time line of cash flows) 2,221 ($23,000.00) $12,754.05 $8,490.00 S7,891.80 S7.512.04 Part 4. Key Output: Appraisal of the Proposed Project 2 3 1 Net Present Value (at 9%) $6,267 4 IRR 20.00% 5 MIRR 6 7 Payback (See calculation below) 16.00% 2.88 9 Data for Payback Years 30 81 82 83 Net cash flow Cumulative CF Part of year required for payback: (23,000) (141,510) (6,618) (894) (13,648) 1.00 1.00 1.00 1.00 Actual $0 + Spreadsheet Prob Template J H G E A h. Conduct a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs per unit, and number of units sold. Set these variables' values at 10% and 25 % above and below their base case values. Include a graph in your analysis. 87 88 Evaluating Risk: Sensitivity Analysis 89 90 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 92 sales prices, holding other thing constant. 91 93 WACC 1st YEAR UNIT SALES NPV % Deviation % Deviation from 94 NPV from Units 95 6,267 WACC $6,267 Base Case Sold 96 Base Case -25% | % -10 % S0 971 -25% 97 -10 % 4,149 98 S0 0% 6,267 S0 0% 99 8,386 11,564 S0 S0 10% 10% 100 25% 101 S0 25% 102 % Deviation VARIABLE COSTS % Deviation 103 SALES PRICE NPV 104 from Variable from Sales NPV Base Case 105 Costs $6,267 Base Case Price $6,267 -25% 106 $8,241.01 S0 -25% -10 % 107 $7,056.80 S0 -10% 0% S0 108 0% $6,267.32 $5,477.85 S0 S0 109 10% 10% 110 25% so $4,293.64 25% S0 111 NON-VARIABLE COSTS 112 % Deviation 113 from Fixed NPV Base Case 114 Costs $6,267 Note about data tables. The data in the column input shouldNOT be input using a cell reference to the column input cell. For example the base case number of units sold in cell B100 should be the number 1000; you should NOT have the formula-D29 in that cell. This is because you'll use D29 as the column input cell in the data table and if Excel tries to iteratively replace cell D29 with the formula -D29 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! 115 -25% 56,667 -10 % 116 $3,359 S0 117 0% $6,267 $6,107 $5,868 S0 118 10% 119 25% S0 120 121 122 Sensitivity Analysis $12,000.00 $10,100.00 $8,200.00 $6,300.00 Sales price VC -Units Non-var. cost WACC $4,400.00 $2,500.00 $600.00 1 2 3 ($1,300.00) ($3,200.00) ($5,100.00) 4 5 37 38 (S7,000.00) -20% 20% 0% 40 41 42 43 Deviation NPV at Different Deviations from Base Note: Copy the results from the sensitivity analysis into this table. Sales Price 44 from Variable Non-variable Cost 145 146 Base Case Cost Units Sold WACC -25% -10% 147 0% 148 10% 25% 149 150 151 00 152 Range -00. 0S -00. S -00. 153 154 155 c. Would you recommend that the projeet be accepted? Explain. 156 157 154 159 160 161 162 Actual $0 NPV B 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 (NOWCO 20 % ( Salesl ) , NOWCI 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 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's returns are expected to be highly correlated with returns on the firm's other assets. The firm believes it could sell 5,000 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 $500,000. 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%. a. Develop a spreadsheet model and use it to find the project's NPV, IRR, and payback. Key Output: NPV Part 1. Input Data (in thousands of dollars except for unit amount) 2 3 Equipment cost 4 Net Operating WC/sales 5 Yearly sales (in units) 6 Sales price per unit 27 Variable cost per unit 28 Non-variable costs 29 1 IRR MIRR 20,000.00 Market value of equipment at Year 4 20% $500.00 5,000 Tax rate 30% $3.00 WACC 9% S0.79 Inflation 2.0% $800 CCA rate 30.0% 30 31 Part 2. CCA Schedule 32 33 vear 1 Beg. UCC 20,000 Years CCA End UCC 6,000 4,200 2,940 S14,000 34 year 2 14,000 $9,800 35 ar 3 vear 4 9,800 $6,860 36 6,860 2,058 $4,802 37 38 39 40 Part 3. Projected Net Cash Flows (Time line of annual cash flows) 41 42 C A Investment Outlays at Time Zero: S (20,000.00) Equipment Operating Cash Flows over the Project's Life: Units sold Sales price 5,000 $3.12 S0.82 5,000 5,000 5,000 $3.18 $3.00 $0.79 $3.06 $0.84 S0.81 Variable costs S15,918.12 $15,300.00 $15,606.00 $15,000.00 Sales revenue 4,110 4,192 4,029 3,950 Variable costs 832 849 800 816 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 4,200 6,255 2,502 3,753 2,058 2,940 7,724 6.000 8,819 4,250 3,528 3,090 4,634 1,700 5,292 2,550 2,940 2,058 600 4,200 $7.953.00 S8,550.00 S7,574.46 S7,349.63 Net Working Capital: Required level of net operating working capital Required investment in NOWC S3,121.20 S3,000.00 $3,060.00 S3,183.62 $0.00 ($3,000.00) (S61.20) ($60.00) ($62.42) $3,183.62 Terminal Year Cash Flows: 7 Net salvage value 8 9 Net Cash Flow (Time line of cash flows) 2,221 ($23,000.00) $12,754.05 $8,490.00 S7,891.80 S7.512.04 Part 4. Key Output: Appraisal of the Proposed Project 2 3 1 Net Present Value (at 9%) $6,267 4 IRR 20.00% 5 MIRR 6 7 Payback (See calculation below) 16.00% 2.88 9 Data for Payback Years 30 81 82 83 Net cash flow Cumulative CF Part of year required for payback: (23,000) (141,510) (6,618) (894) (13,648) 1.00 1.00 1.00 1.00 Actual $0 + Spreadsheet Prob Template J H G E A h. Conduct a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs per unit, and number of units sold. Set these variables' values at 10% and 25 % above and below their base case values. Include a graph in your analysis. 87 88 Evaluating Risk: Sensitivity Analysis 89 90 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 92 sales prices, holding other thing constant. 91 93 WACC 1st YEAR UNIT SALES NPV % Deviation % Deviation from 94 NPV from Units 95 6,267 WACC $6,267 Base Case Sold 96 Base Case -25% | % -10 % S0 971 -25% 97 -10 % 4,149 98 S0 0% 6,267 S0 0% 99 8,386 11,564 S0 S0 10% 10% 100 25% 101 S0 25% 102 % Deviation VARIABLE COSTS % Deviation 103 SALES PRICE NPV 104 from Variable from Sales NPV Base Case 105 Costs $6,267 Base Case Price $6,267 -25% 106 $8,241.01 S0 -25% -10 % 107 $7,056.80 S0 -10% 0% S0 108 0% $6,267.32 $5,477.85 S0 S0 109 10% 10% 110 25% so $4,293.64 25% S0 111 NON-VARIABLE COSTS 112 % Deviation 113 from Fixed NPV Base Case 114 Costs $6,267 Note about data tables. The data in the column input shouldNOT be input using a cell reference to the column input cell. For example the base case number of units sold in cell B100 should be the number 1000; you should NOT have the formula-D29 in that cell. This is because you'll use D29 as the column input cell in the data table and if Excel tries to iteratively replace cell D29 with the formula -D29 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! 115 -25% 56,667 -10 % 116 $3,359 S0 117 0% $6,267 $6,107 $5,868 S0 118 10% 119 25% S0 120 121 122 Sensitivity Analysis $12,000.00 $10,100.00 $8,200.00 $6,300.00 Sales price VC -Units Non-var. cost WACC $4,400.00 $2,500.00 $600.00 1 2 3 ($1,300.00) ($3,200.00) ($5,100.00) 4 5 37 38 (S7,000.00) -20% 20% 0% 40 41 42 43 Deviation NPV at Different Deviations from Base Note: Copy the results from the sensitivity analysis into this table. Sales Price 44 from Variable Non-variable Cost 145 146 Base Case Cost Units Sold WACC -25% -10% 147 0% 148 10% 25% 149 150 151 00 152 Range -00. 0S -00. S -00. 153 154 155 c. Would you recommend that the projeet be accepted? Explain. 156 157 154 159 160 161 162 Actual $0 NPV