Please complete the tables given the below information.
a. Determine the following for the proposed extraction equipment investment and specify whether or not the investment is acceptable under each of the following approaches. Assume a minimum payback period of 2 years is required. a. Net Present Value (NPV) b. Internal Rate of Return (IRR) [Under TOOLS select GOAL SEEK and SET CELL for NPV equal to 0 BY CHANGING after-tax discount rate cell B41 in ASSUMPTIONS tab]. c. Payback (in years) round to nearest whole year.
ERMS, CORP. 1. (a) (b) (Depr Tax Shield) (c) |Present Value of Tax effect (%) |Cash Flows ($) Discount Factor Tax Shield ($) 76,347 Depreciation Deductions 429,000 After-tax Year Ending 0.8475 1 $ 21% $ 90,090 $ 2 3 4 5 6 7 TOTALS 429,000 76,347 $ $ CO Present Value of Cash Flows ($). After-tax Year(s) Ending Now Amount (S) Tax effect (%) | Cash Flows ($) Discount Factor Description Equipment cost Working capital Revenue Now 79% 1-10 3,200,000 2,528,000 4.4941 Processing & Testing Salaries & benefits Maintenance PV of depr shield Release of W.C. Site Remediation 1-10 1-10 1-10 1-8 76,347 10 0.1911 10 NPV (S) (a) = check figure: 1236370 IRR= Unrecovered PAYBACK ANALYSIS After-Tax Years Cash Flow Investment $ 0 2,618,090 1 2 3 4 5 6 PAYBACK Years a. The initial cost of the extraction equipment is $2,500,000. In addition to this cost, the equipment will require a large concrete foundation at a cost of $300,000. The vendor has quoted an additional cost of $200,000 to install and test the equipment. These costs are all considered part of the cost of acquiring the equipment. b. The useful life of the equipment is 10 years with no salvage value at the end of this period. However, for tax purposes, the equipment will be classified as 7-year property and use the following MACRS depreciation allowances (half-year convention) for computing tax depreciation deductions: Percentage of Original Cost Year 14.3% 1.. . 24.5 2 .17.5 3 4. .12.5 5 8.9 6 8.9 7 8.9 8 4,5 100.0% c. Using the new equipment, 250 pounds of platinum can be extracted annually for the next 10 years from the previously inaccessible area of the mine. d. The cost to extract and separate platinum from the ore is $4,000 per pound of platinum. After separation, the platinum must undergo further processing and testing that costs $1900 per pound of platinum. These are all out-of-pocket, variable costs e. Two skilled technicians will be hired to operate the new equipment. The total salary and fringe benefit expense for these two employees will be $200,000 annually over the 10 years. f. Periodic maintenance on the equipment is expected to cost $175,000 per year. g. The project requires an investment in additional working capital of $300,000. This working capital would be released for use elsewhere at the conclusion of the project in 10 years. h. Environmental and safety regulations require that the mine be extensively restored and toxic substances be safely disposed at the conclusion of the project. The cost of environmental remediation work is expected to be $4,500,000. The current market price of platinum is $12,800 per pound j. ERMS's tax rate is 21% and it uses an 18% after-tax discount rate (minimum required rate of i. return) Required: Complete the Excel template and answer the following questions. Submit a completed Excel file (baseline scenario only with write-ups in a separate tab) to the D2L drop box. 1. Depreciation tax shields and cash flow Assume the platinum can be sold for $12,800 per pound. For years 1 through 8 compute the: Depreciation deductions b. After-tax cash flows. a. Present value of after-tax cash flows. C. 2. Capital investment evaluation Determine the following for the proposed extraction equipment investment and specify whether or not the investment is acceptable under each of the following approaches. Assume a minimum payback period of 2 years is required. a. Net Present Value (NPV) b. Internal Rate of Return (IRR) [Under TOOLS select GOAL SEEK and SET CELL for NPV equal to "O" BY CHANGING after-tax discount rate cell "B41" in ASSUMPTIONS tab] Payback (in years) - round to nearest whole year. C C. 3. Sensitivity Analyses The future market value of platinum and the appropriate discount rate are uncertain. Hence, the President of ERMS seeks to determine the effect of changes to these critical assumptions. What is the market price of platinum at which ERMS's will break even from an NPV perspective? Use the initial discount rate to determine the sales price that makes NPV =0. [Use GOAL SEEK and SET CELL for NPV equal to "O" - BY CHANGING Sales price cell "B27" in ASSUMPTIONS tab] b. Is the NPV of the project acceptable using a $11,000 per pound price assumption and a 15 percent discount rate? Explain. a. 4. Other considerations a. What type, or category, of capital investment decision ERMS is considering (e.g., strategic, expansion, replacement, etc.). Explain b. What due diligence, if any, do you recommend that ERMS perform prior to undertaking this investment? Explain c. Should ERMS conduct a post-investment audit of this investment decision? Explain how this might be performed. ERMS, CORP. 1. (a) (b) (Depr Tax Shield) (c) |Present Value of Tax effect (%) |Cash Flows ($) Discount Factor Tax Shield ($) 76,347 Depreciation Deductions 429,000 After-tax Year Ending 0.8475 1 $ 21% $ 90,090 $ 2 3 4 5 6 7 TOTALS 429,000 76,347 $ $ CO Present Value of Cash Flows ($). After-tax Year(s) Ending Now Amount (S) Tax effect (%) | Cash Flows ($) Discount Factor Description Equipment cost Working capital Revenue Now 79% 1-10 3,200,000 2,528,000 4.4941 Processing & Testing Salaries & benefits Maintenance PV of depr shield Release of W.C. Site Remediation 1-10 1-10 1-10 1-8 76,347 10 0.1911 10 NPV (S) (a) = check figure: 1236370 IRR= Unrecovered PAYBACK ANALYSIS After-Tax Years Cash Flow Investment $ 0 2,618,090 1 2 3 4 5 6 PAYBACK Years a. The initial cost of the extraction equipment is $2,500,000. In addition to this cost, the equipment will require a large concrete foundation at a cost of $300,000. The vendor has quoted an additional cost of $200,000 to install and test the equipment. These costs are all considered part of the cost of acquiring the equipment. b. The useful life of the equipment is 10 years with no salvage value at the end of this period. However, for tax purposes, the equipment will be classified as 7-year property and use the following MACRS depreciation allowances (half-year convention) for computing tax depreciation deductions: Percentage of Original Cost Year 14.3% 1.. . 24.5 2 .17.5 3 4. .12.5 5 8.9 6 8.9 7 8.9 8 4,5 100.0% c. Using the new equipment, 250 pounds of platinum can be extracted annually for the next 10 years from the previously inaccessible area of the mine. d. The cost to extract and separate platinum from the ore is $4,000 per pound of platinum. After separation, the platinum must undergo further processing and testing that costs $1900 per pound of platinum. These are all out-of-pocket, variable costs e. Two skilled technicians will be hired to operate the new equipment. The total salary and fringe benefit expense for these two employees will be $200,000 annually over the 10 years. f. Periodic maintenance on the equipment is expected to cost $175,000 per year. g. The project requires an investment in additional working capital of $300,000. This working capital would be released for use elsewhere at the conclusion of the project in 10 years. h. Environmental and safety regulations require that the mine be extensively restored and toxic substances be safely disposed at the conclusion of the project. The cost of environmental remediation work is expected to be $4,500,000. The current market price of platinum is $12,800 per pound j. ERMS's tax rate is 21% and it uses an 18% after-tax discount rate (minimum required rate of i. return) Required: Complete the Excel template and answer the following questions. Submit a completed Excel file (baseline scenario only with write-ups in a separate tab) to the D2L drop box. 1. Depreciation tax shields and cash flow Assume the platinum can be sold for $12,800 per pound. For years 1 through 8 compute the: Depreciation deductions b. After-tax cash flows. a. Present value of after-tax cash flows. C. 2. Capital investment evaluation Determine the following for the proposed extraction equipment investment and specify whether or not the investment is acceptable under each of the following approaches. Assume a minimum payback period of 2 years is required. a. Net Present Value (NPV) b. Internal Rate of Return (IRR) [Under TOOLS select GOAL SEEK and SET CELL for NPV equal to "O" BY CHANGING after-tax discount rate cell "B41" in ASSUMPTIONS tab] Payback (in years) - round to nearest whole year. C C. 3. Sensitivity Analyses The future market value of platinum and the appropriate discount rate are uncertain. Hence, the President of ERMS seeks to determine the effect of changes to these critical assumptions. What is the market price of platinum at which ERMS's will break even from an NPV perspective? Use the initial discount rate to determine the sales price that makes NPV =0. [Use GOAL SEEK and SET CELL for NPV equal to "O" - BY CHANGING Sales price cell "B27" in ASSUMPTIONS tab] b. Is the NPV of the project acceptable using a $11,000 per pound price assumption and a 15 percent discount rate? Explain. a. 4. Other considerations a. What type, or category, of capital investment decision ERMS is considering (e.g., strategic, expansion, replacement, etc.). Explain b. What due diligence, if any, do you recommend that ERMS perform prior to undertaking this investment? Explain c. Should ERMS conduct a post-investment audit of this investment decision? Explain how this might be performed
