Question

Please see answer below. Can someone please explain how to get the answer with excel?

Company C is seeking for help to decide this option to choose to upgrade their current bottleneck equipment. There are two vendors and one rental option. The cost details are shown in the table below.

Option	Vender R	Vender T	Rental
Initial Cost	75000	125000	0
Annual Operation Cost	28000	12000	52000
Salvage Value	0	30000	0
Estimated Life in Year	2	3	Maximum 3 years

MARR = 10% per year compounded monthly.

1. Select from the two sales vendors using the AW analysis.
2. Determine which of the three options is cheaper over a 3-year study period.

P3
PP - Year
CP- Month
Time Window - Year
Year	R		T
PMT	($71,487.79)		($53,659.67)
			Winner
2	T vs. Rental
	T		Rental
PMT	($71,487.79)		AW=	$                    (52,000.00)
				Winner

Answer 1

			Year	Amount	PV factor 10% [1/(1+r)^n]	PV
Vendor R			0	(75,000.00)	1.000	(75,000.00)
Initial cost	75,000.00	$	1	(28,000.00)	0.909	(25,454.55)
Annual cost	28,000.00	$	2	(28,000.00)	0.826	(23,140.50)
Salvage value	-	$	Total		1.736	(123,595.041)
Life	2	Years
Rate	10	%			AW=	(71,195.30)

			Year	Amount	PV factor 10% [1/(1+r)^n]	PV
Vendor T			0	(125,000.00)	1.000	(125,000.00)
Initial cost	125,000.00	$	1	(12,000.00)	0.909	(10,909.09)
Annual cost	12,000.00	$	2	(12,000.00)	0.826	(9,917.36)
Salvage value	30,000.00	$	3	18,000.00	0.751	13,523.67
Life	3	Years	Total		2.487	(132,302.78)
Rate	10	%
					AW=	(53,200.91)

			Year	Amount	PV factor 10% [1/(1+r)^n]	PV
Rental			1	(52,000.00)	0.909	(47,272.73)
Annual cost	52,000.00	$	2	(52,000.00)	0.826	(42,975.21)
Life	3	Years	3	(52,000.00)	0.751	(39,068.37)
Rate	10	%	Total		2.487	(129,316.30)
					AW=	(52,000.00)

	Alternative	AW
	Vendor R	(71,195.30)
	Vendor T	(53,200.91)
	Rental	(52,000.00)
	Conclusion
1	While considering only vendors, vendor T is having lowest AW. SO vendor T should be selected
2	While considering 3 options, rental provides low AW. So rental should be selected

Notes:

1. Salvage value is adjusted with last year cash outflow

2. AW = Total discounted outflow / total pv factor

3. In total PV factor, 0th year factor will not be considered