Monte Carlo Simulation Tutorial - Introducing Uncertainty Continued

So far, we've modified our spreadsheet model to introduce uncertainty for Sales Volume at cell B4 and Selling Price at B5.  Now we'll deal with Unit Cost.  We have not just three, but many possible values for this variable: It can be anywhere from $5.50 to $7.50, with a most likely cost of $6.50.  A crude but effective way to model this is to use a triangular distribution.  Risk Solver provides a function called PsiTriangular() for this distribution.

With cell B6 selected, from the Risk Solver Ribbon we click the Continuous button to display a dropdown gallery of continuous probability distributions.  (Unlike a discrete distribution, a sample drawn from a continuous distribution can be any numeric value, such as 5.8 or 6.01, in a range.)  We choose "Triangular" from the gallery.

Risk Solver displays the Uncertain Variable dialog with a chart of the triangular distribution -- initially with parameters min 0, likely 1 and max 3.  We want to edit these parameters -- but instead of entering fixed numbers, we'll use the range selector icon at the right of each field to select cells containing the parameters:  min E11 ($5.50), likely E12 ($6.50) and max E13 ($7.50).This means that on each trial, we'll draw a number between $5.50 and $7.50, where $6.50 is the most likely value to be drawn -- as shown in the chart of the triangular distribution below.  Hence $6.45 and $6.55 are more likely than $5.55 or $7.45 -- but any of these and other numbers has a chance of being drawn on each trial.  When we click the Save icon in the dialog toolbar, a formula =PsiTriangular(E11,E12,E13) is written to B6.  We now have a second uncertain variable in our model.

Next, we'll move on to define an Uncertain Function.  But we can do much more than shown here with the Uncertain Variable dialog:  The view above shows all of its panes open (click the image to see it full size).  You can browse different distributions, shift and truncate a distribution, see each distribution's Probability Density Function (PDF), Cumulative Density Function (CDF) or Reverse CDF, see statistics and percentiles for the distribution, automatically fit a distribution to user-supplied data, and customize the chart.To learn more about probability distributions and how to choose them, consult our Probability Distribution Introduction.

Next: Uncertain Functions and Statistics >

< Back to Monte Carlo Simulation Introduction