How can scenario variables and statistics be used in a simulation model?
An Excel sheet containing both the data and the results for use in this tutorial can be downloaded by clicking here.
Simulation models allow to obtain information, such as mean or median or confidence intervals, on variables that do not have an exact value, but for which we either know or assume a distribution. If some "result" variables depend on these "distributed" variables by the way of known or assumed formulae, then the "result" variables will also have a distribution. XLSTAT-Sim allows you to define the distributions, and then to obtain, through simulations, an empirical distribution of the input and output variables as well as the corresponding statistics.
Simulation models are used in many areas such as finance and insurance, medicine, oil and gas prospecting, accounting, or sales prediction.
Four elements are involved in the construction of a simulation model:
- Distributions are associated to random variables. XLSTAT gives a choice of more than 30 distributions to describe the uncertainty on the values that a variable can take. For example, you can choose a triangular distribution if you have a quantity for which you know it can vary between two bounds, but with a value that is more likely (a mode). At each iteration of the computation of the simulation model, a random draw is performed for each distribution that has been defined.
- Scenario variables allow to include in the simulation model a quantity that is fixed in the model, except during the tornado analysis where it can vary between two bounds.
- Result variables correspond to outputs of the model. They depend either directly or indirectly, through one or more Excel formulae, on the random variables to which distributions have been associated and, if available, on the scenario variables. The goal of computing the simulation model is to obtain the distribution of the result variables.
- Statistics allow to track a given statistic for a result variable. For example, we might want to monitor the standard deviation of a result variable.
A correct model should comprise at least one distribution and one result variable. Models can contain any number of these four elements. A model can be limited to a single Excel sheet or can use a whole Excel folder.
In this tutorial the same model than in the first tutorial is used. It is extended with a scenario variable and a statistic.
Our simulation model is based on sales and costs of a shop. The benefit is simply the difference between sales and costs in this simple case. Based on historical data for costs and sales that were analyzed with the tool "distribution fitting" we found out that the costs follow a normal distribution (mu=120, sigma=10) and the sales a normal distribution (mu=80, sigma=20) (see tutorial on distribution fitting for more details).
Additionally we suppose that 80 % of the costs and 30 % of the sales depend on a currency exchange rate. The exchange rate is set to 1 in the beginning. During the tornado analysis, several scenarios of an exchange rate from 0.8 to 1.2 are simulated.
Sales and costs are defined like in tutorial sim1. The formula of the benefit is more complicated taking into account the exchange rate: = (0.3 * E2 * B2 + 0.7 *B2) -( 0.8 * E2* B3+ 0.2 * B3).
Based on this model, the different model variables will be inserted into the model:
This model can be found in the sheet Model.
Choose the scenario variable cell E2 that has the value of the exchange rate as active cell.
Once XLSTAT is activated, select the XLSTAT/ XLSTAT-SIM /Define scenario variable command, or click on the corresponding button of the "XLSTAT-SIM" toolbar (see below).
The define scenario variable dialog box will appear. Then select the data on the Excel sheet. Choose the Excel cell with the name "exchange rate" as name. Select the lower limit as the cell with the value 0.8 and for the upper limit the cell with the value 1.2 to define the limits of the exchange rate. Choose the continuous data type, because the exchange rate can take every value between 0.8 and 1.2.
Once you have clicked on "OK", the corresponding function call of XLSTAT_SimSVar is inserted into the active cell.
Defining a statistic
Choose the result cell that is right from the cell containing "std. dev benefit" to define a statistic during the running simulation to track the standard deviation of the result variable benefit. This statistic will be calculated during every simulation iteration. Select the XLSTAT/ XLSTAT-SIM /Define statistic command, or click on the corresponding button of the "XLSTAT-SIM" toolbar.
The define statistic dialog box will appear. Then select the data on the Excel sheet. Choose the Excel cell with the name "std. dev benefit" as relative name in it. Select the option descriptive statistics and choose the standard deviation (n-1) from the list.
Once you have clicked on "OK", the corresponding function call of XLSTAT_SimStat is inserted into the active cell.
Running a simulation with scenario variables and statistics
The module XLSTAT-SIM also allows you to do the simulation in step by step mode. In this way you can look at the first model steps and verify if your model handles the dependencies correctly. In this example you are able to track the evolution of the defined statistic.
To initialize the simulation model and to delete information from an earlier step by step simulation, select the XLSTAT/ XLSTAT-SIM /Reinitialization of the simulation command, or click on the corresponding button of the "XLSTAT-SIM".
You will see the results of the first simulation iteration in the cell sales, costs and benefit. The scenario variable will only be used in the tornado mode and does not change its value at the moment. The statistic is not yet calculated, because there have to be at least two historical values to calculate the standard deviation.
To do further simulation steps, select the XLSTAT/ XLSTAT-SIM /Simulation – Do simulation step command, or click on the corresponding button of the "XLSTAT-SIM".
You see how the statistic of the standard deviation rapidly converges to a fix value.
To start the simulation run, select the XLSTAT/ XLSTAT-SIM /Simulation - Run command, or click on the corresponding button of the "XLSTAT-SIM".
The simulation - run dialog box will appear. Then select the data on the Excel sheet. Set the number of simulations to 1000.
In the "Options" tab, enter the parameters of the tornado and spider analysis. Select the standard cell value as default value. Choose 10 data points in the interval from -10% up to +10% of the value deviation:
In the "Outputs" tab, we activate the option simulation details.
The computations begin once you have clicked on "OK". You are asked to confirm the number of rows and columns (this message can be bypassed by un-selecting the "Ask for selections confirmation" in the XLSTAT options panel).
Interpreting the results of a simulation model with scenario variables and statistics
The first results are the same like in the first tutorial.
Tornado and spider analyses are not based on the iterations of the simulation but on a point by point analysis of all the input variables (random variables with distributions and scenario variables).
During the tornado analysis, for each result variable, each input random variable and each scenario variable are studied one by one. We make their value vary between two bounds and record the value of the result variable in order to know how each random and scenario variable impacts the result variables. For a random variable, the values explored can either be around the median or around the default cell value, with bounds defined by percentiles or deviation. For a scenario variable, the analysis is performed between two bounds specified when defining the variables. The number of points is an option that can be modified by the user before running the simulation model.
In the tornado analysis, there is now the scenario variable displayed. Its impact is on the same level of importance as the two other variables costs and sales. Further details are visible in the spider diagram. The exchange rate is clearly negatively correlated. A rising exchange rate shrinks the benefit.
Finally the simulation details are displayed. For each of the model variables costs, sales, benefit, and std. dev benefit the evolution in each iteration is shown. We can see clearly the evolution of the statistic and conclude that the statistic quickly converges towards 22 and then only oscillates around this value.
Click here for other tutorials.