To see how Risk Engine works we will first create a model in Numbers. We will consider a simple example of a problem that might occur in the real world.
We are working in a developing village and have just been asked to construct a water tank. The village has recently been suffering from water shortages because the rainfall is intermittent. On some days there is a lot of rain and on others there is very little rain. The water tank needs to collect the water on the days when there is a lot of rain so that it can be used on the days when there is little rain.
We know that the village uses about 5 cubic metres of water per day. We also know that the rainfall distribution can be represented by a Log-Normal probability distribution where the mean of the natural logarithm of the daily rainfall is 1.3 cubic metre per day. The standard deviation of the rainfall rate is also 1 cubic metre per day. Water distribution trucks will be sent every 10 days and can fill up the tanks. Will a 15 cubic metre water tank be sufficient?
A solution to a problem like this could be derived mathematically using statistics, aggregating probability distributions. The problem in this instance is a practical one, that the tank can not hold more than 15 cubic metres of water. The method that we use to solve this problem has to reflect this issue.
A simple spreadsheet model combined with Monte-Carlo analysis is the ideal method to solve this problem. Out first problem then is to create the spreadsheet model for the water in the system. You can download my example spreadsheet here or you can create your own if you wish.
If we just assume that the rainfall on any given day is equal to the average rainfall of 3.7 cubic metres then our spreadsheet model predicts that there will be 1.7 cubic metres of water left at the end of the ten day cycle as shown in the following spreadsheet.
The 15 cubic metre tank looks good on paper but we still have some concerns. In this example there is not much water left at the end of the cycle and what happens if the rainfall is a little less than we expected? To look at this problem let us see if Monte-Carlo analysis can help.
On starting Risk Engine you will be presented with two simple tables, one for the input cells for the model and one for the
output cells.
We will start by editing our input cells. The key parameter that we want to change is the daily rainfall. If you have downloaded my example model click on cell D2, the rainfall amount for Day 1. Then, in Risk Engine, click on the Add cell button for the Model input sheets. A reference to cell D2 should be added to the first row in the Model input cells table like this:
Repeat this process with the rainfall in the other days. We can then define the probability distribution of the rainfall for each day. The type of distribution is selected in the distribution column. Columns A and B are for the parameters that define the probability distribution, which vary depending on which probability distribution is being adopted. Check the Numbers documentation for what values the first and second parameter of the distribution actually define. In this instance A will be 1.3 (the log of the mean daily rainfall) and parameter B will be the standard deviation of 1.0. The input model is now complete and should look like:
At this point we can define our data model by clicking on the Create model button and a new sheet will be created in the Numbers spreadsheet with the input data model.
The process for defining the cells which will be monitored during the Monte Carlo analysis is similar to the process for defining the input cells except in this case the output table and the associated Add cell button will be used. In this instance I've chosen to monitor the minimum daily rainfall, the average daily rainfall and the minimum amount of water left in the tank at the end of the day. Obviously other parameters could be selected such as the number of days when no water is left in the tank, it all depends on what your anticipated outcomes are. The final parameter to be defined is the number of cycles for the Monte-Carlo analysis. In this instance I will run 100 cycles so I enter 100 in the cycles box. The Risk Engine window now looks like:
Clicking on the Run Model will run the Monte-Carlo analysis.
A table will open once the analysis has completed giving the results. To view the data in Numbers or Excel click on the Export button in the toolbar and a table will be automatically generated in the chosen spreadsheet application.
The results can be found on the new sheet titled Monte Carlo outputs in the table Monte Carlo output table. Each column is the values from one of the monitored cells and each row of the table represents one cycle of the model. You can now process the data as you see fit, however in my case I have chosen a histogram giving the frequency for different ranges of minimum water quantity. Risk Engine has a built in histogram viewer that you can use to view histograms like this directly if you wish. This can be accessed by choosing Output and then Histogram from the menu bar:
About 25% of the time there is no water left in the tank at some point during the 10 day cycle.This seems far too often and so my recommendation would be that the tank size has to increase and we could do further analysis to try and find out what size the tank actually needs to be. We can also determine correlations using scatter plots. By plotting the average rainfall against the minimum volume of water left in the tank we can see that even with a high average daily rainfall, there is still a good chance of running out of water. You can generate scatter plots like this directly within Risk Engine by choosing Output and then Scatter plot from the menu bar:
