XLSTAT: Simulation Model with Scenario Variables in Excel

 

Background

Simulation models estimate statistics like mean, median, and confidence intervals for variables with uncertain values defined by known or assumed distributions. By simulating these inputs, the distributions of result variables—dependent on inputs via formulas—are also obtained.

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

Key elements of a simulation model include:

• Distributions: Random variables assigned from 30+ options (e.g., triangular distribution) with random draws at each simulation iteration.

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

• Result variables: Outputs depending on random and scenario variables via Excel formulas, with the goal of deriving their distributions.

• Statistics: Metrics tracked for result variables, such as standard deviation.

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

 

Dataset for Example

The attached dataset was also used for running a Simple Simulation Model . It is extended by a scenario variable and a statistic. 
Our simulation model is based on the sales and costs of a business. The benefit in this simple case is simply the difference between sales and costs. Based on historical data for costs and sales analyzed with the distribution fitting tool, we found that costs follow a normal distribution (mu=120, sigma=10). Sales also follow a normal distribution (mu=80, sigma=20) - See Fitting a distribution to a sample of data in Excel for more details.

We also assume that 80% of costs and 30% of sales depend on an exchange rate. The exchange rate is set to 1 at the beginning. During the tornado analysis, several scenarios are simulated with an exchange rate of 0.8 to 1.2. 
The sales and costs are defined as in the tutorial Running a simple simulation model with XLSTAT. The formula of the benefit is more complicated because we now take into account the exchange rate: = (0.3 * E2 * B2 + 0.7 B2) -( 0.8 * E2 B3+ 0.2 * B3).

 

The scenario variable and statistic are now added to the model created on the Model sheet. Select cell E2, which contains the value of the exchange rate, as the active cell. The scenario variable will be defined there.

 

Setting up

• Open XLSTAT and go to Monte Carlo Simulations > Define a scenario variable.

• In the dialog box, select the Excel cell with “exchange rate” as the variable name.

• Set the lower limit to 0.8 and the upper limit to 1.2 to define the range.

• Choose continuous data type, as the exchange rate can vary anywhere between 0.8 and 1.2.

 

• Click on OK , and the corresponding XLSTAT_SimSVar function call will be inserted in the active cell.

 

Defining a Statistic

• The Define a statistic dialog box appears.

• Select the Excel cell with the name “std. dev benefit” (D5).

• Check the option Descriptive statistics and select standard deviation (n-1) in the list.

 

• Click on OK , and the corresponding function call to XLSTAT_SimStat is inserted in the active cell (E5).

 

Running a Simulation

• The Monte Carlo simulation lets you run step-by-step to check your model’s behavior.

• To reset previous results, click XLSTAT > Monte Carlo Simulations > Reinitialize the simulation model.

  • The first iteration results appear for "Sales," "Costs," and "Benefit." The scenario variable stays fixed until tornado analysis. Standard deviation isn't calculated until at least two iterations are done.

• To continue stepwise, use XLSTAT > Monte Carlo Simulations > Do a simulation step and watch the standard deviation stabilize.

• To run the full simulation, select XLSTAT > Monte Carlo Simulations > Run, set the number of simulations to 1000 in the dialog box, and start.

 

• In the Outputs tab, enter the parameters of the Tornado and Spider Analysis. 

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The first results are the same as in the Simple Simulation Model.

The Tornado and Spider analysis are not based on the iterations of the simulation, but on a point-by-point analysis of all input variables (random variables with distributions and scenario variables).

In the Tornado analysis, for each outcome variable, each input random variable, and each scenario variable are examined one by one.

We allow their value to vary between two bounds and record the value of the result variable to learn how each random and scenario variable affects the result variables.

For a random variable, the values examined can be either around the median or around the standard value of the cell, with bounds defined by percentiles or deviation percentages. For a scenario variable, the analysis is performed between the two bounds specified when the variables were defined. The number of points is an option that can be changed by the user before running the simulation model.

In the Tornado analysis, the results for the scenario variable are displayed. Its effects are as important as those of the other two 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 show the iteration-by-iteration evolution of "Costs," "Sales," "Benefit," and "std. dev benefit." The statistic quickly converges to 22 and then oscillates around that value.