In: Economics
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.
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) |