In many risk analysis situations one may be required to find the parameters for a distribution which result in the distribution most closely matching an initial given distribution. A frequent example is when modeling with decision trees. Often the branches associated with chance events of a decision tree are used to represent discrete points of an underlying process which is continuous. For example, one may be provided with the P10 and P90 point of a process which is normally distributed (and by implication also the P50, as the average of these), and one may wish to find the weights (or frequencies or probabilities) that should be associated with these points so that the resulting discrete (three point) distribution has the same standard deviation as the underlying normal distribution. Such procedures may be required for example in the context of decision making under uncertainty in the Oil and Gas industry (for the evaluation of exploration and production decisions), as well as in financial applications (such as value at risk calculations for a portfolio of assets where the range of each asset’s return is estimated using the P10, P90 estimated returns etc).
The above process can be achieved readily when using @RISK, and usually involves using the alternate parameter form of the distribution, the RiskTheo statistics functions and then Excel’s GoalSeek or Solver. In the above example involving the normal distribution, then procedure would be:
- Use the alternate parameter form of the normal distribution to create a normal distribution based of the given P10 and P90 points (calculate also the P50 as the average of these).
- Form a discrete distribution whose x-values are the P10, P50 and P90 values, and whose weights can be changed (i.e. as flexible cell references). In this example, there is only one degree of freedom (i.e. parameter to flex) i.e. the weight associated with the P10 point (say p) will be the same as that of the P90 point, and the P50 point will have a weight of 1-2p.
- Use the RiskTheoStdDev function to calculate the standard deviation of the resulting normal and discrete distributions.
- Use Excel’s GoalSeek (or Solver) to find the p which matches the standard deviations.
In the case of a normal distribution, it turns out that the weights are about 30-40-30. In the oil and gas industry, this has been come to be known as Swanson’s rule.
There are many other variations of this basic topic. For example, if we were not given the P10 and P90 points of the original distribution (but knew the distribution using its standard parameters), then one would not need the alternate parameter form of the distribution, but one would need to use the RiskTheoPercentile functions to find (for example) its P10, P50 and P90 points before forming the discrete distribution (in othere cases one may wish to approximate one continuous distribution with another, so this step would not be necessary at all). Similarly, when approximating a skewed distribution (such as a lognormal) with a discrete distribution, then there would be more than one degree of freedom. In this case, one may be required to use Excel’s Solver (rather than GoalSeek), and in theory a new matching would need to be done every time the parameters of the original lognormal distribution were changed.
Dr. Michael Rees
Director of Training and Consulting