Easily Create Conditional Distributions with Error Filtering
Risk Solver carefully distinguishes between Monte Carlo trials that yield normal results, and trials that yield Excel error values for one or more uncertain functions. You can use this "error filtering" to create conditional distributions -- frequency distributions, charts and statistics for outcomes that satisfy a condition you specify. Your condition can involve anything that can be tested with an Excel formula -- arithmetic and string comparisons, logical operators like AND or OR, and much more. This capability -- not found in most other risk analysis products for Excel -- opens up a whole realm of new possibilities for risk analysis.
Using Error Filtering
During the simulation process, individual Monte Carlo trials may yield Excel error values for one or more uncertain functions. For example, you might have a formula for an uncertain function C1 such as =B1+100/A1, where A1 is an uncertain variable. If the sample drawn for A1 on a given trial is zero, C1 will have the Excel error value #DIV/0!.
Risk Solver filters out 'error trials' from your simulation results when computing statistics and displaying charts. An 'error trial' is a Monte Carlo trial where any uncertain function returns an Excel error value. Without this filtering, you would have error values for statistics and erroneous charts for some of your functions, or -- if error values were filtered only on a function-by-function basis -- you'd have statistics and charts for different uncertain functions based on different numbers of observations (since not all functions necessarily yield error values on the same trials). The PsiCount() function, which you can drag and drop from the Statistics dropdown gallery -- gives you the number of total trials, normal trials or error trials.
Creating Conditional Distributions
In risk analysis, it is often valuable to be able to estimate the conditional probability of an outcome. For example, you might create a model to estimate the effects on profit and loss of a pricing move, with uncertainties for customer response to price increases or decreases, and other uncertainties about countermoves by competitors. You might want to know 'what are our chances of losing money in scenarios where Competitor B cuts their price to be below ours?'
You can take advantage of Risk Solver's filtering of error trials to compute results that include only the trials that satisfy a test or logical condition that you specify. You simply create an uncertain function cell that tests for your condition, and returns 0 (or some other useful result) when your condition is satisfied, but returns the Excel function NA() when your condition is not satisfied. Any logical condition that you can express in Excel can be used.
For example, if on each trial, cell A10 holds your price, and cell B10 holds the price response of Competitor B, you could create a cell containing:
=IF (B10<A10, 0, NA()) + PsiOutput()
Elsewhere in your model, you'd calculate various quantities of interest, including your Net Profit. Since the NA() function returns the error value #N/A, and Risk Solver filters out trials where any uncertain function returns an error value when it computes statistics and displays charts, your simulation results for Net Profit will include only those trials where Competitor B cut their price to be below yours.
Displaying Normal and Error Trials
If trials that return Excel error values are a "surprise," or if they are part of your model design, you may find it useful to examine the normal trials, the error trials, or both. To do this, click the Model button on the Risk Solver Ribbon to display the Model Outline window, and click to open the Trials portion of the window:
You can display All Trials, only Normal Trials, or only Error Trials. Within each group, you can click the buttons to display the Next or Previous, or First or Last trial. You can also click the Mean Value choice, which causes each PSI Distribution function to return its sample mean value. Each time you select a trial, all PSI Distribution functions return their sample values on that trial, the Excel worksheet is recalculated, and uncertain function cells will display their calculated values for that trial.