Monte Carlo Simulation Excel Template
Originally published: 16/04/2018 13:40
Last version published: 23/04/2019 15:41
Publication number: ELQ-63581-3
View all versions & Certificate

Monte Carlo Simulation Excel Template

This model aims to predict probable outcomes by running numerous simulations.

Usually in deterministic models, events are predicted in a simple linear system, and it is assumed that the conditions recorded initially do not change. However, if conditions do not change results will not change. However, obviously things are more complex than this and conditions do change, and events are generally determined by a complex interrelation of various variables, some of which can be nearly impossible to estimate. Monte Carlo simulations can solve this problem by utilizing probability distributions for each input variable, and then by running numerous simulations to produce likely outcomes. So, this model allows the user to predict an outcome without conducting numerous costly experiments.

To perform a Monte Carlo simulation, the steps are:

--> Definition of the mathematical formula for the outcome

--> Identification of the probability distributions of the input variables and definition of their parameters

--> Running of the simulations

--> Analyzation and optimization

If using your results for a risk analysis or a business plan is the objective, then you can stop after analyzation and optimization, however if you'd like to also optimize the outcomes, then a sensitivity analysis is necessary. In this type of analysis, the 'importance' of each input is measured, and from there you may decide to act on the ones you see as the most influential. Generally, the correlation coefficient between each input and the output is utilized, however different techniques are able to be adopted.

This Best Practice includes
1 Monte Carlo Simulation Excel Template

Acquire business license for $5.00

Add to cart

Add to bookmarks



keyboard_arrow_downShow all


More Best Practices from Alberto Scappini

See all

Discussion feed for Monte Carlo Simulation Excel Template

The user community and author are here to help. Go ahead!

  • Mark Cooke
    Hi there,

    Is this a black box solution ie calculations are locked away or is this an open spreadsheet?

    Many thanks,
    arrow_drop_uparrow_drop_downReply reply
    • Alberto Scappini
      Hi Mark,

      you can modify everything: values, distribution formulas, and the output function.

      For example, input 1 and 2 have a uniform distribution of 3 values, you can change the values in the tables on the left to adapt them to your situation. But if you want to change the kind of distribution you'll have to modify formulas of the input columns. Input 4 has a triangular distribution, you can modify its values by modifying values in C4, C5, and C6 of the hidden sheet (Triangular Distr). But again, if you want to modify the kind of distribution you'll have to modify excel formulas in the column of input 4. In the sheet DISTRIBUTIONS you have some calculations that can help you in case you want to change distributions.

      arrow_drop_uparrow_drop_downReply reply

    4.5 / 5 (15 votes)

    please wait...