Question

Consider four alternatives, each of which has an 8-year useful life:

A                  B                 C                 D

Initial cost             $100           $80             $60             $50

Annual benefit               $12.20                  $12.00                  $9.70          $12.20

Salvage Value                 $75.00                  $50.00                  $50.00                  $0

a) Construct a plot with interest rate on the x axis and PW on the y axis. Plot the PW vs interest rate for all 4 alternatives. Label graphs and make sure the fonts are readable. If your computer chooses very light colors, please change them to darker or brilliant colors for readability.  

b) If MARR = 8%, which project should you choose based upon your plot?

c) Set up an excel file with Year and Cash Flows for each of the alternatives for the 8 year useful life.   Calculate the net present value, the rate of return, and the benefit to cost ratio for each alternative at 8%.    Which project should you choose? Does your choice change depending upon the method used?

d) If MARR = 8%, use an incremental analysis to choose your project. Start with the spreadsheet that you created for problem #3. Recall… the challenger is the higher cost project, the defender is the lower cost project… and the incremental cost is the higher cost project – the lower cost project. Start with the lowest cost project first.   If IRR of incremental cost < MARR, choose defender; otherwise, choose challenger…    The “winner” of the first “battle” becomes the defender in the next one. Recall that you will need to show (number of alternatives-1) battles… and the winner of each battle…… Then… choose your best alternative after you have done all incremental analyses.

Answer 1

a).

Please refer to below spreadsheet for Plot of Interest rate and Net Present worth of Project relationship.

Winner 0 1 Projects 2 Initial Cost 3 Annual Benefits$12.20 $ 12.00$ 9.70 $ 12.20 4 Salvage Value $ 75.00 S 50.00 $ 50.00 S - 5 Interest rate Useful life ars S 100.0080.00 $70.00 60.00 S50.00 40.00 30.00 $20.00 $10.00 Net Present Worth (NP Choice 1% $53.38 50.58 53.73 39.37 36.94 37.33 S 41.84 $32.14 28.85 10 5% $29.61 $ $ 22.82 $ 12 $16.50 $20.76 27.02 22.85 $ 10.63 15.97 $ 22.76 $ 20.11 11.51 $ 18.78 $ 17.52 12% 16% S(10.00) S(20.00) (30.00) 10% 11% 0.07 7.34 S 15.07 15.09 S 12.78 S 10.61 (13.24)(3.61) 5.36 $ 8.54 6.59 4.75 (4.67 S 3.45 S 11.61 (0.19) $ 8.38 17 9.10 13% 14% 15% -Net Present Worth (NPW) A ฯ-Net Present Worth (NPW) B S (17.11)$ (6.81 9.81 20 Net Present Worth INPW)CNet Present Worth (NPW) D 20.74

Formula reference -

b)

If MARR = 8%, then according above plot Project-C has highest Net Present Worth at 8%. Thus, Project-C recommended here.

c)

Please refer to below spreadsheet for calculation of NPV,IRR and Benefit to cost.

E42 23 Projects 24 Initial Cost 25 Annual Benefits$12.20$ 12.00$9.70 $12.20 26 Salvage Value S75.00 S50.00S50.00 S 27 Useful Life 28 MARR $100.00$80.00 $ 60.00 $50.00 8 years Battle-1 Battle-2 Challenger Defender Challenger Defender Challenger Defender CASH FLOWS YEAR Incremental Cash Flow Incremental Cash Flow Incremental Cash F 50.00) $ $ 100.00)(80.00)$ (60.00)(50.00) $12.20 $12.20 $12.20 $12.20 $12.20 $12.20 $12.20 $ 12.20 $12.20$12.00 $12.20$12.00 $12.20$12.00 $12.20$12.00 $12.20$12.00 $12.20$12.00 $12.20$12.00 9.70 9.70 9.70 9.70 9.70 9.70 9.70 62.00 $ 59.70 $22.76 30.00$ (0.20) $ (0.20) $ (0.20) $ (0.20) $ (0.20) $ (0.20) $ (0.20) $ 49.80 $ 10.00 2.50 2.50 2.50 2.50 2.50 2.50 2.50 47.50 75.00 $ $10.63 10.02% $15.97 11.95% $20.11IRR 17.84% 5% 696 10% 40 NPV 41 IRR 42 Benefit to Cost 14.95% IRR < MARR IRR < MARR IRR >MARR 1.11 1.20 1.38 1.40 Winner

Formula reference-

E42 NPV($B$28,E32:E39)/-E31 Projects 24 Initial Cost 25 Annual Benefits 26 Salvage Value 27 Useful Life 28 MARR 23 12.2 12.2 50 8 years Battle-1 Battle3 Challenger Defender Challenger Defender Challenger Defender CASH FLOWS YEAR Incremental Cash Flowincremental Cash FlowIncremental Cash Flow -D24 -SD$25 SD$25 -SD$25 SD$25 -SD$25 -SD$25 -SD$25 -SD$25+D26 E24 SE$25 SE$25 B31-E31 -B32-E32 B33-E33 B34-E34 B35-E35 B36-E36 -B37-E37 B38-E38 B39-E39 RR H31:39 C31-E31 -C32-E32 -C33-E33 -C34-E34 -C35-E35 -C36-E36 -C37-E37 -C38-E38 -C39-E39 D31-E31 D32-E32 =SCS25 -$C$25 -$C$25 12.2 D34-E34 D35-E35 D36-E36 D37-E37 -D38-E38 35 4 -$C$25 -$C$25 -SC$25 SC S25+C26 SE$25 SE$25+E26 12.2 40 NPV 41 IRR 42 Benefit to Cost -NIV(SB$28, 32: 39)+831 :NPV($e$28,C32 C39 C31 :NPV($B$28,D32:039)+D31 -NIV(SB$28,E32:E39) tE31 RR IRR(B31:B39 NPV(SB$28,B32:B39)/-B31 NPV(SB$28,C32:C39)/-C31NPV(SB$28,D32:D39)/-D31 NPV(SB$28,E32:E39)/-E31Winner 39) MARR -IRR(L31: M39) IRR>MARR RR 31 K IRR(C31 -IRR(D31:D39 IRR(E31:E39 IRR< MARR IRR<

In all methods - NPV,IRR and Benefit to Cost , higher is the better

Project - C has highest NPV and Project-D has highest IRR and BCR.

Overall Decision = NPV is a absolute measure and IRR and BCR is a relative measure, NPV should be preferable to other methods unless specially required.

Thus, Project-C is recommended here.

d)

Please refer to spreadsheet in (c) for calculation of incremental analysis.

Decision - Project-C is recommended.