### Engineering Economics: Introduction to Spreadsheet Use

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 deﬁ 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 ﬁ rst ﬁ 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 ﬂ 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 ﬁ 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 inﬂ 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 inﬂ ation- adjusted rate of 3.85% in cell B2 on the ﬁ rst spreadsheet. [Remember to return to the $300,000 revenue estimates for years 3 and 4 after working part ( b ).] Inﬂ 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 . |

Later we will learn how to utilize the NPV and FV Excel ﬁ 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 <~>.

## 0 comments:

## Post a Comment