Abstract:

Spreadsheets are handy in teaching since they allow complex systems to be modelled and presented visually and interactively: we can see all the elements of the system, and immediately appreciate the complex interactions between them. This is so because a change in a component of the system triggers a sequence of changes in other elements, dependent on the first one. In addition, the design of the spreadsheet can be done in such a way that the relationships mentioned above between elements are evidenced, because of the use of different formats, tables and graphics.

In this work we model a specific system: the time we need to complete a project. A project is understood as a sequence of tasks to be completed, so that the possibility of starting each task may be conditioned by having completed other tasks in the project that are considered precedents of the same. We have chosen to model a project as something easily understandable for readers from different disciplines. It is a system with relatively few elements, but very interrelated. In addition, it is a widely used system, with analytical models based on very rigid assumptions (CPM, PERT). This will allow us to verify what happens when the hypotheses on which an analytical model is based are not met and will serve to understand the advantages of simulation-based methods.

We will start with the formal definition of our system and its elements and we will describe the well-known analytical methods usually used in this system to exploit it and obtain conclusions. We will see that these methods rest on a rigid set of hypotheses that, in practice, can rarely be verified, so the conclusions obtained may not be valid.
We will propose to use alternative methods, based on computer modeling of the system in a spreadsheet and on the use of Monte Carlo Simulation to exploit the model and obtain valid conclusions. We will confront the conclusions obtained in both ways (with the analytical methods and with the methods based on Monte Carlo simulation) in two different cases: a case in which the hypotheses are verified and another in which they are not verified. We will see that in the first case both solutions coincide and in the second case there are important differences. In this way we will conclude that simulation-based methods offer valid solutions even when analytical methods fail.

In this work we will see that the Monte Carlo simulation method is very useful to estimate, not only the expected value of one or more stochastic variables that depend on various inputs through a more or less complex system of relationships, but also allows us to estimate the multivariate probability distribution of these variables, as a reflection of the uncertainty existing in them.

This work contributes to demonstrate the pedagogical possibilities of the Monte Carlo simulation and the Spreadsheet, in contexts in which it is necessary to deal with uncertainty and in which it is impossible to obtain an analytical solution. In addition, the work serves to understand the mistake that is made when an analytical solution is offered that assumes hypotheses that are not fulfilled in practice.

We end up seeing some difficulties of Monte Carlo simulation in modeling stochastic systems as the stochastic nature of the solutions, the dependence on the proper selection of the probabilistic model for the inputs, the correct number of repetitions and the events with a low probability of occurrence.

Keywords:

Project, Critical Path Method (CPM), Program Evaluation and Review Techniques (PERT), Monte Carlo simulation.