Microsoft® Excel Spreadsheet Exercise #7

How to use this section: Each exercise requires the development of an Excel spreadsheet. The exercises are keyed to sections in the text Engineering Economy, 5th edition, by Blank and Tarquin. Appendix A of the text is a complete primer for using Excel and the financial functions pertinent to engineering economic analysis. Sample problems are included in this appendix for setting up each function.

The spreadsheet exercises presented here are especially well suited to an engineering economy course with laboratory sessions or activities that help a student become more adept with spreadsheet-based solutions.

Spreadsheet Exercise #7

Project Evaluation: Internal Rate of Return and Benefit/Cost Ratio Methods

Exercise Objective: This exercise will expand upon project evaluation methods you used in the previous exercise by including the IRR and B/C ratio methods of analysis.

Blank and Tarquin Text Reference: Chapters 7 and 8 for IRR; Chapter 9 for B/C.

Problem Statement: Recall from the previous Exercise #6 that the ABCD company is faced with 4 investment projects worth specific conditions.

Conditions:

• Proposals A and C are mutually exclusive
• Proposal D is contingent on Proposal B being selected
• The firm has $1,200,000 available for investment
• The study period or planning horizon is 10 years
• MARR = 18% per year

Part 1: Rate of Return Evaluation
On a separate worksheet, create a net cash flow table for all feasible alternatives. (If you worked Exercise #6 above, you may be able to utilize the set of feasible alternatives developed there.)

Add 3 additional rows at the bottom of the table and perform the following computations:

1. Use the Excel IRR financial function to compute the rate(s) of return for each alternative. Consider multiple rates if they are present, and select the most practical one for your evaluation. (Sections 7.2, 7.4)
2. Compute the PW (also called the NPV) of each alternative net cash flow at i = 0%
3. Compute the PW of each alternative net cash flow at i = ∞

Part 2: IRR Method of Analysis

1. Perform an incremental rate of return analysis for the feasible alternatives. Clearly show each set of comparisons. Which alternative is the best financially? (Sections 8.4 and 8.5)
2. Over what ranges of rate of return are the alternatives financially viable?
3. Generate a PW vs. i graph for each incremental comparison.

Part 3: Benefit/Cost Ratio Method of Analysis
Assess the set of alternatives using the incremental benefit/cost ratio. (Section 9.4) Rank order the alternatives by increasing PW of costs. Which alternative do you select using B/C analysis? Use MARR = 18% per year.