completing this activity will help you learn to:

$$ Determine the financial feasibility of an investment using the net present value of future cash flows.

$$ Extract information from a data set using Excel formulas, functions, and$/$or pivot tables.

Scenario:

Company A is evaluating the financial feasibility of investing in $140$ pieces of machinery and equipment.

Each investment is being considered independently and has its own desired rate of return. The analyses of the different investments are not being compared to one another.

The first step in the analysis of each investment is to compare the net present value of its future cash flows to its cost.

If the present value of the future cash flows is greater than the cost of the investment, the company will look further into the purchase of the investment.

If the present value of the future cash flows is less than the cost of the investment, the company will no longer consider the purchase of the investment.

The Data worksheet contains a list of investments $($equipment or machinery$)$ and projected cash flows for each.

Each row provides information about one investment, including its type, desired rate of return, cost, cash inflows for each of $10$ years, and residual value.

Required:

Open spreadsheet

Complete four columns on the Data worksheet: Total cash flows, PV net cash flows, Net cash flow, and Consider further.

R$1.$ Cell P$4$: Enter a formula that results in total undiscounted future cash flows using the data in row $4.$

fill in the blank $1$ of $1$$

R$2.$ Cell Q$4$: Enter a formula using PV functions that results in total net present value of the future cash flows using the data in row $4.$

fill in the blank $1$ of $1$$

R$3.$ Cell R$4$: Enter a formula that results in net cash flow from the investment, considering cost and future cash flows, using the data in row $4.$

fill in the blank $1$ of $1$$

R$4.$ Cell S$4$: Use an IF function to return either "Yes" or $"$No$"$ to state whether the investment should be considered further using the data in row $4.$

Yes

No

R$5.$ Copy down the entries in cells P$4,$ Q$4,$ R$4,$ and S$4$ to all other rows with data.

Steps to create a pivot table:

S$1.$ Select a cell in the data on the Data worksheet.

S$2.$ Select Insert $/$ PivotTable. The data range will automatically be inserted. A $$Create PivotTable$$ window should appear.

S$3.$ In the window, under "Choose where you want the PivotTable report to be placed", select $$New Worksheet$.$ A new worksheet should appear with the PivotTable.

S$4.$ A PivotTable Field List should pop up$.$ Drag fields into rows, columns, filters, and values areas as needed. $($If this gray work box does not appear, right click in the pivot table and select "Show Field List".$)$

Questions:

Use the information on the Data worksheet and pivot tables, formulas, and$/$or functions to answer the following questions.

Q$1.$ What percentage of the investments should be considered further? $($Round the percentage to one decimal place, such as $10\mathrm{.}2\%.)$

fill in the blank $1$ of $1\%$

Q$2.$ How many of the investments with a desired rate of return of $8\%$ had cash flows for $8$ years?

fill in the blank $1$ of $1$

Q$3.$ What is the difference between the present value of the cash flows of Equipment $46$ if the desired rate of return had been $7\%$ rather than $8\%?($Round the answer to the nearest whole dollar and as a positive number.$)$

fill in the blank $1$ of $1$$

Q$4.$ How many of the investments have a positive net cash flow over $$24,800$ and a desired rate of return of $6\%$ or $8\%?$

fill in the blank $1$ of $1$

Q$5.$ A capital investment should be considered if

the investment's residual value is less than the present value of its future cash flows.

the investment's cost is greater than the present value of its future cash flows.

the investment's residual value is more than the present value of its future cash flows.

the investment's cost is less than the present value of its future cash flows.