Requirement 6 table & chart for reference:

CAPBUD2 File & its formulas:

Chart Tickler Data Table to Use:

Chart. Using the CAPBUD2 file, develop a chart just like the one used in requirement 6 to show the sensitivity of net present value to changes in cost of the investment amount from $440,000 to $500,000 (use $10,000 increments). Complete the Chart Tickler Data Table and use it as a basis for preparing the chart. Enter your name somewhere on the chart. Save the file again as CAPBUD2. Print the chart. Sensitivity Analysis 30% 25% 20% 15% 10% Intemal Rate of Return 5% 0% -5% -10% -15% $20,000 $40,000 $60,000 $80,000 $100,000 $120,000 $140,000 Annual Cash Flows Chart Data Table Cash Flow IRR 17.95% 20,000 -11.48% 30,000 -6.76% 40,000 -2.72% 50,000 0.86% 60,000 4.14% 70,000 7.18% 80,000 10.05% 90,000 12.79% 100,000 15.41% 110,000 17.95% 120,000 20.41% 130,000 22.82% 140,000 25.17% A B D E F G 1 2 3 CAPBUD Capital Budgeting $500,000 10 years $25,000 $110,000 20.00% 4.55 years 12.50% ($34,790) 17.95% 5 Data Section 6 7 Cost of investment (initial outlay) 8 Estimated life of investment 9 Estimated salvage value 10 Estimated annual net cash inflow 11 Required rate of return 12 13 Answer Section 14 15 Payback period 16 Accounting (average) rate of return 17 Net present value 18 Internal rate of return 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Scratch Pad Cash flow table needed for NPV & IRR calculations NPV IRR Annual Salvage Combined Year Cash Flow Value Flows 0 -500000 1 110000 0 110000 2 110000 0 110000 3 110000 0 110000 4 110000 0 110000 5 110000 0 110000 6 110000 0 110000 7 110000 0 110000 8 110000 0 110000 9 110000 0 110000 10 110000 25000 135000 B D E F years 500000 10 25000 110000 0.2 years =E7/E10 =(E10-(E7-E97/E8)E7 =NPV(E11,E30:E39)+E29 =IRR(E29:E39,E11) Scratch Pad 1 2 3 4 5 Data Section 6 7 Cost of investment initial outlay) 8 Estimated life of investment 9 Estimated salvage value 10 Estimated annual net cash inflow 11 Required rate of return 12 13 Answer Section 14 15 Payback period 16 Accounting (average) rate of retum 17 Net present value 18 Internal rate of return 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 10 NPV & IRR calculations NPV Annual Salvage Cash Flow Value Year 0 1 =(B30+1) =(B31+1) =(B32+1) =(B33+1) =(B34+1) =(B35+1) =(B36+1) =(B37+1) =(B38+1) =IF((E8>=1),E10,0) =IF((E8>=2),E10,0) =IF((E8>=3).E10,0) =IF((E8>=4),E10,0) =IF((E8>=5),E10,0) =IF((E8>=6),E10,0) =IF((E8>=7),E10,0) =IF((E8>=8),E10,0) =IF((E8>=9),E10,0) =IF((E8>=10),E 10,0) =IF((E8=1),E9,0) =IF((E8=2),E9,0) =IF((E8=3).E9.0) =IF((E8=4),E9,0) =IF((E855).E9,0) =IF((E8=6),E9,0) =IF((E8=7),E9,0) =IF((E8=8),E9.0) =IF((E8=9),E9,0) =IF((E8=10),E9,0) IRR Combined Flows --E7 =C30+D30 =C31+D31 =C32+D32 =C33+D33 =C34+D34 =C35+D35 =C36+D36 =C37+D37 =C38+D38 =C39+D39 Chart Tickler Data Table Investment Amount NPV 440000 450000 460000 470000 480000 490000 500000 Chart. Using the CAPBUD2 file, develop a chart just like the one used in requirement 6 to show the sensitivity of net present value to changes in cost of the investment amount from $440,000 to $500,000 (use $10,000 increments). Complete the Chart Tickler Data Table and use it as a basis for preparing the chart. Enter your name somewhere on the chart. Save the file again as CAPBUD2. Print the chart. Sensitivity Analysis 30% 25% 20% 15% 10% Intemal Rate of Return 5% 0% -5% -10% -15% $20,000 $40,000 $60,000 $80,000 $100,000 $120,000 $140,000 Annual Cash Flows Chart Data Table Cash Flow IRR 17.95% 20,000 -11.48% 30,000 -6.76% 40,000 -2.72% 50,000 0.86% 60,000 4.14% 70,000 7.18% 80,000 10.05% 90,000 12.79% 100,000 15.41% 110,000 17.95% 120,000 20.41% 130,000 22.82% 140,000 25.17% A B D E F G 1 2 3 CAPBUD Capital Budgeting $500,000 10 years $25,000 $110,000 20.00% 4.55 years 12.50% ($34,790) 17.95% 5 Data Section 6 7 Cost of investment (initial outlay) 8 Estimated life of investment 9 Estimated salvage value 10 Estimated annual net cash inflow 11 Required rate of return 12 13 Answer Section 14 15 Payback period 16 Accounting (average) rate of return 17 Net present value 18 Internal rate of return 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Scratch Pad Cash flow table needed for NPV & IRR calculations NPV IRR Annual Salvage Combined Year Cash Flow Value Flows 0 -500000 1 110000 0 110000 2 110000 0 110000 3 110000 0 110000 4 110000 0 110000 5 110000 0 110000 6 110000 0 110000 7 110000 0 110000 8 110000 0 110000 9 110000 0 110000 10 110000 25000 135000 B D E F years 500000 10 25000 110000 0.2 years =E7/E10 =(E10-(E7-E97/E8)E7 =NPV(E11,E30:E39)+E29 =IRR(E29:E39,E11) Scratch Pad 1 2 3 4 5 Data Section 6 7 Cost of investment initial outlay) 8 Estimated life of investment 9 Estimated salvage value 10 Estimated annual net cash inflow 11 Required rate of return 12 13 Answer Section 14 15 Payback period 16 Accounting (average) rate of retum 17 Net present value 18 Internal rate of return 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 10 NPV & IRR calculations NPV Annual Salvage Cash Flow Value Year 0 1 =(B30+1) =(B31+1) =(B32+1) =(B33+1) =(B34+1) =(B35+1) =(B36+1) =(B37+1) =(B38+1) =IF((E8>=1),E10,0) =IF((E8>=2),E10,0) =IF((E8>=3).E10,0) =IF((E8>=4),E10,0) =IF((E8>=5),E10,0) =IF((E8>=6),E10,0) =IF((E8>=7),E10,0) =IF((E8>=8),E10,0) =IF((E8>=9),E10,0) =IF((E8>=10),E 10,0) =IF((E8=1),E9,0) =IF((E8=2),E9,0) =IF((E8=3).E9.0) =IF((E8=4),E9,0) =IF((E855).E9,0) =IF((E8=6),E9,0) =IF((E8=7),E9,0) =IF((E8=8),E9.0) =IF((E8=9),E9,0) =IF((E8=10),E9,0) IRR Combined Flows --E7 =C30+D30 =C31+D31 =C32+D32 =C33+D33 =C34+D34 =C35+D35 =C36+D36 =C37+D37 =C38+D38 =C39+D39 Chart Tickler Data Table Investment Amount NPV 440000 450000 460000 470000 480000 490000 500000