Engineering Economics Blog

The functions on a computer spreadsheet can greatly reduce the amount of hand work for equivalency computations involving   compound interest  and the terms   P, F, A , i, and n . The use of a calculator to solve most simple problems is preferred by many students and professors as described in Appendix D. However, as cash fl  ow series become more complex, the spreadsheet offers a good alternative.  Microsoft Excel   is used throughout this book because it is readily available and easy to use. Appendix A is a primer on using spreadsheets and Excel. The functions used in engineering economy are described there in detail, with explanations of all the parameters. Appendix A also includes a section on spreadsheet layout that is useful when the economic analysis is presented to someone else - a coworker, a boss, or a professor.
 
A total of seven Excel functions can perform most of the fundamental engineering economy calculations. The functions are great supplemental tools, but they do not replace the understanding of engineering economy relations, assumptions, and techniques. Using the symbols   P, F, A, i , and n defi  ned in the previous section, the functions most used in engineering economic analysis are formulated as follows.



If some of the parameters don’t apply to a particular problem, they can be omitted and zero is assumed. For readability, spaces can be inserted between parameters within parentheses. If the parameter omitted is an interior one, the comma must be entered. The last two functions require that a series of numbers be entered into contiguous spreadsheet cells, but the fi  rst fi  ve can be used with no supporting data. In all cases, the function must be preceded by an equals sign ( ) in the cell where the answer is to be displayed.
 
To understand how the spreadsheet functions work, look back at Example 1.6a , where the equivalent annual amount   A  is unknown, as indicated by   A =?. (In Chapter 2, we learn how engineering economy factors calculate   A , given   P, i, and n .)  To  fi  nd  A using a spreadsheet function, simply enter the PMT function PMT(5%,5,5000).  Figure 1–13  is a screen image of a spreadsheet with the PMT function entered into cell B4. The answer ($1154.87) is dis-
played. The answer may appear in red and in parentheses, or with a minus sign on your screen
to indicate a negative amount from the perspective of a reduction in the account balance. The
right side of  Figure 1–13  presents the solution to Example 1.6  b.  The future value   F  is determined by using the FV function. The FV function appears in the formula bar; and many examples throughout this text will include cell tags, as shown here, to indicate the format of important entries. 
 
The following example demonstrates the use of a spreadsheet to develop relations (not built-in functions) to calculate interest and cash fl ows. Once set up, the spreadsheet can be used to perform sensitivity analysis for estimates that are subject to change. We will illustrate the use of spreadsheets throughout the chapters. (  Note:  The spreadsheet examples may be omitted, if spreadsheets are not used in the course. A solution by hand is included in virtually all examples.)

Figure 1–13 Use of spreadsheet functions PMT and FV, Example 1.6.

EXAMPLE 1.17 - Introduction to Spreadsheet Use