The VacuTech Company manufactures and sells microcircuits. The demand for the microcircuits has been continually increasing over the past 4 years. The Company?s manufacturing facilities are currently operating at full capacity. Vacu-Tech management is considering the purchase of a new pump (equipment to produce microcircuits) and has asked the company?s controller to evaluate the financial impact of the purchase.The controller has gathered the following information prior to preparing her analysis. Cost of equipment (including installation and training) $500,000 Economic useful life of the equipment 5 years Salvage value at the end of useful life $60,000 Increase in annual operating cash flows (incremental cash revenues less incremental operating cash expenses) $145,000 Applicable income tax rate 30% For tax purposes, the equipment will be assigned to the five-year class for depreciation, following the half-year rule under the Modified Accelerated Cost Recovery System (MACRS).The rates apply to the original cost and are provided in Requirement 2 below. The company?s minimum desired after-tax rate of return is 13%.Assume the investment occurs immediately and annual cash flows occur at the end of the year.Also assume the equipment will be sold at the end of 5 years. Required: 1.Assume that the company is exempt from income tax. a.Complete a cash flow schedule identifying the cash outflow related to the investment at the beginning of the first year and the annual cash flows at the end of each year. b.Use the Excel function =pv( to calculate the present value of the annuity of the cash flows over the five-year useful life of the equipment.For the disposal value, use the same function =pv( but ignore the annuity (called pmt in Excel).Calculate the net present value of this investment. c.Calculate the Internal Rate of Return (IRR) on the investment, using the appropriate Excel function =irr(. d.Should Vacu-Tech make the investment? 2.Now consider income tax. MACRS schedule using the half-year rule is 20% in year 1, 32% in year 2, 19.2% in year 3, 11.52% in year 4, and 11.52% in year 5.The remainder in year 6 is 5.76%. However, the asset will be sold at the end of year 5. Note that any gain on the disposal of the asset will be taxable or loss deductible for tax purposes at that time. a.Complete a cash flow schedule identifying the cash outflow related to the investment at the beginning of the first year and the after-tax annual cash flows at the end of each year. b.Use the Excel function to determine the present value of the uneven cash flow =npv( over the five-year useful life of the equipment. Calculate the net present value of this investment. c.Calculate the Internal Rate of Return (IRR) on the investment, using the appropriate Excel function =irr(. d.Should Vacu-Tech make the investment? INSTRUCTIONS 1.Download the Excel Assignment Template (in Excel) and save it. 2.Enter your first name and last name in cell A1 on the Excel Assignment worksheet. 3.DO NOT ALTER THE FORMATTING OF ANY OF THE SHADED AREAS OR SHADED CELLS in the Excel Assignment worksheet. 4.The data from the assignment is provided in rows 3 - 14.Do not alter these cells. 5.Use only cell references in making your calculations. Do not manually re-enter any of the data already provided. 6.Do not use PV tables or financial calculators.Make all your calculations on the Excel worksheet, including Excel functions. 7.Use cell references, Excel calculations, or Excel functions to show the results in the yellow-shaded cells. Manually entered answers in the yellow cells will result in a zero score for that answer. 8.After you complete your assignment, save your work. 9.Submit your assignment by uploading your Excel worksheet file on Moodle (under assignments) by the due date.

INSTRUCTIONS 1 Download the Excel Assignment spreadsheet and save it. 2 Enter your first name and last name in cell A1 on the Excel Assignment worksheet. 3 DO NOT ALTER THE FORMATTING OF ANY OF THE SHADED AREAS OR SHADED CELLS in the Excel Assignment worksheet. 4 The data from the assignment is provided in rows 3 - 14. Do not alter these cells. 5 Use only cell references in making your calculations. Do not manually re-enter any of the data already provided. 6 Do not use PV tables or financial calculators. Make all your calculations on the Excel worksheet, including Excel functions. 7 Use cell references, Excel calculations, or Excel functions to show the results in the yellow-shaded cells. Manually entered answers in the yellow cells will result in a zero score for that answer. 8 After you complete your assignment, save your work. 9 Submit your assignment by uploading your Excel worksheet file on Moodle (under assignments) by the due date. Excel Assignment Vacu-Tech Cost of equipment (including installation and training) Salvage value at the end of 5 years Increase in annual operating cash flow Useful life of equipment (in years) Applicable income tax rate MACRS tax class (in years); 1/2 year rule Desired rate of return $500,000 $60,000 Assume the asset will be sold at the end of the 5th year. $145,000 5 30% 5 13% MACRS 5 Year Asset Depreciation; 1/2 year rule MACRS Depreciation Schedule Year 1 20% Year 2 32% Year 3 19.20% Year 4 11.52% Year 5 11.52% Year 1 Year 2 Year 3 Year 4 Year 5 Year 2 Year 3 Year 4 Year 5 I. Ignoring Income Tax a. Cash Flow Schedule Cost of equipment Increase in annual operating cash flow Disposal value Total cash flows, ignoring disposal Total cash flows including disposal Year 0 b. Using Excel Function for PV of Annuity and for PV of a Single Payment (=pv) PV of the annuity PV of disposal value Total PV of future cash flows Investment NPV c. Using Excel Function for IRR (=irr) IRR d. Decision II. Considering Income Tax a. Cash Flow Schedule Cost of equipment Increase in pre-tax annual operating cash flow After-tax annual cash flow (ignoring depreciation) Year 0 Year 1 Depreciation using MACRS Cash flow from deduction of depreciation Disposal, end of year 5, ignoring income tax Book value at time of disposal Gain on disposal of asset Income tax on gain from disposal of asset Net cash flow resulting from disposal of asset Total cash flows including disposal b. Using Excel Function to calculate the PV of an uneven cash flow (=npv) PV of future cash flows Investment NPV IRR d. Decision The VacuTech Company manufactures and sells microcircuits. The demand for the microcircuits has been continually increasing over the past 4 years. The Company's manufacturing facilities are currently operating at full capacity. Vacu-Tech management is considering the purchase of a new pump (equipment to produce microcircuits) and has asked the company's controller to evaluate the financial impact of the purchase.The controller has gathered the following information prior to preparing her analysis. Cost of equipment (including installation and training) $500,000 Economic useful life of the equipment 5 years Salvage value at the end of useful life $60,000 Increase in annual operating cash flows (incremental cash revenues less incremental operating cash expenses) $145,000 Applicable income tax rate 30% For tax purposes, the equipment will be assigned to the five-year class for depreciation, following the half-year rule under the Modified Accelerated Cost Recovery System (MACRS).The rates apply to the original cost and are provided in Requirement 2 below. The company's minimum desired after-tax rate of return is 13%.Assume the investment occurs immediately and annual cash flows occur at the end of the year.Also assume the equipment will be sold at the end of 5 years. Required: 1.Assume that the company is exempt from income tax. a.Complete a cash flow schedule identifying the cash outflow related to the investment at the beginning of the first year and the annual cash flows at the end of each year. b.Use the Excel function =pv( to calculate the present value of the annuity of the cash flows over the five-year useful life of the equipment.For the disposal value, use the same function =pv( but ignore the annuity (called pmt in Excel).Calculate the net present value of this investment. c.Calculate the Internal Rate of Return (IRR) on the investment, using the appropriate Excel function =irr(. d.Should Vacu-Tech make the investment? 2.Now consider income tax. MACRS schedule using the half-year rule is 20% in year 1, 32% in year 2, 19.2% in year 3, 11.52% in year 4, and 11.52% in year 5.The remainder in year 6 is 5.76%. However, the asset will be sold at the end of year 5. Note that any gain on the disposal of the asset will be taxable or loss deductible for tax purposes at that time. a.Complete a cash flow schedule identifying the cash outflow related to the investment at the beginning of the first year and the after-tax annual cash flows at the end of each year. b.Use the Excel function to determine the present value of the uneven cash flow =npv( over the five-year useful life of the equipment. Calculate the net present value of this investment. c.Calculate the Internal Rate of Return (IRR) on the investment, using the appropriate Excel function =irr(. d.Should Vacu-Tech make the investment? INSTRUCTIONS 1.Download the Excel Assignment Template (in Excel) and save it. 2.Enter your first name and last name in cell A1 on the Excel Assignment worksheet. 3.DO NOT ALTER THE FORMATTING OF ANY OF THE SHADED AREAS OR SHADED CELLS in the Excel Assignment worksheet. 4.The data from the assignment is provided in rows 3 - 14.Do not alter these cells. 5.Use only cell references in making your calculations. Do not manually re-enter any of the data already provided. 6.Do not use PV tables or financial calculators.Make all your calculations on the Excel worksheet, including Excel functions. 7.Use cell references, Excel calculations, or Excel functions to show the results in the yellowshaded cells. Manually entered answers in the yellow cells will result in a zero score for that answer. 8.After you complete your assignment, save your work. 9.Submit your assignment by uploading your Excel worksheet file on Moodle (under assignments) by the due date