Engineering Economic Analysis

262 INCREMENTAL ANALYSIS

feasible situations. Ironically,the most difficultmethod-rate of return analysis-is the one

most frequently used by engineers in industry!

Sensitivity and Breakeven Analysis

An incremental analysis of two alternativesis easily done with the RATEor IRR functions

when the lives of the alternatives are the same. The problem is more difficult, however,

when the lives are different.As discussed in Chapters 5 and 6, when comparing alternatives

having lives of different lengths, the usual approach is to assume that the alternatives are

repeated until the least common multiple of their lives.This can be done with a spreadsheet,

but Excel supports an easier approach.

Excel has a tool called GOAL SEEK that identifies a formula cell, a target value, and a

variable cell. This tool causes the variable cell to be changed automatically until the formula

cell equals the target value. To find an IRR for an incremental analysis, the formula cell

can be the difference between two equivalent annual worths with a target value of O.Then

if the variable cell is the interest rate, GOAL SEEK will find the IRR.

In Excel this tool is accessed by selecting T(ools) on the main toolbar or menu and

G(oal seek) on the submenu.As shown in Example 8-10, the variable cell (with the interest

rate) must somehow affect the formula cell (differencein equivalentuniform annual worths

or costs (EUAWs or EUACs», although it need not appear directly in the formula cell. IiI

Figure 8-12a and 8-12b the interest rate (cell AI) appears in the EUAC formulas (cells D3

and D4), but not in the formula cell (cell D5).

Two different asphalt mixes can be used on a highway. The good mix will last 6 years, and it will

cost $600,000 to buy and lay down. The better mix will last 10 years, and it will cost $800,000 to

buy and lay down. Find the incremental IRR for using the more expensive mix.

SOLUTION

This example would be difficult to solve without the GOAL SEEK tool. The least common

multiple of 6 and 10 is 30 years, which is the comparison period. With the GOAL SEEK tool

a very simple spreadsheet does the job. In Figure 8-12a the spreadsheeUs shown before GOAL

SEEK. Figure 8-12b shows the result after the goal (D5) has been set .0 and A 1 selected as the

variable cell to change.

- --- ~ ~

FIGURE 8-12a Spreadsheetbefore,GOAI--SEp:rC..

-.. -----

T

--

. -. -. ..
A B C D E F
;...---,.,'-...,.".,.-",.."..,.,-",-.
1 8.00% Interest rate
2 Alternative Cost Life EUAC ..

3 Good (^6000006) 129,789 ,:: -PMT($A$I,C3,B3)
4 Better! 800000 " (^10) ..119,2 -.......:I'::S'-PMT($A$1C4,B4)'
5 difference=. to,5§ .,0=;D3 -D4