Please show ALL work with calculations in excel!!! Will like for correct answer!

Due to the increasing energy costs in the US$,$ Jackie Moora is anxious. The old machine her

company has been using to manufacture steel support beams for the construction industry is $5$

years old, and it served the company well over the past $5$ years. Jackie expected the machine to

last another $7$ years. At which point she would have to replace the machine with another one. The

existing machine was purchased for $$3$ million $($$$3$M$)$ and is expected to have no resale value at

the end of its life or $$ for that matter $$ if it were to be sold at present. The machine is depreciated

straight$-$line for $10$ years, so has another $5$ years of depreciation left.

Jackie is concerned that higher cost of energy may reduce the profitability of their operations

when utilizing the existing machine, and is considering whether buying a new machine now $$ that

is$,$ replacing the existing machine at present rather than waiting another $7$ years $$ makes financial

sense. Or whether continuing production makes sense at all.

For the most recent fiscal year, sales have been $2$M per year, and all costs pre$-$tax are $1\mathrm{.}4$M per

year. Of these costs, direct labor associated with the existing machine is $$300,000,$ energy costs

are $600,000,$ and depreciation is $300,000($expected for another $5$ years$).$ $$200,000$ are

administrative costs, mainly administrative salaries.

While sales and salaries are expected to increase at the rate of inflation of $5\%$ per year for the

foreseeable future, energy costs outpace inflation and are expected to grow at $20\%$ in the future

starting now. Demand for the support beams is quite elastic due to competition from foreign

producers, meaning that the company cannot raise prices for their products above and beyond

the $5\%$ inflation rate.

As an alternative, Jackie Moora is looking for another option. She is considering whether it would

be better to buy a wholly new machine. The current price is $4\mathrm{.}5$M$,$ but the machine would have

the effect of dropping energy costs to $200,000$ for the next year. These costs, again, would grow

at the $20\%$ energy growth rate in the future. The new machine would be expected to last $10$ years,

depreciated straight$-$line over that period, and have no resale value at the end of that period.

Tax rate is $25\%$ and since this is a small enterprise with a lot of risk, discount rate used in $20\%$ per

year.

Jackie and the team of directors are seeking your help with the analysis of all possible scenarios.

$1)$ What should the company do$?$ Should the company stay in business or shut down operations?

$2)$ If they should stay in business, should they keep the old machine, or buy the new one?

Jackie and the team of directors encourage you to think carefully about how to approach the

analysis. They need the decisions to be backed by present value analysis and numbers. All possible

scenarios of operations need to be considered.

IMPORTANT HINT: The most sensible approach is to a$)$ figure out if operating with the existing

machine makes financial sense. If it does, then, perhaps, you should consider b$)$ whether replacing

existing machine with a new one creates value. If operating with the existing machine makes no

sense, then, perhaps, you should consider c$)$ whether operating with the new machine makes

sense. You should carefully think and describe what NPV or PV you are calculating, and interpret

each value you calculate clearly and carefully.