This is a type of a problem that one can come across when trying to sum a number of random variables that has to come to a pre-determined limit.
Let's take a look at the following example: A farmhouse supplies sheep to the slaughterhouse each week. Knowing the distribution of the amount of de-boned meat in a sheep, the farmhouse needs to calculate the number of sheep it needs to supply in order to make the total amount of de-boned meat equal X kilograms. Let's assume that the de-boned meat yield from a sheep is lognormally distributed with mean m and standard deviation s.
So, we have:
a) Amount of de-boned meat in a sheep:
| m_i = Lognormal(\mu,\sigma) |
b) Amount of de-boned meat to supply = X
One way of solving the problem is shown in the model: Sheep in 10 Tons
The links to the Sheep in 10 Tons software specific models are provided here:
A random number for the weight of each sheep is generated in column C (C13:C212), and the corresponding cell in column D is returning the running total if it is less than cell C11 (kilograms needed), and zero if it is more than cell C11. Column E checks the corresponding cell in column D and returns the current sheep number if the running total is more than 0, and zero otherwise. The output cell is located in the cell G9, and is just returning the maximum value from the column E, thus giving a distribution of the number of sheep.
A random number for the weight of each sheep is generated in column C (C14:C213), and the corresponding cell in column D is returning the running total if it is less than cell C11 (kilograms needed), and zero if it is more than cell C11. Column E checks the corresponding cell in column D and returns the current sheep number if the running total is more than 0, and zero otherwise. The output cell is located in the cell G9, and is just returning the maximum value from the column E, thus giving a distribution of the number of sheep.
The next example model is an extension to the previous one: Sheep with Confidence. In this example the farmhouse needs to be 95% confident that the number of sheep it supplies to the slaughterhouse is optimal to give 10 tons of de-boned meat.
The links to the Sheep with Confidence software specific models are provided here:
Column E (E13 - E212) uses Crystal Ball Percentile function (called CB.GetForePercentFN(x,perc), which takes 2 parameters: a) reference to a cell with a random variable, b) required confidence level. At the end of the simulation, CB.GetForePercentFN(x,perc) will return the value that corresponds to the required level of confidence. Column F (F13 - F212) returns the number of sheep when the corresponding cell in column E reaches 10000, and blank cell otherwise.
Column E (E14 - E213) uses @RISK's RiskPercentile function, which takes 2 parameters: a) reference to a cell with a random variable , b) required confidence level. At the end of the simulation, RiskPercentile will return the value that corresponds to the required level of confidence. Column F (F14 - F213) returns the number of sheep when the corresponding cell in column E reaches 10000, and blank cell otherwise.
These types of problems can also be solved using the Central Limit Theorem when the number of random variables is large, or the random variables being added are approximately Normally distributed. The model People in a Lift calculates directly (rather than simulate) the probability that a certain number of random people entering a lift will exceed the lift maximum load, under the (rather arguable) assumption that people's mass is normally distributed.
The links to the People in a Lift software specific models are provided here:
