Microsoft® Excel Spreadsheet Exercise #11

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 #11

After-Tax Economic Analysis

Exercise Objective: The objective of this exercise is to apply economic analysis for modeling cash flow streams after taxes. This exercise will build upon previous exercises related to modeling cash flow streams and depreciation.

Blank and Tarquin Text Reference: Chapters 16 and 17.

Problem Statement: The Acme Widget Company is considering the purchase of a new machine for its manufacturing process. The production engineer must determine the most economical method for acquiring this machine. There are four financing proposals.

1. Purchase the machine outright with retained earnings.
2. Initial investment is borrowed at 8% per year with repayment of simple interest on the original loan principal at the end of each year, and repayment of entire principal at the end of 6 years.
3. Initial investment is borrowed at 8% per year compounded and repaid with 6 equal annual payments.
4. Lease the machine for $2000 per year for 8 years, plus a $3000 initial down payment.

The costs and interest rates for these four alternatives are:

Create a spreadsheet model of the cash flow after taxes (CFAT) for each of the four alternatives. Apply the present worth method of analysis to determine the most economically viable option.