The production department is intending on acquiring a more energy$-$efficient machine. The production manager has asked for your help with evaluating two alternative machine models that have comparable technical applications.

Scenario: The production department is intending on acquiring a more energy$-$efficient machine. The production manager has asked for your help with evaluating two alternative machine models that have comparable technical specifications.

$$ Model $1$: Requires initial investment of $$252,000.$ The expected cash inflow for the first year is $$42,000$ and this is expected to grow at $6\%$ per annum.

$$ Model $2$: Requires initial investment of $$230,000.$ The expected cash inflow for the first year is $$35,000$ and this is expected to grow at $8\%$ per annum. The expected useful life of both machines is seven years. The business requires a rate of return of $8\%.$ Create a new Excel workbook and complete the requirements below on one sheet. Present everything in a tidy and formatted manner.

Requirements:

$1.$ Set up columns A and B for the input data and input relevant information from the scenario above.

$2.$ Calculate the following for Model $1$ and Model $2$ using cell references and functions $($i$.$e$.,$ no hard coding$)$:

a$)$ Projected future cash flows

b$)$ Net present value $($use the NPV function$)$

c$)$ Internal rate of return $($use the IRR function$)$

d$)$ Payback period

$3.$ For Model $1$:

a$)$ Use the formula $=1(1+)$ to manually calculate the present value of annual cash flows and sum the results to obtain the net present value.

b$)$ Create a column chart that compares the undiscounted cash flows with the present value of cash flows.

$4.$ Insert a text box below your completed analysis and comment on which model you recommend the production manager acquire.

Insert a text box below your completed analysis and comment on which model you recommend the production manager acquire.