5. Two public infrastructure projects have the economic profiles that follow:
 Option A Option B
  Operating and   Operating and  
 Capital Maintenance   Capital Maintenance 
Year Cost ($) Cost ($) Benefits ($) Cost ($) Cost ($) Benefits ($)
 1 2,000,000 0 0 2,500,000 0 0
 2 1,000,000 10,000 0 500,000 50,000 750,000
 3 500,000 70,000 120,000  100,000 750,000
 4  90,000 600,000  100,000 750,000
 5  90,000 800,000  100,000 750,000
 6  90,000 800,000  100,000 750,000
 7  90,000 800,000  100,000 750,000
 8  90,000 800,000  100,000 750,000
 9  100,000 800,000  100,000 750,000
 10  100,000 500,000  100,000 300,000
 Use these data to compute for each (a) the NPV at discount rates of 10 and 
5 percent, (b) the BCR at the same rates, and (c) the internal rate of return for 
each. Describe the facts about the projects that would dictate which criterion is 
appropriate, and indicate which project is preferable under each circumstance.
 
Do it in excel, be correct

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.

Two public infrastructure projects have the economic profiles that follow:
 Option A Option B
  Operating and   Operating and  
 Capital Maintenance   Capital Maintenance 
Year Cost ($) Cost ($) Benefits ($) Cost ($) Cost ($) Benefits ($)
 1 2,000,000 0 0 2,500,000 0 0
 2 1,000,000 10,000 0 500,000 50,000 750,000
 3 500,000 70,000 120,000  100,000 750,000
 4  90,000 600,000  100,000 750,000
 5  90,000 800,000  100,000 750,000
 6  90,000 800,000  100,000 750,000
 7  90,000 800,000  100,000 750,000
 8  90,000 800,000  100,000 750,000
 9  100,000 800,000  100,000 750,000
 10  100,000 500,000  100,000 300,000
 Use these data to compute for each (a) the NPV at discount rates of 10 and 5 percent, (b) the BCR at the same rates, and (c) the internal rate of return for each. Describe the facts about the projects that would dictate which criterion is appropriate, and indicate which project is preferable under each circumstance.

Analyze two public infrastructure projects with detailed cash flow data over a 10-year period. Learn how to calculate Net Present Value (NPV), Benefit-Cost Ratio (BCR), and Internal Rate of Return (IRR) using Excel formulas at discount rates of 10% and 5%, helping to determine which project is more economically viable.

Math Problem Statement

Solution

Ask a new question for Free

By Image

Math Problem Analysis

Mathematical Concepts

Formulas

Theorems

Suitable Grade Level

Related Recommendation