Monte Carlo Simulation Tutorial - Introducing Uncertainty

solver.com

Frontline Systems, Inc.

	*Developers of Your Spreadsheet's Solver*

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.

Discrete Distributions dropdown 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.

Uncertain Variable dialog - Integer Uniform 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 example models, full-text User Guides, and to download free 15-day trial versions of our software products whenever you're ready, you can register now.

User Type
Email Address
Name First Last
Company University
Phone

Trial version passwords are sent to the above email address: See Privacy Policy.

Our Premium Solver Platform works with existing Excel Solver models, solves much larger problems up to hundreds of times faster, and solves new kinds of problems via Evolutionary Solver. Solver Engines plug into the Premium Solver Platform.

Solver Platform SDK makes it easy to solve any type or size of optimization problem in your Visual Basic, VB.NET, C/C++, C#, Java, or MATLAB program. And it's easy to deploy your application with our flexible licensing for software vendors and corporate developers.