Engineering Economics: Introduction to Spreadsheet Use

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.6  a , where the
equivalent annual amount   A  is unknown, as indicated by   A  = ?.  To  find   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.)

EXAMPLE 1.6

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

EXAMPLE 1.17

A Japan-based architectural fi rm has asked a United States–based software engineering group to infuse GPS sensing capability via satellite into monitoring software for high-rise structures in order to detect greater than expected horizontal movements. This software could be very beneficial as an advance warning of serious tremors in earthquake-prone areas in Japan and the United States. The inclusion of accurate GPS data is estimated to increase annual revenue over that for the current software system by $200,000 for each of the next 2 years, and by $300,000 for each of years 3 and 4. The planning horizon is only 4 years due to the rapid advances made internationally in building-monitoring software. Develop spreadsheets to answer the questions below.  

  (a)    Determine the total interest and total revenue after 4 years, using a compound rate of return of 8% per year. 
  (b)    Repeat  part  (  a ) if estimated revenue increases from $300,000 to $600,000 in years 3 and 4.  
  (c)    Repeat  part  (  a ) if infl ation is estimated to be 4% per year. This will decrease the   real rate  of return  from 8% to 3.85% per year (Chapter 14 shows why).

Solution by Spreadsheet

Refer to  Figure 1–14   a  to   d  for the solutions. All the spreadsheets contain the same information,  but some cell values are altered as required by the question. (Actually, all the questions can be  answered on one spreadsheet by changing the numbers. Separate spreadsheets are shown here  for explanation purposes only.)

The Excel functions are constructed with reference to the cells, not the values them- selves, so that sensitivity analysis can be performed without function changes. This ap- proach treats the value in a cell as a   global variable  for the spreadsheet. For example, the  8% rate in cell B2 will be referenced in all functions as B2, not 8%. Thus, a change in the  rate requires only one alteration in the cell B2 entry, not in every relation where 8% is used.  See Appendix A for additional information about using cell referencing and building  spreadsheet relations. 

(a)   Figure  1–14  a  shows the results, and  Figure 1–14  b  presents all spreadsheet relations for  estimated interest and revenue (yearly in columns C and E, cumulative in columns D  and F). As an illustration, for year 3 the interest   I3  and revenue plus interest   R3   are

The equivalent amount after 4 years is $1,109,022, which is comprised of $1,000,000 in  total revenue and $109,022 in interest compounded at 8% per year. The shaded cells in  Figure  1–14  a  and   b  indicate that the sum of the annual values and the last entry in the cu- mulative columns must be equal.  

(b)  To determine the effect of increasing estimated revenue for years 3 and 4 to $600,000,  use the same spreadsheet and change the entries in cells B8 and B9 as shown in Figure  1–14  c . Total interest increases 22%, or $24,000, from $109,222 to $133,222.

(c)   Figure  1–14  d  shows the effect of changing the original   i  value from 8% to an infl  ation- adjusted rate of 3.85% in cell B2 on the fi rst spreadsheet. [Remember to return to the  $300,000 revenue estimates for years 3 and 4 after working part (  b ).]  Infl  ation has now reduced total interest by 53% from $109,222 to $51,247, as shown in cell C10.

 Figure 1–14   Spreadsheet solutions with sensitivity analysis, Example 1.17  a  to   c .

Comment

Later we will learn how to utilize the NPV and FV Excel fi nancial functions to obtain the same  answers determined in  Figure 1–14 , where we developed each basic relation.  When you are working with an Excel spreadsheet, it is possible to display all of the entries  and functions on the screen as shown in  Figure 1–14   b  by simultaneously touching the <Ctrl> and < `> keys, which may be in the upper left of the keyboard on the key with <~>.