we assume that a company is considering a new project. the projects life has 11 years. this project requires initial investment of $680 million to purchase equipment and $25 million for shipping and installation fee. the fixed assets fall in the 10-year MACRS class. the number of units of nee 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 variable cost is $200 per unit in first year, but they should be adjusted accordingly based on the estimated annualized imflation rate of 1.8%. the required net operating working capital is 12% of sales. coprorate tax rate is 27%. project is assumed to have same risk as corporation so use the WACC obtained. the WACC is 8.5%
please fill out the following model, and provide work on seperate sheet for further understanding! need as fast as possible, thank you in advance!
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 Key Results: NPV = IRR = Payback = $0 0.0% 0.00 12% 2,560,000 $255.00 $200.00 $0 1.8% $0 Year 1 10.00% 27% 0% Year 2 18.00% Year 3 14.40% Year 4 11.52% 0 2 3 1 2,560,000 $255.00 $200.00 12% of sales 7% ?% 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 Dep'in 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%) 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) 0 Years 2 3 Key Results: Appraisal of the Proposed Project Key Results: Appraisal of the Proposed Project Net Present Value (at 10%) = IRR = MIRR = Payback = Discounted Payback = Data for Payback Years Years 0 1 3 Net cash flow Cumulative CF Part of year required for payback Data for Discounted Payback Years 0 $0 1 $0 Years 2 $0 3 $0 $0 Net cash flow Discounted cash flow Cumulative CF Part of year required for discounted payback 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 Key Results: NPV = IRR = Payback = $0 0.0% 0.00 12% 2,560,000 $255.00 $200.00 $0 1.8% $0 Year 1 10.00% 27% 0% Year 2 18.00% Year 3 14.40% Year 4 11.52% 0 2 3 1 2,560,000 $255.00 $200.00 12% of sales 7% ?% 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 Dep'in 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%) 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) 0 Years 2 3 Key Results: Appraisal of the Proposed Project Key Results: Appraisal of the Proposed Project Net Present Value (at 10%) = IRR = MIRR = Payback = Discounted Payback = Data for Payback Years Years 0 1 3 Net cash flow Cumulative CF Part of year required for payback Data for Discounted Payback Years 0 $0 1 $0 Years 2 $0 3 $0 $0 Net cash flow Discounted cash flow Cumulative CF Part of year required for discounted payback
