Question

Instructions: • Read the case below and answer all four questions at the end of the case. • Show all work when comparing alternatives and using calculated values. • Submit your answers on a Word (.doc) or Excel (.xls). Handwritten submittals will not be accepted. Acme Engineering is evaluating 10 projects for next year's capital budget investments. Their corporate MARR is set at 12% and all projects have a 10-year life: Initial Cost Annual Benefit Project (thousands) (thousands) 1 $50 $10.3 2 $150 $32.2 3 $100 $17.7 $300 $48.8 $50 $11.9 $200 $38.3 7 $50 $10.0 8 $200 $36.9 19 $50 $11.5 10 $100 $22.3 1. Calculate the IRR for every project 2. If money is not a problem which projects should be approved? 3. Rank order all the acceptable projects according to IRR 4. If only $450,00 is available for the budget which projects should be approved?

Answer 1

NPV and IRR is calculated using NPV and IRR functions in excel respectively

A B с D E F G H K L M 1 Year 1 2 3 4 7 8 9 10 N 3 0 -50 -150 -100 -300 Project 5 6 -50 -200 11.9 38.3 11.9 38.3 -50 -200 -50 -100 4 1 32.2 17.7 48.8 10 36.9 11.5 22.3 10.3 10.3 5 2 32.2 10 36.9 22.3 17.7 17.7 48.8 48.8 11.5 11.5 6 3 10.3 32.2 11.9 38.3 10 36.9 22.3 7 4 10.3 32.2 11.9 38.3 10 36.9 22.3 17.7 17.7 48.8 48.8 8 5 10.3 11.9 38.3 10 36.9 32.2 32.2 11.5 11.5 11.5 11.5 9 6 10.3 17.7 11.9 38.3 10 36.9 10 7 10.3 17.7 11.9 38.3 10 36.9 48.8 48.8 48.8 48.8 22.3 22.3 22.3 22.3 22.3 22.3 11 8 10.3 17.7 38.3 32.2 32.2 32.2 32.2 10 11.5 12 9 10.3 10.3 17.7 17.7 11.9 11.9 11.9 38.3 38.3 10 10 36.9 36.9 36.9 11.5 11.5 13 10 48.8 14 15 NPV IRR 8.20 31.94 0.01-24.27 17.24 16.40 6.50 8.49 14.98 26.00 15.88% 17.00% 12.00% 9.99% 19.94% 13.97% 15.10% 13.03% 18.94% 18.06% 16 17 18

A B C D E F G H - J K 1 Project 2 Year 1 2 3 14 5 6 17 19 10 8 -200 3 0 -50 - 150 -100 -300 -50 -200 |-50 -50 4 1 32.2 38.3 10 11.5 10.3 10.3 17.7 17.7 11.9 11.9 36.9 36.9 5 2 32.2 38.3 10 11.5 6 3 10.3 32.2 17.7 48.8 48.8 48.8 48.8 48.8 11.9 38.3 10 36.9 11.5 4 10.3 11.9 38.3 10 11.5 7 8 9 32.2 32.2 17.7 17.7 36.9 36.9 - 100 22.3 22.3 22.3 22.3 22.3 22.3 22.3 22.3 22.3 5 10.3 38.3 10 11.5 11.9 11.9 6 10.3 32.2 17.7 48.8 38.3 10 36.9 11.5 10 7 10.3 32.2 17.7 48.8 10 11.5 11.9 11.9 38.3 38.3 36.9 36.9 11 8 32.2 17.7 48.8 10 11.5 10.3 10.3 12 9 32.2 11.9 38.3 10 36.9 11.5 17.7 17.7 48.8 48.8 13 10 10.3 32.2 11.9 38.3 10 36.9 11.5 22.3 14 15 NPV =C3+NPV(12%,C4:C13) =D3+NPV(12%,D4:D13) =E3+NPV(12%,E4:E13) =F3+NPV(12%,F4:F13) =G3+NPV(12%, G4:G13) =H3+NPV(12%,H=13+NPV(12%,14=J3+NPV(12%,J4=K3+NPV(12%,K=L3+NPV(12%, =IRR(C3:C13) =IRR(D3:013) =IRR(E3:E13) =IRR(F3:F13) =IRR(G3:G13) =IRR(H3:H13) (=IRR(13:113) =IRR(13:13) =IRR(K3:K13) =IRR(L3:L13) IRR 16 17

1)

	1	2	3	4	5	6	7	8	9	10
IRR	15.88%	17.00%	12.00%	9.99%	19.94%	13.97%	15.10%	13.03%	18.94%	18.06%

2) If money if not a problem, all the projects except project 3 and 4 must be choosen since they have a positive NPV and IRR>MARR

3) Higher the IRR, better is the acceptability of the project

Project	IRR
5	19.94%
9	18.94%
10	18.06%
2	17.00%
1	15.88%
7	15.10%
6	13.97%
8	13.03%
3	12.00%
4	9.99%

4)

Capital available = 450,000

We prioritize project with the highest NPV/Investment ratio

F 2. А B C D E 1 Project NPV Initial investment NPV/Investment Cumulative investments 5 17.24 -50 0.344753081 -50 3 9 14.98 -50 0.299551297 -100 4 10 26.00 -100 0.259999735 -200 5 2 31.94 -150 0.212914543 -350 6 1 8.20 -50 0.163945944 -400 7 7 6.50 -50 0.130044606 -450 6 16.40 -200 0.08201771 -650 9 8 8.49 -200 0.042466149 -850 10 3 0.01 -100 8.9476E-05 -950 11 4 -24.27 -300 -0.080897054 - 1250 12 8

B C E A Project 1 Initial investment Cumulative investments 2 5 39 4 10 52 =C2 =C3+E2 =C4+E3 D NPV/Investment =B2/(-C2) =B3/(-C3) =B4/(-C4) =B5/(-C5) =B6/(-C6) =B7/(-C7) =B8/(-C8) =B9/(-09) =B10/(-C10) =B11/(-011) NPV 17.2376540380-50 14.9775648267-50 25.9999735339-100 31.9371815148-150 8.1972971926-50 6.50223028410-50 16.4035419881-200 8.49322974834-200 0.00894760287-100 -24.269116213-300 =C5+E4 =C6+E5 6 1 7 7 =C7+E6 =C8+E7 8 6 98 10 3 1=C9+E8 =C10+E9 1=C11+E10 11 4 12

Here, we see that projects till 7 number must be approved since the investments reach 450,000

Hence, Project 5,9,10,2,1,7 must be approved.