Question
Problem 9-17 One year ago, your company purchased a machine used in manufacturing for $110,000. You have learned that a new machine is available that
Problem 9-17
"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 and you can purchase it for $150,000 today. It will be depreciated on a straight-line basis over 10 years and has no salvage value. You expect that the new machine will produce a gross margin (revenues minus operating expenses other than depreciation) of $40,000 per year for the next 10 years. The current machine is expected to produce a gross margin of $20,000 per year. The current machine is being depreciated on a straight-line basis over a useful life of 11 years, and has no salvage value, so depreciation expense for the current machine is $10,000 per year. 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%. Should your company replace its year-old machine?
Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section."
Price of machine (1) $110,000
Price of machine (2) $150,000
Machine life (1) 11
Machine life (2) 10
Salvage value (1) & (2) $-
Gross margin (1) $20,000
Gross margin (2) $40,000
Depreciation expense (1) $10,000
Market value (1) $50,000
Tax rate 45%
Opportunity cost 10%
For old machine
Book value today
Capital loss after sale
Tax savings
Proceeds from sale including tax savings
For new machine
EBITDA increase
Depreciation expense
Depreciation increase
FCF increase (annual benefit of replacement) a year for 10 years
FCF in year 0
NPV of replacement
Take opportunity (Yes/No)
Requirements
1 In cell D19, by using cell references, calculate the book value of the old machine (1 pt.).
2 In cell D20, by using cell references, calculate the capital loss after the sale of the old machine. Note: The output of the expression you typed in this cell is expected as a negative number. (1 pt.).
3 In cell D21, by using cell references, calculate the tax savings from the old machine. Note: The output of the expression you typed in this cell is expected as a positive number. (1 pt.).
4 In cell D22, by using cell references, calculate the proceeds from the sale of the old machine (1 pt.).
5 In cell D24, by using cell references, calculate the EBITDA increase from the new machine. Note: EBITDA increase is the increase in Gross Margin. The output of the expression you typed in this cell is expected as a positive number. (1 pt.).
6 In cell D25, by using cell references, calculate the depreciation expense from the new machine. Note: The output of the expression you typed in this cell is expected as a positive number. (1 pt.).
7 In cell D26, by using cell references, calculate the depreciation increase from the new machine. Note: Depreciation increase is the difference between the depreciation of the old and new machines. The output of the expression you typed in this cell is expected as a positive number. (1 pt.).
8 In cell D27, by using cell references, calculate the free cash flow increase from the new machine (1 pt.).
9 In cell D28, by using cell references, calculate the free cash flow in year 0 from the new machine. Note: The output of the expression you typed in this cell is expected as a negative number. (1 pt.).
10 In cell D29, by using the PV function and cell references, calculate the NPV of the replacement. Note: (1) Calculate the NPV as the sum of the Present Value of the FCF increase for years 1-10 plus the FCF in year 0. (2) In the PV function, omit the [fv] and [type] arguments. (1 pt.).
11 In cell D30, type either Yes or No depending on whether you would take this investment opportunity (1 pt.).
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started