1. Build a model in Excel.

2. Specify certain cells to contain random variables inputs, and other cells to be tracked as outputs.

3. Run a pre-specified number of trials and wait for the results.

4. Create and display statistics of the output cells.

In the mid-1990s, I began experimenting with a different paradigm using the built-in RAND() function in Excel. A new simulation was run each time the user changed the spreadsheet in any way. I called the interactive histograms that resulted "Blitzograms" [4], and for very small models they provided insights available no other way. As computers became faster, I began to apply interactive simulation to a few real-world applications. Building these models required copying literally thousands of formulas, but my consulting clients loved being able to interact with probability distributions on a gut level. I even developed a prototype system that ran a thousand Monte Carlo trials through an arbitrary spreadsheet model after each user input. The prototype revealed the true complexity of the project, and convinced me that my time would be better spent on that half-finished perpetual motion machine in my garage.

Now, in what may be the start of a trend, Frontline Systems has put its Risk Solver Engine (RSE) for Excel into public beta test at www.solver.com/rse. This software performs a new simulation whenever the user changes the spreadsheet, and does so extremely quickly, thanks to Frontline's proprietary PSI [5] technology, which already powers the "Extreme Speed" mode of Crystal Ball. According to Dan Fylstra, Frontline's CEO, "We evaluate all the Excel formulas in the model, for all simulation trials in parallel." I'm glad I didn't try this at home.

I did not have much time to test the software before we went to press, so I will describe my experience with a small model demonstrating a classic example of the flaw of averages from my tutorial on risk and uncertainty. This example shows what can happen if you make a capital investment in capacity based on average demand. If demand is less than this average, you won't make your numbers. And the kicker is that if demand exceeds the average, you won't have the capacity to serve the additional customers, so there is no upside (see deadly sin number 5 in [6]). The flaw of averages in this case dictates that average profit is less than the profit associated with average demand. The model is shown in Figure A and described in more detail in [7].

Figure A

It takes three short steps to detect the flaw of averages in this interactive environment:

1. Enter the formula =PsiNormal(2000000,500000) in cell B8 to indicate the distribution of demand.

2. Add PsiOutput() to the beginning of the profit formula in cell B16. This is flags the cell to be tracked during simulation. The formula would now read: = PsiOutput() + REVENUE-INVESTMENT

3. Enter the formula =PsiMean(B16) in B18.

At this point, a number of things happen over a short period of time. Actually 10,000 things in a fraction of a second to be exact! This is the parallel evaluation of Excel formulas described earlier. Once that is accomplished, PsiMean dutifully calculates the average profit over all 10,000 trials (see Figure B). And this all takes place essentially instantaneously on my 1.6 Gigahertz Thinkpad!

Figure B

If you have time on your hands, you may want to run 1 million iterations, but the two seconds required just isn't interactive enough for me. By simulation model standards, this example is trivially small. So I tried replacing the PsiNormal formula with the sum of 100 independent PsiNormals, and at 10,000 trials (1 million normals generated) it took a prohibitive three seconds. Then I returned the number of trials to the default of 1,000 and got back into the acceptable few tenths of a second range.

Excel of course already provides statistical functions for ranges of data, such as Average and Percentile. Think of interactive simulation as allowing you to apply statistical formulas to single cells, which, of course, now contain samples of probability distributions (see Figure C).

Figure C

Perhaps my favorite of these is PsiFrequency, which produces a histogram (well blitzogram, actually) of a selected output cell, relative to a set of bin values. I used this feature to create a doubly interactive model by linking a spinner to the investment level in the model described above. If you are not already using Excel's spinners, slide bars, etc., they are a great way to make a deterministic model interactive by controlling the numeric value of a cell. But in this version, every click of the spinner yielded another 10,000 Monte Carlo iterations.

I started with an investment level of $1 million to get the histogram in Figure D based on 10,000 trials.

Figure D

Then, click, I raised investment to $1.2 million and another simulation ran for a total of 20,000 trials (Figure E).

Figure E

Then click, click, click, click, as fast as I could go, for investments of $1.4, $1.6, $1.8 and $2 million. I had run 30,000, 40,000, 50,000, 60,000 total trials, with the resulting histograms displayed in Figure F. This quickly shows that an investment of $1.2 million yields a high average profit with minimal downside risk.

Figure F (Click here to view a larger version in a new window.)

RSE also supports many distributions beyond the Normal (see Figure G for a partial list). Of particular interest to me is PsiSlurp, which reads trials directly from a stochastic library, which is compatible with the coherent modeling approach to Probability Management.

Figure G

By this point, the discerning reader may have concluded, due to my enthusiasm, that I am not an unbiased observer. That is an underestimate. I can't wait to try this and other potential offerings of interactive simulation in applications with my clients and in my management science class next fall. I believe it will promote the growth of Probability Management, thereby increasing my consulting income, making me famous, and more attractive to members of the opposite sex. But that's just my opinion. Since the beta test is open to the public, you may wish to form your own.

But seriously folks, lots of good ideas take years to gain acceptance. For example, in spite of the obvious difference between $1 million in cash and $1 per year for a million years, it required decades for the idea of Net Present Value to catch on. First, we had to wait for the NPV key on the pocket calculator to arrive. Secondly, the CFOs had to come up with credible discount rates to use in the calculations. Interactive simulation is the NPV key for Probability Management. The question now is how long it will take for the CPOs to come up with credible distributions.

So is conventional simulation a thing of the past? Far from it. I believe the CPOs are going to generate their SLURPS the conventional way. The SLURPS will then be distributed to the end user, where interactive simulation will be the order of the day.

• Return to the main story: Probability Management — Part 2


References

1. www.palisade.com/
2. www.decisioneering.com/
3. www.AnalyCorp.com
4. Savage, Sam L., 2001, "Blitzograms: Interactive Histograms," INFORMS Transactions on Education, January 2001, http://ite.pubs.informs.org/Vol1No2/Savage/Savage.php
5. www.solver.com/rse
6. Savage, Scholtes & Zweidler, 2006, "Probability Management," OR/MS Today, February 2006, Vol. 33, No.1, www.lionhrtpub.com/orms/orms-2-06/frprobability.html
7. Savage, S.L., "Understanding Uncertainty Through Simulation, Section III," http://analycorp.com/uncertainty/

Table of Contents

OR/MS Today Home Page


OR/MS Today copyright © 2006 by the Institute for Operations Research and the Management Sciences. All rights reserved.


Lionheart Publishing, Inc.
506 Roswell Rd., Suite 220, Marietta, GA 30060 USA
Phone: 770-431-0867 | Fax: 770-432-6969
E-mail: lpi@lionhrtpub.com
URL: http://www.lionhrtpub.com


Web Site © Copyright 2006 by Lionheart Publishing, Inc. All rights reserved.