Abstract:

The Monte Carlo simulation method is a tool that allows us to solve extremely complicated problems, in a tremendously simple way, if the right tool is available.

What “tremendously complicated” problems are we talking about?
We are talking about knowing the behavior of a variable (the response variable) that depends on other variables (the explanatory variables) with uncertainty.

What tool allows us to do this?
Microsoft Excel is an excellent tool for modeling, in a very intuitive way, the behavior of variables with complicated relationships between them.

What do we mean by saying in a “tremendously simple” way?
Basically, we have to build the relationships model between the variables for a specific case (a reasonable combination of values). Next, we model the uncertainty in the explanatory variables, by using Excel functions. The relationships between the variables define the way uncertainty is transmitted from the explanatory variables to the response variable.

How can this be done?
With just five lines of code, we can convert any spreadsheet which reflects a model with greater or lesser complexity, into a Monte Carlo simulation model.

What is Monte Carlo simulation?
The Monte Carlo simulation method is a procedure that consists of repeatedly generating random values based on the estimated distributions of the explanatory variables. These variables can be related among them in a complex way and also with the response variable. This allows us to build, in an empirical way, the distribution of the response variable, without having to develop explicitly the process. Excel is an ideal support to implement this method.

What is this for?
This way of implementing the Monte Carlo simulation method is especially useful in educational settings so that students can turn a problem, that from a statistical point of view is very complex, to a simple modeling problem. The rest is done by this method.
It is very gratifying to see the reaction of students when they understand the enormous possibilities that this tool provides them while giving them a sense of empowerment in problem-solving. To achieve this level of understanding, it is especially useful to apply it to gambling: How many times do you have to throw a balanced coin to get three sides in a row? What is the best strategy to win in a game with well-defined rules?
The novelty of this contribution is not only using the Monte Carlo simulation method, but also the fact that the student, starting from a model implemented on a spreadsheet, build a simulation tool when it is needed. This allows you to convert any spreadsheet already created into a simulation model.

We will also see that, with just a bit more effort, the tool can be generalized to work with several response variables (multivariate) and with probability distribution models that are not included in Excel.

Keywords:

Monte Carlo, Simulation, Excel, VBA.