Engineering Economics Blog

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 benefi  cial 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 r eturn 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 themselves, so that sensitivity analysis can be performed without function changes. This approach 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 detailed relations shown in  Figure 1–14   b  calculate these values in cells C8 and E8.


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 cumulative 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  ationadjusted 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 .