Spreadsheet Exercise #6

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.

Project Evaluation: Present Worth, Annual Worth,
and Future Worth Methods

Exercise Objective: This exercise introduces project evaluation for sets of projects using the commonly accepted methods of evaluating and comparing more than one alternative.

Blank and Tarquin Text Reference: Chapters 5 and 6.

Note to instructors and students: To correctly work this exercise, it is necessary to understand the difference between mutually exclusive alternatives and independent projects. It is also necessary to know how to correctly combine independent projects to formulate mutually exclusive alternatives subject to a limited initial investment budget. The limited budget topic is covered in the first section of Chapter 12, especially Figure 12-1. It is suggested that this material be briefly discussed in class and/or recommended as preliminary reading material prior to working this exercise.

Problem Statement: The ABCD company has four investment projects with the following conditions:

• Proposals A and C are mutually exclusive (that is, only one, not both, of the projects may be selected for investment)
• 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: Developing Feasible Alternatives

On a worksheet, enumerate all possible combinations of projects. Indicate which are financially feasible and which are infeasible. For those that are infeasible projects considering the investment limit and other conditions, provide a brief explanation.

Part 2: PW, AW, and FW Analysis

1. Present worth (Section 5.2)
2. Future worth (Section 5.4)
3. Annual worth using two different relations (Section 6.2)
   Which alternative is best? Why?

Part 3: Evaluating Alternatives with Unequal Lives

Item 1 - On a new worksheet set up a net cash flow table for the two alternatives.

Item 2 - Single life cycle analysis: Compute the PW and AW for the two projects over one life cycle.

(a) Which alternative is best?
(b) Explain the apparent contradiction in your results?

Item 3 - Compare the alternatives by the least common multiple (LCM) approach. Assume identical replication of projects cash flows for each reinvestment cycle. Use the present worth method of analysis (Section 5.3)