Paper Authors

Abstract

NOTE: The first page of text has been automatically extracted and included below in lieu of an abstract

Session 2520

Economic Simulations for Risk Analysis John H. Ristroph University of Southwestern Louisiana

Abstract Simulations using spreadsheets provide a method to address a pivotal weakness in most engineering economics courses: Estimates of cash flows are almost never correct. Initial estimates can be in error by 25% or more, and errors in final estimates can exceed 10%. Minor economic analyses simply use best estimates on the premise that the mistakes will average out as design after design is implemented. On the other hand, risks associated with larger, more expensive projects are extremely important to any company and hence to careers.

Texts frequently show how to use spreadsheets containing single estimates of cash flows. This paper shows how to incorporate risk into such classroom examples quickly and easily by using Microsoft's Excel. It illustrates extending the simple example in Table 1 into the spread- sheet shown in Table 3 first by explaining how to generate random cash flows. Then it describes how to perform the simulation using a macro, compute statistics, and plot results, concluding with a discussion of classroom use. A copy of the spreadsheet can be obtained by emailing the author at ristroph@usl.edu. Cash Flow Model Table 1 shows a simple problem involving the internal rate of Table 1. Cash Flows return (IROR) of a single project, a common economic measure. A B The cash flows in cells B4 through B7 (B4:B7) have the IROR of Cash Flow Model 1 9.70% shown in B8. The entry in B8 is = IRR(B4:B7, 0.1), where 2 B4:B7 is the location of the cash flows and 0.1 is an initial estimate 3 Year Cash Flow of the IROR. The simulation logic can support a model of any 4 0 –10,000 degree of complexity, as long as it results in a single economic 5 1 4,000 measure placed on the first worksheet. 6 2 4,000 7 3 4,000 Now suppose that the estimates of cash flows are thought to be 8 IROR 9.70% accurate to within 10%. For example, consider year 0 where 10% of 10,000 is 1,000. Any value between -9,000 and -11,000 is equally likely to occur. Similarly, each cash flow for years 1 through 3 can independently vary between 3,600 and 4,400.

Excel supplies the function RAND() that randomly Table 2. Random Cash Flows generates numbers between 0 and 1, so the transformation B a + RAND()*(b-a) generates numbers on the interval (a, b). 3 Cash Flow Thus revising B4 through B8 so that they appear as in Table 4 = –9000 – RAND()*2000 2 results in the desired random cash flows. Pressing F9 5 = 3600 + RAND()*800 causes the spreadsheet to recalculate and generate a new set 6 = 3600 + RAND()*800 of random numbers. The first time F9 is pressed, 11.35% 7 = 3600 + RAND()*800 might appear in B6, whereas the second time it might be 8 = IRR(B4:B7, 0.1) 9.62%.

• Citation

• Format
  • APA
  • APA - LaTeX bibitem
  • MLA
  • MLA - LaTeX bibitem
  • Bibtex
  • EndNote - RIS

Ristroph, J. H. (1999, June), Economic Simulations For Risk Analysis Paper presented at 1999 Annual Conference, Charlotte, North Carolina. 10.18260/1-2--7608