Question

Two alternative machines will produce the same​ product, but one is capable of​ higher-quality work, which can be expected to return greater revenue. The following are relevant data. Determine which is the better​ alternative, assuming repeatability and using SL​ depreciation, an​ income-tax rate of 23%, and an​ after-tax MARR of 8%.

	Machine A	Machine B
Capital Investment	$20,000	$29,000
Life	12 Years	6 Years
Terminal BV (and MV)	$4,500	$500
Annual Receipts	$158,000	$188,000
Annual Expenses	$129,000	$174,000

1) Calculate the AW value for the Machine A.

2)Calculate the AW value for the Machine B.

Answer 1

For machine A

SL dep per year = (P-S)/N = (20000-4500) / 12 = 1291.67

BTCF = 158000 - 129000 = 29000

taxable income = 29000 - 1291.67 = 27708.33

tax = 0.23 * 27708.33 = 6372.92

ATCF = BTCF - tax = 29000 - 6372.92 = 22627.08

AW of machine A = -20000*(A/P,8%,12) + 22627.08 + 4500*(A/F,8%,12)

= -20000*0.132695 + 22627.08 + 4500*0.052695

= 20210.31

For machine B

SL dep per year = (P-S)/N = (29000-500) / 6 = 4750

BTCF = 188000 - 174000 = 14000

taxable income = 14000 - 4750 = 9250

tax = 0.23 * 9250 = 2127.50

ATCF = BTCF - tax = 14000 - 2127.50 = 11872.50

AW of machine B = -29000*(A/P,8%,6) + 11872.50+ 500*(A/F,8%,6)

= -29000*0.216315 + 11872.50+ 500*0.136315

= 5667.53

As AW of machine A is more, it should be selected

Using Excel

		Alternative A
Yrs	BTCF	Depreciation	Taxable income	Tax	ATCF
0	-20000				-20000
1	29000	1291.67	27708	6373	22627
2	29000	1291.67	27708	6373	22627
3	29000	1291.67	27708	6373	22627
4	29000	1291.67	27708	6373	22627
5	29000	1291.67	27708	6373	22627
6	29000	1291.67	27708	6373	22627
7	29000	1291.67	27708	6373	22627
8	29000	1291.67	27708	6373	22627
9	29000	1291.67	27708	6373	22627
10	29000	1291.67	27708	6373	22627
11	29000	1291.67	27708	6373	22627
12	29000	1291.67	27708	6373	27127
12	4500		0	0
				NPW	152306
				AW	20210
		Alternative B
Yrs	BTCF of B	Depreciation of B	Taxable income	Tax of B	ATCF of B
0	-29000				-29000
1	14000	4750	9250	2127.5	11872.5
2	14000	4750	9250	2127.5	11872.5
3	14000	4750	9250	2127.5	11872.5
4	14000	4750	9250	2127.5	11872.5
5	14000	4750	9250	2127.5	11872.5
6	14000	4750	9250	2127.5	12372.5
6	500	0	0	0
NPW	25641			NPW	26200
				AW	5668

Showing formula in excel

		Alternative A
Yrs	BTCF	Depreciation	Taxable income	Tax	ATCF
0	-20000				=B31
1	=158000-129000	=(20000-4500)/12	=B32-C32	=D32*0.23	=B32-E32
2	=158000-129000	=(20000-4500)/12	=B33-C33	=D33*0.23	=B33-E33
3	=158000-129000	=(20000-4500)/12	=B34-C34	=D34*0.23	=B34-E34
4	=158000-129000	=(20000-4500)/12	=B35-C35	=D35*0.23	=B35-E35
5	=158000-129000	=(20000-4500)/12	=B36-C36	=D36*0.23	=B36-E36
6	=158000-129000	=(20000-4500)/12	=B37-C37	=D37*0.23	=B37-E37
7	=158000-129000	=(20000-4500)/12	=B38-C38	=D38*0.23	=B38-E38
8	=158000-129000	=(20000-4500)/12	=B39-C39	=D39*0.23	=B39-E39
9	=158000-129000	=(20000-4500)/12	=B40-C40	=D40*0.23	=B40-E40
10	=158000-129000	=(20000-4500)/12	=B41-C41	=D41*0.23	=B41-E41
11	=158000-129000	=(20000-4500)/12	=B42-C42	=D42*0.23	=B42-E42
12	=158000-129000	=(20000-4500)/12	=B43-C43	=D43*0.23	=B43-E43+B44
12	4500		0	0
				NPW	=NPV(8%,F32:F44)+F31
				AW	=PMT(8%,12,-F45)
		Alternative B
Yrs	BTCF of B	Depreciation of B	Taxable income	Tax of B	ATCF of B
0	-29000				=B49
1	=188000-174000	=(29000-500)/6	=B50-C50	=D50*0.23	=B50-E50
2	=188000-174000	=(29000-500)/6	=B51-C51	=D51*0.23	=B51-E51
3	=188000-174000	=(29000-500)/6	=B52-C52	=D52*0.23	=B52-E52
4	=188000-174000	=(29000-500)/6	=B53-C53	=D53*0.23	=B53-E53
5	=188000-174000	=(29000-500)/6	=B54-C54	=D54*0.23	=B54-E54
6	=188000-174000	=(29000-500)/6	=B55-C55	=D55*0.23	=B55-E55+500
6	500	0	0	=D56*0.5
NPW	=NPV(14%,B50:B56)+B49			NPW	=NPV(8%,F50:F56)+F49
				AW	=PMT(8%,6,-F57)