Evaluate the 10 projects given by NPV, IRR, PI and Discounted Payback methods. Identify if they are acceptable or should be rejected. Finally, rank each method appropriately. You should use a hurdle rate of 19% and utilize 5 years as maximum payback years. Verify you have entered these factors into the appropriate box in the model so the results will calculate accurately for you. Remember you should only rank ACCEPTABLE projects-ranking unacceptable projects is incorrect and a deduction in your score. If a Payback project never pays off, you may simply indicate "Never in the documentation grid; otherwise the model will indicate the payback results. Using the results of your above analysis, various information elements will feed the problem section dealing with capital rationing. Finish the problem by identifying which projects should be done by management with the given capital budget of $160,000,000. 1 Project Year Project Cash Flow Capital Budgeting Evaluation Methods Discounted Discounted Payback Cash Flow Payback Years Minimum Payback Yrs Accept or Reject PVIFs 0 1 2 2 3 4 5 6 7 8 9 3 4 5 1.0000 0.8403 0.7062 0.5934 0.4987 0.4190 0.3521 0.2959 0.2487 0.2090 0.1756 1.00 2.00 3.00 4.00 5.00 6.00 7.00 8.00 9.00 10.00 5 Accept 5 Accept 5 Accept 5 Accept 5 Accept 5 Reject 5 Reject 5 Reject 5 Reject 5 Reject 6 7 8 9 10 10 11 12 13 14 15 16 17 18 Payback Yrs 5 Total Outflows Total Inflows Net Present Value Hurdle rate 19.00% 19 IRR #NUM! Profit Index #DIV/0! 20 Year 0 1 2 3 4 21 22 23 24 25 26 27 28 29 30 31 32 Project 1 (32,000,000) 5,000,000 6,000,000 8,000,000 10,000,000 16,000,000 18.000.000 6,000,000 2,000,000 800,000 400,000 Project 2 (9,200,000) (150,000) 700,000 1,100,000 2,500,000 2,700,000 2,700,000 1,900,000 900,000 130,000 84,000 Project 3 (1,500,000) (150,000) (100,000) (50,000) 500,000 770,000 790,000 490,000 530,000 700,000 900,000 Project 4 (96,000,000) 5,000,000 10,000,000 59,900,000 45,000,000 24,000,000 24,000,000 18,000,000 14,000,000 16,000,000 14,000,000 Project 5 (75,000,000) 12,500,000 13,900,000 14,000,000 14,000,000 14,000,000 14,000,000 14,000,000 14,000,000 14,000,000 14,000,000 Project 6 (30,000,000) 2,000,000 5,000,000 7,000,000 9,000,000 12,000,000 16,000,000 20,000,000 22,000,000 24,000,000 10,000,000 Project 7 (14,800,000) 1,000,000 800,000 600,000 400,000 200,000 5,000,000 9,000,000 5,000,000 7,000,000 8,550,000 Project 8 (17,000,000) 2,000,000 3,500,000 3,600,000 2,800,000 2,640,000 2,580,000 2,000,000 8,000,000 12,000,000 18,000,000 Project 9 (28,000,000) 6,250,000 6,250,000 6,250,000 6,250,000 6,250,000 5,950,000 5,950,000 5,950,000 5,950,000 5,950,000 Project 10 (35,600,000) 4,800,000 6,000,000 7,000,000 8,500,000 9,500,000 10,700,000 11,700,000 13,480,000 14,540,000 16,700,000 5 6 7 8 9 10 Document your answers in this section as you process each project, using the project numbers from directly above 34 35 Year Project 1 Project 2 Project 3 Project 4 Project 5 Project 6 Project 7 Project 8 Project 9 Project 10 36 Investment (32,000,000) (9,200,000) (1,500,000) (96,000,000) (75,000,000) (30,000,000) (14,800,000) (17,000,000) (28,000,000) (35,600,000) 37 NPV 38 A/R 39 Rank 40 41 IRR 42 A/R 43 Rank 44 45 PI 46 A/R 47 Rank 48 49 Disc Payback 50 A/R 51 Rank 52 0 Problem 1 10 Points Capital Rationing Problem - Available Capital Budget $160,000,000. Identify in the include/exclude columns which project to include in the current year given the available budget $'s Project Project Cost - YO NPV Accept/Reject NPV Rank Include / Exclude Budget Available Information imported from Problem #2 Type X Type x $ 160,000,000 1 (32,000,000) 0 $ 160,000,000 2 (9,200,000) 0 0 $ 160,000,000 3 (1,500,000) 0 0 160,000,000 4 (96,000,000) 0 0 $ 160,000,000 5 (75,000,000) 0 0 $ 160,000,000 6 (30,000,000) 0 0 $ 160,000,000 (14,800,000) 0 0 $ 160,000,000 8 (17,000,000) 0 0 $ 160,000,000 9 (28,000,000) 0 $ 160,000,000 10 (35,600,000) 0 $ 160,000,000 7 O O Evaluate the 10 projects given by NPV, IRR, PI and Discounted Payback methods. Identify if they are acceptable or should be rejected. Finally, rank each method appropriately. You should use a hurdle rate of 19% and utilize 5 years as maximum payback years. Verify you have entered these factors into the appropriate box in the model so the results will calculate accurately for you. Remember you should only rank ACCEPTABLE projects-ranking unacceptable projects is incorrect and a deduction in your score. If a Payback project never pays off, you may simply indicate "Never in the documentation grid; otherwise the model will indicate the payback results. Using the results of your above analysis, various information elements will feed the problem section dealing with capital rationing. Finish the problem by identifying which projects should be done by management with the given capital budget of $160,000,000. 1 Project Year Project Cash Flow Capital Budgeting Evaluation Methods Discounted Discounted Payback Cash Flow Payback Years Minimum Payback Yrs Accept or Reject PVIFs 0 1 2 2 3 4 5 6 7 8 9 3 4 5 1.0000 0.8403 0.7062 0.5934 0.4987 0.4190 0.3521 0.2959 0.2487 0.2090 0.1756 1.00 2.00 3.00 4.00 5.00 6.00 7.00 8.00 9.00 10.00 5 Accept 5 Accept 5 Accept 5 Accept 5 Accept 5 Reject 5 Reject 5 Reject 5 Reject 5 Reject 6 7 8 9 10 10 11 12 13 14 15 16 17 18 Payback Yrs 5 Total Outflows Total Inflows Net Present Value Hurdle rate 19.00% 19 IRR #NUM! Profit Index #DIV/0! 20 Year 0 1 2 3 4 21 22 23 24 25 26 27 28 29 30 31 32 Project 1 (32,000,000) 5,000,000 6,000,000 8,000,000 10,000,000 16,000,000 18.000.000 6,000,000 2,000,000 800,000 400,000 Project 2 (9,200,000) (150,000) 700,000 1,100,000 2,500,000 2,700,000 2,700,000 1,900,000 900,000 130,000 84,000 Project 3 (1,500,000) (150,000) (100,000) (50,000) 500,000 770,000 790,000 490,000 530,000 700,000 900,000 Project 4 (96,000,000) 5,000,000 10,000,000 59,900,000 45,000,000 24,000,000 24,000,000 18,000,000 14,000,000 16,000,000 14,000,000 Project 5 (75,000,000) 12,500,000 13,900,000 14,000,000 14,000,000 14,000,000 14,000,000 14,000,000 14,000,000 14,000,000 14,000,000 Project 6 (30,000,000) 2,000,000 5,000,000 7,000,000 9,000,000 12,000,000 16,000,000 20,000,000 22,000,000 24,000,000 10,000,000 Project 7 (14,800,000) 1,000,000 800,000 600,000 400,000 200,000 5,000,000 9,000,000 5,000,000 7,000,000 8,550,000 Project 8 (17,000,000) 2,000,000 3,500,000 3,600,000 2,800,000 2,640,000 2,580,000 2,000,000 8,000,000 12,000,000 18,000,000 Project 9 (28,000,000) 6,250,000 6,250,000 6,250,000 6,250,000 6,250,000 5,950,000 5,950,000 5,950,000 5,950,000 5,950,000 Project 10 (35,600,000) 4,800,000 6,000,000 7,000,000 8,500,000 9,500,000 10,700,000 11,700,000 13,480,000 14,540,000 16,700,000 5 6 7 8 9 10 Document your answers in this section as you process each project, using the project numbers from directly above 34 35 Year Project 1 Project 2 Project 3 Project 4 Project 5 Project 6 Project 7 Project 8 Project 9 Project 10 36 Investment (32,000,000) (9,200,000) (1,500,000) (96,000,000) (75,000,000) (30,000,000) (14,800,000) (17,000,000) (28,000,000) (35,600,000) 37 NPV 38 A/R 39 Rank 40 41 IRR 42 A/R 43 Rank 44 45 PI 46 A/R 47 Rank 48 49 Disc Payback 50 A/R 51 Rank 52 0 Problem 1 10 Points Capital Rationing Problem - Available Capital Budget $160,000,000. Identify in the include/exclude columns which project to include in the current year given the available budget $'s Project Project Cost - YO NPV Accept/Reject NPV Rank Include / Exclude Budget Available Information imported from Problem #2 Type X Type x $ 160,000,000 1 (32,000,000) 0 $ 160,000,000 2 (9,200,000) 0 0 $ 160,000,000 3 (1,500,000) 0 0 160,000,000 4 (96,000,000) 0 0 $ 160,000,000 5 (75,000,000) 0 0 $ 160,000,000 6 (30,000,000) 0 0 $ 160,000,000 (14,800,000) 0 0 $ 160,000,000 8 (17,000,000) 0 0 $ 160,000,000 9 (28,000,000) 0 $ 160,000,000 10 (35,600,000) 0 $ 160,000,000 7 O O