Question

Please use the excel cells provided.

HI A B Problem 9-17 One year ago, your company purchased a machine used in manufacturing for $110,000. You have leamed 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. $ $ 110,000 150,000 11 10 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 20,000 40,000 10,000 50,000 45% 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) FCF in year 0 NPV of replacement Take opportunity (Yes/No) a year for 10 years 33 Requirements 11 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

Answer 1

I have answered the question below using excel and have attached the image below.

Please upvote for the same and thanks!!!

Do reach out in the comments for any queries

Answer:

NPV is $3915.51

We should take the opportunity as NPV is positive

I ENPVIBI1,823,B23,823,823,B23,823,B23,B23,823,B23)-B24 110.000 150,000 $ Detailed NPV calculation S 1 Price of Machine (1) 2 Price of Machine (2) 3 Machine Life (1) 4 Machine Life (2) 5 Salvage Value (1) & (2) 6 Gross Margin (1) 7 Gross Margin (2) 8 Depreciation Expense (1) 9 Market Value (1) 10 Tax Rate 11 Opportunity Cost 20,000 40,000 10,000 50,000 45% 10% Year Cash Flow NPV factor @10% NPV of cash flows 0 $ (77,500)| 1.00000 S 77,500.00) 1$ 13,250 0.90909 S 12,045.45 2 $ 13,250 0.82645 S 10.950.41 3 S 13,250 0.75131 S 9,954.92 4 S 13,250 0.68301 S 9,049.93 5 $ 13,250 0.62092 S 8.227.21 6 S 13,250 0.564471 S 7,479.28 7 $ 13,250 0.51316 S 6,799.35 8 $ 13.250 0.46651 S 6,181.22 9 $ 13,250 0.42410 $ 5,619.29 10 $ 13,250 0.38554 S 5 ,108.45 Explanation $ 3,915.51 Purchase price of old machine minus one year depreciation Book value today minus current market value at which old machine will be sold Tax Saved on capital loss of sale of machine Capital loss after sale plus tax savings resulting from capital loss NPV $ $ $ $ Formula used including cell reference) 100,000 81-81/83) 50,000 814-09 22,500 815*810 72,500 894616 13 For Old Machine 14 Book Value Today 15 Capital Loss After Sale 16 Tax Savines 17 Proceeds from Sale including Tax Savings 18 19 For New Machine 20 EBITDA Increase 21 Depreciation Expense 22 Depreciation Increase 23 FCF increase (Annual benefit from replacement) 24 FCF in year o 25 NPV of replacement 26 Take Opportunity (Yes/No) $ $ $ $ Formula used (including cell reference) 20,000 87-86 15,000 B2/84 5,000 321-88 13,250 (820-8221*(1-810))+622 77,500 (B2-817) $3,915.51 NPV(B11,623,623,823,823,823,823,823,823,823,823)-524 IF(B25>0, "Yes","No") Explanation New gross marein minus old gross margin Yearly depreciation on new machine Depreciation on new machine minus Depreciation on old machine After tax Earning plus depreciation expense, shown more clearly in below given annexure Price of new machine minus after tax sale proceeds of old machine Discounted cash flows of 10 years of new machine $3915.51 is NPV for replacement (Final Answer) Yes as NPV of replacement is positive (Final Answer) S Yes Calculation of incremental FCF 29 EBIT increase 30 Tax 45% 31 FAT (EBIT - Tax) 32 Add: Incremental Depreciation (beine non-cash) 33 FCF Increase I$ $ $ $ $ 15000 820-822 6,750 829*810 8.250 829-830 5,000 822 13,250 8314832