Question

Pucker Distributors handles the warehousing of perishable foods and is considering replacing one of it primary cold storage units. The company had previously hired a consultant at a cost of $25,000 to study the feasibility of replacing its cold storage unit.

One vendor has offered to sell Pucker a cold storage unit for $250,000 with an expected life of 10 years. The unit is projected to reduce electricity costs over the current unit by $50,000 per year. However, it requires a $20,000 refurbishing every two years, beginning two years after purchase. The value of the unit at the end of 10 years is $5,000.

Another vendor has offered to sell Pucker a cold storage unit with similar capabilities for $300,000 and also with an expected life of 10 years. It will produce the same savings in electricity costs, but requires refurbishing every five years at a cost of $40,000. The value of the unit at the end of 10 years is $10,000.

Pucker's cost of capital is 8.5%.

Please show with Excel formatting and formulas used

Answer 1

Net asset value of Vendor 1st:

Year	Cash Inflow	Cash Outflow	Net Cash Flow	PV Factor	Present Value
0		250000	-250000	1	-250000
1	50000		50000	0.921659	46082.94931
2	50000	20000	30000	0.849455	25483.6586
3	50000		50000	0.782908	39145.40492
4	50000	20000	30000	0.721574	21647.22853
5	50000		50000	0.665045	33252.27116
6	50000	20000	30000	0.612945	18388.35272
7	50000		50000	0.564926	28246.31754
8	50000	20000	30000	0.520669	15620.08343
9	50000		50000	0.47988	23993.98377
10	50000	20000	30000	0.442285	13268.56245
Scrap	5000	0	5000	0.442285	2211.425
Net Present Value					15128.81243

Net Present Value of Vendor 2nd:

Year	Cash Inflow	Cash Outflow	Net Cash Flow	PV Factor	Present Value
0		300000	-300000	1	-300000
1	50000		50000	0.921659	46082.94931
2	50000		50000	0.849455	42472.76434
3	50000		50000	0.782908	39145.40492
4	50000		50000	0.721574	36078.71421
5	50000	40000	10000	0.665045	6650.454233
6	50000		50000	0.612945	30647.25453
7	50000		50000	0.564926	28246.31754
8	50000		50000	0.520669	26033.47239
9	50000		50000	0.47988	23993.98377
10	50000	40000	10000	0.442285	4422.85415
Scrap	10000	0	10000	0.442285	4422.85
Net Present Value					-16225.83061

As the NPV of Vendor 2nd is negative, Pucker should by the project from Vendor 1st.

Formulas as as follows

net cash inflow= cash inflow- cash out flow

pv factor=1/(1+r)^{^n}

Present value = pv factor* net cash flow

Please do not forget to upvote my solution if you like the same.

Feel free to ask any query via comments.

Good Luck!