In this project, you will be estimating the Net Present Value (NPV), Crossover Rate, and Internal Rate of Return (IRR) of two firms. Note that your final output should be formatted as though you were submitting the information to your boss. Column headings should be aligned/formatted for easy reading. The number of decimal places should be set to an appropriate level. Make sure to show percentages as %s, use the comma and $ formatting options, widen your columns, and spell check.

Unprofessional formatting will result in points being deducted. A firm is considering taking Project A or Project B. Project A will cost $100,000 initially and will generate the following cash flows: Year 1: $20,000; Year 2: $30,000; Year 3: $10,000; Year 4: $60,000; Year 5: $10,000 Project B will cost $50,000 initially and will generate the following cash flows: Year 1: $10,000; Year 2: $20,000; Year 3: $5,000; Year 4: $30,000; Year 5: $10,000

Step 1: In an excel sheet, list all the cash flows for Project A and Project B, and calculate the cash flow difference of Project A and Project B in each year. Fill in the numbers in Table 1.

Step 2: Calculate the NPV of Projects A and B, and NPV differences at different WACC levels (3%, 6%, 9%, 12%, 15%, and 18%) using Table 2 in the Template. Please use excel function=NPV(Discount rate, CF1,CF2,CF3,CF4,CF5)+CF0 to calculate NPV. Note CF0 is the initial cost, so it should be negative.

Step 3: Select the highlighted cells in Table 2, click Insert, and click Scatter Button. Choose the third scatter chart sample. See below picture. Change Chart Title to Projects NPV at Different WACC Level. Step 4: Using the Chart generated from Step 3, click the chart and click Axis Title, change Y Axis Title to Net Present Value and change X Axis Title to WACC. See below picture. Step 5: Based on Table 2 and the chart from Step 3 and

Step 4, estimate the range of IRRs for both Project A and Project B. Are your estimated IRRs for Projects A and B in 3% to 6%, 6% to 9%, 9% to 12%, 12% to 15%, or 15% to 18% range? Provide your IRR range estimates and explanations for Project A and B in Table 3. Note: IRR is the implied rate of return that makes NPV=0. Step 6: Based on Table 2 and the chart from Step 3 and Step 4, estimate the range of Crossover Rate. Is your estimated Crossover Rate in 3% to 6%, 6% to 9%, 9% to 12%, 12% to 15%, or 15% to 18% range? Provide your crossover range estimates and explanations in Table 3. Note: Crossover Rate is the implied rate of return that makes NPV(A)=NPV(B).

Step 7: Confirm your answers in Step 5 and Step 6 by calculating the IRRs of Project A and Project B, and Crossover Rate. Write your answers in Table 4. Are your answers consistent with your guess in Table 3? Use IRR formula =IRR(CF0,CF1,CF2,CF3,CF4,CF5) Project As (Bs) IRR can be calculated by cash flows generated by Project A (B). Crossover Rate can be calculated by cash flows differences between Projects A and B.

Template: Table 1: CF0 CF1 CF2 CF3 CF4 CF5 Project A Project B Project B- Project A Table 2: WACC 3% 6% 9% 12% 15% 18% NPV(A) NPV(B) NPV(B)- NPV(A) Table 3: Estimated Range Why estimated rate is in this range? Project A IRR Project B IRR Crossover Rate Table 4: IRR/Crossover Rate Consistent with your estimates in Table 3