One year ago, your company purchased a machine used in manufacturing for $110.000. You have learned that a new machine is available that offers many advantages; you can purchase it for $150.000 today. It will be depreciated on a straight-line basis over 10 years, after which it has no salvage value. You expect that the new machine will produce EBITDA (eaming before interest, taxes, depreciation, and amortization) of $40.000 per year for the next 10 years. The current machine is expected to produce EBITDA of $20.000 per year. The current machine is being depreciated on a straight-line basis over a useful life of 11 years, after which it will have no salvage value, so depreciation expense for the current machine is $10.000 per year. All other expenses of the two machines are identical. The market value today of the current machine is $50.000. Your company's tax rate is 45%, and the opportunity cost of capital for this type of equipment is 10%. Is it profitable to replace the year-old machine? Price of machine (1) Price of machine (2) Machine life (1) Machine life (2) Salvage value (1) & (2) Gross margin (1) Gross margin (2) Depreciation expense (1) Market value (1) Tax rate Opportunity cost $110,000 $150,000 11 10 SO $20,000 $40,000 $10,000 $50,000 45% 10% For old machine Book value Capital gain loss after sale Tax savings Proceeds from sale For new machine EBITDA increase Depreciation expense Depreciation increase FCF increase FCF in year 0 NPV of replacement Take opportunity Requirements 1. Start Excel - completed. 2. In cell D19, by using cell references, calculate the book value of the old machine (1 pt.). 3. In cell D20, by using cell references, calculate the capital gain loss after the sale of the old machine (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a negative number. 4. In cell D21, by using cell references, calculate the tax savings from the old machine (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. 5. In cell D22, by using cell references, calculate the proceeds from the sale of the old machine (1 pt.). 6. In cell D24, by using cell references, calculate the EBITDA increase from the new machine (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. 7. In cell D25, by using cell references, calculate the depreciation expense from the new machine (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. 8. In cell D26, by using cell references, calculate the depreciation increase from the new machine (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. 9. In cell D27, by using cell references, calculate the free cash flow increase from the new machine (1 pt.). 10. In cell D28, by using cell references, calculate the free cash flow in year 0 from the new machine (1 pt.). 11. In cell D29, by using cell references and the function PV, calculate the NPV of the replacement (1 pt.). Note: Calculate the NPV as the sum of the Present Value of the FCF increase for years 1-10 plus the FCF in year 0. 12. In cell D30, select either Yes or No depending on whether you would take this investment opportunity (1 pt.). 13. Save the workbook. Close the workbook and then exit Excel Submit the workbook as directed