XLSTAT: Generating Many Distributions in a Simulation Model in Excel

 

Background

Simulation models estimate statistics like mean, median, or confidence intervals for variables with uncertain values defined by known or assumed distributions. Result variables, depending on these, also have distributions. Simulations produce empirical distributions and related statistics.

Simulation models are used in finance, insurance, medicine, oil and gas, accounting, and sales forecasting.

Key elements in a simulation model:

• Distributions: Assigned to random variables, with over 30 options in XLSTAT (e.g., triangular distribution for bounded values with a likely mode). Random draws occur each iteration.

• Scenario variables: Fixed quantities except during tornado analysis, where they vary within set bounds.

• Result variables: Outputs depending on random and scenario variables via formulas.

• Statistics: Track specific metrics (e.g., standard deviation) for result variables.

A valid model requires at least one distribution and one result variable, and can span a single sheet or an entire Excel workbook.

 

Dataset for Example

The attached simulation model deals with the payments of interest of a in fine loan. The interest are calculated during 5 years. At the end the net profit value at the initial time is calculated using the Excel function NPV and the interest rate of the first year and the payments of interest during the 5 years. The interest rate is support to be equally distributed between 3.5% and 5.5%. The capital of the in fine loan is 10000 Euro.

Starting with a static model using a mean interest rate of 4.5%. The net profit value is calculated to 1975 Euro in that case.

This model can be found in the "Model" sheet.

 

Setting up

• Select the first distribution variable in B5, the "interest rate" for 2008, to make it the active cell.

• Once XLSTAT is activated, select the  XLSTAT / Sim / Define a distribution or click on the corresponding button of the Sim toolbar (see below).

• Choose the Excel cell with the name “2008” as name. This will be integrated as a relative reference (A1 format) in the formula.

• Choose a uniform distribution with a = 0.035 and b = 0.055.

• Once you have clicked on OK, the corresponding function call to XLSTAT_SimDist is inserted into the active cell.

 

Creating distribution by copying

It is possible to enter the other four distributions using copy and paste of the cell that we have generated into the four cells at the right of this first cell. You have as well the possibility, as with any Excel formula, to select the cell B6 that you have just generated, go with the mouse over the lower left corner where the cursor is displayed as a black cross, press and hold the left mouse button and move the mouse up to the F6 cell. This way you have defined the 5 cells as well. The name of the distributions will be “2008, …, 2012”.

• Choose the result cell B7 that contains the formula = NPV(B4,B5,C5,D5,E5,F5) as active cell. Now the result variable will be defined.

• Select the  XLSTAT / Sim / Define a result or click on the corresponding button of the  Sim toolbar.

• The define result dialog box will appear. Then select the data on the Excel sheet. Choose the Excel cell with the name “NPV” as name in it.

• Once you have clicked on OK , the corresponding function call of XLSTAT_SimRes is inserted into the active cell.

• This can be found in the Excel sheet Model.

 

Running the simulation

• Select the XLSTAT / Sim / Simulation - Run click on the corresponding button of the Sim.

• The simulation - run dialog box will appear. Set the number of simulations to 1000.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The first result is a summary of the elements included in the model. Details on the distribution variables and on the result variable are displayed.

The following tables dsiplay descriptive statistics, histograms and quantiles for each distribution variable.

The following tables show details for the result variables (descriptive statistics, histograms and quantiles). Then, results of the sensitivity analysis are displayed. These results depend on the iterations of the simulations.

The Tornado analysis examines each input variable (random and scenario) individually by varying its value between set bounds and recording the effect on each result variable. For random variables, values vary around the median or default cell, within percentile or deviation limits; for scenario variables, within predefined bounds. Users can adjust the number of points analyzed.

Spider analysis extends this by showing the result variable’s value at each point, revealing whether the relationship is monotonic.

The first table shows the minimum and maximum changes for each distribution variable. Here, all interest rates impact results similarly, but spider analysis reveals the first year’s interest rate affects NPV less, as it is directly used in the calculation.