Every serious business understands the importance of numerical analysis and makes decisions based on the most likely outcomes of a situation. Creating a mathematical model of systematical probabilities is a reliable way to simulate such processes. One of the most famous methods is the Monte Carlo simulation.
“The probable is what usually happens” - Aristotle
Monte Carlo simulation is a special type of probability simulation and is mainly used to determine the risk factors by observing the cluster of possible results. First developed to see the outcomes of a solitaire game, Monte Carlo takes its name from the famous casino in Monaco.
“The record of a month’s roulette playing at Monte Carlo can afford us material for discussing the foundations of knowledge.” - Karl Pearson
The simulation takes random values of the inputs within constraints and the results are recorded as more iterations are run. Then, you get a rather big pool of answers, created from all those random inputs.
In a problem with probabilistic inputs, running a Monte Carlo simulation can give you an idea what the result will look like; the more iterations you run, more accurate the result gets. Repeat tries might yield different patterns. This way, different characteristics of the data can be identified; such as risk factors or scheduling issues. Monte Carlo simulations are mostly used in logistics, finance and scheduling. It also has countless other applications, such as in meteorology, astronomy and particle physics.
Professionals often create these models in Microsoft Excel; because it’s extremely convenient, comes with tons of extra other features, and it’s a lot easier to create the logic using formulas — instead of coding. Using the existing skillsets of their employees, businesses still prefer running these complex calculations on Excel.
Now, let’s see Monte Carlo in action. Below is an investment model created in Microsoft Excel. We will be using this calculator to demonstrate the concept and show how you can move your complex models to web.
Here, the standard deviation, median and a few percentile aggregations of the results pool can also tell you how accurate the result is. Used for solving neutron diffusion problems in the past, the same concept of simulation can be applied to pretty much every field there is. Life is full of probabilities, and a good analyst factors in every possibility.
Excel’s RAND function is perfect for getting the “randomized” set of values. After you assign a range, you can easily apply this to all other tries. More complex applications will also require running these values in tons of other formulas. These features make Excel the perfect candidate for creating such calculators.
This is a great model for creating an estimate of retirement investment. However, this is an Excel spreadsheet and the incredible knowledge pool of this software come with some issues. First of all, you will not be able to distribute this calculator without a risk of having your work stolen. Or, even if you intend to use it internally, users won’t be able to work on the latest version. Distributing a document is never a good idea for futureproofing a process.
Monte Carlo models can get extremely complicated and may take days, if not weeks, to solve in some cases. Excel runs on desktop PCs and their configurations can only go so far, as to support such processing power. Running them on mobile devices would most probably be a bad idea altogether. Moving the data, along with the calculation logic; will allow you to enjoy the incredible processing and storage servers provide. Using SpreadsheetWEB, you can utilize the flexibility Microsoft Excel offers, while removing vulnerabilities and technical weaknesses from the equation. Below is our web application running 200 iterations with the given inputs. This application was optimized for all platforms, and can also be accessed from mobile.
Monte Carlo simulation method defines a set of possible results. Occurrence frequency of reach values is plotted on the chart below. Looking at the distribution, you can decide what the most likely value is. On top of that, you can also see other possibilities and specific risk areas.
After you enter the inputs, you get a number random solutions to the problem. You can see several possible scenarios at a glance.
Statistical analysis methods, such as Monte Carlo method, are among most reliable tools for decision making; smart businesses capitalize on their use. Creating such complex models in Excel is usually no challenge for most professionals from get-go.
However, having your model in Excel format won’t allow for any scalability, nor will it help for keeping your intellectual property safe and accessible. Converting statistical models created in Microsoft Excel, into web applications allow for larger scale automation and extensive security options.
Solving probabilistic models is already a huge challenge. You don’t need any more headaches caused by versions or employing coders, you can do all of this by yourself with SpreadsheetWEB! Add a little web design knowledge, and your web applications can become full-scale, stand-alone projects.
Repost from Medium
