Question

A specialty concrete mixer used in construction was purchased for $290,000 7 years ago. It is MACRS-GDS 5-year property. Its annual O&M costs are $80,000. At the end of an 8-year planning horizon, the mixer will have a salvage value of $6,500. If the mixer is replaced, a new mixer will require an initial investment of $375,000, and at the end of the 8-year planning horizon, the new mixer will have a salvage value of $45,000. Its annual O&M cost will be only $40,000 due to newer technology. Use an EUAC measure, a tax rate of 40 percent, and an after-tax MARR of 9 percent to perform an after-tax analysis to see if the concrete mixer should be replaced if the old mixer is sold for its market value of $55,000.

Answer 1

Formula sheet

A

B

C

D

E

F

G

H

I

J

K

L

M

2

3

4

Calculation of EUAC of Old Machine:

5

Equivalent uniform annual cost (EUAC) can be calculated using following formula:

6

EUAC

=-NPV of the cash flows *(A/P,i,n)

7

8

Given the following data:

9

Initial Cost

290000

10

Planning horizon

8

years

11

Salvage value at the End of 8 Year

6500

12

Life of machine used

7

years

13

Remaining Life of machine

=D10

years

14

Current Market Value

55000

15

Annual Operating Cost

80000

16

MARR

0.09

17

Tax Rate

0.4

18

19

Depreciation each year can be calculated as follows:

20

Capital cost (B)

=D9

21

Depreciation follows MACRS 5 year-half year convention.

22

23

Hence depreciation each year can be calculated as follows:

24

Year 1

Year 2

Year 3

Year 4

Year 5

Year 6

Year 7

Year 8

25

MACRS 5 Year depreciation rate (rt)

0.2

0.32

0.192

0.1152

0.1152

0.0576

0

0

26

Depreciation (B*rt)

=$D20*E25

=$D20*F25

=$D20*G25

=$D20*H25

=$D20*I25

=$D20*J25

=$D20*K25

=$D20*L25

27

Book Value

=D20

=D27-E26

=E27-F26

=F27-G26

=G27-H26

=H27-I26

=I27-J26

=J27-K26

=K27-L26

28

29

Calculation of opportunity cost:

30

Current market value of the old machine will act as a opportunity cost.

31

Current market value

=D14

32

Book value at Present

=K27

33

Gain or loss on sale

=D31-D32

34

Tax Expense on gain or loss

=-D33*D17

=-D33*D17

35

Net Proceed from sale of Old Machine

=D31+D34

=D31+D34

36

37

38

NPV of Old Machine can be calculated by finding the present value of cash flows.

39

Current market value of Old Machine will be the opportunity cost to use the machine further.

40

Cash flows for Old Machine can be represented as follows:

41

Year

0

1

2

3

4

5

6

7

8

42

O&M Cost

=-$D$15

=-$D$15

=-$D$15

=-$D$15

=-$D$15

=-$D$15

=-$D$15

=-$D$15

43

Depreciation

=L26

=E43

=F43

=G43

=H43

=I43

=J43

=K43

44

EBIT

=SUM(E42:E43)

=SUM(F42:F43)

=SUM(G42:G43)

=SUM(H42:H43)

=SUM(I42:I43)

=SUM(J42:J43)

=SUM(K42:K43)

=SUM(L42:L43)

45

Tax Expense

=-E44*$D$17

=-F44*$D$17

=-G44*$D$17

=-H44*$D$17

=-I44*$D$17

=-J44*$D$17

=-K44*$D$17

=-L44*$D$17

46

EBIT*(1-T)

=E44+E45

=F44+F45

=G44+G45

=H44+H45

=I44+I45

=J44+J45

=K44+K45

=L44+L45

47

Add Depreciation

=-E43

=-F43

=-G43

=-H43

=-I43

=-J43

=-K43

=-L43

48

Operating Cash Flow

=E46+E47

=F46+F47

=G46+G47

=H46+H47

=I46+I47

=J46+J47

=K46+K47

=L46+L47

49

Opportunity Cost

=-D35

50

After Tax Proceed from sale of machine

=D11*(1-D17)

51

Free cash flow

=D49

=SUM(E48:E50)

=SUM(F48:F50)

=SUM(G48:G50)

=SUM(H48:H50)

=SUM(I48:I50)

=SUM(J48:J50)

=SUM(K48:K50)

=SUM(L48:L50)

52

53

NPV calculation:

54

NPV of the project is present value of future cash flows discounted at required rate of return less the initial investment.

55

Given the following cash flow and WACC, NPV for the project can be calculated as follows:

56

Year

0

1

2

3

4

5

6

7

8

57

Free Cash Flow (FCF)

=D51

=E51

=F51

=G51

=H51

=I51

=J51

=K51

=L51

58

MARR (i)

=D16

59

(P/F,i,n) for each year

=1/((1+$D58)^E56)

=1/((1+$D58)^F56)

=1/((1+$D58)^G56)

=1/((1+$D58)^H56)

=1/((1+$D58)^I56)

=1/((1+$D58)^J56)

=1/((1+$D58)^K56)

=1/((1+$D58)^L56)

60

Present Value of cash flows = FCF*(P/F,i,n)

=E57*E59

=F57*F59

=G57*G59

=H57*H59

=I57*I59

=J57*J59

=K57*K59

=L57*L59

61

Present value if future cash flows

=SUM(E60:L60)

=SUM(E60:L60)

62

63

NPV of Cash Flows

=Present value fo future cash flows - Initial investment

64

=D61+D57

65

66

EUAC Calculation

67

EUAC

=-NPV of the cash flows *(A/P,i,n)

68

69

NPV of cash flow

=D64

70

MARR

=D16

71

n

=D13

72

73

EUAC

=-NPV of the cash flows *(A/P,i,n)

74

=-D64*(1/PV(D70,D71,-1,0))

75

76

Hence EUAC for Old Machine is

=D74

77

78

79

Calculation of EUAC of New Machine:

80

Equivalent uniform annual cost (EUAC) can be calculated using following formula:

81

EUAC

=-NPV of the cash flows *(A/P,i,n)

82

83

Given the following data:

84

85

Life of the Machine

8

years

86

Salvage value at the End of 8 Year

45000

87

Life of machine used

0

years

88

Remaining Life of machine

8

years

89

Price of Machine

375000

90

Annual Operating Cost

40000

91

MARR

0.09

92

93

Depreciation each year can be calculated as follows:

94

Capital cost (B)

=D89

95

Depreciation follows MACRS 5 year-half year convention.

96

97

Hence depreciation each year can be calculated as follows:

98

Year 1

Year 2

Year 3

Year 4

Year 5

Year 6

Year 7

Year 8

99

MACRS 5 Year depreciation rate (rt)

0.2

0.32

0.192

0.1152

0.1152

0.0576

0

0

100

Depreciation (B*rt)

=$D94*E99

=$D94*F99

=$D94*G99

=$D94*H99

=$D94*I99

=$D94*J99

=$D94*K99

=$D94*L99

101

Book Value

=D94

=D101-E100

=E101-F100

=F101-G100

=G101-H100

=H101-I100

=I101-J100

=J101-K100

=K101-L100

102

103

Net Proceed from sale of machine calculation:

104

105

Proceed from sale of machine at the end of 8th year

=D86

106

Book Value of Machine at the end of 8th year

=L101

107

Gain or Loss on sale

=Proceed From Sale - Book value at the end of sale

108

=D105-D106

109

110

Gain or Loss on sale of Machine

=D108

111

Tax on Gain & Loss

=D110*D17

112

Net Proceed from Sale

=Proceed from Sale - Tax Expense on gain or loss

113

=D105-D111

114

115

NPV of New Machine can be calculated by finding the present value of cash flows.

116

Current market value of new Machine will be the opportunity cost to use the machine further.

117

Cash flows for new Machine can be represented as follows:

118

Year

0

1

2

3

4

5

6

7

8

119

O&M Cost

=-$D$90

=-$D$90

=-$D$90

=-$D$90

=-$D$90

=-$D$90

=-$D$90

=-$D$90

120

Depreciation

=-E100

=-F100

=-G100

=-H100

=-I100

=-J100

=-K100

=-L100

121

EBIT

=SUM(E119:E120)

=SUM(F119:F120)

=SUM(G119:G120)

=SUM(H119:H120)

=SUM(I119:I120)

=SUM(J119:J120)

=SUM(K119:K120)

=SUM(L119:L120)

122

Tax Expense

=-E121*$D$17

=-F121*$D$17

=-G121*$D$17

=-H121*$D$17

=-I121*$D$17

=-J121*$D$17

=-K121*$D$17

=-L121*$D$17

123

EBIT*(1-T)

=E121+E122

=F121+F122

=G121+G122

=H121+H122

=I121+I122

=J121+J122

=K121+K122

=L121+L122

124

Add Depreciation

=-E120

=-F120

=-G120

=-H120

=-I120

=-J120

=-K120

=-L120

125

Operating Cash Flow

=E123+E124

=F123+F124

=G123+G124

=H123+H124

=I123+I124

=J123+J124

=K123+K124

=L123+L124

126

Initial investment

=-D89

127

Net Proceed from Sale of Machine

=D113

128

Free cash flow

=D126

=SUM(E125:E127)

=SUM(F125:F127)

=SUM(G125:G127)

=SUM(H125:H127)

=SUM(I125:I127)

=SUM(J125:J127)

=SUM(K125:K127)

=SUM(L125:L127)

129

130

NPV calculation:

131

NPV of the project is present value of future cash flows discounted at required rate of return less the initial investment.

132

Given the following cash flow and WACC, NPV for the project can be calculated as follows:

133

Year

0

1

2

3

4

5

6

7

8

134

Free Cash Flow (FCF)

=D128

=E128

=F128

=G128

=H128

=I128

=J128

=K128

=L128

135

MARR (i)

=D91

136

(P/F,i,n) for each year

=1/((1+$D135)^E133)

=1/((1+$D135)^F133)

=1/((1+$D135)^G133)

=1/((1+$D135)^H133)

=1/((1+$D135)^I133)

=1/((1+$D135)^J133)

=1/((1+$D135)^K133)

=1/((1+$D135)^L133)

137

Present Value of cash flows = FCF*(P/F,i,n)

=E134*E136

=F134*F136

=G134*G136

=H134*H136

=I134*I136

=J134*J136

=K134*K136

=L134*L136

138

Present value if future cash flows

=SUM(E137:N137)

139

140

NPV of Cash Flows

=Present value fo future cash flows - Initial investment

141

=D138+D134

142

143

EUAC Calculation

144

EUAC

=-NPV of the cash flows *(A/P,i,n)

145

146

NPV of cash flow

=D141

147

MARR

=D91

148

n

=D88

149

150

EUAC

=-NPV of the cash flows *(A/P,i,n)

151

=-D141*(1/PV(D147,D148,-1,0))

=D143*(1/PV(D149,D150,-1,0))

152

153

Hence EUAC for Machine B is

=D151

154

155

156

Should Old Machine be replaced with new machine?

157

Using the following data:

158

EUAC of Old Machine

=D76

159

EUAC of New Machine

=D153

160

161

It can be seen that the old machine has lower EUAC than new machine.

162

Since Lower EUAC machine should be selected,

163

Therefore old machine should not be replaced with new machines.

164