Specific Motors (SM) is considering replacing its old assembly line with a new line. Old line was bought 3 years ago at a cost of $500,000. It is being depreciated at a straight line down to zero over a life of 5 years ($100,000 a year). The operating costs on the old line are $800,000 a year. At the end of its life (2 years from now) old can be sold for $50,000. The current market value of the old is $100,000. The old has spare parts inventory of $50,000 which can be sold whenever old machine is no longer needed. The new line costs $600,000 today. It has a life of two years. It would be depreciated straight line over 2 years. The operating costs with the new line are $200,000 per year. The market value of new at the end of its life is $100,000. The new line needs spare parts inventory of $100,000 which can also be sold whenever new machine is no longer needed.

Tax rate is 40%, cost of capital is 10%. Should we get the new or stay with the old?Show all the work and solve the question using excel and the above template

\begin{tabular}{|c|c|c|c|c|} \hline Years & 0 & 1 & 2 & 3 \\ \hline \multicolumn{5}{|l|}{ Revenue } \\ \hline \multicolumn{5}{|l|}{ - Operating costs } \\ \hline \multicolumn{5}{|l|}{ - Other costs } \\ \hline \multicolumn{5}{|l|}{ - Depreciation } \\ \hline \multicolumn{5}{|l|}{ + Gain from salvage } \\ \hline \multicolumn{5}{|l|}{ = Taxable income } \\ \hline \multicolumn{5}{|l|}{= =Tax @35\% } \\ \hline \multicolumn{5}{|l|}{= NOPAT } \\ \hline \multicolumn{5}{|l|}{ +Depreciation } \\ \hline \multicolumn{5}{|l|}{ - Gain from salvage } \\ \hline \multicolumn{5}{|l|}{= Operating CF } \\ \hline \multicolumn{5}{|l|}{+ Change in NWC } \\ \hline \multicolumn{5}{|l|}{+ Investment } \\ \hline \multicolumn{5}{|l|}{ + Salvage } \\ \hline \multicolumn{5}{|l|}{= Net Cash Flow } \\ \hline & & & & \\ \hline Net Present Value & & & & \\ \hline \end{tabular}