Question

1.) Projects A and B both have 5 years of life. The initial investments and cash flows from these two projects are shown below.

Year	0	1	2	3	4	5
Project A	-12,300	4,500	4,000	3,500	3,000	2,500
Project B	-15,000	6,000	5,000	4,000	3,200	2,400

A.) Use Data Table tool to find the NPVs of both projects when the discount rate is 10%.

B.) Use Data Table tool to find the IRRs of both projects

C.) Plot the NPV profiles for both projects on the same graph.

D.) Use Goal Seek to find the crossover rate of these two projects. (Note that the solution of this part must include an image of the Goal Seek dialog showing all input values. The image can be shown in a non-Excel file.)

Please show all cells like the given picture (same format if possible). I need to know which cells to use and input. Please show the entire excel file.

PLEASE! USE EXCL! I have posted this problem several times, and each time I have gotten NOTHING! PLEASE PLEASE PLEASE HELP ME! Show your work using excel, showing all the cells you used and step by step instructions on how you used goal seek!

Draw Page Layout Formulas Data Review View Kutools TM Kutools Plus Help Tell me what you want to do Share AutoSum - Az Commen O Calibri === D. General File Home Insert X Cut L6 Copy - Paste & Format Painter Clipboard 11 - A - A- Wrap Text 3 Merge & Center - E Insert Delete Format BI U - 9 P2 D Conditional Format as Cell Formatting Table Styles Styles ¢ , $ - %688 , 40.00 Clear Sort & Find & Filter - Select Editing Font Alignment Number Cells A1 - X for Data A B C D G H I J K L M N O P Q R S T U V W X 1 Data Year E NPV Profiles Discount Rate NPV (A) NPV (B) 1 Project A -12,300 4,500 4,000 3,500 3,000 2,500 Project B -15,000 6,000 5,000 4,000 3,200 2,400 0% 1% 2% NPV Profiles of Projects A and B 10 Cost of Captia 10% 5% 6% 11 12 NPV 13 IRR 50.60 $0.40 $0.20 9% 10% 11% 12% 13% 14% 15% 16% 17% 18% 19% 20% $0.00 096 596 10% 1595 20% Discount Rate — NPV (A) - NPV (8) Crossover Rate NPV NPV difference Sheet1 Ready @ J -— H

Answer 1

1 A. Calculation of Net present value of Project A 3 Discount rate @10% = 4 Year Cashflows (1/1+10%)^n 5 1 4500 =1/1.1 6 2 4000 C5/1.1 73 =C6/1.1 8 4 3000 =C7/1.1 9 5 2500 =C8/1.1 10 3500 Present Value = Cash Discount rate flows *Disc. Rate 0.909091 =B5*C5 0.826446 =B6*C6 0.751315 =B7*C7 0.683013 =B8*C8 0.620921 =B9*C9 Total Present Value Cashflows Less : Initial Investment Net Present Value (NPV) Present Value 4090.91 3305.78 2629.6 2049.04 1552.3 13627.65 -12300 1327.65 16 14 A. Calculation of Net present value of Project B Discount rate @10% = 17 Year Cashflows (1/1+10%)^n 18 1 6000 =1/1.1 19 2 5000 =C18/1.1 20 3 4000 =C19/1.1 21 4 3200 =C20/1.1 22 5 2400 =C21/1.1 Present Value = Cash Discount rate flows * Disc. Rate Present Value 0.909091 =B18*C18 5454.55 0.826446 =B19*C19 4132.23 0.751315 =B20*C20 3005.26 0.683013 =B21*C21 2185.64 0.620921 =B22*C22 1490.21 Total Present Value Cashflows 16267.89 Less : Initial Investment - 15000 Net Present Value (NPV) 1268.89

E A 28 B. Finding IRR, where NPV is Zero. 29 If Discount rate is 15% 30 Project A 31 Discount rate @15% = 32 Year Cashflows (1/1+15%)^n 33 1 4500 =1/1.15 34 2 4000 =C33/1.15 35 3 3500 =C34/1.15 36 4 3000 =C35/1.15 37 5 2500 =C36/1.15 38 Present Value = Cash Discount rate flows *Disc. Rate Present Value 0.86956 =B33*C33 3913.04 0.75614 =B34*C34 3024.57 0.65752 =B35*C35 2301.31 0.57175 =B36*C36 1715.26 0.49718 =B37* C37 1242.94 Total Present Value Cashflows 12197.13 Less : Initial Investment -12300 Net Present Value (NPV) -102.87 42 IRR = ra+NPVa(rb-ra)/(NPVA-NPVb) 43 ra= Lower Discount rate = 10% 44 rb= Higer Discount rate = 15% 45 NPVa = NPV at lower discount Rate = 1,327.65 46 NPVb = NPV at Higer discount Rate = -102.87 47 48 IRR = 10% + 1,327.65*(15%-10%) 49 (1327.65-(-102.87)) 50 IRR = 10% + 66.38 1430.52 52 IRR = 10% +4.6% = 14.6% 51

А 54 Project B 55 56 Year 57 58 2 59 3 60 4 61 5 Cashflows 6000 5000 4000 3200 2400 Discount rate @15% = (1/1+15%)^n =1/1.15 =C57/1.15 =C58/1.15 =C59/1.15 =C60/1.15 Present Value = Cash Discount rate flows *Disc. Rate Present Value 0.86956 =B57*C57 5217.39 0.75614 B58*C58 3780.72 0.65752 =B59*C59 2630.06 0.57175 =B60*C60 1829.61 0.49718 =B61*C61 1193.22 Total Present Value Cashflows 14651.01 Less : Initial Investment - 15000 Net Present Value (NPV) -348.99 63 65 IRR = ra+NPVa(rb-ra)/ (NPVA-NPVb) 66 ra= Lower Discount rate = 10% 67 rb= Higer Discount rate = 15% 68 NPVa = NPV at lower discount Rate = 1,268.89 69 NPVb = NPV at Higer discount Rate = -348.99 70 71 IRR = 10% + 1,268.89*(15%-10%) (1,268.89-(-348.99)) 73 IRR = 10% + 63.45 74 1617.88 75 IRR = 10% + 3.9% = 13.9% 72

A B C Rate Project A Project B 0% 5,200.00 5,600.00 1% 4,735.30 2,035.30 2% 4,290.43 1,590.43 3% 3,864.29 1,164.29 4% 3,455.87 755.87 5% 3,064.19 364.19 6% 2,688.36 (11.64) 7% 2,327.56 (372.44) 8% 1,980.98 (719.02) 9% 1,647.91 (1,052.09) 10% 1,327.64 (1,372.36) 11% 1,019.53 (1,680.47) 12% 722.98 (1,977.02) 13% 437.42 (2,262.58) 14% 162.30 (2,537.70) 15% (102.87) (2,802.87) 16% (358.58) (3,058.58) 17% (605.27) (3,305.27) 18% (843.36) (3,543.36) 19% (1,073.24) (3,773.24) 20% (1,295.31) (3,995.31)

NPV Profile Poject A and B 12,000.00 10,000.00 8,000.00 6,000.00 4,000.00 2,000.00 (4,000.00) (6,000.00) (8,000.00) - Project A — Project B

Note : I do not know about the goal seek, hence not able to provide your requested data.