You can use the Monte Carlo Simulation to generate random variables with the help of a mathematical technique. You can use this technique to determine uncertainty and modeling the risk of a system. You use random inputs and variables according to the simple probability distribution, such as log-normal. This simulation helps generate the path and result of a model through simple numerical computations or simulation.

This method is reasonable when you have to analyze a complex system or parameters of an uncertain model. You can model risk in the system with this method. A Monte Carlo Simulation will only provide an estimate of the model’s uncertainty. You cannot consider it as a final analysis. However, with this method, you can generate an approximate of the system’s risk and uncertainty. The best part about this simulation is that you can use this technique widely. For instance, many experts use it in quantitative finance, artificial intelligence, statistics, computational biology, and physical sciences.

How Monte Carlo Simulation Works?

Monte Carlo Simulation does not generate a single outcome value, but it produces a series of possible outcomes. That is why this is the favorite and easy technique for analyzing the risk of a model—the model substitute a different range of possible outcomes. In short, it derives the probability distribution of a factor that is uncertain.

This simulation runs repeatedly and calculates different random values every time using the probability functions. To complete a simulation, it takes thousands of recalculations according to the model’s uncertainty.

You can use probability distribution to find different outcomes from different variables. For risk analysis, this is the most sensible and realistic method to use. Here are some of the common probability distributions this simulation involves:

Normal Distribution

This probability distribution is also called the bell curve. You can define the mean and a standard deviation to describe the mean variation. The values in the center and close to the mean are possibly the outcomes. This method is symmetrical, and you can find the mean weight of the people. Furthermore, you can also determine natural phenomena such as energy prices and growth rates.

Log-normal Distribution

These values are not symmetrical but skewed and involve normal distribution. This distribution does not have values below zero but includes limitless positive potential. The examples of this variable include stock prices, property values, and oil reserves.

Uniform Distribution

Every value may occur with equal chance. You need to define if the chances are minimum or maximum. The distribution is uniformly divided and contains results such as future sales and the manufacturing cost of a product that you manufacture.

Triangular Distribution

You can define the sales history of a unit according to the level of inventory and time. The result will be maximum, minimum, and most likely, in this distribution. 

PERT Distribution

You need to define maximum, minimum, and most likely value in this distribution. For instance, this distribution can define how much duration a task will take in the project management model.

Discrete Distribution

You can also find the likelihood or a specific value from the data that the user defines. It may define the verdict as 30% positive, 20 % negative, 40% mistrial, and 10% settlement.

What Is The Monte Carlo Simulation Used For

Monte Carlo Simulation can solve various problems in different fields of science and technology. The following section outlines some fields that use this simulation:

Industrial Research

Experts in industrial and operational research centers use this method to find reliability systems, queuing networks, job scheduling, and inventory processes. Many people from the machine and robot design and control departments rely on this technique to solve computational problems. This simulation also provides help with optimization issues, scheduling, optimal design, and other satisfiability problems.

Economics and Finance

Many economists and financial institutions use this simulation technique as an analyzing tool. They can use it to analyze risk and uncertainty in various components, such as prices and stocks. You can also estimate the time and quality of the product.

Computational Statistics

This simulation has changed the way we conduct data analysis and use the resulting information. To process big data, we no longer use traditional methods for statistical analysis and models. You can use the Monte Carlo Simulation to derive posterior distribution and various other quantities. In addition, you can find different complex values such as p-values.

How To Run Monte Carlo Simulation In Excel

You can use the below method to run Monte Carlo Simulation in excel on a normal distribution:

Input Variables

You need to include three variables in a normal distribution. Mean, probability, and standard deviation. Suppose we are taking a financial company’s variables involving three columns: Revenue, Fixed, and Variable Expense. If you minus Revenue from Variable Expense and then minus Fixed Expense, you will get the Profit as a result. You can then assume the distribution curves of the Variable and Revenue Expense.

2014-10-28_15-36-48

Simulation Number One

We will use the formula NORM.IVN(). In this formula, you will use probability as the RAND() of the distribution, Forecast revenue as the mean as C3, and Standard Deviation Revenue as C4

1000 Simulations

You can use various methods to perform 1000 simulations. You can copy and paste the formula on different cells from previous steps 1000 times. 

Summary Statistics

When you run the simulation, you can collect summary statistics. You can use the formula of COUNTIF() to find the unprofitable percentage of the simulation. 

2014-10-28_18-30-21

Conclusion

Monte Carlo Simulation has wide-ranging applications in multiple industries. It helps in solving problematic and uncertain values in a system. This simulation provides expected values and the probability of a result occurring.