Explain in detail by excel if possible, please

1 Problem Case Two (35 points) 2 3 Taurus Inc. wants to evaluate the acquisition of an equipment. The equipment's basic acquisition price is $ 100 million, and would cost another $10 million for 4 shipping & handling, plus another $5 million to install & modify it for use by the firm at the beginning. Taurus plans to use this equipment for 4 years, and then 5 at the end of Year 4, the equipment is expected to be salvaged for $20 million of market value. Per US tax rules about depreciation treatment, this equipment is 6 categorized as an MACRS 3-year class asset --- that is, 33%, 45%, 15% and 7% to be depreciated for Year 1, 2, 3 and 4, respectively. 7 8 With this 4-year equipment investment project, Taurus will also be subject to such changes: 9 10 11 12 13 Produce 30,000 units of products each year, from Year 1 to Year 4; Each product unit sells for the price of $1,200 at Year 1, thereafter growing 4% per year in real terms; Each product unit costs $800 to produce at Year 1, thereafter growing 3% per year in real terms; The side effects and opportunity costs of this equipment are negligible to Taurus; Each year, Taurus needs to invest to prepare a net operating working capital beforehand, amounting to 10% of revenue projected for the year that follows; The applicable federal-plus-state tax rate for future years is 24%. 14 15 16 17 18 19 20 QUESTIONS: 21 221) What should be the net free cash flow amount of this equipment investment project for Years 0, 1, 2, 3 and 4, respectively? 23 242) Taurus's WACC is 7.50% per year, nominally. But the US inflation rate is averagely 3.02% per year in the long run. What should be the accurate amount of WACC in real terms (i.e., deflated)? 25 26 28 273) Based on the free cash flow amounts calculated in 1) and the real-term WACC calculated in 2): What should be the amount of NPV? What should be the amount of IRR? What should be the amount of MIRR? What should be the amount of payback period? What should be the amount of EAA? (Use those corresponding Excel functions that we learn in this course.) 29 30 31 32 33 34 35 1 Problem Case Two (35 points) 2 3 Taurus Inc. wants to evaluate the acquisition of an equipment. The equipment's basic acquisition price is $ 100 million, and would cost another $10 million for 4 shipping & handling, plus another $5 million to install & modify it for use by the firm at the beginning. Taurus plans to use this equipment for 4 years, and then 5 at the end of Year 4, the equipment is expected to be salvaged for $20 million of market value. Per US tax rules about depreciation treatment, this equipment is 6 categorized as an MACRS 3-year class asset --- that is, 33%, 45%, 15% and 7% to be depreciated for Year 1, 2, 3 and 4, respectively. 7 8 With this 4-year equipment investment project, Taurus will also be subject to such changes: 9 10 11 12 13 Produce 30,000 units of products each year, from Year 1 to Year 4; Each product unit sells for the price of $1,200 at Year 1, thereafter growing 4% per year in real terms; Each product unit costs $800 to produce at Year 1, thereafter growing 3% per year in real terms; The side effects and opportunity costs of this equipment are negligible to Taurus; Each year, Taurus needs to invest to prepare a net operating working capital beforehand, amounting to 10% of revenue projected for the year that follows; The applicable federal-plus-state tax rate for future years is 24%. 14 15 16 17 18 19 20 QUESTIONS: 21 221) What should be the net free cash flow amount of this equipment investment project for Years 0, 1, 2, 3 and 4, respectively? 23 242) Taurus's WACC is 7.50% per year, nominally. But the US inflation rate is averagely 3.02% per year in the long run. What should be the accurate amount of WACC in real terms (i.e., deflated)? 25 26 28 273) Based on the free cash flow amounts calculated in 1) and the real-term WACC calculated in 2): What should be the amount of NPV? What should be the amount of IRR? What should be the amount of MIRR? What should be the amount of payback period? What should be the amount of EAA? (Use those corresponding Excel functions that we learn in this course.) 29 30 31 32 33 34 35