Business Risk and Simulation Modelling in Practice: Using Excel, VBA and @RISK

CHAPTER 10 Creating Samples from Distributions

This chapter describes the calculations required to create random samples from distributions. This is fundamental to modellers who wish to use the Excel/VBA approach, but relevant only as a background for those using @RISK (who may choose to skim or skip this chapter). In traditional statistics (and in Excel), distribution functions return a measure of relative or cumulative probability at a particular value of the variable (rather than a random sample), whereas in @RISK, distribution functions directly return samples (rather than probability information).

As described in Chapter 8 (see Figure 8.8), random samples can be created explicitly by inversion of the cumulative distribution function, involving two steps:

• The creation of a random sample from a (standard) uniform continuous distribution, i.e. of a random value between zero and one (0% and 100%). In Excel, this can be done using the RAND() function.
• The calculation of the associated percentile of the distribution.

Recall from Chapter 9 that a cumulative distribution is a function, F, that evaluates the cumulated probability, P, up to the point x:

where Par1, Par2… are the parameters of the distribution (e.g. μ, σ, α, β, λ, Min, ML, Max).

The inversion process involves finding, for any value of P, the corresponding x-value. Although one may conceive of this as requiring ...