We assume that the company of your choice is considering a new project. The project had 11 years life. This project requires initial investment of $680 million to purchase equipment, and $25 million for shipping and instillation fee. The fixed assets fall in the 10-year MACRS class. the number of units of the new product expected to be sold in the first year is 2,560,000 and the expected annual growth rate is 5.5%. The sales price is $255 per unit and the variable cost is $200 per unit in the first year, but they should be adjusted accordingly based on the estimated annualization inflation rate of 1.8%. The required net operating working capital is 12% of sales. The corporate tax rate is 27%. The project is assumed to have the same risk as the the corporation, so you should use the WACC you obtained from prior steps as the discount rate. The WACC is 8.5%.
corporate taxe rate is 27%
WACC is 8.5%
The depreciation rates are wrong and should be from year 1-4, 10%, 18%, 14.40% and 11.52%
*Really need done by tomorrow*
please fill out the below model as MUCH AS POSSIBLE: (if able please provide work on seperate page so I can get an undertsanding.)
Question is updated.. 
Base Case Note: the items in red will be used in a scenario analysis. 50% Input Data: Scenario name Probability of scenario Equipment cost Net operating working capital/Sales First year sales (in units) Sales price per unit Variable cost per unit (excl. depr.) Nonvariable costs (excl. depr.) Inflation in prices and costs Estimated salvage value at year 4 Depreciation years Depreciation rates Tax rate WACC for average-risk projects 12% 2,560,000 $255.00 $200.00 $0 1.8% Key Results: NPV = IRR Payback = $0 0.0% 0.00 $0 Year 2 32.00% Year 3 19.20% Year 4 11.52% Year 1 20.00% 27% 0% 0 2 3 4 1 2,560,000 $255.00 $200.00 7% ?% ?% ?% Intermediate Calculations Units sold Sales price per unit (excl. depr.) Variable costs per unit (excl. depr.) Nonvariable costs (excl. depr.) Sales revenue Required level of net operating working capital Basis for depreciation Annual equipment depr. rate Annual depreciation expense Ending Bk Val: Cost - Accum Depirn Salvage value Profit (or loss) on salvage Tax on profit (or loss) Net cash flow due to salvage Cash Flow Forecast Sales revenue Variable costs Nonvariable operating costs Depreciation (equipment) Oper. income before taxes (EBIT) Taxes on operating income (40%) 5 Net operating profit after taxes Add back depreciation Equipment purchases Cash flow due to change in NOWC Net cash flow due to salvage Net Cash Flow (Time line of cash flows) Years 2 0 1 3 4 Key Results: Appraisal of the Proposed Project Net Present Value (at 10%) = IRR = MIRR = Payback = Discounted Payback = Data for Payback Years Years 2 0 1 3 4 Net cash flow Cumulative CF Part of year required for payback Data for Discounted Payback Years 0 SO 1 SO Years 2 $0 3 $0 4 $0 Net cash flow Discounted cash flow Cumulative CF Part of year required for discounted payback b. Now 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 20% above and below their base- case values. Include a graph in your analysis. % Deviation 1st YEAR UNIT SALES from Base NPV Base Case 1,000 $0 -20% -10% 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 1st Year Unit Sales in Cell B100 should be the number 1,000 and NOT have the formula =D31 in that cell. This is because you'll use D31 as the column input cell in the data table and if Excel tries to iteratively replace Cell D31 with the formula =D31 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! 0% 10% 20% SALES PRICE Base NPV $24.00 $0 VARIABLE COST Base NPV $18.00 $0 % Deviation from Base Case -20% -10% 0% 10% 20% % Deviation from Base Case -20% -10% 0% 10% 20% t 3 Base Case Note: the items in red will be used in a scenario analysis. 50% Input Data: Scenario name Probability of scenario Equipment cost Net operating working capital/Sales First year sales (in units) Sales price per unit Variable cost per unit (excl. depr.) Nonvariable costs (excl. depr.) Inflation in prices and costs Estimated salvage value at year 4 Depreciation years Depreciation rates Tax rate WACC for average-risk projects 12% 2,560,000 $255.00 $200.00 $0 1.8% Key Results: NPV = IRR Payback = $0 0.0% 0.00 $0 Year 2 32.00% Year 3 19.20% Year 4 11.52% Year 1 20.00% 27% 0% 0 2 3 4 1 2,560,000 $255.00 $200.00 7% ?% ?% ?% Intermediate Calculations Units sold Sales price per unit (excl. depr.) Variable costs per unit (excl. depr.) Nonvariable costs (excl. depr.) Sales revenue Required level of net operating working capital Basis for depreciation Annual equipment depr. rate Annual depreciation expense Ending Bk Val: Cost - Accum Depirn Salvage value Profit (or loss) on salvage Tax on profit (or loss) Net cash flow due to salvage Cash Flow Forecast Sales revenue Variable costs Nonvariable operating costs Depreciation (equipment) Oper. income before taxes (EBIT) Taxes on operating income (40%) 5 Net operating profit after taxes Add back depreciation Equipment purchases Cash flow due to change in NOWC Net cash flow due to salvage Net Cash Flow (Time line of cash flows) Years 2 0 1 3 4 Key Results: Appraisal of the Proposed Project Net Present Value (at 10%) = IRR = MIRR = Payback = Discounted Payback = Data for Payback Years Years 2 0 1 3 4 Net cash flow Cumulative CF Part of year required for payback Data for Discounted Payback Years 0 SO 1 SO Years 2 $0 3 $0 4 $0 Net cash flow Discounted cash flow Cumulative CF Part of year required for discounted payback b. Now 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 20% above and below their base- case values. Include a graph in your analysis. % Deviation 1st YEAR UNIT SALES from Base NPV Base Case 1,000 $0 -20% -10% 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 1st Year Unit Sales in Cell B100 should be the number 1,000 and NOT have the formula =D31 in that cell. This is because you'll use D31 as the column input cell in the data table and if Excel tries to iteratively replace Cell D31 with the formula =D31 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! 0% 10% 20% SALES PRICE Base NPV $24.00 $0 VARIABLE COST Base NPV $18.00 $0 % Deviation from Base Case -20% -10% 0% 10% 20% % Deviation from Base Case -20% -10% 0% 10% 20% t 3
