To perform this analysis in Excel, here’s the step-by-step guide for calculating the Net Present Value (NPV), Benefit-Cost Ratio (BCR), and Internal Rate of Return (IRR) for both projects.

Step 1: Set Up the Excel Spreadsheet

1. Create Column Headers: Label the columns to represent each year from 1 to 10, then add rows for the "Capital Cost," "Operating and Maintenance Cost," "Benefits," and "Net Cash Flow" for both options.
2. Enter Data for Each Project: Input the given data for capital costs, maintenance costs, and benefits for each project in each respective row.

Step 2: Calculate Net Cash Flow

• Net Cash Flow Formula: Net Cash Flow = Benefits - (Capital Cost + Maintenance Cost).
• For each year, calculate the net cash flow for both projects using this formula in a new row.

Step 3: Calculate NPV for Both Projects

1. Select Discount Rate: Set up calculations for two discount rates, 10% and 5%.
2. NPV Formula in Excel: Use the formula =NPV(rate, net cash flow range) for each discount rate and project.
   • For example, if Option A’s net cash flows are in cells C10:C19, the formula for NPV at 10% would be =NPV(10%, C10:C19).
3. Apply for Each Option and Discount Rate.

Step 4: Calculate Benefit-Cost Ratio (BCR) for Both Projects

1. Discount Benefits and Costs Separately: Use the NPV formula to discount the streams of benefits and the streams of costs separately at each discount rate.
   • For benefits: =NPV(rate, benefits range).
   • For costs: =NPV(rate, costs range).
2. Calculate BCR: Divide the discounted benefits by the discounted costs for each discount rate and project.
   • BCR formula: $\text{BCR} = \frac{\text{NPV of Benefits}}{\text{NPV of Costs}}$.

Step 5: Calculate Internal Rate of Return (IRR) for Both Projects

• IRR Formula in Excel: Use =IRR(cash flow range) for each project's net cash flows.
  • For example, if Option A’s net cash flows are in cells C10:C19, the formula would be =IRR(C10:C19).

Step 6: Decision Criteria and Analysis

1. NPV Criterion: Choose the project with the higher NPV as it indicates greater net economic value.
2. BCR Criterion: If BCR > 1, the project is considered economically feasible. Choose the project with a higher BCR if both are feasible.
3. IRR Criterion: Select the project with a higher IRR if it exceeds the discount rate, as it suggests a more favorable return on investment.

Conclusion

• Project Selection: Depending on the analysis, choose the project that maximizes NPV, has a BCR > 1, and a higher IRR than the discount rate.

Let me know if you would like additional help setting up any specific formulas in Excel, or need more detailed instructions on any step.

---

Related Questions

1. What is the meaning of IRR, and how does it compare with the discount rate?
2. How do changes in the discount rate affect the NPV and BCR results?
3. Why is a BCR > 1 necessary for project feasibility?
4. What are the implications of choosing a project based on NPV versus IRR?
5. How would cash flow adjustments impact each project's financial feasibility?

Tip

Always double-check that all cash flows are correctly entered, as one error in the cash flow stream can impact NPV, BCR, and IRR calculations.