Monte Carlo 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 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 Platform using an integer uniform probability distribution. We'll then base the Sales Volume and Selling Price on this uncertain variable. With cell B9, currently an empty cell, selected, we click the Distributions button on the Risk Solver Ribbon and then select IntUniform from the Discrete distributions gallery. A sample drawn from a discrete distribution is always one of a set of discrete values, such as integer numbers.

Risk Solver Platform displays the Uncertain Variable dialog with a chart of the integer uniform distribution -- initially with parameters lower 0 and upper 10. We can edit these parameters in the Parameters section to the right of the chart, 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 edit this the chart will update to reflect the changes.

When we click the Save icon in the dialog toolbar (or just close the window), 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 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 Platform supports more versatile ways to specify correlation between uncertain variables, but this approach is easy to understand in this example.

Next: Introducing Uncertainty Continued >

< Risk Analysis Tutorials Overview Page