Question

NAML: Trump Inc. is in its annual Capital Budgeting review p investment opportunities and gathered information for each company has decided to use a hurdle rate of 11.50% for all pro want as determined that for any project to be accepted, it must first na ess than 4.00 years and must have a positive NPV based on the huraietan company has decided to ration the amount of capital for new projects at $1,000,000 " review process. The company has searched for ormation for each of the 5 proposals listed below. The e of 11.50% for all proposals being considered. project to be accepted, it must first have a Payback e a positive NPV based on the hurdle rate. Lastly, the mount of capital for new projects at a maximum of The Investment for each project is assumed to be at for each project is assumed to be at time 0. The Cash Flows for each project are assurned to occur at the end of the year stated, Cash Flows beyond 7 years are not conside to the increased uncertainty of these potential future cash flows. Calculate the Payback Period in years for each project. Calculate the NPV for each project in the Capital Budgeting Decision Form at the bottom of this assignment. NOTE: You must show your work for each calculation to receive credit. I. Investment required = $200,000. Cash Flows = Year 1 = $45,000 2 = 50,000 3 = 55,000 4 - 60,000 5 = 65,000 6- 75,000 7 = 75,000 Also in year 7 = Project shut-down cost estimated at the end of year 7-$10,000

Answer 1

В C D E F G Н К 1 Total Capital Contribution 10,00,000 2 3 Project I 4Year 0 1 2 3 4 5 6 7 7 5 Cashflow 45,000 75,000 -2,00,000 50,000 55,000 60,000 65,000 75,000 -10,000 6 Present Value Factor at 11.5% 0.8969 0.5803 1.0000 0.8044 0.7214 0.6470 0.5204 0.4667 0.4667 7 Present Value 38,820 39,031 -4,667 -2,00,000 40,359 40,218 39,677 37,717 35,006 8 Net Present Value 66,160 9 Cumulative Cashflow 40,359 80,577 1,20,254 1,59,073 1,96,790 2,35,822 2,70,827 2,66,160 10 Remaining Amount 40,927 1,19,423 2,00,000 1,59,641 79,746 3,210 -35,822 -70,827 -66,160 Payback Period Completed Years+ Remaining 11 Amount/Available Amount 5.08 Years 12 13 Project I 14 Year 0 1 2 3 4 5 7 15 Cashflow 16 Present Value Factor at 11.5% 17 Present Value 65,000 -2,50,000 38,000 50,000 55,000 55,000 60,000 70,000 1.0000 0.5204 0.8969 0.8044 0.7214 0.6470 0.5803 0.4667 40,218 -2,50,000 34,081 39,677 35,585 34,816 33,827 32,672 18 Net Present Value 19Cumulative Cashflow 875 1,13,976 1,49,560 34,081 74,299 1,84,376 2,18,203 2,50,875 20 Remaining Amount Payback Period Completed Years+Remaining 21 Amount/Available Amount 1,75,701 65,624 31,797 2,50,000 2,15,919 1,36,024 1,00,440 -875 6.97 Years 22

fx X Total Capital Contribution A1 A B C D E G 22 23 Project III 24 Year 25 Cashflow 6. 2 4 7 90,000 90,000 -3,50,000 90,000 90,000 90,000 90,000 90,000 26 Present Value Factor at 11.5% 1.0000 0.8969 0.8044 0.7214 0.6470 0.5803 0.5204 0.4667 27 Present Value -3,50,000 72,392 58,229 42,007 80,717 64,926 52,224 46,837 28 Net Present Value 67,333 29 Cumulative Cashflow 30 Remaining Amount 4,17,333 80,717 1,53,110 2,18,036 2,76,265 3,28,489 3,75,326 -67,333 3,50,000 2,69,283 73,735 -25,326 1,96,890 1,31,964 21,511 Payback Period = Completed Years Remaining 31 Amount/Available Amount 5.46 Years 32 33 Project IV 34 Year 2 5 6 35 Cashflow 36 Present Value Factor at 11.5% 75,000 1,00,000 1,00,000 -4,00,000 1,25,000 1,50,000 -25,000 1.0000 0.8969 0.8044 0.7214 0.6470 0.5803 0.5204 0.5204 37 Present Value -4,00,000 67,265 80,436 90,175 97,049 58,026 -13,010 38 Net Present Value 20,059 39 Cumulative Cashflow 3,92,951 3,92,951 67,265 1,47,701 2,37,875 3,34,925 3,79,941 40 Remaining Amount 4,00,000 3,32,735 2,52,299 20,059 1,62,125 65,075 7,049 7,049 Payback Period Completed Years+ Remaining 41 Amount/Available Amount More than 7 #DIV/o! Years years 42 I LL

fix Total Capital Contribution A1 A В C E G Н К Payback Period Completed YearsRemaining More than 7 41 Amount/Available Amount #DIV/0! Years years 42 43 Project V 44 Year 0 1 2 3 4 5 7 45 Cashflow 46 Present Value Factor at 11.5% -7,00,000 1,85,000 1,85,000 1,85,000 1,85,000 1,85,000 1,85,000 1,85,000 1.0000 0.8969 0.8044 0.7214 0.6470 0.5803 0.5204 0.4667 47 Present Value 1,48,807 -7,00,000 1,65,919 1,33,459 1,19,694 1,07,349 96,277 86,347 48 Net Present Value 1,57,851 49 Cumulative Cashflow 50 Remaining Amount 3,14,726 7,71,504 1,65,919 4,48,185 5,67,879 6,75,227 8,57,851 7,00,000 5,34,081 24,773 3,85,274 2,51,815 1,32,121 -71,504 -1,57,851 Payback Period Completed Years Remaining 51 Amount/Available Amount 5.26 Years 52 53 Tabular Comparison Accept/Reject 54 Payback Capital Contribution Project NPV Remarks Positive NPV and Payback is less than 7 years 55 66,160 2,00,000 5.08 Ассept Project Il and Ill are mutually exclusive, therefore only one of them can be selected. Since NPV and Payback period of Project III is better than Project lI, Accepting Project III is a better option but considering the overall contribution, the maximum cap for investment is $1,000,000, Therefore, investing in Project I with a capital investment of $200,000 is giving a good return as compared to Project IIl with an investment of $350,000 6.97 875 Reject 56 5.46 67,333 Ассept 57 #DIV/o! 58 IV Reject Negative NPV and Payback is more than 7 years -20,059 Positive NPV and Payback is less than 7 years 59 5.26 1,57,851 Ассept 7,00,000 60

fix Total Capital Contribution A1 А C D E F G Н К 1 Total Capital Contribution 1000000 2 3 Project 4 Year 5 Cashflow 1 2 3 5 6 7 60000 75000 -200000 45000 50000 55000 65000 75000 -10000 |=(1/(1+0.115)) ^B4 - B5*B6 1/(1+0.115))^14 (1/(1+0.115))^4 1/(1+0.115))AC4 =(1/(1+0.115)) D4 (1/(1+0.115))^E4 =(1/(1+0.115))^F4 =(1/(1+0.115))^G4 1/(1+0.115))^H4 Present Value Factor at 11.5% 6 7 Present Value -C5*C6 =D5*D6 -E5*E6 =F5*F6 G5*G6 H5*H6 15*16 J5*J6 8 Net Present Value -SUM(B7:17) -19+ J7 -$B$10-J9 9 Cumulative Cashflow 10 Remaining Amount H9+ 17 =F9+G7 0 B9+C7 -C9+D7 =D9+E7 =E9+F7 G9+H7 =$B$ 10-G9 -SB$10- C9 - S B$ 10 - D9 =SB$10-19 =$B$10-E9 -$B$10-F9 -$B$10-H9 -B5 Payback Period Completed Years 5+G10/H7 11 Remaining Amount/Available Amount Years 12. 13 Project II 14 Year 15 Cashflow 0 1 2 3 4 5 6 7 55000 -250000 38000 50000 55000 60000 65000 70000 | (1/(1+0.115))^D14 | (1/(1+0.115))^G14 =(1/(1+0.115)) B14 -(1/(1+0.115))^c14 =(1/(1+0.115)) E14 (1/(1+0.115))^F14 1/(1+0.115))^H14 (1/(1+0.115) ) ^|14 16 Present Value Factor at 11.5% 17 Present Value B15*B16 -C15*C16 =D15*D16 E15*E16 =F15*F16 -G15*G16 H15*H16 115*116 =SUM(B17:117) 18 Net Present Value 19 Cumulative Cashflow 20 Remaining Amount - E19+F17 -В19+C17 -G19+H17 H19+117 0 C19+D17 D19+E17 =F19+G17 -$B$20-C19 -$B$20-D19 $B$20-E19 -$B$20-F19 -$B$20-G19 =$B$20-H19 -$B$20-119 B15 Payback Period = Completed Years 21 Remaining Amount/Available Amount 6+H20/117 Years 22 23 Project I 24 Year 0 1 2. 3 4 5 6 7 25 Cashflow 26 Present Value Factor at 11.5% 27 Present Value |-350000 90000 90000 90000 90000 90000 90000 90000 =(1/(1+0.115) B24 - B25*B26 (1/(1+0.115))^C24 (1/(1+0.115))^D24 1/(1+0.115)) E24 - E25*E26 (1/(1+0.115))AF24 =(1/(1+0.115)) G24 (1/(1+0.115)) H24 1/(1+0.115)) 24 F25*F26 C25*C26 D25*D26 -G25*G26 H25*H26 =125*126 28 Net Present Value -SUM(B27:127) 29 Cumulative Cashflow 30 Remaining Amount 0 -В29+C27 -C29+D27 D29+E27 -E29+F27 =F29+G27 G29+H27 H29+127 -$B$30-F29 -SB$30-C29 -SB$30-D29 -$B$30-E29 -$B$30-G29 =$B$30-H29 -$B$30-129 -B25 Payback Period =Completed Years 31 Remaining Amount/Available Amount Choot1 =5+G30/H27 Years

fix X M49 В C D E F G н J K. | =$B$30-C29 -$B$30-D29 =$B$30-E29 -$B$30-F29 -$B$30-G29 -$B$30-H29 -$B$30-129 30 Remaining Amount 3-В25 Payback Period Completed Years 31 Remaining Amount/Available Amount =5+G30/H27 Years 32 33 Project IV 34 Year 35 Cashflow 0 1 2 4 5 6 25000 -400000 75000 100000 125000 150000 100000 0 | (1/(1+0.115))^G34 36 Present Value Factor at 11.5% =(1/(1+0.115))^B34 1/(1+0.115))^C34 - B35*B36 =(1/(1+0.115)) D34 1/(1+0.115)) E34 (1/(1+0.115)) f34 1/(1+0.115))^H34 (1/(1+0.115) ) ^134 37 Present Value ЕС35*С36 -D35*D36 -E35*E36 F35*F36 -G35*G36 H35*H36 135*136 38 Net Present Value =SUM(B37:137) 39 Cumulative Cashflow 40 Remaining Amount Payback Period Completed Years 41 Remaining Amount/Available Amount -C39+D37 D39+E37 -E39+F37 H39+137 -В39+С37 F39+G37 -G39+H37 SB$40-C39 -$B$40-D39 -$B$40-E39 $B$40-F39 $B$40-G39 =$B$40-H39 $B$40-139 -B35 -6+140/ J37 More than 7 years Years 42 43 Project V 44 Year 0 1 2 4 6 7 45 Cashflow -700000 185000 C45 D45 E45 F45 G45 H45 | (1/(1+0.115))^E44 1/(1+0.115))^f44 1/(1+0.115)) B44 (1/(1+0.115)) C44 1/(1+0.115))^D44 (1/(1+0.115))^G44 (1/(1+0.115))^H44 (1/(1+0.115))^144 =145*146 46 Present Value Factor at 11.5% 47 Present Value 48 Net Present Value 49 Cumulative Cashflow -B45 B46 C45*C46 =D45*D46 -E45*E46 F45*F46 G45*G46 -H45*H46 |=SUM(B47:147) =B49+C47 D49+E47 0 -C49+D47 =E49+F47 =F49+G47 G49+H47 H49+147 -SB$50-D49 =SB$50-C49 -$B$50-E49 -$B$50-F49 -$B$50-G49 -$B$50-H49 -$B$50-149 50 Remaining Amount Payback Period Completed Years 51 Remaining Amount/Available Amount -B45 =5+G50/H47 Years 52. 53 Tabular Comparison Capital Contributio Accept/Reject Acсept Project Payback J11 Remarks 54 NPV Positive NPV and Payback is less than 7 years J8 =-B5 55 Project Il and IIl are mutually exclusive, therefore only one of them can be selected. Since NPV and Payback period of Project IIl is better than Project II, Accepting Project IIl is a better option but considering the overall contribution, the maximum cap for investment is $1,000,000, Therefore, investing in Project I with a capital investment of $200,000 is giving a good return as compared to Project III with an investment of $350,000 Reject II E121 56 = 1 18 Ассеpt E131 E128 57

fix X M49 A В C D F Н J К 32 33 Project IV 34 Year 35 Cashflow 0 1 2 4 5 6 б 100000 150000 100000 -400000 75000 125000 0 -25000 | (1/(1+0.115))^D34 | (1/(1+0.115))^G34 36 Present Value Factor at 11.5% 37 Present Value =(1/(1+0.115))^B34 1/(1+0.115))^C34 - B35*B36 =(1/(1+0.115)) E34 (1/(1+0.115))AF34 1/(1+0.115))^H34 (1/(1+0.115) ) ^134 ЕС35*С36 -D35*D36 -E35*E36 F35*F36 -G35*G36 H35*H36 135*136 38 Net Present Value =SUM(B37:137) 39 Cumulative Cashflow 40 Remaining Amount Payback Period Completed Years 41 Remaining Amount/Available Amount ЕВ39+С37 D39+E37 -E39+F37 -G39+H37 H39+137 C39+D37 =F39+G37 -$B$40-C39 -$B$40-D39 -$B$40-E39 $B$40-F39 $B$40-G39 -$B$40-H39 $B$40-139 3-В35 -6+140/ J37 More than 7 years Years 42 43 Project V 44 Year 0 1 2 3 4 6 7 45 Cashflow -700000 185000 C45 D45 E45 F45 G45 H45 | (1/(1+0.115))^E44 - E45*E46 | (1/(1+0.115))^G44 (1/(1+0.115))^H44 (1/(1+0.115))^144 =(1/(1+0.115))^B44 1/(1+0.115))^C44 (1/(1+0.115)) D44 1/(1+0.115))^f44 46 Present Value Factor at 11.5% 47 Present Value 48 Net Present Value B45*B46 -C45*C46 -D45*D46 =F45*F46 -G45*G46 H45*H46 l45*146 -SUM(B47:147) 49 Cumulative Cashflow -C49+D47 F49+G47 -G49+H47 H49+147 0 - B49+C47 D49+E47 E49+F47 =$B$50-E49 =$B$50-C49 =SB$50-D49 =$B$50-F49 -$B$50-G49 -$B$50-H49 =$B$50-149 50 Remaining Amount Payback Period Completed Years --B45 51 Remaining Amount/Available Amount =5+G50/H47 Years 52. 53 Tabular Comparison Project Capital Contributio Accept/Reject Payback NPV Remarks 54 Positive NPV and Payback is less than 7 years J8 --B5 55 J11 Аcсept Project Il and Ill are mutually exclusive, therefore only one of them can be selected. Since NPV and Payback period of Project IIl is better than Project II, Accepting Project III is a better option but considering the overall contribution, the maximum cap for investment is $1,000,000, Therefore, investing in ProjectI with a capital investment of $200,000 is giving a good return as compared to Project lII with an investment of $350,000 Reject -121 =l 18 56 III Ассept 131 -128 0 0 -B45 57 Negative NPV and Payback is more than 7 years Positive NPV and Payback is less than 7 years 141 -138 Reject Acсept 58 IV 151 148 59 V 60
For any query or clarification, please leave a comment.