Monte Carlo Simulation Tutorial - Introducing Uncertainty

To turn the spreadsheet model on the previous page into a risk analysis model, we need to replace the fixed Sales Volume, Selling Price, and Unit Cost amounts with variable amounts that reflect their uncertainty.

Sales and Price

Since there are equal chances that the market will be will be Slow, OK, or Hot, we want to create an uncertain variable that selects among these three possibilities, by drawing a random number -- say 1, 2 or 3 -- with equal probability.  We can do this easily in Risk Solver using an integer uniform probability distribution.  We'll then base on Sales Volume and Selling Price on this uncertain variable. With cell B9 selected, we click the Discrete button on the Risk Solver Ribbon.  This displays a dropdown gallery of discrete probability distributions.  (A sample drawn from a discrete distribution is always one of a set of discrete values, such as integer numbers.)  We click to choose "IntUniform" from the gallery.

Risk Solver displays the Uncertain Variable dialog with a chart of the integer uniform distribution -- initially with parameters lower 0 and upper 10.  We edit these parameters to read lower 1 and upper 3.  This means that on each trial, we'll draw a number 1, 2 or 3 from this distribution.

When we click the Save icon in the dialog toolbar, a formula =PsiIntUniform(1,3) is written to B9.  B9 is now an uncertain variable.  If we now press F9 to recalculate the spreadsheet, a different value -- either 1, 2 or 3 -- appears each time in cell B9.Now, we need to select one of the three sales scenarios in formulas for Sales Volume and Selling Price.  With cell B4 selected, we enter the formula:

=CHOOSE(B9,E5,E6,E7)  for Sales Volume

This will cause B4 to return 100,000, 75,000, or 50,000, depending on the value in B9.  Next, with cell B5 selected, we enter the formula:

=CHOOSE(B9,F5,F6,F7)  for Selling Price

This will cause B5 to return $8, $10 or $11, depending on the value in B9.Notice that the values returned by B4 and B5 are related, or correlated:  Higher sales volume is accompanied by lower selling prices, and vice versa.  If we allowed scenarios with 100,000 units sold at $11 each, our model would be unrealistic.  Risk Solver supports more versatile ways to specify correlation between uncertain variables, but this approach is easy to understand in this example.

Next: Introducing Uncertainty Continued >

< Back to Monte Carlo Simulation Introduction

To Learn More: For instant access to our white papers, example models, full-text User Guides, and to download free 15-day trial versions of our software products whenever you're ready, register now with no obligation.

User type Please Select Commercial User Consultant Corp Developer Govt/Military Professor Student VAR/ISV Firm Email address Name First Last Company University Phone

Trial version passwords are sent to the above email address. Our Privacy Policy protects you.