Simulation demo in Excel

In this video, we'll demonstrate how to construct one or more simulation paths, calculate a value (e.g. median) for the simulation path, then run the simulation several time and collect the calculated value for each run. Finally, using the collected values, we conduct few summary statistics (e.g. Histogram, normality test, etc.) to better understand the statistical property of this value.

Video script

Scene 1:

Hello and welcome to the NumXL simulation tutorial. In this video we will demonstrate the steps to construct one simulation path using an ARIMA model then run the simulation several times and collect the target value or statistics. For our example we will use an ARIMA 511 model for monthly sales process, recent monthly sales figures, and estimate the median of monthly sales for the following year. We'll do the whole tutorial using only NumXL 1.63 and Microsoft Excel functionality.

Select the cell that heads our ARIMA model in column E, then locate and click on the simulation icon in the NumXL toolbar. 

Scene 2:

The simulation wizard pops up. Notice that the model field references the current active cell in your worksheet. Now, select the latest observations in your sample data. Change the horizon to 12, leave the number of simulation paths to 1, and the seed to 100. Select an empty cell in your worksheet where you wish the simulation path to be displayed. Click OK when you're done.

Scene 3:

The simulation path is generated in your workbook, note  you add one more simulation path by simply copying the formula to the adjacent columns. For now one simulation path is sufficient.

Next, we wish to calculate the median of the monthly sales for the next 12 months. To get meaningful statistics for the median value we need to run the simulation multiple times. Select an empty cell where you wish to display the median value of the different simulation runs. Locate the Run icon on the NumXL toolbar.

Scene 4:

The simulation run wizard pops up, note that the output field references the current active cell in your worksheet.

Next, in the target cell select the cell that has the median formula F30. For the calculation range select active worksheet. By doing so, the wizard will evaluate all of the cells on the current worksheet during each simulation run. You can further fine-tune it by selecting a cells range, but for our tutorial we will select current worksheet.

Now set the number of simulations to 100, then click OK.

Scene 5:

The simulation run wizard generated the median for each of the 100 independent simulation runs. Now we may wish to run any type of summary statistics to better understand this value, in other words the median.

Let's plot the histogram for those values. Select an empty cell in your worksheet, locate the summary statistics icon, and click on the histogram item.

Scene 6:

Select the simulation runs output as the input data and then click OK.

Scene 7:

And here's the histogram table along with a reference gaussian distribution. Now let's examine the normality assumption of the value. 

Scene 8:

Select an empty cell in your workbook, locate the statistical test icon, and select the normality test item.

Scene 9:

Again, select the simulation run output and then click OK.

Scene 10:

And here we go! The normality tests for the median value of the monthly sales in the next 12 months.

Scene 11:

That's it for now, thank you for watching!



Please sign in to leave a comment.

Was this article helpful?
0 out of 0 found this helpful