Summary Statistics in Excel

Sample Size (n): =COUNT(G:G)
Sample Mean: =AVERAGE(G:G)
Median: =MEDIAN(G:G)
Sample Standard Deviation (s): =STDEV(G:G)
Maximum: =MAX(G:G)
Mininum: =MIN(G:G)
Q(.75): =QUARTILE(G:G,3)
Q(.25): =QUARTILE(G:G,1)
Skewness: =SKEW(G:G)
Kurtosis: =KURT(G:G)

Note: These Excel functions ignore text within the data set.

The sample size, n, is the number of observations or data points from a single MC simulation. For this example, we obtained n = 5000 simulated observations. Because the Monte Carlo method is stochastic, if we repeat the simulation, we will end up calculating a different set of summary statistics. The larger the sample size, the smaller the difference will be between the repeated simulations. (See standard error below).

The sample mean and median statistics describe the central tendancy or "location" of the distribution. The arithmetic mean is simply the average value of the observations.

If you sort the results from lowest to highest, the median is the "middle" value or the 50th Percentile, meaning that 50% of the results from the simulation are less than the median. If there is an even number of data points, then the median is the average of the middle two points.

Extreme values can have a large impact on the mean, but the median only depends upon the middle point(s). This property makes the median useful for describing the center of skewed distributions such as the Lognormal distribution. If the distribution is symmetric (like the Normal distribution), then the mean and median will be identical.

The standard deviation and range describe the spread of the data or observations. The standard deviation is calculated using the STDEV function in Excel.

The range is also a helpful statistic, and it is simply the maximum value minus the minimum value. Extreme values have a large effect on the range, so another measure of spread is something called the Interquartile Range.

The Interquartile Range represents the central 50% of the data. If you sorted the data from lowest to highest, and divided the data points into 4 sets, you would have 4 Quartiles:

In Excel, the Interquartile Range is calculated as Q3-Q1 or:

The IQR is used in creating a box and whisker plot.

Skewness describes the asymmetry of the distribution relative to the mean. A positive skewness indicates that the distribution has a longer right-hand tail (skewed towards more positive values). A negative skewness indicates that the distribution is skewed to the left.

Kurtosis describes the peakedness or flatness of a distribution relative to the Normal distribution. Positive kurtosis indicates a more peaked distribution. Negative kurtosis indicates a flatter distribution.

The sample mean is just an estimate of the true population mean. How accurate is the estimate? You can see by repeating the simulation (using F9 in this Excel example) that the mean is not the same for each simulation.

Standard Error

If you repeated the Monte Carlo simulation and recorded the sample mean each time, the distribution of the sample mean would end up following a Normal distribution (based upon the Central Limit Theorem). The standard error is a good estimate of the standard deviation of this distribution (the distribution of multiple mean values), assuming that the sample is sufficiently large (n >= 30).

The standard error is calculated using the following formula:

In Excel: =STDEV(G:G)/SQRT(COUNT(G:G))

95% Confidence Interval

The standard error can be used to calculate confidence intervals for the true population mean. For a 95% 2-sided confidence interval, the Upper Confidence Limit (UCL) and Lower Confidence Limit (LCL) are calculated as:

To get a 90% or 99% confidence interval, you would change the value 1.96 to 1.645 or 2.575, respectively. The value 1.96 represents the 97.5 percentile of the standard normal distribution. (You may often see this number rounded to 2). To calculate a different percentile of the standard normal distribution, you can use the NORMSINV() function in Excel.

Commentary

Keep in mind that confidence intervals make no sense (except to statisticians), but they tend to make people feel good. The correct interpretation: "We can be 95% confident that the true mean of the population falls somewhere between the lower and upper limits." What population? The population we artificially created! Lest we forget, the results depend completely on the assumptions that we made in creating the model and choosing input distributions. "Garbage in ... Garbage out ..." So, I generally just stick to using the standard error as a measure of the uncertainty in the mean. Since I tend to use Monte Carlo simulation for prediction purposes, I often don't even worry about the mean. I am more concerned with the overall uncertainty (i.e. the spread).