XLSTAT: Run a Simple Simulation Model in Excel

 

Background

Simulation models estimate statistics like mean, median, or confidence intervals for variables with uncertain values based on known or assumed distributions. When result variables depend on these distributed variables via formulas, they also have distributions. XLSTAT’s Monte Carlo simulations let you define these distributions and generate empirical distributions and statistics through repeated simulations.

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

 

Dataset for Example

The attached dataset contains a simple simulation model with two distributions and one result is created to explain the basics of simulation modeling.

Our simulation model is based on the sales and costs of a business. Profit 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)

The various model variables are created:

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

 

Setting Up

Define the Distribution Variables

• Open XLSTAT.

• Select cell B1 cell that contains the amount of sales.

• Click on the Monte-Carlo simulations menu and select the Define a distribution feature.

• The Define a distribution dialog box appears.

• Select the Variable Name  (sales) in cell A1. Choose a normal distribution with mu=120 and sigma=10. A formula calling the XLSTAT_Sim function is created in cell B1.

 

• Start again in the cell below to generate a normal distribution with mu=80 and sigma=20.

 

• Click on the OK button to launch the computations.

 

Define the Result Variable

Select the result cell showing 40 (from the formula =B1-B2), ensuring it depends on at least one distribution variable. Then, go to the Monte Carlo simulations menu and choose Define a result variable.

• The Define a result variable dialog box appears.

• Select cell A3 as Variable name. The corresponding function call to XLSTAT_SimRes is inserted in cell B3.

 

• Click on the OK button to launch the computations.

 

Execute a Simple Simulation Model

• Click on the Monte-Carlo simulations menu and select the Run feature.

• The Run dialog box appears.

• Set the number of simulations to 1000.

 

• Click on the OK button to launch the computations.

 

Interpreting the Results

The first result summarizes the simulation model, showing default cell values, variable distributions, and the formula used to compute the result variable.

 
Several statistical indicators such as mean, median, quartiles, variance, standard deviation and skewness coefficients of the distributions of the two random variables (sales and costs) are shown in the following table.

  
The following histogram shows the distribution of the Costs variable. A histogram for the variable sales is also shown. 

 

Sensitivity analysis

The same tables and graphs are displayed to examine the result variable after the simulation. This is followed by a sensitivity analysis based on the simulation results. The table below shows the results of this analysis for the outcome variable (Benefit).

The cost evolution accounts for nearly 80% of the benefit variation, with a negative correlation—higher costs lead to lower benefits, as shown in the graph below.