pter $14$: Applying Excel

Data

Example E

Cost of equipment needed $$180,000$

Working capital needed $$25,000$

Overhaul of equipment in four years $$20,000$

Salvage value of the equipment in five years $$25,000$

Annual revenues and costs:

Sales revenues $$410,000$

Cost of goods sold $$265,000$

Out$-$of$-$pocket operating costs $$90,000$

Discount rate $16\%$ Purchase of equipment

S$(60,000)$

$4$ Investment in working capital

$ $(100,000)$

$5$ Sales

$200,000$ $ $200,000$ $ $200,000$ $ $200,000$ $ $200,000$

$6$ Cost of goods sold

$ $(125,000)$ $ $(125,000)$ $ $(125,000)$ $ $(125,000)$ $ $(125,000)$

$7$ Out$-$of$-$pocket costs for salaries, advertising, etc.

$(35,000)(35,000)(35,000)(35,000)(35,000)$

$8$ Overhaul of equipment

$ $(5,000)$

$9$ Salvage value of the equipment

$10,000$

$10$ Working capital released

$ $100,000$

$11$ Total cash flows $($a$)$

$ $(160,000)$ $ $40,000$ $ $40,000$ $ $40,000$ $ $35,000$ $ $150,000$

$12$ Discount factor $(14\%)($b$)$

$1\mathrm{.}000$

$0\mathrm{.}877$

$0\mathrm{.}769$

$0\mathrm{.}675$

$0\mathrm{.}592$ S

$0\mathrm{.}519$

$13$ Present value of cash flows $($a$)$ x $($b$)$

$(160,000)$ $ $35,080$ $ $30,760$ $ $27,000$ $ $20,720$

$77,850$

$14$ Net present value $($SUM B$13$:G$13)$

$$

$31,410$

$15$

$16$ Note: The discount factors come from Exhibit $14$B$-1$ in Appendix $14$B$.$ What is the net present value of the project? $($Negative amount should be indicated by a minus sign. Round your present value factor to $3$ decimals and round all other intermediate calculations to nearest whole dollar.$)$ Reset the discount rate to $16\%.$ Suppose the salvage value is uncertain. How large would the salvage value have to be to result in a positive net present value?