## Introducing Uncertainty in a Model

To turn the spreadsheet model on the previous page into a **risk analysis model**, we need to replace the fixed average 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 generating a random number -- say 1, 2 or 3 -- with equal probability. We could then associate the number 1 with the "Hot Market" state, the number 2 with the "OK Market" state, and the number 3 with the "Slow Market" state. We can generate these numbers easily in **Risk Solver** using an **integer uniform **probability distribution. We'll then base the Sales Volume and Selling Price on the value of this uncertain variable.

With **cell F4 **selected, we click the **Distributions** button on the Risk Solver Ribbon and then select **IntUniform** from the **Discrete** distributions gallery (as shown below).

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 as shown below to read: lower 1 and upper 3. This means that on each trial, Risk Solver will randomly draw a number of 1, 2 or 3 from this distribution. Again, these numbers will represent the three different assumed market states that can occur in our problem.

When we click the **Save** icon in the dialog toolbar, the formula **=PsiIntUniform(1,3)** is written to cell F4. F4 is now an **uncertain variable**. (Note that you could also type the formula **=PsiIntUniform(1,3)** in cell F4.) If we now repeatedly press function key [F9] to recalculate the spreadsheet several times, a different value -- either 1, 2 or 3 -- appears in cell F4 with each recalculation.

Next, we will use the values of 1, 2 or 3 in cell F4 to display the appropriate Sales Volume and Selling Price values in cells F5 and F6, respectively, associated with each market state . With cell F5 selected, we enter the formula:

Formula for cell F5 (Sales Volume): =CHOOSE(F4,B4,B5,B6)

This will cause F4 to return 100,000, 75,000, or 50,000, depending on the value in F4 (which will be 1, 2 or 3). Next, with cell F6 selected, we enter the formula:

Formula for cell F6 (Selling Price): =CHOOSE(F4,C4,C5,C6)

This will cause F6 to return $8, $10 or $11, depending on the value in F4 (again, 1, 2, or 3). Notice that the values returned by F5 and F6 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.