Crystal Ball offers a wide variety of distributions, but there are an enormous number available, and we may want to make use of one that Crystal Ball does not offer. The section on creating distributions illustrates the four methods we use at Epix Analytics to generate your own distribution.
One way we can generate our own distribution is if one can determine an equation for x = G(F(x)).
The process for generating values from one's own distribution is as follows:
1. determine the probability density function f(x);
2. determine the distribution function F(x) from f(x) or determine F(x) directly;
3. determine the generating function G(F(x)) from F(x).
Example:
Let's say you wish to construct a distribution that followed the shape of a sine curve from 0 to a, where a is an input to the distribution. This distribution shape is shown below:
The probability density function f(x) is given by:
f(x)= b ~ sin (\frac{\pi x}{a} ) |
where b is to be determined such that the area under the curve equals one.
The distribution function F(x) is then:
F(x) =\int\limits_0^x b * sin (\pi x / a)dx |
=\Big[(ab / \pi) \left\{ -cos(\pi x / a) \right\} \Big]_0^x\quad =(ab / \pi) \left\{ 1-cos(\pi x / a)\right\} |
For the area under the curve to equal one, b must be determined such that F(a) = 1, i.e.:
F(a) = (ab / \pi) cos(\pi) = 2ab / \pi =1 |
Therefore,
b = \pi / 2a |
and F(x) becomes:
F(x) = \frac{1}{2} (1-cos(\pi x / a)) |
We now need to find the generating function G(F(x)):
G(F(x)) = x
So, rearranging the equation above for x:
x = (a / \pi) cos^{-1}(1-2.F(x)) = G(F(x)) |
Now, to generate this distribution, we put a Uniform(0,1) distribution in cell A1 (say), the value for a in cell B1 (say) and, in the cell that generates values of x, we write:
= B1/PI() * ACOS (1-2*A1)
If we use Latin Hypercube Sampling to generate the values from the Uniform(0,1) distribution, we will generate a smoothly distributed set of x-values that will replicate the desired distribution.