Question

A company needs a modern material handling system for facilitating access to and from a busy warehouse. If the company’s management use a MARR in their economic decisions.

The company found two systems, the first system is a second-hand and the second system is a new system.

The economic consequences of both systems are given in the below table.

Answer the following questions:

MARR= 22.5% per year

For Second-Hand system:

Investment Cost= $146,600

Annual expenses=$17,300

Annual savings= $61,000

Useful Life = 7 years

Salvage Value = $43,000

For New system:

Investment cost =$199,500

Annual expenses=$34,200

First year savings=$98,400 decreasing by 4% each year thereafter

Useful life= 14 years

Salvage value=$51,000

PART 2: Considering the two mutually exclusive alternatives and assume permanent need for the system (repeatability assumption).

a) Using any equivalency analysis technique of your choice (PW or FW or AW), which alternative should the company pick? (explain why)

b) Apply incremental analysis (rate of return technique) to determine the preferred alternative.

c) Determine the unfavorable system’s investment cost at which the company would be indifferent between alternatives. (For example, if when you analyze the problem, you pick the new system, determine the investment cost of the second-hand system that makes it on same level of economic desirability).

The MARR is 22.5% per year.

I need a solution for A,B &C using excel functions with steps how to do them.

Answer 1

a> The second hand system has a life of 7 years while the new system has a life of 14 years. So we can use the Second Hand System twice to gain equivalence to the New System.

So for Second Hand System:

Initial Investment = -146000 at end of year 0 and at end of year 7

Annual Expense = -17300 every year from year 1 to year 14

Annual Savings = 61000 every year from year 1 to year 14

Salvage Value= 43000 at end of year 7 and end of year 14

For New System:

Initial Investment = -199500 at end of year 0

Annual Expense = -34200 every year from year 1 to year 14

Annual Savings = 98400 in 1st year and 4% reduction every year from year 2 to year 14.

So
Year 2: 0.96*98400; Year 3: 98400 * 0.96^2 and so on...

Salvage Value= 51000 at end of year 7 and end of year 14

The Below Tables are used for calculation from excel. We use the PW analysis

SECOND HAND SYSTEM
End of Year	Initial Investment	Annual Expense	Annual Savings	Salvage Value	Cashflow	NPV of Cashflow
A	B	C	D	E	F=B+C+D+E	G=F/(1+MARR)^A
0	-146000				-146000	-146000.00
1		-17300	61000		43700	35673.47
2		-17300	61000		43700	29121.20
3		-17300	61000		43700	23772.41
4		-17300	61000		43700	19406.05
5		-17300	61000		43700	15841.67
6		-17300	61000		43700	12931.98
7	-146000	-17300	61000	43000	-59300	-14325.25
8		-17300	61000		43700	8617.73
9		-17300	61000		43700	7034.88
10		-17300	61000		43700	5742.76
11		-17300	61000		43700	4687.97
12		-17300	61000		43700	3826.91
13		-17300	61000		43700	3124.01
14		-17300	61000	43000	86700	5059.57
					Total	14515.35

New System
End of Year	Initial Investment	Annual Expense	Annual Savings	Salvage Value	Cashflow	NPV of Cashflow
A	B	C	D	E	F=B+C+D+E	G=F/(1+MARR)^A
0	-199500				-199500	-199500.00
1		-34200	98400		64200	52408.16
2		-34200	94464.00		60264	40159.27
3		-34200	90685.44		56485.44	30727.57
4		-34200	87058.02		52858.02	23472.89
5		-34200	83575.70		49375.7	17899.17
6		-34200	80232.67		46032.67	13622.28
7		-34200	77023.37		42823.37	10344.94
8		-34200	73942.43		39742.43	7837.29
9		-34200	70984.73		36784.73	5921.65
10		-34200	68145.35		33945.35	4460.87
11		-34200	65419.53		31219.53	3349.11
12		-34200	62802.75		28602.75	2504.81
13		-34200	60290.64		26090.64	1865.16
14		-34200	57879.01	51000	74679.01	4358.06
					Total	19431.22

So from PW analysis, We see that it is better to go with New System than Second Hand System

b>

Incremental Analysis Rate of Return is done by finding the IRRs or Internal Rate of Return of Second Hand System, New System and difference between the two. The Table is given below

End of Year	Cashflow 2nd Hand System	Cashflow New System	Incremental New Over Second hand
A	B	C	D=C-B
0	-146000	-199500.00	-53500.00
1	43700	64200.00	20500.00
2	43700	60264.00	16564.00
3	43700	56485.44	12785.44
4	43700	52858.02	9158.02
5	43700	49375.70	5675.70
6	43700	46032.67	2332.67
7	-59300	42823.37	102123.37
8	43700	39742.43	-3957.57
9	43700	36784.73	-6915.27
10	43700	33945.35	-9754.65
11	43700	31219.53	-12480.47
12	43700	28602.75	-15097.25
13	43700	26090.64	-17609.36
14	86700	74679.01	-12020.99
IRR = IRR(All Cashflows)	25.29%	25.47%	26.10%

The Internal rate of return is calculated using the excel formula for IRR

IRR(B0:B14) where B0 is the first cashflow of -146000 and B14 is the last cashflow of 86700

The saame is done for C for New System and D for Difference.

So we see that the Internal Rate of Return for incremental analysis is 26.10% which is higher than the MARR.

So it is better to go for New System than Second Hand System.

c>

Since we have selected the New system as out Favourable system, so the Second Hand System is the unfavourable system.

Let the initial cost of the Second Hand System be x

So the new table can be given as

SECOND HAND SYSTEM
End of Year	Initial Investment	Annual Expense	Annual Savings	Salvage Value	Cashflow	NPV of Cashflow
A	B	C	D	E	F=B+C+D+E	G=F/(1+MARR)^A
0	-x				-x	-x
1		-17300	61000		43700	35673.47
2		-17300	61000		43700	29121.20
3		-17300	61000		43700	23772.41
4		-17300	61000		43700	19406.05
5		-17300	61000		43700	15841.67
6		-17300	61000		43700	12931.98
7	-x	-17300	61000	43000	86700 - x	(86700-x)/(1.225)^7
8		-17300	61000		43700	8617.73
9		-17300	61000		43700	7034.88
10		-17300	61000		43700	5742.76
11		-17300	61000		43700	4687.97
12		-17300	61000		43700	3826.91
13		-17300	61000		43700	3124.01
14		-17300	61000	43000	86700	5059.57
					Total	174840.59 + (86700-x)/(1.225)^7 - x

To be indifferent among projects, the NPV of the Second Hand System should be equak to the NPV of the New System.

So

174840.59 + (86700-x)/(1.225)^7 - x = 19431.22

or (86700 - x) / 4.14 - x = 19431.22 - 174840.59

or 20942.03 - x/4.14 - x = -155408.78

or 5.41x / 4.14 = 176350.81

or x = 142041.31.

So if the second hand ystem costs 142041.31, then we will be indifferent between selecting the Second Hand and the New System.

This can also be solved using solver. We set up the table as below

C66 and C73 has the value of negative of D62. So C66 = -D62 and C73 = -D72

The solver is made as follows

On Solving we get the initial investment as equal to 142040.76 which is almost equal to what we had found on solving it above.

If you found this helpful, please rate it so that I can have higher earnings at no extra cost to you. This will motivate me to write more.